I am finding difficulty in understanding the output of execution plan. Execution plan consist of terms like "Estimated sub tree cost", "Estimated I/O cost" etc. I can find the definition of those on net, but not detailed explanation. Can anyone help me to understand what is "Estimated sub tree cost" with an example ?

Also by looking at what factor in execution plan I can say that my query needs to be optimized ?

Please help me.

  1. Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole query plan.

  2. There are a lot of sign in a query plan that show it needs optimization, however, I've seen a lot of situations when even perfect plans caused troubles.
    At first you can look at following:

    • Estimated number of rows is far different from actual number of rows;
    • If you see thick lines (millions of records);
    • If you see spills in Tempdb;
    • If you see any red signs or exclamation points;
    • If you see Key Lookups
    • Many, many more...

All of these signs do not mean that plan is bad, but you have to look at them and make sure everything is OK.

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