-1

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.

Community
  • 1
IT researcher
  • 3,168
  • 15
  • 59
  • 82

1 Answers1

2

  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.

Slava Murygin
  • 474
  • 3
  • 11