Understand SET STATISTICS IO as a first step for performance tuning in SQL Server

Friday, July 1, 2011 |


SET STATISTICS IO” provides us very crucial information about the query we run, like scan count, logical read, physical read etc. but this command is really very ignored. While it’s coming to query optimization, many newbie or less experience person used to talk about DTA, profiler etc. but they JUST IGNORE simple yet powerful command “SET STATISTICS IO” whereas they first need to look at the information provided by “SET STATISTICS IO” so that you can move ahead and dig more details with the information provided by “SET STATISTICS IO”.

Let us see how it practically useful for us.

We are going to create one database, named “SQLHub” and one table, named “Orders” under “SQLHub” database. “Orders” table would have approx 1,00,000 rows dynamically inserted.

--create one database which you can delete after running this example
create database SQLHub
GO

USE SQLHub
GO

--if orders table is already there. you can delete it than create new one with name "Orders"
IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN
      DROP TABLE orders
END
GO

--creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

--inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Now we are ready to see how query works. Let us create one simple “SELECT” T-SQL query with “SET STATICTICS IO”.

SET STATISTICS IO ON
--you might have different refno in your table, so please check it first
SELECT * from orders where refno=23
SET STATISTICS IO OFF

--here is the stats info came via "SET STATISTICS ON" in "Message" tab.
--(1982 row(s) affected)
--Table 'orders'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

After running this query, you would get its results in result tab and some stats in “Message” tab, look at the screen capture.






You can see “Logical Read 409”. To find out all rows which has refno 23, SQL Server has to go for 409 pages internally. Now let us create an Index on RefNo column and see stats.

--creating clustered index, however it is not meeting the criteria I have
--given in article, but this is ok for example
CREATE CLUSTERED INDEX idx_refno ON orders(refno)
GO

After creating “Clustered Index” on “Orders” table for “refno” column, let us see the same query.

SET STATISTICS IO ON
SELECT * from orders where refno=23
SET STATISTICS IO OFF
--here is the stats info came via "SET STATISTICS ON" in "Message" tab.
--(1982 row(s) affected)
--Table 'orders'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
GO

See screen capture of this query:

 
You can see now “Logical Read 14” rather than “409”, you see how much difference clustered index have made? And you don’t need to go to profiler to see this difference, even no need for execution plan in this case. However I am not against the use of profiler and execution plan. They both are very intelligent and useful tools but just wanted to clear it up that; we can get important information from simple command like “SET STATISTICS IO” too.

Apart from “Logical Read”, it provides you many more information in “Message” tab which you can see above like “Scan Count”, “Physical Read”, “Read-Ahead reads”, “log logical reads”, “lob physical reads” etc. you can get description of all these from Microsoft’s MSDN.

If you want to refer all other articles related to index, click here.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile



0 comments: