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.
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.
Read the following article for 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“.
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
WHERE PersonID IS NOT NULL
SELECT cte.CustomerID, cte.PersonID, cte.StoreID
JOIN Sales.Customer AS e
ON cte.PersonID = e.CustomerID
--Query using CTE and using the MAXRECURSION query hint
SELECT CustomerID, PersonID, StoreID
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.
You can use MAXDOP Query hint as follows.
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
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.
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.
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.
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|
SELECT * FROM [AdventureWorks2012].[Person].[Address] WITH(XLOCK)
You may want to read the below article about lock types in SQL Server.