Query Store in SQL Server: Understanding, Implementing, and Optimizing : cybexhosting.net

Baca Cepat show

Hello and welcome, fellow database enthusiasts. Whether you’re a seasoned SQL Server professional or a curious beginner, you’ve probably heard of the Query Store, a powerful feature introduced in SQL Server 2016 that helps you monitor, troubleshoot, and optimize the performance of your queries. If you haven’t explored the Query Store yet, or if you’re looking for tips and tricks to make the most of it, this article is for you.

What is the Query Store and why is it important?

Before we dive into the details of the Query Store, let’s start with the basics: what is it, and why should you care? Simply put, the Query Store is a built-in feature in SQL Server that captures and stores query execution statistics, plans, and runtime information for every query that runs on your database. By doing so, the Query Store enables you to:

  • Identify poorly performing queries that consume a lot of resources, such as CPU, memory, or IO.
  • Analyze query execution plans and compare them over time to detect changes, regressions, or optimization opportunities.
  • Force a specific plan for a query that performs well with a particular plan but regresses with another.
  • Monitor the workload and performance of your database, including the number of queries, their frequency, duration, and resource usage.

As you can see, the Query Store provides valuable insights and controls that can help you improve the performance, stability, and scalability of your database applications. But how does it work, and how do you use it?

How does the Query Store work?

The Query Store works by intercepting every query that is executed on your database and collecting information about its performance, plan, and other attributes. The Query Store stores this information in system tables and views that you can query and analyze using standard SQL statements. The Query Store also provides a graphical user interface in SQL Server Management Studio (SSMS) that lets you explore the data and visualize the trends and patterns.

The Query Store is enabled by default on all user databases in SQL Server 2016 and later versions. However, you can customize its behavior and settings, such as:

  • Turning it off or on for specific databases, or for the entire server.
  • Configuring the data retention period, which determines how long the Query Store keeps the data before deleting it.
  • Changing the maximum size of the Query Store, which limits the amount of disk space it can consume.
  • Specifying the query execution mode, which controls how the Query Store captures and stores the query plans.

As you can see, the Query Store is a flexible and configurable feature that can adapt to your needs and constraints. But how do you use it in practice?

How do you use the Query Store?

Using the Query Store is a multi-step process that involves the following stages:

  1. Enabling and configuring the Query Store for your database.
  2. Collecting and analyzing data from the Query Store.
  3. Identifying and troubleshooting performance issues based on the data.
  4. Optimizing the queries by modifying their code or their execution plans.
  5. Validating the improvements by monitoring the performance over time.

Let’s explore each of these stages in more detail.

Enabling and configuring the Query Store

The first step to using the Query Store is to enable it for your database and configure its settings according to your needs. To do so, you can use the following T-SQL statements:

Statement Description
ALTER DATABASE [database_name] SET QUERY_STORE = ON; Enables the Query Store for the specified database.
ALTER DATABASE [database_name] SET QUERY_STORE ( OPERATION_MODE = <mode>, MAX_STORAGE_SIZE_MB = <size>, INTERVAL_LENGTH_MINUTES = <interval>, CLEANUP_POLICY = <policy> ); Configures the Query Store settings for the specified database. The <mode> can be READ_WRITE or READ_ONLY, depending on whether you want to allow the plans to be forced. The <size> is the maximum size of the Query Store in megabytes. The <interval> is the frequency of data collection in minutes. The <policy> is the retention policy for the Query Store data, which can be NONE, AUTO, or CUSTOM.

For example, to enable the Query Store for the AdventureWorks database and configure it to retain the data for 30 days, collect it every 15 minutes, and limit the size to 1 GB, you can use the following statements:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
ALTER DATABASE AdventureWorks SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), INTERVAL_LENGTH_MINUTES = 15, MAX_STORAGE_SIZE_MB = 1024);
GO

Once you have enabled and configured the Query Store, you can start collecting and analyzing data from it.

Collecting and analyzing data from the Query Store

