{"id":7945,"date":"2019-09-08T14:24:09","date_gmt":"2019-09-08T14:24:09","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/microsoft-sql-server-query-store\/"},"modified":"2026-11-03T19:29:10","modified_gmt":"2026-11-03T19:29:10","slug":"microsoft-sql-server-query-store","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/microsoft-sql-server-query-store\/","title":{"rendered":"Microsoft SQL Server Query Store"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

Microsoft\u00a9 SQL Server\u00a9 Query Store, as the name suggests, is like a store that captures the database history of executed queries, query runtime execution statistics, and execution plans. Because the data is stored on a disk, you can retrieve the query store data anytime for troubleshooting purposes, and SQL Server restarts do not affect the data. Use Query Store, which was introduced
\nin SQL Server 2016 and is available in all later editions, to troubleshoot performance issues caused by query plan changes.<\/p>\n

\nYou might find baseline data analysis useful for performance troubleshooting, but until Query Store was introduced, that information was not natively available in SQL server. If Query Store is enabled for a database, it keeps information about executed queries along with execution plans and runtime statistics. It is enabled at the database level and can be enabled for all users’ databases and the MSDB system database. Query store-related information and metadata are stored in internal tables within the database itself. There is no need to manage a separate backup of the Query Store because a standard database backup has all the required information. To view Query Store data, you need to have the `View Database State` permission, and you need to have `DB_Owner<\/em>` rights to force and unforce plans. You can view this data through the management studio and through T-SQL.<\/p>\n

 Setting Up Query Store<\/strong><\/p>\n

Perform the following steps to enable Query Store for a database:<\/p>\n

1. Right-click Database -> Go to Properties<\/strong>.
\n2. Under Select a Page<\/strong>, choose Select Query Store<\/strong>.
\n3. In the General<\/strong> section, change Operation Mode (Requested)<\/strong> from Off to Read-Write<\/strong>.
\n4. Leave the other fields in the Operation Mode (Requested)<\/strong> drop-down box set to the pre-populated default values.
\n5. Click Ok<\/strong> in the database properties box to enable Query Store for the selected database.<\/p>\n

You can enable Query Store with T-SQL by using the following code:<\/p>\n

    ALTER DATABASE [DB_Name] SET QUERY_STORE = ON;<\/em><\/p>\n

 Various Configuration Options of Query Store<\/strong><\/p>\n

The following list contains descriptions of some Query Store options:<\/p>\n

–    Operation Mode (Requested)<\/strong> has three values: `Off`, `Read Only`<\/em>, and Read Write`<\/em>. When the Query Store has a mode set to `Read`, no new    execution plans or query runtime statistics are collected. This mode is for read-only operations related to Query Store. Changing the mode to `Read Write` enables Query Store to gather queries that are executed, execution plans that are used for those queries, and runtime statistics for those queries run in the selected database.<\/p>\n

–    Data Flush Interval (Minutes)<\/strong> allows you to set how frequently the
\n   collected execution plans and query runtime statistics are flushed from
\n   memory to disk. By default, it is set to 15 minutes.<\/p>\n

–    Statistics Collection Interval<\/strong> defines the aggregation interval of
\n   query runtime statistics that should be used inside the Query Store. By
\n   default, it is set to 60 minutes.<\/p>\n

–  Max Size (MB)<\/strong> is used to configure the maximum size of the Query Store.
\n   By default, it is set to 100 MB. The data in Query Store is stored in the
\n   database where the SQL Server Query Store is enabled. Depending on the size
\n   configured here, after Query Store reaches the maximum size, the operation
\n   mode switches to the `Read Only` mode automatically.<\/p>\n

–  Capture Mode<\/strong> helps you choose which type of query to capture in Query
\n   Store. The default option is `All`, which stores every executed query. When
\n   this option is set to `auto`, Query Store tries to triage which query capture,
\n   by priority, and tries to ignore infrequently executed as well as other ad hoc queries.<\/p>\n

–  Stale Query Threshold (Days)<\/strong> is used to define how long data stays in
\n   Query Store. The default value for this mode is 30 days.<\/p>\n

 Query Store Reports<\/strong><\/p>\n

Query Store includes the following reports:<\/p>\n

–    Regressed Queries<\/strong>: Pinpoint queries for which execution metrics have
\n   recently regressed or changed to worse.
\n–    Overall Resource Consumptions<\/strong>: Analyze the total resource consumption
\n   for the database for any of execution metrics.
\n–    Top Resource Consuming Queries<\/strong>: Queries which have biggest impact on
\n   database resource consumption.
\n–    Queries with Forced Plans<\/strong>: Built-in report, that shows all queries with
\n   forced execution plans.
\n–    Queries with High Variation<\/strong>: This report shows the queries with the most
\n   frequent parameterization problems.
\n–    Tracked Queries<\/strong>: Track the execution of the most important queries in real
\n   time.<\/p>\n

\"ms<\/p>\n

 Forcing Plans with Query Store<\/strong><\/p>\n

Because of a plan regression, a query, which was working fine yesterday, might take too long to execute, consume too many resources or time out today. By default, SQL Server keeps the latest execution plan only for queries. Any changes in the schema, statistics, or indexes could change the query execution plan that is used by the Query Optimizer. The plan can be also dropped due to pressure in the plan cache memory.<\/p>\n

Query Store stores query plans and statistical information aggregated over time within each monitored database. Unlike the plan cache, Query Store can retain multiple plans per query and maintain a history of query plan changes along with associated stats per plan. By choosing among various execution plans, you can go ahead and force any execution plan. Query Optimizer then uses this forced execution plan only for the upcoming query executions.
\n <\/p>\n

The Query Store -> Open Top Resource Consuming Queries<\/strong> report lists queries
\nthat are resource intensive. Let\u2019s select a query for examination:<\/p>\n

\"ms<\/p>\n

Hover your mouse over Plan<\/strong> to see related statistics. <\/p>\n

\"mssql<\/p>\n

Let’s compare different plans.<\/p>\n

Plan 216 details:<\/strong> <\/p>\n

\"mssql<\/p>\n

 Plan 195 details:<\/strong><\/p>\n

\"mssql<\/p>\n

 The average duration for Plan 216 is lower, so it can be used to force this plan in further executions. Click on Force Plan<\/strong> to see a confirmation screen with the message \u201cDo you want to force plan 216 for query 42?\u201d<\/p>\n

\"mssql<\/p>\n

Click Yes<\/strong>.  After the plan is forced, it is highlighted with a checkmark as shown in the following screenshot. In the future, this plan will be used by query optimizer for execution. <\/p>\n

\"mssql<\/p>\n

Best Practices with Query Store <\/strong><\/p>\n

 <\/p>\n