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.

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

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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 Shah Jun 28 '13 at 13:01

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)

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