This article explores the recently announced preview feature of Azure SQL Database – Query Store Hints.
Introduction
SQL Server Query Optimizer creates a cost-effective and optimized execution plan for a query based on the data distribution, statistics, Indexes, and data requirements. However, there are certain times the query does not work as per the expectation. For example, sometimes, we specify query hints such as RECOMPILE so that the query optimizer does not use the existing plan from the cache and builds another optimizer query plan.
- Note: You can refer to SQL Server Query Execution Plans for beginners – Types and Options for understanding the concept behind query execution plans
We can use query hints using the OPTION clause in the object definition. These query hints provide a localized solution for the specific query performance issue. If there are multiple queries in which you want to add query hints, you need to modify each query separately.
- Note: The query hints provide a short-term solution for specific queries. For a long-term and permanent fix, you should work on an optimized query in alternate ways
Suppose you identify a query that is not performing well, and you want to add the query hints in it. Therefore, for an existing object, the DBA requires to perform any of the following actions.
- Alter the object definition
- Drop and Create the object
It might not be feasible for a DBA to alter the object definition. It requires certain approvals, follows the change management process. However, sometimes, you want a quick resolution without modifying any piece of code.
Is there any way to manually optimize the query execution plan without changing the object definition?
Microsoft recently announced Query Store Hints for Azure SQL Databases. It is currently in public preview for Azure SQL Server single database, elastic pools, SQL managed instances and hyper-scale databases. This article explores the query store hints and how they can be helpful for Azure databases.
Query Store Hints in Azure SQL Database
SQL Server Query Store enables DBA to monitor query performances caused by the query plan changes. Sometimes, we observe that the query works slow after it gets a new execution plan. The query store automatically captures the queries, their execution plans, runtime statistics. You can review the multiple execution plan of a query and force a specific execution plan to use in subsequent executions.
If you are new to Query Store, you can refer to excellent content SQL Server Query Store – Overview on SQLShack by Marko Zivkovic.
By default, Query Store is enabled for all Azure SQL Databases. To view query store configuration on Azure Database, right-click on it in SSMS and view its properties. In the properties window, navigate to Query Store.
You cannot disable query stores in Azure SQL Database. If you try to do so, it gives the following error message.
The Query Store Hint (as its name suggests) feature works on top of the query store for Azure databases. It provides an option to modify query plans and their behavior without changing the query text. It allows you to directly specifying query hints using the execution plan captured in the query store. By using the query store hints, you do not need to modify object definition.
The following image (Ref: Microsoft Tech Community) describes the overall process for using query hints using query store. In this method, DBA defines a query store hint for a specific query ID. The subsequent query execution uses the query hint using the query store execution plan.
It can be a helpful feature in following query-level performance issues:
- Cases such as parametrized queries where you require recompilation on each execution
- To cap the maximum memory grant for bulk-insert
- Choose a specific MAXDOP to restrict or avoid parallelism
- Use a specific compatibility level (lower than 150) for a query while the database is at compatibility level 150
- Disable the row goal optimization for the SELECT query with the TOP operator
- To use a different join algorithm
Supported and Unsupported Query Store hints in Azure SQL Database
The supported Query Store hints for Azure SQL Database are as below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
{ HASH | ORDER } GROUP | { CONCAT | HASH | MERGE } UNION | { LOOP | MERGE | HASH } JOIN | EXPAND VIEWS | FAST number_rows | FORCE ORDER | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX | KEEP PLAN | KEEPFIXED PLAN | MAX_GRANT_PERCENT = percent | MIN_GRANT_PERCENT = percent | MAXDOP number_of_processors | NO_PERFORMANCE_SPOOL | OPTIMIZE FOR UNKNOWN | PARAMETERIZATION { SIMPLE | FORCED } | RECOMPILE | ROBUST PLAN | USE HINT ( '<hint_name>' [ , ...n ] ) |
The following Query store hints are not supported for Azure Database:
- OPTIMIZE FOR(@var = val)
- MAXRECURSION
- USE PLAN
- DISABLE_DEFERRED_COMPILATION_TV
- DISABLE_TSQL_SCALAR_UDF_INLINING
How to use Query Store Hints in Azure SQL Databases
To use the query store hints (preview) feature in Azure SQL Database, you can use the below steps.
Step 1: Find the query store query_id of the specific query
We need to use DMVs sys.query_store_query_text and sys.query_store_query to find out the query_id in the first step. For example, in the following query, we query and figure out DMV for SQL statements specified in the WHERE clause.
|
1 2 3 4 5 6 7 |
SELECT query_sql_text, q.query_id FROM sys.query_store_query_text qt INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id WHERE query_sql_text like N'%Select * from dbo.Customer%' and query_sql_text not like N'%query_store%'; GO |
In my demo environment, the query id is 786.
- Note: You can also find the query_id using the query store reports in the SQL Server Management Studio (SSMS) or using the Query performance insight in the Azure portal
Step 2: Add the query hint using the sp_query_store_set_hints
In this step, we use the stored procedure sp_query_store_set_hints and add the query hint. It requires the parameter query_id obtained from step 1 and the query hint that we wish to apply.
For example, we add query hint RECOMPILE in the query_id 786.
|
1 2 |
EXEC sp_query_store_set_hints 786, N'OPTION(RECOMPILE)'; GO |
You can also specify multiple query hints, if required, for a specific query in Azure SQL Database. For example, the below proc adds MAXDOP, MAX_GRANT_PERCENT and RECOMPILE query hints for the query store id 786.
|
1 2 3 |
EXEC sp_query_store_set_hints 786, N'OPTION(MAXDOP=1, MAX_GRANT_PERCENT=1-, RECOMPILE)'; GO |
To verify the query hint, we can use another catalog view sys.query_store_query_hints.
|
1 2 3 4 5 6 |
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc FROM sys.query_store_query_hints; |
This catalog view gives the following columns in the output:
- query_hint_id: It is a unique identifier for the query hint
- query_id: It is the query id for which the query hint is added
- query_hint_text: It returns the query hint added in step 2 for the query store. It is in the form of N’OPTION ()’
- last_query_hint_failure_reason: If due to any specific reason applying query hint is failed, this column returns the message id of the error message
- last_query_hint_failure_reason_desc: It is the error description for the failure of the query hint
- query_hint_failure_count: It tracks the count of failures since the query hint was defined or last modified
- source_desc: It is the source of the query hint. It returns a value User for a user-initiated query hint
Clear the query store hint in Azure SQL Database
Suppose you want to remove the query hint specified for a specific query id. For example, here, we remove query hints for query id 786.
|
1 2 |
EXEC sp_query_store_clear_hints @query_id = 786; GO |
As shown below, once we clear the query store hint for query id 796, the sys.query_store_query_hints catalog view does not return any row.
Important notes about the query store hints
- The query store hints are persisted. Thus, it survives SQL Service failovers or restarts
- These hints override the hardcoded statement hints and plan guides. In case of any conflict, SQL Server skips applying query hints
- Currently, query store hints in the preview phase of Azure SQL Database. There might be a few changes before it is in the general availability phase. If you have any feedback about this feature, you can send an email to QSHintsFeedback@microsoft.com
Conclusion
This article explored the Query Store Hints feature in the Azure SQL Database. This feature helps DBA to provide a temporary solution for a performance issue without modifying the object definition. You can specify the query hint in a query store id and improve query performance. However, you should treat it as a short-term solution, and you should work on a permanent fix by modifying object definition, creating or altering indexes and statistics.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023