Troubleshoot high disk activity with STATISTICS IO

SQL Server BOL defines STATISTICS IO  as “Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.”

When performance troubleshooting and you’ve identified queries executing slower than normal , STATISTICS IO is a useful tool for quick summary of disk activity.

It’s particularly useful to identify missing indexes, statistics out of date , and queries which don’t exploit the indexes

Here is a sample output, from the query Backup history for a single SQL Server database . This output can assist to target certain tables – verify your analysis with the Execution Plan.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'backupmediafamily'. Scan count 1, logical reads 307, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'backupset'. Scan count 1, logical reads 1881, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To use STATISTICS IO :

USE myDB
GO
SET STATISTICS IO ON
GO
MY QUERY
GO
SET STATISTICS IO OFF

Read More

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement