In SQL Server, you can use the SET STATISTICS IO statement to generate detailed information about the amount of disk activity generated by a T-SQL statement.

In graphical tools like SSMS and Azure Data Studio, you can view this information in the Messages tab.

Example

Heres’s a simple example to demonstrate.

SET STATISTICS IO ON;

SELECT 
    c.CityName, 
    s.StateProvinceName AS State, 
    c.LatestRecordedPopulation AS Population
FROM Application.Cities c
INNER JOIN Application.StateProvinces s
ON c.StateProvinceID = s.StateProvinceID
WHERE c.LatestRecordedPopulation > 2000000
ORDER BY c.LatestRecordedPopulation DESC;

Initially, you’ll probably see the query results as per usual:

To view the output of STATISTICS IO, click the Messages tab:

This example was done in Azure Data Studio, and it’s the same process when using SSMS. However the actual steps you need to use may depend on the tool you use to connect to SQL Server.

Below is a copy of the STATISTICS IO message from the above screenshot:

(6 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'StateProvinces'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Cities'. Scan count 1, logical reads 497, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Total execution time: 00:00:00.027

How to Turn it Off

Setting STATISTICS IO to ON affects all subsequent T-SQL statements until it is turned off.

To turn it off, simply run it again using OFF instead of ON:

SET STATISTICS IO OFF;