The second step to using the Query Store is to collect and analyze the data it stores. The Query Store provides several ways to do so, including:

  • Querying the system views that contain the Query Store data, such as sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats, and others.
  • Using the Query Store Reports feature in SSMS, which offers a set of predefined reports that visualize the Query Store data in various ways, such as Top Resource Consuming Queries, Regressed Queries, Tracked Queries, and more.
  • Using custom tools and scripts that leverage the Query Store data to provide additional insights and metrics, such as query performance analysis, trend analysis, and optimization recommendations.

Let’s focus on the first two options, which are the most common and accessible ways to explore the Query Store data.

Querying the Query Store data

To query the Query Store data, you can use T-SQL statements that join the system views and tables that contain the data. The main views are:

  • sys.query_store_query: Contains information about the queries, such as their text, parameters, execution count, and average/total resource usage.
  • sys.query_store_plan: Contains information about the query plans, such as their hash, size, optimization level, and execution statistics.
  • sys.query_store_runtime_stats: Contains information about the query runtime, such as the start and end time, duration, and wait types.
  • sys.query_store_wait_stats: Contains information about the query wait statistics, such as the wait types and durations.

For example, to list the top 10 queries that consume the most CPU time in the last hour, you can use the following query:

SELECT TOP 10 query_id, query_text_id, SUM(cpu_time) AS total_cpu_time
FROM sys.query_store_runtime_stats
WHERE start_time > DATEADD(hour, -1, GETUTCDATE())
GROUP BY query_id, query_text_id
ORDER BY total_cpu_time DESC;

This query joins the sys.query_store_runtime_stats view with the WHERE clause that filters the data for the last hour, groups it by query_id and query_text_id, and sums the CPU time for each group. The result is sorted in descending order by the total CPU time and limited to the top 10 rows.

Using the Query Store Reports

The Query Store Reports feature in SSMS provides a more graphical and interactive way to explore the Query Store data. To access the reports, you can right-click on a database that has Query Store enabled, select Reports > Standard Reports > Query Store, and choose one of the available reports:

  • Top Resource Consuming Queries: Shows the queries that consume the most CPU, memory, I/O, or network resources.
  • Regressed Queries: Shows the queries that have regressed in performance, meaning that they used to perform well but now perform poorly.
  • Tracked Queries: Shows the queries that have been tracked by the Query Store, along with their execution counts, runtime statistics, and plan diagrams.
  • Query Store Wait Statistics: Shows the wait types and durations of the queries in the Query Store.
  • Query Store Queries: Shows the list of queries in the Query Store, along with their text, parameters, and attributes.
  • Query Store Plan Explorer: Shows the execution plans of the queries in the Query Store, along with their details, graphs, and comparison features.

For example, to view the Top Resource Consuming Queries report for the AdventureWorks database, you can use the following steps:

  1. Open SSMS and connect to your SQL Server instance.
  2. Expand the Databases folder and locate the AdventureWorks database.
  3. Right-click on the database and select Reports > Standard Reports > Query Store > Top Resource Consuming Queries.
  4. View the report, which shows the top 100 queries that consume the most CPU time, memory, I/O, or network resources, along with their details and graphs.

You can use this report to identify the queries that are the most resource-intensive and focus on optimizing them first. You can also use other reports to explore other aspects of the Query Store data, such as query plan regressions, tracked queries, wait statistics, and more.

Identifying and troubleshooting performance issues

The third step to using the Query Store is to identify and troubleshoot performance issues based on the data you collect and analyze. There are several types of issues that you can detect and diagnose using the Query Store, such as:

  • Queries that consume too many resources, such as CPU, memory, I/O, or network.
  • Queries that have poor optimization or plan quality, such as missing or outdated statistics, suboptimal join order, or excessive recompilations.
  • Queries that regress in performance over time, such as when a new plan is introduced that performs worse than the previous one.
  • Queries that experience frequent waits or bottlenecks, such as contention or blocking issues.

