Why should you care about SQL Server query hints?
Hints in SQL queries is like a double-edged sword.
While you might find a better query plan based on the current data, you are taking away SQL Server’s ability to adapt to changes.
Every time enough data in one of the tables has changed, SQL Server, by default, reviews the query accessing that table to see if it can come up with a better plan.
It may not do that with queries on which you have forced its way.
Hints can also be dangerous.
In case of a query is using an index hint, what happens when you run it
If the index no longer exists? The code break and you get an error.
Believe in the SQL Server Query Optimizer; it typically selects the best execution plan for a query.
We recommend only using query hints as a last resort for experienced developers and DBAs.
How can I check my SQL Server?
The queries below use the DMV sys.dm_exec_query_optimizer_info you can use them to know how many times an order or join hint have been used since the last SQL Server restart.
Number of times of “join” hinting
SELECT occurrence AS Count -- Number of times of "join" hinting recorded since the restart
WHERE counter = 'join hint'
AND occurrence > 500;
Number of times of “order” hinting
SELECT occurrence AS Count --Number of times of "order" hinting recorded since the restart
WHERE counter = 'order hint'
AND occurrence > 500;
You can easily find queries in the Execution plan cache using an Index Hint using the following query:
Queries in the Execution plan cache using an Index Hint
SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1,
WHEN -1 THEN DATALENGTH(sqltext.text)
END - querystats.statement_start_offset) / 2 + 1) AS sqltext,
CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as querystats
CROSS APPLY sys.dm_exec_text_query_plan
(querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
textplan.query_plan like N'%ForcedIndex="1"%'
and UPPER(sqltext.text) like N'%INDEX%'
How to fix the problem?
- Talk to the developers and try to figure out why and how they’re using order, join and index hints.
- You might get better performance if you clean the code.
- Remember to keep track and monitor the runtime of queries that you have changed.
SQL Server Queries With Hints, Aaron Bertrand – MSSQLTIPS.
Microsoft – Query Hints (Transact-SQL).