Query Store: A Powerful Tool

Database administrators and developers may control and improve the efficiency of SQL queries using the Query Store feature in Microsoft SQL Server. It was first made available in SQL Server 2016 and has since been improved. The Query Store has many features to enhance query performance.

Table of Contents

Introduction

A persistent problem in the changing world of database administration is guaranteeing optimal performance. One of the most popular relational database management systems, SQL Server, has a potent mechanism called the Query Store to deal with this issue. We will go further into the complexities of Query Store in this post, looking at its background, benefits, drawbacks, requirements, manual activation, installation problems, and important catalog views.

History of Query Store

Understanding Query Store’s history is crucial to understanding its importance. Database speed optimization underwent a paradigm leap with the introduction of Query Store in SQL Server 2016. It was created to make life easier for database administrators by streamlining the process of locating and fixing performance-related problems.

You may also like : DBCC CHECKTABLE: An In-Depth Analysis

Advantages of Query Store

The advantages of using Query Store in SQL Server are given below:

Monitoring and Analysis of Performance

It offers a central location for data on query performance, making it simpler to keep track of and evaluate how SQL queries perform over time.

Query optimization

By helping to discover queries that perform badly, database administrators and developers may concentrate their optimization efforts on the most important areas.

Historical Information

It keeps track of historical information, allowing users to monitor changes in query execution speed and efficiency. For root cause investigation and troubleshooting, this is quite helpful.

Query Regressions Detection

It can automatically detect query performance regressions, assisting teams in identifying problems before they have an impact on production systems.

Execution Plan Management

It keeps track of all previous and present execution plans, which is useful for comparing and analyzing plan modifications and their effects on performance.

Plan Forcing

When the query optimizer produces less-than-ideal plans, the ability to impose a certain execution strategy for a query may be useful.

Granularity Control

Administrators can adjust the policies for data gathering and retention to limit the quantity of stored historical data while balancing the need for storage.

Automation

It enables policy-based administration, enabling automatic responses in response to circumstances or query performance limits.

Simple Integration with SSMS

SSMS’s intuitive user interface makes it straightforward to retrieve data from the Query Store and generate performance reports.

You may also like : Extended Events in SQL Server

Disadvantages of Query Store

The disadvantages of using Query Store in SQL Server are given below:

Storage overhead

This needs room to store the execution plans and previous query data. In situations when there are a lot of transactions, this could lead to greater storage expenses.

Performance effect

Enabling this feature could cause a slight performance penalty, although, for the majority of workloads, this effect is frequently ignored.

Maintenance Cost

Regular monitoring and maintenance may be required due to the file’s potential size expansion over time.

Compatibility

Since Query Store is only compatible with Microsoft SQL Server, it could not be present in other database management systems. If you need to move your database to a new platform, this can be a hindrance.

Learning Curve

It may take some time for users to become proficient with the this and understand the data it delivers, especially for those who are new to SQL Server.

Plan Forcing Risks

Risks associated with plan forcing: While plan forcing can be advantageous, it should be used with caution since it might result in subpar performance if plans are forced without a full grasp of the underlying query and data.

Pre-requisites

Prior to utilising it, confirm that the following requirements are met:

The administrator of the SQL Server instance must be you.
The database ought to be compatible with 2016 or later.

Turn on the SQL server’s “Query Store Manually” feature.

It is possible to manually enable this in SQL Server by using Transact-SQL (T-SQL) commands or SQL Server Management Studio (SSMS).

Using SSMS

Launch SQL Server Management Studio to establish a connection to your SQL Server instance.

Right-click the database in the Object Explorer you want to enable it for and select “Properties.”

In the Database Properties window, locate the “Query Store” page.

You should switch the “Operation Mode” to “Read Write” or “Read Only” based on your requirements. “Read Write” enables you to save query statistics and execution plans, whereas “Read Only” just permits you to see the data.

Set the “Data Flush Interval (Minutes)” setting to specify how frequently data from it should be flushed to disc. The norm is 60 minutes.

Using the “OK” button will let you save your changes.

Query Store is currently enabled in the selected database.

Using T-SQL Commands

T-SQL commands can also be used to activate the it. As follows:

-- Connect to your SQL Server instance

