Düzenle

Aracılığıyla paylaş

SET STATISTICS IO (Transact-SQL)

  • Makale

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Causes SQL Server to display information about the amount of physical and logical IO activity generated by Transact-SQL statements. Physical IO is related to accessing data pages on disk and logical IO is related to accessing data pages in memory (data cache).

Transact-SQL syntax conventions

Syntax

SET STATISTICS IO { ON | OFF }

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Output

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 toward 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.
page server reads Number of pages read from page servers. **
read-ahead reads Number of pages placed into the cache by the query, via the read-ahead.
page server read-ahead reads Number of pages read from page servers and placed into the data cache by the query, via the read-ahead. **
lob logical reads Number of LOB* pages read from the data cache.
lob physical reads Number of LOB* pages read from disk.
lob page server reads Number of LOB* pages read from page servers. **
lob read-ahead reads Number of LOB* pages placed into the data cache by the query, via the read-ahead.
lob page server read-ahead reads Number of LOB* pages read from page servers and placed into the data cache by the query, via the read-ahead. **

* Large object binary (LOB) data types include text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or columnstore index pages.

** Non-zero for Azure SQL Database Hyperscale only.

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 Transact-SQL statements return the statistical information until the option is set to OFF.

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 can 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 isn't required.

Examples

This example shows how many logical and physical reads are used by SQL Server 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 message output:

Table 'ProductCostHistory'. Scan count 1, logical reads 76, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

Related content