Giving Hadoop a Microsoft-class UI with HDInsight and Datameer

In this episode of Data Exposed, Scott Welcomes Borko Novakovic, Program Manager in the SQL Server team. Borko joins Scott via Skype from his office in Belgrade, Serbia to introduce us to Query Store in SQL Server 2016, an awesome new technology which, among other things Borko explains, allows you to store the history of query plans in the system and capture the performance of each query over time. The Query Store also lets you look at and identify queries that have performed slower of time and make necessary adjustments. Borko shows us how easy it is to work with the Query Store and work with query plans.
If you troubleshoot query performance as part of your DBA responsibilities, then this show is a "much watch" for you!
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:
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.
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.
This conversation has been locked by the site admins. No new comments can be made.