-- Enable Query Store for a specific database in READ WRITE mode
ALTER DATABASE TestDB SET QUERY_STORE = ON;
-- Enable Query Store for a specific database in READ ONLY mode
ALTER DATABASE TestDB
SET QUERY_STORE (OPERATION_MODE = READ_ONLY);

-- Optionally, you can specify the data flush interval (in minutes)
ALTER DATABASE TestDB SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 600 -- Set to your desired interval

Replace TestDB with the name of the database you want to enable it for.

Enable Query Store Using T-SQL

Common Problems during QS Configuration:

Database Compatibility Level

Check that the database’s compatibility level is set to a version that supports Query Store by checking the database’s compatibility level. Since Query Store was first made available in SQL Server 2016, a database’s compatibility level must be 130 or above.

Insufficient Permissions

If your SQL Server user or role doesn’t have the required permissions to configure Query Store, you can run into problems. Verify that the user has ALTER DATABASE access to the relevant database.

Missing or Incomplete Configuration

Occasionally, the settings for the Query Store may be incomplete or incorrectly specified. Make sure you have enabled Query Store, choose the appropriate operating mode, and specify any necessary parameters.

Data Flush Interval

Setting an exceptionally low data flush period may increase system overhead. Think about modifying the data flush interval to a sensible number that strikes a compromise between performance and data retention requirements.

Storage Limitations

Make sure you have enough storage space on the server because the data might expand over time. Keep an eye on the size of the data and make storage space plans accordingly.

Plan Capture Mode

Plan capture options in Query Store include “Auto,” “All,” and “None.” Make sure you are aware of the effects of each mode before selecting the one that best suits your monitoring and troubleshooting requirements.

Server Restart

Restarting the SQL Server instance is a good idea after it has been enabled. This can assist in guaranteeing that it works as intended.

Data Flush Interval

Setting an exceptionally low data flush period may increase system overhead. Think about modifying the data flush interval to a sensible number that strikes a compromise between performance and data retention requirements.

Storage Limitations

Make sure you have enough storage space on the server because the data might expand over time. Keep an eye on the size of the data and make storage space plans accordingly.

Plan Capture Mode

Plan capture options include “Auto,” “All,” and “None.” Make sure you are aware of the effects of each mode before selecting the one that best suits your monitoring and troubleshooting requirements.

Server Restart

Restarting the SQL Server instance is a good idea after It has been enabled. This can assist in guaranteeing that works as intended.

Resource Bottlenecks

It uses up system resources, which causes resource bottlenecks. Enabling QS might have a negative effect on performance if your server is already running at a high resource utilization rate. Keep an eye on resource utilization and make necessary configuration changes.

1. sys.query_store_query

The text of each query as well as any related query IDs are stored in this view.

2. sys.query_store_runtime_stats

You may monitor performance alterations over time by using the runtime information it includes for each query execution.

3. sys.query_store_plan

This view keeps track of query execution plans so you may examine plan modifications and how they affect performance.

4. sys.query_store_wait_stats

It offers information on wait statistics for queries, assisting you in locating resource bottlenecks.

5. sys.query_store_tracked_queries

This view records queries that Query Store is currently tracking, allowing you to see which queries are being examined for speed enhancements.

Examples

A few examples of SQL Server query stores are given below: 

Query Store on a Database Can Be Enabled

Enabling Query Store on your target database is a straightforward process. Just ensure the database has Query Store enabled with a simple command.

ALTER DATABASE TestDB SET QUERY_STORE = ON;
How to enable Query Store on a database

Track Query Performance

Query Store is designed to automatically track and store data about query performance. You can rely on the built-in views and reports to monitor this data efficiently. For instance, to quickly see the top ten requests that use the most resources:

SELECT TOP 10 * FROM sys.query_store_runtime_stats ORDER BY avg_duration DESC;
Track Query Performance in Query Store

Determine Query Plan Regressions

Query Store is a powerful tool that can help you identify regressions in query plans. It means you can compare the execution plans for a given query over time. Here’s how you can view the plan regression for a query:

SELECT * FROM sys.Query_Store_Plan WHERE 
Query_ID = (SELECT Query_ID 
FROM sys.Query_Store_Query 
WHERE Query_Text_ID = OBJECT_ID('Query'));

Force a particular Execution Plan.

