set-statistics-io-transact-sql.md

title	SET STATISTICS IO (Transact-SQL)
description	SET STATISTICS IO (Transact-SQL)
author	WilliamDAssafMSFT
ms.author	wiassaf
ms.date	11/10/2016
ms.service	sql
ms.subservice	t-sql
ms.topic	reference
f1_keywords	SET_STATISTICS_IO_TSQL IO IO_TSQL SET STATISTICS IO
helpviewer_keywords	disk I/O statistics [SQL Server] I/O [SQL Server], disk activity information disks [SQL Server], statement statistics STATISTICS IO option statements [SQL Server], statistical information SET STATISTICS IO statement statistical information [SQL Server], disk activity
dev_langs	TSQL

SET STATISTICS IO (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Causes [!INCLUDEssNoVersion] to display information about the amount of physical and logical IO activity generated by [!INCLUDEtsql] statements. Physical IO is related to accessing data pages on disk and logical IO is related to accessing data pages in memory (data cache).

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

  
SET STATISTICS IO { ON | OFF }  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Remarks

When STATISTICS IO is ON, statistical information is displayed, and when OFF, the information isn't displayed.

After this option is set ON, all [!INCLUDEtsql] 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 or 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're seeking for only one value. For example, WHERE Primary_Key_Column = <value>. Scan count is 1 when you're searching for one value using a non-unique clustered index defined on a non-primary key column. This process is done to check for duplicate values for the key value that you're searching for. For example, WHERE Clustered_Index_Key_Column = <value>. Scan count is N when N is the number of different seeks or scans 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 pages read from the data cache. Includes text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or columnstore index pages.
lob physical reads	Number of pages read from disk. Includes text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or columnstore index pages.
lob read-ahead reads	Number of pages placed into the cache for the query. Includes text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or columnstore index pages.

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 [!INCLUDEtsql] statement. The SHOWPLAN permission isn't required.

Examples

This example shows how many logical and physical reads are used by [!INCLUDEssNoVersion] as it processes the statements.

USE AdventureWorks2022;  
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

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