Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » IO Statistics
Add to briefcase
20 posts, Page 1 of 2
12»»

IO Statistics Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
goodlaugh
Posted Friday, March 27, 2009 6:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 2:09 AM
Points: 8, Visits: 50
IO Statistics
Post #684946
Christopher Stobbs
Posted Friday, March 27, 2009 6:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
what would you like to know about IO statistics?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #684955
Grant Fritchey
Posted Friday, March 27, 2009 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
Yes, they are very handy, aren't they?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #684982
maechismo_8514
Posted Friday, March 27, 2009 7:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
suhasini.m (3/27/2009)
IO Statistics


http://technet.microsoft.com/en-gb/library/cc966500.aspx
Post #685032
goodlaugh
Posted Friday, March 27, 2009 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 2:09 AM
Points: 8, Visits: 50
I have 2 queries

SELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1

SELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1
AND Table1.Column2 = XXX

Both these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.

The IO statistics of these two queries are as follows.

1st Query
---------
Table1 - Scan Count:0 Logical Reads:100000 Physical Reads:200


2nd Query
---------
Table1 - Scan Count:7000 Logical Reads:80000 Physical Reads:100

There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.

1. Which is the better query in terms of IO?
2. What does Scan Count exactly mean. And howz it related to IO.?
3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?
4. Is there a better way to compare the IO of queries?
5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?
Post #685069
Grant Fritchey
Posted Friday, March 27, 2009 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
suhasini.m (3/27/2009)
I have 2 queries

SELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1

SELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1
AND Table1.Column2 = XXX

Both these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.

The IO statistics of these two queries are as follows.

1st Query
---------
Table1 - Scan Count:0 Logical Reads:100000 Physical Reads:200


2nd Query
---------
Table1 - Scan Count:7000 Logical Reads:80000 Physical Reads:100

There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.

1. Which is the better query in terms of IO?
2. What does Scan Count exactly mean. And howz it related to IO.?
3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?
4. Is there a better way to compare the IO of queries?
5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?



1. The first query, by a long shot. Although it has a higher number of physical reads. But if both these queries are accessing the same data then it was probably in cache when you ran the second query.
2. Scans are the number of times that the engine had to scan an index or a table. It tells you how the data is being accessed and can help explain performance issues. Scans are not inherently bad, but high numbers of scans should give you pause.
3. There must be scans going on. Do you have user defined functions in the query? These can mask all kinds of performance problems including scans. Post the execution plans if you can.
4. Generally, that's the best way.
5. Not specific to the execution of a particular query, no. You can get these measures through a server-side trace. Otherwise you're looking at performance counters that measure what's happening within the server, probably not helpful for performance tuning a particular query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #685101
goodlaugh
Posted Friday, March 27, 2009 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 2:09 AM
Points: 8, Visits: 50
Thanks for the detailed reply.

I am running both the quries at the same time and also doing DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE before for both the queries.

If this is a table or index scan howz the execution cost of 2nd query is less than the first one. Ultimately a table or index scan is costlier than an index seek on this table as it has some millions of rows. I have checked the query plan and there was no index or table scan on this table. I will try and post the plan.

Is there a way to check the number of bytes of data that is being fetched by a sproc per execution if not a query?
Post #685151
Grant Fritchey
Posted Friday, March 27, 2009 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
suhasini.m (3/27/2009)
Thanks for the detailed reply.

I am running both the quries at the same time and also doing DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE before for both the queries.

If this is a table or index scan howz the execution cost of 2nd query is less than the first one. Ultimately a table or index scan is costlier than an index seek on this table as it has some millions of rows. I have checked the query plan and there was no index or table scan on this table. I will try and post the plan.

You just have to remember that those are estimated costs, not actual costs. They're useful as a general measure, but not as a real determining factor. Check out the situation in this blog post to see why.

Is there a way to check the number of bytes of data that is being fetched by a sproc per execution if not a query?

Try checking out sys.dm_exec_query_stats. It's an aggregate, but it'll tell you what you want to know.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #685161
Paul White
Posted Wednesday, April 01, 2009 10:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578
Hi,

In order to decide which is the 'better' query, we would need to know what defines 'best' for you:

1. Logical IO
2. CPU
3. Concurrency
4. Response time
5. Overall server throughput
4. Query plan coolness
5. ...and so on

I would imagine that the second query is prettier as an execution plan. The equality predicate on the partitioning column should mean that the query optimizer (QO) just searches the one partition instead of the whole table.

It may then need to scan (either a full partition scan, or a range scan) to satisfy the join condition. It sounds (from the scan count) as if a loop join is being used.

One would think that the QO might prefer a HASH or MERGE join here - try forcing it with a hint and check out the effects.

The first query does 20%-odd more logical IO, so in that narrow sense it is less efficient.

It's difficult to go into heaps of detail without seeing the actual execution plan - so if you can attach those that'd be great.

BTW a range scan is a very efficient way of grabbing a load of rows all at once (the scan range can be small or large) and will usually outperform the equivalent number of key/rid lookups.

Cheers,


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #688525
GilaMonster
Posted Thursday, April 02, 2009 2:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392
suhasini.m (3/27/2009)

2. What does Scan Count exactly mean. And howz it related to IO.?


The scan count is actually not that useful. It should indicate how many times an object is read, however it doesn't do that consistently. Best bet is to completely ignore that and look at the logical IOs

see - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387322

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #688594
« Prev Topic | Next Topic »

Add to briefcase
20 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.