With SQL Server, you have the power to SQL Server; you have the power to make it utilize a specific execution plan if you find one that works better for a given query. To confidently mandate an execution plan for a certain query, for instance:

DECLARE @QueryID bigint, @PlanID bigint
SET @QueryID = 29
SET @PlanID = 5

EXEC sp_query_store_force_plan @QueryID, @PlanID;

Monitor Resource Usage

Query Store resource utilization must be closely watched to ensure it doesn’t negatively affect database performance. To track the use of Query Store resources, for instance:

SELECT (size * 8) AS [SizeInKB]
FROM sys.database_files
WHERE name = 'QueryStore';
Monitor Resource Usage using Query Store in SQL Server

Adjust Retention Settings

Retention settings allow you to control how much historical data is kept in the Query Store. To change the retention parameters for an instance, we need to run the below query:

USE [master]
GO
ALTER DATABASE [TestDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, SIZE_BASED_CLEANUP_MODE = AUTO)
GO
Adjust Retention Settings in Query Store in SQL Server

View Properties of Query Stores

To see the current setup and status of Query Store, see its properties:

SELECT * FROM sys.database_query_store_options;

Determine the Highest Execution Count Resource Consumers

To determine which searches have had the most executions:

SELECT TOP 10 Query_ID, Query_Text_ID, Count_Compiles
FROM sys.Query_Store_Query;

Analyze Query Performance Over Time

Using historical data, you can examine how query performance varies over time.

SELECT qsq.Query_ID AS [QueryID], qsrs.Avg_Duration AS [Avg. Duration], qsrs.Avg_CPU_Time AS [Avg. CPU Time], 
qsrs.Avg_Logical_IO_Reads AS [Avg. Logical IO Reads],qsq.Query_Text_ID,sqt.query_sql_text
FROM sys.query_store_runtime_stats qsrs
    JOIN sys.query_store_plan qsp ON qsp.Plan_ID = qsrs.Plan_ID
    JOIN sys.query_store_query qsq ON qsq.Query_ID = qsp.Query_ID
	JOIN sys.query_store_query_text sqt ON sqt.Query_Text_ID = qsq.Query_Text_ID
WHERE sqt.query_sql_text = 'SELECT db_id() AS database_id, actual_state AS QueryStoreActualState FROM sys.database_query_store_options WITH(nolock)'
Analyze Query Performance Over Time In Query Store

Find Queries with Forced Plans

Locate Requests with Compulsory Plans: To locate searches with mandatory execution schedules:

SELECT q.query_id, q.query_text_id, p.plan_id
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;

Forced Plan Reset for an Inquiry

You can reset a forced execution plan for a query if necessary:

EXEC sp_query_store_unforce_plan @query_id = 441, @plan_id = 341; 

Determine Which Queries Take a Long Time to Compile

To identify queries that take a long time to compile:

SELECT TOP 10 Query_ID AS [QueryID], Avg_Compile_Duration AS [Avg Compile Duration],
Count_Compiles AS [Compilation Count],Avg_Compile_Duration/Count_Compiles AS [Compilation Time]
FROM sys.query_store_query

Locate Queries with High Eviction Rates from Plan Cache

To determine which queries are evicting plan caches:

SELECT TOP 10 qsq.Query_ID AS [QueryID], count_executions AS [Execution Count],qsrs.plan_id AS [PlanID], qsp.count_compiles AS [Compile Count]
FROM sys.query_store_runtime_stats qsrs
    JOIN sys.query_store_plan qsp ON qsp.Plan_ID = qsrs.Plan_ID
    JOIN sys.query_store_query qsq ON qsq.Query_ID = qsp.Query_ID
ORDER BY qsp.count_compiles DESC;

Identify Query Plan Changes

We can be used to identify the changes in the Query Plan.

SELECT * FROM sys.Query_Store_Plan

WHERE Last_Execution_Time > DATEADD(HOUR, -12, GETDATE());

Examine the Query Store’s Size and Space Usage

To examine the size and space usage of the Query Store:

Use TestDB;
go
EXEC sp_query_store_flush_db;

Example of Query stores in SQL Server

Here’s an example of how to use Query Store in SQL Server:

Enable Query Store on a Database: First, enable Query Store on the desired database:

