Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 261

Statistics IO
When this option is enabled, SQL Server shows the disk activity that is generated by queries. The queries
are executed, and the results are generated. However, at the end of the results is a message that includes
information about the disk access. The previous example query will include this message when this
option is turned on.

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238, physical reads 3, read-
ahead reads 1234, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Most of these are self-explanatory. However, a few need further explanation:

❑ Read-ahead reads: Number of pages placed into the cache.

❑ LOB logical reads: Number of large object types, such as text, image, or varchar(max) pages

read from the data cache.

❑ LOB physical reads: Number of large object types, such as text, image, or varchar(max) pages

read from the disk.

❑ LOB read-ahead: Number of large object types, such as text, image, or varchar(max) pages

placed into the cache.

Statistics Time
When this option is enabled, SQL Server shows the time used to parse, compile, and execute the queries.
The queries are executed and the results are generated. The previous example query will include this
message when this option is turned on:

SQL Server parse and compile time:
CPU time = 30 ms, elapsed time = 666 ms.

(121317 row(s) affected)

SQL Server Execution Times:
CPU time = 50 ms, elapsed time = 1855 ms.

Note that these last two options, STATISTICS TIME and STATISTICS IO, can both be enabled at the
same time. This allows you to get both I/O and time statistics. The other options can only be used by
themselves. They cannot be combined with one another.

Best Practice: Using SHOWPLAN
In many ways the results that you can get from the SHOWPLAN options are more
useful than what you get from the graphical plan. Operator steps from SHOWPLAN are
easier to read with better detail than when hovering over the same step in the graphical
plan. The ability to easily put the SHOWPLAN results into an Excel spreadsheet is
very handy. Putting the results into an Excel spreadsheet will give you the capability
to quickly summarize and sort on expensive operations in your query. This lets you
quickly analyze and focus in on a very specific portion of the query.