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.
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.