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 breaks 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 mySQL Server?
The queries below use the Dynamic Management Views (DMVs) 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
FROM sys.dm_exec_query_optimizer_info
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
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'order hint'
AND occurrence > 500;Queries in the execution plan cache using an index hint
You can easily find queries in the execution plan cache using an index hint using the following query:
SELECT querystats.plan_handle
,querystats.query_hash
,SUBSTRING(sqltext.TEXT, (querystats.statement_start_offset / 2) + 1, (
CASE querystats.statement_end_offset
WHEN - 1
THEN DATALENGTH(sqltext.TEXT)
ELSE querystats.statement_end_offset
END - querystats.statement_start_offset
) / 2 + 1) AS sqltext
,querystats.execution_count
,querystats.total_logical_reads
,querystats.total_logical_writes
,querystats.creation_time
,querystats.last_execution_time
,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) AS textplan
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
WHERE textplan.query_plan LIKE N'%ForcedIndex="1"%'
AND UPPER(sqltext.TEXT) LIKE N'%INDEX%'
OPTION (RECOMPILE)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.
