SQL STATISTICS IO is a set statement , when it is enabled it causes SQL Server to display information about the amount of disk activity generated by T- SQL statement.
SYTANX
SET STATISTICS IO { ON | OFF }
STATISTICS IO { ON | OFF }Â when STATISTICS IO is set to ON, the statistical information is displayed, and when it set to OFF, the information is not displayed.
Once STATISTICS IO is set to ON, the statistical information is returned until the option is set to OFF.
Following are the information that is returned by SQL Server when STATISTICS IO is SET to ON:
Table
Is the name of table.
Scan count
It is the 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 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 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>
.
logical reads
Is the number of pages read from the data cache.
physical reads
Is the number of pages read from disk.
read-ahead reads
Is the 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.
[You can refer microsoft documentation for more details: URL)
Lets see, the information about the amount of disk activity generated by any T- SQL statement.
Following is the T-SQL statement that we will run in database AdventureWorks2017 while set STATISTICS IO to ON.
SELECT * FROM [Production].[TransactionHistory] WHERE YEAR(TransactionDate) ='2014'
Lets run the above T-SQL with STATISTICS IO set to ON.
USE AdventureWorks2017; GO SET STATISTICS IO ON; GO SELECT * FROM [Production].[TransactionHistory] WHERE YEAR(TransactionDate) ='2014' GO SET STATISTICS IO OFF;
Once you click on Messages tab, you will see the information about the amount of disk activity generated by this T-SQL statement as shown below.
Enabling a STATISTICS IO, can be useful when you are performing query tuning as it lets you know how your query performed by displaying the cost of the query in terms of the actual number of physical reads from the disk and logical reads from memory by the T-SQL statement.
Also Read..
SQL Server SET IDENTITY_INSERT
SQL Server SET NOCOUNT ON | OFF