Transact-SQL Syntax ConventionsCauses SQL Server to display information regarding the amount of disk activity generated by SQL Server Transact\-SQL statements.
Transact-SQL Syntax Conventions
SET STATISTICS IO { ON | OFF } When STATISTICS IO is ON, statistical information is displayed. When OFF, the information is not displayed.
After this option is set ON, all subsequent Transact\-SQL statements return the statistical information until the option is set to OFF.
The following table lists and describes the output items.
| Output item | Meaning |
|---|---|
| Table | Name of the table. |
| Scan count | Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output. Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = <value>.Scan count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. For example WHERE Clustered_Index_Key_Column = <value>.Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key. |
| logical reads | Number of pages read from the data cache. |
| physical reads | Number of pages read from disk. |
| read-ahead reads | Number of pages placed into the cache for the query. |
| lob logical reads | Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache. |
| lob physical reads | Number of text, ntext, image or large value type pages read from disk. |
| lob read-ahead reads | Number of text, ntext, image or large value type pages placed into the cache for the query. |
The setting of SET STATISTICS IO is set at execute or run time and not at parse time.
[!NOTE]
When Transact-SQL statements retrieve LOB columns, some LOB retrieval operations might require traversing the LOB tree multiple times. This may cause SET STATISTICS IO to report higher than expected logical reads.
To use SET STATISTICS IO, users must have the appropriate permissions to execute the Transact\-SQL statement. The SHOWPLAN permission is not required.
This example shows how many logical and physical reads are used by SQL Server as it processes the statements.
USE AdventureWorks2012;
GO
SET STATISTICS IO ON;
GO
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS IO OFF;
GO Here is the result set:
Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0. SET Statements (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)
SET STATISTICS TIME (Transact-SQL)