“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
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:
Post a Comment