October 17, 2007 at 6:59 pm
Hi All,
I have been told to use
[font="Courier New"]DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO on
SET STATISTICS TIME on[/font]
to compare the performance of query and compare the Logical Read and Scan count for each query.
However, I found that every time I run the query. The Logical Read and Scan count changes. Is this the best way to compare the query? How to do read these figures?
I also notice that sometime non indexed table sometime has lower Logical Read and Scan Count thank indexed table. What is the reason for that?
Is there anything that can precisely tells us the performance of each query. Thanks heap
October 18, 2007 at 12:36 am
Catcha (10/17/2007)
Hi All,I have been told to use
[font="Courier New"]DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO on
SET STATISTICS TIME on[/font]
to compare the performance of query and compare the Logical Read and Scan count for each query.
However, I found that every time I run the query. The Logical Read and Scan count changes. Is this the best way to compare the query? How to do read these figures?
That shouldn't happen, unless the amount of data in the table is changing between runs. As for how you read them, fairly easy. The number of scans is how many times the table got read during the query (can be either seek or scan). The logical read is the total number of pages read for that table across the entire query.
Generally, but not always, lower reads = lower time
I also notice that sometime non indexed table sometime has lower Logical Read and Scan Count thank indexed table. What is the reason for that?
Amount of data? An unindexed table of 100 rows vs an indexed table of 100 million? Can also be the number of scane. Also, just because a table has an index on, doesn't mean that the index is being used in the query. To confirm that you need to look at the execution plan
Is there anything that can precisely tells us the performance of each query. Thanks heap
Define performance? If you're concerned with the time that the query takes, use statistics time. If you're concerned wiht the amount of IO, use statistics read. If you're concerned with the cost (according to the optimiser) check the execution plan
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
October 18, 2007 at 1:06 am
Define performance? If you're concerned with the time that the query takes, use statistics time. If you're concerned wiht the amount of IO, use statistics read. If you're concerned with the cost (according to the optimiser) check the execution plan
How to analyze from execution plan? What is the thing to look for when making a query faster? What kind of join (Hash,Merge, BookMark lookup) to avoid in a query?
I read somewhere the lower the Logical Read in IO mean the faster the query. Does this always holds?
October 18, 2007 at 1:22 am
Analysing exec plans is a complex topic. I've started a series on reading exec plans on my blog, but is still early days.
As a high level overview, what you'll be wanting to do s look at the exec plan, find the high percentage cost operations and see what you can do to reduce those costs. May require adding or changing indexes, may require re-writing the query.
High cost table or index scans are probably the easiest to fix. I wouldn't go fiddling with the join types unless you really know what you're doing and have exhausted all other options.
Bookmark lookups are generally something you want to avoid. (just note you'll never see a bookmark lookup in SQL 2005. It appears as a clustered indx seek in RTM and SP! and as a key lookup in SP2) Often that is done by widening indexes, where possible, or removing unnecessary columns from the query.
The lower IOs = faster query generally holds true, but I have seen exceptions.
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
October 18, 2007 at 5:45 pm
As a high level overview, what you'll be wanting to do s look at the exec plan, find the high percentage cost operations and see what you can do to reduce those costs. May require adding or changing indexes, may require re-writing the query.
I came across a case where I manage to reduce the cost by introducing indexes from 57% to 37%. However, I can't really see any performance increase? Is there anything else we need to look into ?
Bookmark lookups are generally something you want to avoid. (just note you'll never see a bookmark lookup in SQL 2005. It appears as a clustered indx seek in RTM and SP! and as a key lookup in SP2) Often that is done by widening indexes, where possible, or removing unnecessary columns from the query.
What triggers Bookmark lookup and how to avoid it?
Thanks for you prompt response. These question has bugged me for awhile.
October 19, 2007 at 12:48 am
It depends how expensive the query is before what kind of improvement you see. Generally I only tune queries that have high duration, reads or CPU. If the query is running <5 ms, then a 20% improvement in one operation isn't going to show. If the query runs 5 min, them you're more likely to see results.
The cost is useful for comparisons, but obviously the users don't care about query costs. They just want the query to run fast.
Bookmark looksups occur when SQL uses an index to find the rows required, but the index doesn't contain all the columns necessary. To get the rest of the columns, SQL does a lookup to another index (usually the cluster).
Generally avoided by using covering indexes where appropriate. Sometimes you just can't avoid them (especially if your queries read SELECT * FROM ...)
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
October 19, 2007 at 12:56 am
rush thru this link...
http://www.sql-server-performance.com/tips/query_analyzer_p1.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply