Assess the use of hints for query performance
In this we will learn how to assess the use of hints for query performance.
Hints (Transact-SQL) – Query
Query hints specify that the indicated hints are used in the scope of a query. They affect all operators in the statement. However, if UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause. Query hints are specified as part of the OPTION clause. Error 8622 occurs if one or more query hints cause the Query Optimizer not to generate a valid plan
Arguments
{ HASH | ORDER } GROUP
Firstly, it specifies that aggregations that the query’s GROUP BY or DISTINCT clause describes should use hashing or ordering.
{ MERGE | HASH | CONCAT } UNION
Specifies that all UNION operations are run by merging, hashing, or concatenating UNION sets. However, if more than one UNION hint is specified, the Query Optimizer selects the least expensive strategy from those hints specified.
{ LOOP | MERGE | HASH } JOIN
Specifies all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. If you specify more than one join hint, the optimizer selects the least expensive join strategy from the allowed ones.
Further, if you specify a join hint in the same query’s FROM clause for a specific table pair, this join hint takes precedence in the joining of the two tables. The query hints, though, must still be honored.
EXPAND VIEWS
Specifies the indexed views are expanded. Also specifies the Query Optimizer won’t consider any indexed view as a replacement for any query part. Further, a view is expanded when the view definition replaces the view name in the query text.
This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.
FAST
Specifies that the query is optimized for fast retrieval of the first number of rows. This result is a non-negative integer. After the first number of rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization. However, using FORCE ORDER doesn’t affect possible role reversal behavior of the Query Optimizer.
{ FORCE | DISABLE } EXTERNALPUSHDOWN
Force or disable the pushdown of the computation of qualifying expressions in Hadoop. Only applies to queries using PolyBase. Won’t push down to Azure storage.
MAXDOP
Overrides the max degree of parallelism configuration option of sp_configure. Also overrides the Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. However, if MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). Further, all semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.
MAXRECURSION
Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32,767. When 0 is specified, no limit is applied. If this option isn’t specified, the default limit for the server is 100. However, when the specified or default number for MAXRECURSION limit is reached during query execution, the query ends and an error returns.
RECOMPILE
Instructs the SQL Server Database Engine to generate a new, temporary plan for the query and immediately discard that plan after the query completes execution. The generated query plan doesn’t replace a plan stored in cache when the same query runs without the RECOMPILE hint. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them. However, RECOMPILE is a useful alternative to creating a stored procedure. RECOMPILE uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled.
USE HINT ( ‘hint_name’ )
Provides one or more additional hints to the query processor. The additional hints are specified by a hint name inside single quotation marks.
The following hint names are supported:
- ‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’
Causes SQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the Query Optimizer Cardinality Estimation model of SQL Server 2014 (12.x) or newer. This hint name is equivalent to trace flag 9476. - ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’
Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. However, this hint name is equivalent to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 (11.x) and earlier versions. - ‘DISABLE_BATCH_MODE_ADAPTIVE_JOINS’
Disables batch mode adaptive joins. For more information, see Batch mode Adaptive Joins. - Then, ‘DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK’
Disables batch mode memory grant feedback. For more information, see Batch mode memory grant feedback. - ‘DISABLE_DEFERRED_COMPILATION_TV’
Disables table variable deferred compilation. For more information, see Table variable deferred compilation. - ‘DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK’
Disables row mode memory grant feedback. For more information, see Row mode memory grant feedback.
Specifying Table Hints as Query Hints
We recommend using the INDEX, FORCESCAN, or FORCESEEK table hint as a query hint only in the context of a plan guide. Moreover, plan guides are useful when you can’t modify the original query, for example, because it’s a third-party application. The query hint specified in the plan guide is added to the query before it’s compiled and optimized. Further, for ad-hoc queries, use the TABLE HINT clause only when testing plan guide statements. And, for all other ad-hoc queries, we recommend specifying these hints only as table hints.
When specified as a query hint, the INDEX, FORCESCAN, and FORCESEEK table hints are valid for the following objects:
- Firstly, tables
- Secondly, views
- Thirdly, indexed views
- Then, common table expressions (the hint must be specified in the SELECT statement whose result set populates the common table expression)
- Dynamic Management Views (DMVs)
- Lastly, named subqueries
However, you can specify INDEX, FORCESCAN, and FORCESEEK table hints as query hints for a query that doesn’t have any existing table hints. You can also use them to replace existing INDEX, FORCESCAN, or FORCESEEK hints in the query, respectively.
Examples
A. Using MERGE JOIN
The following example specifies that MERGE JOIN runs the JOIN operation in the query. The example uses the AdventureWorks2012 database.
SQL
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
B. Using OPTIMIZE FOR
The following example instructs the Query Optimizer to use the value ‘Seattle’ for @city_name. And, to use the average selectivity of the predicate across all column values for @postal_code when optimizing the query. The example uses the AdventureWorks2012 database.
SQL
CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),
@postal_code NVARCHAR(15)
AS
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = ‘Seattle’, @postal_code UNKNOWN) );
GO
C. Using MAXRECURSION
MAXRECURSION can be used to prevent a poorly formed recursive common table expression from entering into an infinite loop. However, the following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two. Further, the example below uses the AdventureWorks2012 database.
SQL
–Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
SELECT CustomerID, PersonID, StoreID
FROM Sales.Customer
WHERE PersonID IS NOT NULL
UNION ALL
SELECT cte.CustomerID, cte.PersonID, cte.StoreID
FROM cte
JOIN Sales.Customer AS e
ON cte.PersonID = e.CustomerID
)
–Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO
D. Using MERGE UNION
The following example uses the MERGE UNION query hint. However, the example uses the AdventureWorks2012 database.
SQL
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
Query tuning and hinting
The query optimizer in Azure SQL Database and Azure SQL Managed Instance is similar to the traditional SQL Server query optimizer. Most of the best practices for tuning queries and understanding the reasoning model limitations for the query optimizer also apply to Azure SQL Database and Azure SQL Managed Instance. However, if you tune queries in Azure SQL Database and Azure SQL Managed Instance, you might get the additional benefit of reducing aggregate resource demands. Then, your application might be able to run at a lower cost than an un-tuned equivalent because it can run at a lower compute size.
An example that is common in SQL Server and which also applies to Azure SQL Database and Azure SQL Managed Instance is how the query optimizer “sniffs” parameters. During compilation, the query optimizer evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. However, this strategy often can lead to a query plan that is significantly faster than a plan compiled without known parameter values. But, currently it works imperfectly both in SQL Server, in Azure SQL Database, and Azure SQL Managed Instance.
The next example demonstrates how the query processor can generate a plan that is sub-optimal both for performance and resource requirements. Further, this example also shows that if you use a query hint, you can reduce query run time and resource requirements for your database:
Reference: Microsoft Documentation, Documentation 2