Query Store in SQL Server 2016

The Discussion

• 

  Very nice feature. First feature worth of upgrade. Also it is really good sign that you are still caring about SSMS and desktop. SSMS is becoming nicest Microsoft product haha.

• 

  Wonderful feature. It will definitely be the reason for us to move to SQL 2016 as soon as possible. Question - could you have a mixture of the new 2014 cardinality estimator plans and the old ones done by from SQL 7 cardinality estimator in the same pool to pick from?

• 

  Thank you Mladen.

  Query Store collects all query execution plans as your workload is running. If you have feature constantly enabled and your SQL Server is running under different compatibility levels, all plans that are generated are captured and you can use Query Store to force any of them.

   

  As a matter of fact, that is the main idea of using Query Store in upgrade scenario. As you probably know, since 2014 all cardinality estimator changes and query optimizer enhancements are tied to the latest compatibility level. That means you can upgrade to SQL Server 2016 (and use new features) without exposing your workload to the latest CE/QO. Imagine the following scenario:

  1. Upgrade to SQL 2016 but keep your current COMPATIBILITY_LEVEL
  2. Run Query Store to establish performance baseline (effectively, performance with CE/QO before upgrade)
  3. Move to latest COMPATIBILITY_LEVEL (let SQL Server generate new plans if necessary)
  4. Use Query Store to detect potential regressions (usually new QO brings lot of improvements but in some cases a regression can occur)
  5. Use plan force option from Query Store to  force better plans generated with previous CE/QO

  I hope you'll find this workflow very useful during your upgrade to SQL Server 2016.

  Please note that plan forcing in some cases may fail silently. In that case query will be recompiled and continue and you'll be able to investigate why plan forcing failed by examining the field last_force_failure_reason_desc in sys.query_store_plan.

• 

  SQL Server 2016 Upgrade Advisor Preview and CTP 2.2 now available.

• 

  That is great. So now it will be much easier to compare the effect of the new Cardinality Estimator vs. the old.
  
  Do you know if there have been any concrete studies done on the benefits of SQL Server 2014 Cardinality Estimator compared to the legacy one? I mean, same workload and the only changes is the SET COMPATIBILITY_LEVEL = 120.? Itzik Ben-Gan mentions that there had been cases of companies who tried to upgrade and switch to the new compatibility mode without testing did get into trouble, because for some cases the old estimator did a better job.