Data Exposed

Query Store in SQL Server 2016

Play Query Store in SQL Server 2016

Description

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!

Tag:

SQL Server

Embed

Download

Right click to download this episode

More episodes in this series

Related episodes

rss

The Discussion

  • User profile image
    Tomas L

    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.

  • User profile image
    Mladen  Andrijasevic

    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?

  • User profile image
    bonova

    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.

  • User profile image
    rizm1
  • User profile image
    Mladen Andrijasevic

    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.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.