0

Some facts:

1. The outdated statistics cause generating an inaccurate estimated plan. The estimated execution plan is created by Query Optimizer which uses both statistics and the query processor tree.

2. The actual execution plan is created after executing the query. When the stats are stale, the Storage Engine generates its own plan which is different than the estimated one.

The question: Why to bother about outdated stats if SQL Server can create accurate actual execution plan (by Storage Engine) when it finds out that data has been changed? How can the difference between the estimated and actual plan influences the query performance?

|improve this question
  • At a minimum, the engine uses the estimated plan to calculate how much memor or /disk space (in tempdb) is going to be required. If this is off by a large margin then performance will suffer (either by requesting far too much ram (affecting other queries by starving them) or by requesting too little and having to wait for resources to become available to satisfy the query. But statistics also help with determining the "shape" of the data, even for non-indexed columns. (The DateAdded field is not indexed but high values are usually at the end and low values are at the start). – Jonathan Fite May 22 '18 at 12:24
1

It seems that the facts you presented are not so precise, let me say:

1. The estimated execution plan is created by Query Optimizer which uses statistics to generate the query processor tree. (query processor tree = execution plan)

2. The actual execution plan is the estimated execution plan, annotated with the real cost. This can be used to validade how wrong is the plan.

If your query optimizer don't have good stats, it will generate bad plans. Your query will run with that bad plan and only at the end (after the execution), if you compare with the actual execution plan, it only proves that Query Optimizer estimated it all wrong. (i.e.: estimates 1 seek, executed 239827492 seeks)

What is called execution plan is the optimized version of the original parsed query tree. The process involved on the optimization is complex and it is a NP-complete problem. The changes made on the original tree are called optimization and they are done based on the statistics, as well as other variables such as number of CPUs available, etc. The plan does not changes during the execution, unless you consider Adaptive query processing, added on SQL Server 2017; but then this starts another topic

I would recommend that you check the following links understant better how it works:

SQL Server Statistics Basics - https://www.red-gate.com/simple-talk/sql/performance/sql-server-statistics-basics/

Execution Plan Basics - https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/

|improve this answer
  • Are you trying to say that the view of the actual execution plan will be always the same as the estimated one and only differ in numbers? There is no confirmation of your words in the links you provided. Following piece of text doesn't contest my understanding: "Actual execution plans, unlike Estimated execution plans, do not represent the calculations of the optimizer. Instead this execution plan shows what happened when the query was executed. The two will often be identical but will sometimes differ, due to changes to the execution plan made by the storage engine." – lukaszwinski May 22 '18 at 15:11
  • And another thing needs to be clarified. Query tree is not an execution plan. Parser generates query tree. Query Optimizer takes query tree and generate the execution plan. – lukaszwinski May 23 '18 at 7:53
  • I tried to explain in a generic way, sorry. What is called execution plan is the optimized version of the original parsed query tree. The process involved on the optimization is complex and it is a NP-complete problem. The changes made on the original tree are called optimization and they are done based on the statistics, as well as other variables such as number of CPUs available, etc. The plan does not changes during the execution, unless you consider Adaptive query processing, added on SQL Server 2017; but then this starts another topic. – Evandro Muchinski May 23 '18 at 8:17
  • Thanks for precising. But I am still confused. This is the fragment of SQL book:" there are some cases which create differences in both the execution plans. E.G., If you have old statistics for your index or column, estimated execution plan selects the wrong path to execute the query, in this situation when query is actually going to execute, the storage engine changes the path of query execution, to gain performance, which will be reflected in the actual execution plan. This is how we see the difference between the execution plans." So it seems that plan is being changed during the execution. – lukaszwinski May 23 '18 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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