Here are two statements that can give you additional information about your queries and that you can use as an additional tuning technique. These can be a great complement to execution plans to get additional information about your queries’ optimization and execution. One common misunderstanding I see is developers trying to compare plan cost to plan performance. You should not assume a direct correlation between a query-estimated cost and its actual runtime performance. Cost is an internal unit used by the query optimizer and should not be used to compare plan performance; SET STATISTICS TIME and SET STATISTICS IO can be used instead. This section explains both statements.
You can use SET STATISTICS TIME to see the number of milliseconds required to parse, compile, and execute each statement. For example, run
SET STATISTICS TIME ON
and then run the following query:
To see the output, you will have to look at the Messages tab of the edit window, which will show an output similar to the following:
“Parse and compile” refers to the time SQL Server takes to optimize the SQL statement, as explained earlier. SET STATISTICS TIME will continue to be enabled for any subsequently executed queries. You can disable it like so:
SET STATISTICS TIME OFF
As mentioned previously, parse and compile information can also be seen on the execution plan, as in the following:
Obviously, if you only need the execution time of each query, you can see this information in the Management Studio Query Editor.
SET STATISTICS IO displays the amount of disk activity generated by a query. To enable it, run the following statement:
SET STATISTICS IO ON
Run this next statement to clean all the buffers from the buffer pool to make sure that no pages for this table are loaded in memory:
Then run the following query:
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 870
It will show an output similar to the following:
Here are the definitions of these items, which all use 8K pages:
- Logical reads Number of pages read from the buffer pool.
- Physical reads Number of pages read from disk.
- Read-ahead reads Read-ahead is a performance optimization mechanism that anticipates the needed data pages and reads them from disk. It can read up to 64 contiguous pages from one data file.
- Lob logical reads Number of large object (LOB) pages read from the buffer pool.
- Lob physical reads Number of large object (LOB) pages read from disk.
- Lob read-ahead reads Number of large object (LOB) pages read from disk using the read-ahead mechanism, as explained earlier.
Now, if you run the same query again, you will no longer get physical and read-ahead reads, and you will get an output similar to this:
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
“Scan count” is defined as the number of seeks or scans started after reaching the leaf level (that is, the bottom level of an index). The only case when scan count will return 0 is when you’re seeking for only one value on a unique index, like in the following example:
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 51119
If you try the following query, in which SalesOrderID is defined in a nonunique index and can return more than one record, you can see that scan count now returns 1:
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = 51119