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

 IO Statistics Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 27, 2009 6:09 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 28, 2011 2:09 AM Points: 8, Visits: 50
 IO Statistics
Post #684946
 Posted Friday, March 27, 2009 6:12 AM
 SSCommitted 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 PracticesNumbers / Tally TablesSQL-4-Life
Post #684955
 Posted Friday, March 27, 2009 6:32 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:16 AM Points: 14,425, Visits: 26,727
 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 RooseveltThe Scary DBAAuthor of: SQL Server 2012 Query Performance TuningSQL Server 2008 Query Performance Tuning Distilled and SQL Server Execution PlansProduct Evangelist for Red Gate Software
Post #684982
 Posted Friday, March 27, 2009 7:29 AM
 Ten 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 Statisticshttp://technet.microsoft.com/en-gb/library/cc966500.aspx
Post #685032
 Posted Friday, March 27, 2009 8:07 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 28, 2011 2:09 AM Points: 8, Visits: 50
 I have 2 queriesSELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1SELECT * FROM Table1JOIN Table2ON Table1.Column1 = Table2.Column1AND Table1.Column2 = XXXBoth 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:2002nd Query---------Table1 - Scan Count:7000 Logical Reads:80000 Physical Reads:100There 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
 Posted Friday, March 27, 2009 8:38 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:16 AM Points: 14,425, Visits: 26,727
Post #685101
 Posted Friday, March 27, 2009 9:29 AM
 Forum 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
 Posted Friday, March 27, 2009 9:40 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:16 AM Points: 14,425, Visits: 26,727
 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 RooseveltThe Scary DBAAuthor of: SQL Server 2012 Query Performance TuningSQL Server 2008 Query Performance Tuning Distilled and SQL Server Execution PlansProduct Evangelist for Red Gate Software
Post #685161
 Posted Wednesday, April 01, 2009 10:19 PM
 SSChampion Group: General Forum Members Last Login: 2 days ago @ 12:24 PM Points: 11,055, Visits: 10,889
 Hi,In order to decide which is the 'better' query, we would need to know what defines 'best' for you:1. Logical IO2. CPU3. Concurrency4. Response time5. Overall server throughput4. Query plan coolness5. ...and so onI 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #688525
 Posted Thursday, April 02, 2009 2:05 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 6:14 AM Points: 41,005, Visits: 33,931
 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 IOssee - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387322 Gail ShawMicrosoft Certified Master: SQL Server 2008, MVPSQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #688594

 Permissions