ALTER DATABASE TestDB SET QUERY_STORE = ON;

Monitor Query Performance

Query Store automatically starts capturing query performance data. You can monitor this data using built-in views and reports. For example, to view the top 10 resource-consuming queries:

SELECT TOP 10 * FROM sys.query_store_runtime_stats ORDER BY avg_duration DESC;

Identify Query Plan Regressions

Query Store helps identify query plan regressions. You can compare execution plans for a specific query over time. For example, to view plan regression for a query:

SELECT * FROM sys.query_store_plan b WHERE query_id IN (SELECT query_id FROM sys.query_store_query a WHERE a.query_text_id = '<Query>');

Force a Specific Execution Plan

If you identify a better execution plan for a query, you can force the SQL Server to use it. For example, to force an execution plan for a specific query:

EXEC sp_query_store_force_plan @query_id = 654, @plan_id = 345;

Monitor Resource Usage

Monitoring Query Store resource usage is essential to ensure it doesn’t impact overall database performance. For example, to monitor Query Store resource usage:

SELECT * FROM sys.query_store_runtime_stats

Conclusion

In terms of improving SQL Server speed, It is revolutionary. Database administrators are given the tools necessary to make sure their systems are running properly by giving historical data, query plan stability, and simple troubleshooting. It’s important to be aware of the storage overhead and potential performance effects, though. It has the potential to be an important tool in your SQL Server arsenal with the correct understanding and safety measures.

FAQs

Q: What is a Query Store (QS)?

Ans: A built-in feature of SQL Server called Query Store (QS) aids in monitoring and optimizing query performance.

Q: Can I utilize QS in earlier SQL Server versions?

Ans: Since It was first introduced in SQL Server 2016, older versions do not have access to it.

Q: Is there a need for further licensing for the QS?

Ans: No, Query Store comes pre-licensed with SQL Server and is not an additional purchase.

Q: Can I turn off QS if it slows things down?

Ans: Yes, you may disable Query Store if necessary, although doing so is not advised for performance problems.

Q: Does QS work with all kinds of databases?

Ans: The majority of databases benefit from Query Store, although its utility may change based on your particular workload.

Q: How can QS assist with debugging performance issues?

Ans: By saving runtime information and execution plans, Query Store offers insights into query performance. This data can be used by administrators to spot performance regressions, comprehend query behavior over time, and enhance query efficiency.

Q: What information is stored in QS?

Ans: For every query that is conducted against a database, Query Store records the execution plan, runtime metrics (such CPU time, duration, and logical reads), and other pertinent information.

Q: How do I set up my database to use QS?

Ans: The ALTER DATABASE statement or the SQL Server Management Studio (SSMS) graphical interface can be used to enable this feature at the database level.

Q: Is it possible to set up policies for data retention on QS?

Ans: Indeed, administrators can set up retention policies to regulate how much historical data is kept in Query Store. This guarantees that only pertinent performance data is kept and aids in storage space management.

Q: What advantages does QS offer?

Ans: Improved query performance troubleshooting, historical performance analysis, query plan regression detection, and performance baseline establishment are just a few advantages of using Query Store.

Q: How can I use QS to find queries that aren’t working well?

Ans: Built-in reports and views will help you find the most resource-consuming queries, queries that exhibit performance regressions, and queries whose plans change over time. Administrators can prioritize optimization efforts by using these insights.

Q: Is it possible to use QS to enforce a particular query execution plan?

Ans: Yes, administrators can use the ‘Query Store hints‘ feature to impose a certain query execution plan. This can help return to a known optimal strategy or stabilize performance.

Q: Does database speed suffer when using QS?

Ans: The performance overhead on databases is negligible. Administrators should keep an eye on resource utilization linked to the Query Store as it may occasionally affect performance due to high query volume or excessive data retention.

Q: Is QS compatible with all SQL Server editions?

Ans: This feature is offered in the Enterprise, Standard, and Developer editions of SQL Server. Nevertheless, certain editions could only be able to access some sophisticated functions.

Review the below articles also

SQL Server Database Mail

Dbcc Freeproccache: A powerful command

Understand Deadlocks in SQL Server

Unleash Database Insights with Extended Events in SQL Server

SQL Server Pivot: Top 5 Concepts

Loading

Leave a Comment