Q1: I see that the tables are sys.query_store_query_text, and sys.query_store_query, and not sys.dm_*, which leads to question that the query store is persistent between instance restarts, unlike the DMVs?
A1: Very good question. Absolutely correct. The Query Store tables are persisted to disk and you do not lose any information across restarts. In fact, you can even disable Query Store and then re-enable it and all of the previously saved data is still there unless you purposefully purged the data.
Q2: Hi, would it be possible to get copies of Robert’s querystore scripts? Thanks
A2: But of course. You can download my slide deck (in pdf form) and demo code from the page for my session on the event site. As an added bonus, you can now download the recording from this page as well.
Q3: During the Query store demo, you turned off a trace flag for CE. What was it for?
A3: Trace flag 9481 is used to disable the newest version of the cardinality estimator (estimates resulting rows from joins, etc) which was released with SQL Server 2014. Enabling the flag causes the engine to revert to using the old cardinality estimator that was in SQL Server 2012 and earlier versions. This is not relevant to Query Store other than I was using it to ensure that I could generate vastly different performance for the same query in the demo.
Q4: If the query has 2 plans why doesn’t SQL Server pick the best one automatically?
A4: Another excellent question. A common misconception is that the goal of the Query Optimizer is to pick the best plan. In reality, its goal is to pick a “good-enough” plan in a quick time. Often it will stop considering alternate plans if it finds one that it thinks it is good enough. This is why simplifying your queries as much as possible (not using functions when you don’t need to, not nesting views or functions, etc) often leads to better plans, because it has less possible plans to consider and has a much better chance of hitting a good one within the time it has allotted.
That brings up another good point. The Optimizer won’t search forever. If it hasn’t found a good plan by the time it reaches its time limit, it will just go with whichever one was least bad. So help the Optimizer help you.
Q5: Are these features Enterprise Edition only?
A5: I’m so glad you asked. Query Store and the Plan Compare Tool are available in all editions of SQL Server 2016. The Plan Compare Tool is a feature of SSMS, not the engine, so not only is it Edition-independent, it can be used with older versions of SQL Server.
Read more about the features supported by the editions of SQL Server 2016.
Q6: I wish somebody would report all the SSMS crashes to Microsoft. It happens all too often.
A6: I learned after my presentation that the particular crash I encountered twice during my demos is already fixed in the next release of SSMS. If you experience other crashes, you can report them on the Microsoft Connect website. Issues reported on that site will be reviewed by members of the SQL product team.
Thanks again to those who attended and especially those who asked questions. I would love to give you public acknowledgement of your great questions, but PASS wisely does not allow presenters to know the identities of the attendees. You know who you are! Kudos to PASS for protecting your identity.