SET STATISTICS IO

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 XACT_ABORT

SQL Server SET DATEFORMAT

SQL Server SET IDENTITY_INSERT

SQL Server SET NOCOUNT ON | OFF

SQL Server SET ROWCOUNT

SQL SET LANGUAGE

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,116 total views,  1 views today