SQL Server Performance Tuning

Forcing Index, Order, and Join Hints and Being Careful

Updated August 4, 20264 min read

Query hints are a double-edged sword. A wide double-edged sword rests on two small wooden stands.

Why should you care about SQL Server query hints?

Hints in SQL queries are 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.

If 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 my SQL 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 has been used since the last SQL Server restart.

Number of times of “join” hinting

T-SQL
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

T-SQL

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:

T-SQL
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?

  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

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