30 June,2014 by Jack Vamvas
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
Top 5 SQL Server DMV for Index Analysis
SQL Server - Find all DMV and DMF
SQL Server sql_handle returns sql statement
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |