Troubleshoot high disk activity with STATISTICS IO

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

 

Read More

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on Troubleshoot high disk activity with STATISTICS IO


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer