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 textntextimagevarchar(max)nvarchar(max)varbinary(max), or columnstore index pages.

lob physical reads

Number of pages read from disk. Includes textntextimagevarchar(max)nvarchar(max)varbinary(max), or columnstore index pages.

lob read-ahead reads

Number of pages placed into the cache for the query. Includes textntextimagevarchar(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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 106 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.