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

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. Whileit’s coming to query optimization, many newbie or less experience person usedto talk about DTA, profiler etc. but they JUST IGNORE simple yet powerfulcommand “SET STATISTICS IO” whereasthey first need to look at the information provided by “SET STATISTICS IO” so that you can move ahead and dig more detailswith the information provided by “SETSTATISTICS IO”.
Let us see how it practically useful for us.
We are going to create one database, named “SQLHub” and onetable, named “Orders” under “SQLHub” database. “Orders” table would have approx1,00,000 rows dynamically inserted.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable 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
–inserting100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERTscript 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 onesimple “SELECT” T-SQL query with “SET STATICTICS IO”.
SET STATISTICS IO ON
–you might havedifferent refno in your table, so please check it first
SELECT * from orders where refno=23
SET STATISTICS IO OFF
–here is thestats 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 reads0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
After running this query, you would get its results inresult tab and some stats in “Message” tab, look at the screen capture.

You can see “Logical Read 409”. To find out all rows which hasrefno 23, SQL Server has to go for 409 pages internally. Now let us create anIndex on RefNo column and see stats.
–creatingclustered index, however it is not meeting the criteria I have
–given inarticle, but this is ok for example
CREATE CLUSTERED INDEXidx_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 thestats 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

Seescreen capture of this query:

 

You can see now “Logical Read 14” rather than “409”, you seehow much difference clustered index have made? And you don’t need to go toprofiler to see this difference, even no need for execution plan in this case. HoweverI am not against the use of profiler and execution plan. They both are veryintelligent and useful tools but just wanted to clear it up that; we can getimportant information from simple command like “SET STATISTICS IO” too.
Apart from “Logical Read”, it provides you many moreinformation in “Message” tab which you can see above like “Scan Count”, “PhysicalRead”, “Read-Ahead reads”, “log logical reads”, “lob physical reads” etc. youcan get description of all these from Microsoft’s MSDN.
If you want to refer all other articles related to index, clickhere.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

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

%d bloggers like this: