Query Hints in SQL Server

dbtut May 10, 2019 MSSQL

What is Query Hint?

You can change the execution plan of the query by adding the query hints to the end of the query. For example, if you add the maxdop query hint to the end of a query that uses a single CPU, the query may use multiple CPUs(parallelism). Or, you can use the recompile query hint to ensure that the query generates a query plan each time it is executed.

Before you apply Query Hint, you need to make sure that the operation you want is correct. Because using query hint, you are changing SQL Server’s normal behavior. The scripts mentioned in the article were taken from microsoft’s site and tested on the AdventureWorks2012 database.

Below, I share some examples of query hints that I think you might find useful.

Recompile Query Hint

When a stored procedure or parameterized query executed, SQL Server creates a query plan as soon as they are running. The second time they are executed, they are not compiled again, they use the query plan they produced earlier. In this way, they do not need to be recompiled each time.

This is SQL Server’s default behaviour. You can change this in many ways. For Example; “Optimize for ad hoc workloads and Parameterization in SQL Server” or Query Hints.

The normal behavior of SQL Server often improves performance. However, if the data distribution in the table is uneven, sometimes it causes parameter sniffing. In the article “What is Parameter Sniffing” you will find details about the parameter sniffing. To resolve this issue, you can use Recompile Query Hint to generate a query plan each time the query is executed. In the article “What is Parameter Sniffing” you can find the example of using Recompile Query Hint.

OPTIMIZE FOR Query Hint

Optimize For Query Hint is the query hint that instructs the query optimizer to use a specific value for a local variable when creating the Query Plan. Like RECOMPILE query hint, this can be useful for parameter sniffing. In my article “What is Parameter Sniffing” you can find an example with this type of query hint.

USE PLAN Query Hint

Use Plan Query Hint is the query hint that instructs the query optimizer to use an existing query plan for a query when creating the Query Plan.

If there is long-running queries because of they use the wrong query plan, and you know that there is a better query plan, you can use this query hint.

But my advice is to leave this job to sql server and keep your statistics up to date. You may want to read the article “Statistic Concept and Performance Effect on SQL Server” for detailed information on statistics.

However, if you want to use the use plan query hint, you can find details about how to use it in the link below.

https://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx

PARAMETERIZATION Query Hint

The behavior of some queries changes when we set parameterization in the database. With the Template plan guide, we can change the parameterization method for specific queries, difference from the parameterization method set at the database level.

Read the following article for Template Plan Guide.

“Increase Query Performance in SQL Server Using sp_create_plan_guide System Stored Procedure”

Read the following article for Parameterization.

“Optimize for ad hoc workloads and Parameterization”

MERGE JOIN Query Hint

The following example uses a query hint that forces join to MERGE JOIN. For more information about join operations in SQL Server, please refer to “Join Types in SQL Server” and “JOIN Types in SQL Server Execution Plan“.

SELECT *   
FROM Sales.Customer AS c  
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID  
WHERE TerritoryID = 5  
OPTION (MERGE JOIN);  
GO

SELECT *

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID

WHERE TerritoryID = 5

OPTION (MERGE JOIN);

MAXRECURSION Query Hint

MAXRECURSION Query Hint prevents a poorly designed CTE (Common Table Expression) from entering the infinite loop. For more information about CTE (Common Table Expression), you may want to read the article “Common Table Expression (CTE)“.

The following example creates a CTE that enters an infinite loop, and sets the maximum number of loops of the query that uses the CTE.

--CTE that enters 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  
)  
--Query using CTE and using the MAXRECURSION query hint
SELECT CustomerID, PersonID, StoreID  
FROM cte  
OPTION (MAXRECURSION 2);  

GO

--CTE that enters 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

)

--Query using CTE and using the MAXRECURSION query hint

SELECT CustomerID, PersonID, StoreID

FROM cte

OPTION (MAXRECURSION 2);

When I execute the query in the database, it entered the loop 2 times and terminated with an error like the following.

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

Removing the OPTION (MAXRECURSION 2) indian in the query changed the error as follows. I performed this test on SQL Server 2014. After the query entered the loop 100 times, SQL Server automatically stopped the script.

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

MAXDOP Query Hint

By specifing OPTION (MAXDOP 2) hint at the end of the query, we have specified that the maxdop setting of this query will be 2 regardless of the instance setting. Thus, this query will work two parallel.

It is a query hint that will be useful for report queries. But be careful while using. Because, if many report queries works in parallel, you may encounter CPU bottleneck. You may want to read the below article about MAXDOP and CPU usage.

“Numa Nodes, MAX/MIN Server Memory, Log Pages In Memory and MAXDOP”

You can use MAXDOP Query hint as follows.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total 
FROM Sales.SalesOrderDetail 
WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty 
ORDER BY ProductID, OrderQty OPTION (MAXDOP 2);

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty

ORDER BY ProductID, OrderQty OPTION (MAXDOP 2);

FORCE INDEX Query Hint

In SQL Server, we use this hint to tell a query to work with the specified index. Actually SQL Server almost always makes the best choice. So if you keep your statistics up-to-date and rebuild your indexes on a regular basis, using this hint will usually slow you down.

I suggest you read the following articles about statistics and indexes.

“Index Concept and Performance Effect on SQL Server”,

“Statistic Concept and Performance Effect on SQL Server”

Let’s take a look at the execution plan of the following select statement by clicking the checkbox in the following screen. As you can see, under normal circumstances Clustred Index Scan process is performed as follows.

USE AdventureWorks2014Yeni
GO
SELECT *
FROM Person.Person
GO

USE AdventureWorks2014Yeni

SELECT *

FROM Person.Person

Let’s add a hint to the query. As you can see, it used the Index we specified and then it had to perform Key Lookup to bring the needed records.

I wanted to show it because it could be needed in very rare cases, but don’t use it unless it’s really necessary. Because as I mentioned before, it will mostly decrease your performance.

Nolock Query Hint

This query hint force query to work like read uncommitted. Usually dbas advice to software developers due to locking. But be careful using this query hint. Because if you use this hint in select statements, it reads dirty data.

I suggest you read the following articles.

“Isolation Levels 1“,

“Isolation Levels 2“,

“Isolation Levels 3“,

Example:

SELECT *  FROM [AdventureWorks2012].[Person].[Address] WITH(NOLOCK)

1	SELECT * FROM [AdventureWorks2012].[Person].[Address] WITH(NOLOCK)

Other Query Hints

ROWLOCK	It force the query to place the row-level lock instead of the page or table-level locking
TABLOCK	It force the query to place the table-level lock instead of the page or row-level locking
TABLOCKX	It force the query to place the exclusive lock on the entire table.
UPDLOCK	It force the query to place the update lock. This guarantees that the data will be same until the update will be finished.
XLOCK	It force the query to place the exclusive lock

Sample Usage

SELECT *  FROM [AdventureWorks2012].[Person].[Address] WITH(XLOCK)

1	SELECT * FROM [AdventureWorks2012].[Person].[Address] WITH(XLOCK)

You may want to read the below article about lock types in SQL Server.

“SQL Server Lock Types”

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux