SET STATISTICS IO (Transact-SQL)

Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

Transact-SQL Syntax Conventions

Syntax

SET STATISTICS IO { ON | OFF }

Remarks

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

Permissions

To use SET STATISTICS IO, users must have the appropriate permissions to execute the Transact-SQL statement. The SHOWPLAN permission is not required.

For more information, see Showplan Security.

Examples

This example shows how many logical and physical reads are used by SQL Server as it processes the statements.

USE AdventureWorks;
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.

See Also

Reference

SET (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)
SET STATISTICS TIME (Transact-SQL)

Other Resources

Analyzing a Query
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance