Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What factors should I check when I compare the performance of a query?

I saw many articles stating about IO cost, subtree cost (by using Execution plans), statistics such as CPU time, elapsed time (by using set statistics time on), time required for running query etc.

I have seen some queries which have high subtree cost but execute fast. So I want to know which factors I have to consider while checking performance of a query.

share|improve this question
You can check SQL-Sentry Plan Explorer which shows actual execution plans. –  ypercube Jun 28 '13 at 12:09
The is a very broad topic. Can you be more specific as to what you are trying to do and where you need help ? It is like saying - get me everything I need, without saying what you need ! –  Kin Jun 28 '13 at 13:01
add comment

closed as too broad by Mark Storey-Smith, Jon Seigel, Kin, dezso, RolandoMySQLDBA Jun 28 '13 at 15:16

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

Subtree cost is not a good general indicator. Even in actual plans the costs shown are based on estimates and inaccurate cardinality estimates can occur.

Even if the cardinality estimates are perfect however the costs shown are still just derived from costing formulas which may bear little relation to the true actual performance. Certain constructs such as scalar UDFs are generally woefully undercosted in the plan.

Logical reads can be a very useful indicator but not all logical reads are equal. A read of one row on a page that is already in cache is obviously much cheaper than reading all rows on a page that needs to be brought in from disc. One other issue to be aware of is that logical reads for work tables report rows read not pages so the units are not always consistent.

Elapsed time is a useful indicator but that requires some interpretation too. When looking at the elapsed time of two queries you might prefer a query with a slightly greater elapsed time that uses less resources (e.g. lower memory grant or serial plan rather than parallel)

share|improve this answer
Will probably add some more later but the question may be closed by that point. –  Martin Smith Jun 28 '13 at 13:15
Didn't know that worktables report rows as logical reads. Thanks Martin :-) –  John Alan Jun 28 '13 at 13:32
@JohnAlan - I didn't know it either until the answer to Why are logical reads for windowed aggregate functions so high? –  Martin Smith Jun 28 '13 at 13:33
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.