Just a quick introduction to the Query Store feature. Query Store is a feature new to SQL Server 2016 that will capture data on query performance. Once it is enabled, we’ll have access to dashboards to track query plans as well as statistics on query execution.
I’m using SQL Server 2016 RTM Developer Edition.
Query Store is enabled at the database level. Going to the database properties you’ll see a ‘Query Store’ page. Setting ‘Operation Mode (requested)’ from ‘Off’ to ‘Read Write’ will enable this functionality. You’re also able to set the max space taken up, among other settings.
Once enabled, you can go back to SSMS and you’ll see a ‘Query Store’ tree under the database.
Under the ‘Query Store’ tree in SSMS, we’ll see four entries. These are for available dashboards to return data on our query performance and query plans.
– Regressed Queries: Returns queries that have become worse performing over time. We can select from certain criteria and view query plans to determine issues.
– Overall Resource Consumption: A summary of query stats for Duration, Execution Count, CPU Time and Logical Read over a set period of time.
– Top Resource Consuming Queries: We can select our criteria, such as duration, CPU Time, Physical Reads, etc. and see the worst performing queries by that criteria.
– Tracked Queries: Takes a query ID as a parameter – Returns query plans, the time the query was executed along with the average duration.