Forcing index, order, and join hints and being careful

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.

By the way, this check is a part of our SQL Server Health Check service.

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

  1. SELECT occurrence AS Count -- Number of times of "join" hinting recorded since the restart
  2. FROM    sys.dm_exec_query_optimizer_info
  3. WHERE   counter = 'join hint'
  4. AND occurrence > 500;

Number of times of “order” hinting

  1. SELECT occurrence AS Count --Number of times of "order" hinting recorded since the restart
  2. FROM    sys.dm_exec_query_optimizer_info
  3. WHERE   counter = 'order hint'
  4. 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

  1. SELECT 
  2. querystats.plan_handle,
  3. querystats.query_hash,
  4. SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1, 
  5. (CASE querystats.statement_end_offset 
  6. WHEN -1 THEN DATALENGTH(sqltext.text) 
  7. ELSE querystats.statement_end_offset 
  8. END - querystats.statement_start_offset) / 2 + 1) AS sqltext, 
  9. querystats.execution_count,
  10. querystats.total_logical_reads,
  11. querystats.total_logical_writes,
  12. querystats.creation_time,
  13. querystats.last_execution_time,
  14. CAST(query_plan AS xml) as plan_xml
  15. FROM sys.dm_exec_query_stats as querystats
  16. CROSS APPLY sys.dm_exec_text_query_plan
  17. (querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset) 
  18. as textplan
  19. CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext 
  20. WHERE 
  21. textplan.query_plan like N'%ForcedIndex="1"%'
  22. and UPPER(sqltext.text) like N'%INDEX%'
  23. OPTION (RECOMPILE)

How to fix the problem?

  1. Talk to the developers and try to figure out why and how they’re using order, join and index hints.
  2. You might get better performance if you clean the code.
  3. Remember to keep track and monitor the runtime of queries that you have changed.

More information:

SQL Server Queries With Hints, Aaron Bertrand – MSSQLTIPS.
Microsoft – Query Hints (Transact-SQL).

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *