Performance Tuning – STATISTICS IO September 3rd, 2010

Vinod Kumar

Today I was sitting inside a training that my friend Pinal Dave was doing. Spent just about over an hour with the attendees, what came to my notice is some of the simple text statistics techniques that have been with SQL Server for more than a decade are still so relevant and interesting. One such method of measuring the SELECT statement is using the SET STATISTICS ON. It still works with SQL Server 2008 R2.

Getting back to the basics … The command SET STATISTICS IO ON forces SQL Server to report actual I/O activity on executed transactions. It cannot be paired with SET NOEXEC ON option, because it only makes sense to monitor I/O activity on commands that actually execute. Once the option is enabled every query produces additional output that contains I/O statistics. In order to disable the option, execute SET STATISTICS IO OFF.

An typical output would have details like:

Table ‘Employees’. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0

  • The scan count tells us the number of scans performed.
  • Logical reads show the number of pages read from the cache.
  • Physical reads show the number of pages read from the disk.
  • Read-ahead reads indicate the number of pages placed in the cache in anticipation
    of future reads.

These are vital information to tune your SQL Server workloads. Use them more when you have narrowed down your query for tuning.

Tags: , , , , , , , ,

This entry was posted on Friday, September 3rd, 2010 at 17:12 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Performance Tuning – STATISTICS IO”

  1. Rajesh says:

    This is very useful things for those who are new in database tunning.

  2. Phani says:

    Thanks Vinod for sharing this basic information. Many times, it so happens that, after getting into usage of advanced tools for tuning, we tend to miss using the basic features like looking at Statistics IO etc., This would definitely help me as a ready reference.

Leave a Reply