Be careful with queries forcing index, order, and join hints

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

  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).

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.