How to Read Statistics IO in SQL Server 2005

The Microsoft SQL Server Query Optimization Team has a great post today about how to read the output of Statistics Profile in SQL Server Management Studio (SSMS). 

Since imitation is the sincerest form of flattery, I thought I would do a similiar (but much shorter) post on how to read the output of Statistics IO in SSMS. By running the statement SET STATISTICS IO ON in a query window, you will get some very useful IO statistics for both logical and physical IO, which can be extremely helpful for diagnosing and tuning a problem query.

If you run the following statements in a query window in SSMS, you should see the output below on the Messages tab.

USE AdventureWorks

SET STATISTICS IO ON

SELECT * FROM Purchasing.PurchaseOrderDetail

Here is the output: Table ‘PurchaseOrderDetail’. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scan count is the number of scans performed against the table, which really means how many times the table was accessed, not the number of full table scans (which would be pretty scary). Logical reads are the number of pages read from the data cache (physical memory), while physical reads are the number of pages read from disk. Obviously, logical reads are much less costly than physical reads. Read-ahead reads are the number of pages placed into the data cache for the query. The lob read statistics (which are new for SQL Server 2005) are the number of pages for large data types (such as ntext, nvarchar(max), etc.)

When I’m tuning, I always run my queries in SSMS with SET STATISTICS IO turned on. As you make query or index changes, these statistics are a great tool for determining (as opposed to simply guessing) whether the change was beneficial or not. For example, if your query is doing a table or index scan, you will see high numbers for logical reads. Making the query more selective by adding a WHERE clause or adding a useful index may allow the optimizer to do an index seek, which will dramatically reduce the logical read numbers.

Technorati Tag: SQL Server

About these ads
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s