Rajendra Gupta
Query Store configuration

Query Store Hints in Azure SQL Database

June 22, 2021 by

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.

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.

Query Store configuration

You cannot disable query stores in Azure SQL Database. If you try to do so, it gives the following error message.

Turn off Query Store

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.

Query Store hints

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.

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.

In my demo environment, the query id is 786.

Search for specific Query ID

  • 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.

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.

To verify the query hint, we can use another catalog view sys.query_store_query_hints.

Insert query store hint

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.

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.

Remove query hints

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.

Rajendra Gupta
Azure, Query store, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

201 Views