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