SET STATISTICS IO ON; SET STATISTICS TIME ON – performance tuning

SET STATISTICS IO ON and SET STATISTICS TIME ON are beautiful ways which can be used while performance tuning individual queries.

SET STATISTICS IO ON
Enabling STATISTICS IO is very informative as it displays Scan count, logical reads, physical reads, read-ahead reads, lob logical reads, lob physical reads, and lob read-ahead reads.

SET STATISTICS TIME ON
Enabling STATISTICS TIME will display the time taken to complete the execution Time.

Combination of enabling STATISTICS and Query execution plan will help to analyze the performance and fine-tune the query!

Example:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

select * from AdventureWorks2012.sales.SalesOrderDetail

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

---- OUTPUT 
--SQL Server parse and compile time: 
--   CPU time = 0 ms, elapsed time = 1 ms.

--(121317 row(s) affected)
--Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- SQL Server Execution Times:
--   CPU time = 171 ms,  elapsed time = 1636 ms.

-- SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.

-Vijred

Advertisements
This entry was posted in Performance, Script, SQL, SQL Query and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s