Demystifying Query Store Errors: Troubleshooting and Solutions

Blogs

Exploring Text Similarity with Spacy in NLP
May 28, 2023
Embracing Transitioning: Migration from Qlik to Power BI – Part 1
July 24, 2023

Demystifying Query Store Errors: Troubleshooting and Solutions

There is a fact that, “those who monitor a goal are much more likely to achieve and maintain it than those who don’t”. This makes sense. If you are monitoring your progress, you will know if you are on track. If you are not on track, you can take steps to get back on track.

Monitoring is crucial in every domain, sector, and field because it allows us to gather data and information about the system or situation being observed. By continuously monitoring various parameters, we can detect any abnormalities or deviations from normal behavior, enabling us to identify any potential issues, track progress and diagnose problems effectively.

When it comes to Database monitoring it is very tough to see database challenges from all perspectives. To track performance, Microsoft developed a useful feature for SQL server in 2016 called Query store.

Figure 1:

 

This article talks about challenges we had faced when assisting one of our biggest customers (in terms of SQL server databases they own) while enabling  a query store on their 2019 SQL server instance for their monitoring benefits, and we were eager to solve it because we enjoy troubleshooting and challenging  situations.

As previously stated, query store is a flexible feature with a variety of built-in capabilities that may be adjusted in accordance with our requirements and database characteristics.

Note: For a thorough knowledge of the Query store, click the link below.

https://learn.microsoft.com/en-us/sql/relational-databdsases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16

The query store setting properties are displayed in the image below.

Figure 2:

The Query Store offers three operational modes that can be configured. Despite our attempts to set the operation mode to “read write”, we encountered difficulties in achieving the desired setting. Even after restarting the server and attempting to enable the “read write” mode, we observed that it would eventually revert to the initial “read only” mode.

The query store was getting into read only mode with the error “Size of in-memory items waiting to be persisted on disk has reached the internal memory limit. Query Store will be in read-only mode temporarily until the in-memory items are persisted on disk.”, After conducting numerous tests in collaboration with the Microsoft team, it was determined that the problem stemmed from the internal memory limit assigned to the query store. Microsoft recognized this issue as a bug and recommended a solution. The problem was addressed by either restarting the server or utilizing a trace flag. However, it is important to note that this recommendation is not a viable long-term solution for the production instance.

Upon conducting a thorough assessment of the production database within our client’s environment, it was discovered that the query store had been configured under the SQL Server 2019 version. By default, the capture mode was set to AUTO, which imposed its own limitation. In this mode, queries were captured once they reached a specific threshold. As we endeavored to enable the query store on a crucial database with a heavy workload, it attempted to capture data from all queries, illustrating the issue.

Starting with SQL Server 2019 (15.x), the QUERY_CAPTURE_MODE = AUTO setting captures query store details when any of the following thresholds are hit:

  • EXECUTION_COUNT = 30 executions = execution count
  • TOTAL_COMPILE_CPU_TIME_MS = 1 second = compile time in milliseconds
  • TOTAL_EXECUTION_CPU_TIME_MS = 100 ms = execution CPU time in milliseconds

From this mode all the queries that take 100 ms to execute are captured in the query store and that has led to overhead in the memory and storage.

After several testing, we recommended setting capture mode of Query store to CUSTOM, after the analysis on the production database, some of the pre-requisites mentioned below are satisfied to set capture mode as CUSTOM on query store of production database.

Tuning an appropriate custom capture policy for the environment should be considered when:

  • The database is very large.
  • The database has many unique, ad hoc queries.
  • The database has specific size or growth limitations.

Set QUERY_CAPTURE_MODE = CUSTOM, these setting captures query store details when any of the following thresholds are hit:

  • EXECUTION_COUNT = 30 executions (execution count)
  • TOTAL_COMPILE_CPU_TIME_MS = 1000 ms (compile time in milliseconds)
  • TOTAL_EXECUTION_CPU_TIME_MS = 1000 ms (execution CPU time in milliseconds)
  • SIZE_BASED_CLEANUP_MODE= AUTO (automatically cleans up when it reaches 90% of the maximum size)
  • STALE_CAPTURE_POLICY_THRESHOLD = 1 HOUR (Defines the evaluation interval period to determine if a query should be captured)

After changing the total execution CPU time to 1 sec, it captures the queries that hit 1 sec to execute and reduce storage and memory overhead.

We had recommended to set the capture policy in SQL Server’s Query Store based on the workload that was hitting our customer’s database by specifying the criteria for capturing queries based on various factors such as execution time, CPU usage, logical reads, or other performance-related metrics.

By configuring the capture policy, we controlled the amount of information stored in the Query Store and focused on capturing queries that are relevant to your performance analysis and tuning efforts. This helped our customers in managing the Query Store’s size and avoiding unnecessary overhead.

The syntax for configuring a capture policy on a Query store is shown below. The value can be adjusted in accordance with the workload, requirements, and frequency at which you must capture and flush the queries into the disk from memory.

T-SQL to set capture policy for the database:

ALTER DATABASE [Database_name]

SET QUERY_STORE = ON

(

OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 80 ),

DATA_FLUSH_INTERVAL_SECONDS = 300,

MAX_STORAGE_SIZE_MB = 55200,

INTERVAL_LENGTH_MINUTES = 30,

SIZE_BASED_CLEANUP_MODE = AUTO,

QUERY_CAPTURE_MODE = CUSTOM,

QUERY_CAPTURE_POLICY = (

STALE_CAPTURE_POLICY_THRESHOLD = 1 HOUR,

EXECUTION_COUNT = 30,

TOTAL_COMPILE_CPU_TIME_MS = 1000,

TOTAL_EXECUTION_CPU_TIME_MS = 1000

)

);

Ultimately, our client experienced satisfaction and peace of mind, enabling Query store for them to effectively oversee the production database and assess query performance. This newfound capability allows them to see what’s happening in their database activities. Notably, the transition from AUTO to CUSTOM capture mode in the query store yielded remarkable results. This modification alleviated memory pressure by selectively gathering essential information, thereby reducing the burden on the Query store.

Additional Information:

  • Query store is enabled by default in the READ_WRITE mode for new databases starting with SQL Server 2022.

 

 

 


thejas.krishna

Leave a Reply

Your email address will not be published. Required fields are marked *