Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IO Statistics


IO Statistics

Author
Message
goodlaugh
goodlaugh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 50
IO Statistics
Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1692 Visits: 2232
what would you like to know about IO statistics?

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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20215 Visits: 32374
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 2228
suhasini.m (3/27/2009)
IO Statistics


http://technet.microsoft.com/en-gb/library/cc966500.aspx
goodlaugh
goodlaugh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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?
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20215 Visits: 32374
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
goodlaugh
goodlaugh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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?
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20215 Visits: 32374
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11396 Visits: 11354
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56673 Visits: 44707
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search