Advertisements

Archive

Posts Tagged ‘Query Store’

Enable Query Store on a Database – SQL Server 2016

June 2, 2015 4 comments

… continuing from my [previous post] on Query Store.

Here we will see how can we enable it on a Database:

–> Right click on the Database you want to enable Query Store and select Properties. Now select the new feature Query Store at the bottom left side as shown below:

SQL Server 2016 Query Store 01

Set the Enable option to True, and click OK.
 

–> Alternative: You can also enable the Query Store by this simple ALTER DATABASE Statement:

USE [TestManDB]
GO

ALTER DATABASE [TestManDB] SET QUERY_STORE = ON
GO

–> After enabling the Query Store you can check the Database, expand it in Object Explorer, you will see a new folder with the same name “Query Store”, on expanding it you will see 4 reports, as shown in below snapshot:
SQL Server 2016 Query Store 02
 

–> You can check more about Query Store on MSDN BoL [here] with more coverage on what information/stats it captures and how you can Query them.


Advertisements

Query Store – new feature in SQL Server 2016

May 30, 2015 1 comment

In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Query Store is on of those feature that will add a lot value in troubleshooting and getting insights on Query & overall performance of a database.
 

–> Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. in a persistent store inside a database. You can think of it as a aircraft’s black box, for your database.

It can also enforce policies to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as forcing plans.

It is a database-scoped persistent store of query workload history. Query store is primarily targeting administrative scenarios for performance troubleshooting and identifying regressed workloads.

Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics. This collection process works across restarts or upgrades of the server and across recompilation of indexes
Query Store 01

It provides many configurable options for customization and it integrates with existing Query Execution Statistics, Plan forcing, and Manageability tools.
 

–> Query Store lowers the bar dramatically for performance troubleshooting and allows you to perform several kinds of scenarios:
– Conduct system-wide or database-level analysis and troubleshooting
– Access the full history of query execution
– Quickly pinpoint the most expensive queries
– Get all queries whose performance regressed over time
– Easily force a better plan from history
– Safely conduct server restarts or upgrades
– Identify issues with upgrades
 

–> Typical performance troubleshooting workflow:
Query Store 02
 

–> All data that query store stores will be available through the following Views:
– sys.query_store_query_text
– sys.query_store_query
– sys.query_store_plan
– sys.query_context_settings
– sys.query_store_runtime_stats_interval
– sys.query_store_runtime_stats

By combining these views in various queries, you will gain necessary insights about user workload for a period of time when Query Store was active. Query Store also has a rich UI component that uses several of these same DMVs as shown below:

SQL Server 2016 - Query Store
 

I’ve collated all these details from SQL Server white papers and other sources from Microsoft.
 

Check my [next post] on how to enable the Query store on a database from SSMS.