To identify such issues, you can use the Query Store data and the tools we discussed in the previous sections. For example, you can run queries that filter the data based on specific criteria, such as:

  • Show me the queries that have an average CPU time higher than 500ms and have executed more than 100 times in the last week.
  • Show me the queries that have more than 5 different plans in the Query Store and have a plan stability score lower than 50%.
  • Show me the queries that have regressed in CPU usage by more than 20% in the last day.
  • Show me the queries that experience a high wait time for the PAGEIOLATCH_SH wait type and have a large number of read and write operations.

Once you have identified a performance issue, you can start troubleshooting it by examining the query plan, the statistics, the indexes, the configuration, and other factors that might affect its performance. You can also force a specific plan for a query if you find that it performs better with a particular plan but regresses with another.

For example, to force a plan for a query that you know performs well with a specific plan, you can use the following statement:

USE AdventureWorks;
GO
EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <plan_id>;
GO

This statement forces the specified plan for the query with the given query_id. You can find the query_id and plan_id by querying the appropriate system views.

Optimizing the queries

The fourth step to using the Query Store is to optimize the queries that have performance issues based on your analysis and troubleshooting. There are several ways to optimize a query, such as:

  • Modifying the query code to improve its logic, readability, and performance.
  • Adding, modifying, or removing indexes to improve the query execution plan.
  • Updating the statistics to ensure that the query optimizer has accurate information about the data distribution.
  • Changing the database or server configuration to optimize certain aspects of the query execution, such as memory allocation, parallelism, or query hints.

To optimize a query, you need to have a good understanding of its performance characteristics, its data model, and its business requirements. You also need to test your changes and measure their impact on the performance and stability of your system. The Query Store can help you validate your optimizations by comparing the performance before and after your changes, and by showing you the execution plans and runtime statistics of the queries over time.

For example, suppose you have identified a query that performs poorly due to a missing index. You can create the index and see how it affects the query performance by running the query again and comparing its execution plan and runtime statistics before and after the index creation. You can also analyze other queries that use the same table or columns and see if they benefit from the new index as well. You can finally validate your changes by monitoring the performance of the database over time and observing if the query or the system as a whole improves.

Validating the improvements

The fifth and final step to using the Query Store is to validate the improvements you have made by monitoring the performance of the database over time and observing if the query or the system as a whole improves. Validation is an essential step in the optimization process, as it ensures that the changes you made have the desired effect and do not introduce new issues or regressions.

To validate your improvements, you can use the Query Store data and the tools we discussed in the previous sections to:

  • Compare the performance of the query before and after your changes, and see if the resource usage, execution time, or other metrics have improved.
  • Compare the execution plans of the query before and after your changes, and see if the new plan is more optimal, stable, or efficient.
  • Compare the Query Store data of the database before and after your changes, and see if the overall performance, stability, or scalability have improved or not.

For example, suppose you have optimized a query by adding an index and correcting the statistics. You can validate your improvements by running the query again and comparing its execution plan and runtime statistics before and after your changes. You can also run other queries that use the same table or columns and observe if their performance has improved as well. Finally, you can monitor the Query Store data of the database over time and see if the number of queries, the resource usage, or the wait statistics have changed significantly. If everything looks good, congratulations! You have successfully used the Query Store to optimize your SQL Server database.

FAQs

Q: What is the Query Store?

A: The Query Store is a built-in feature in SQL Server that captures and stores query execution statistics, plans, and runtime information for every query that runs on your database. It helps you monitor, troubleshoot, and optimize the performance of your queries.

Q: How do I enable the Query Store?

A: You can enable the Query Store for your database by using the following statement:

ALTER DATABASE [database_name] SET QUERY_STORE = ON;

You can also configure its settings according to your needs, such as retention period, maximum size, and data collection interval.

Q: How do I access the Query Store data?

A: You can access the Query Store data by querying the system views and tables that contain it, such as sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats, and others. You can also use the Query Store Reports feature in SQL Server Management Studio (SSMS) to visualize the data in various ways.

Q: How do I optimize a query using the Query Store?

A: To optimize a query using the Query Store, you need to perform the following steps:

  1. Collect and analyze the Query Store data to identify the performance issues of the query.
  2. Troubleshoot the issues by examining the query plan

    Source :

Copyright 2023 KabarKini