Example of how to capture SQL Server query IO statistics from a .Net based client.

Log-SQL-IO-Stats.ps1

Set-StrictMode -Version Latest
$ErrorActionPreference = 'stop'

$configuration = 'Server=.\SQLEXPRESS;Database=master;Trusted_Connection=True;'
$connection = New-Object System.Data.SqlClient.SqlConnection $configuration
$connection.Open()

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
    param($sender, $event)

    if ($event.Message -match "^Table '(?<table>.+)'.*, logical reads (?<logical>\d+).*, physical reads (?<physical>\d+),.*$")
    {
        Write-Host ('Table: {0}, Logical: {1}, Physical: {2}' -f $Matches.table,$Matches.logical,$Matches.physical)
    }
}
$connection.add_InfoMessage($handler)

$command = $connection.CreateCommand()

$command.CommandText = 'SET STATISTICS IO ON'
[void]$command.ExecuteNonQuery()

$command.CommandText = "select * from sys.databases;"
$reader = $command.ExecuteReader()
while ($reader.Read())
{
    #Write-Host $reader.GetValue(0)
}
[void]$reader.NextResult()
$reader.Close();

$connection.Close()

In SQL Server Management Studio (SSMS) you can enable the reporting of I/O statistics after running a query. This allows you to see some important metrics such as the number of logical and physical (page) reads that the query required. These can tell you a fair bit about how optimal you think a query is and about how much the data is being cached. For example, if you're expecting only a handful of reads as the query should be highly selective, and it's not, the query plan may no longer be what you think it is.

When used in conjunction with correlation IDs, see Causality – Relating Distributed Diagnostic Contexts and Monitoring: Turning Noise into Signal, you will have metrics logged over time at a business transaction level that can be aggregated and give you a trend to show how the performance is changing over time. For a couple of real-life scenarios where having these kinds of statistics would have been useful see The Cost of Not Designing the Database Schema and The Cost of Defensive Programming.