A query hint added years ago can outlive the slow plan it was meant to fix. Auditing query hints exposes those old instructions before they block a better plan after data, indexes, or SQL Server versions change. The work is to find them, measure their impact, and remove them one at a time.
Why Auditing Old Query Hints Pays Off
A hint is a deliberate override of the optimizer's choices. It can be useful when a tested workaround protects an important workload, but it also freezes an assumption about row counts, indexes, or join shape. A table grows, an index changes, or a compatibility-level upgrade improves cardinality estimates. The old override stays in source control and in the stored procedure, even when its reason has disappeared.
I keep a record of why each hint was added and when it was last measured. Without that, a hint becomes folklore. Which hint has a current benchmark proving it still helps? A review should prioritize high-execution statements and severe regressions, not assume every hint is harmful.
Start Auditing Query Hints in Module Definitions
sys.sql_modules holds the definitions of unencrypted procedures, functions, views, and triggers visible to your account. A text search is a discovery pass, not a parser. It can match a comment or string literal, and it can miss a dynamically built query. Search for the requested patterns, then inspect each result in its full statement context.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
OBJECT_NAME(m.object_id) AS object_name,
CASE WHEN m.definition LIKE N'%NOLOCK%' THEN 1 ELSE 0 END AS has_nolock,
CASE WHEN m.definition LIKE N'%FORCESEEK%' THEN 1 ELSE 0 END AS has_forceseek,
CASE WHEN m.definition LIKE N'%RECOMPILE%' THEN 1 ELSE 0 END AS has_recompile,
CASE WHEN m.definition LIKE N'%MAXDOP%' THEN 1 ELSE 0 END AS has_maxdop,
m.definition
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%NOLOCK%'
OR m.definition LIKE N'%INDEX%=%'
OR m.definition LIKE N'%INDEX (%'
OR m.definition LIKE N'%FORCESEEK%'
OR m.definition LIKE N'%OPTION%RECOMPILE%'
OR m.definition LIKE N'%MAXDOP%'
OR m.definition LIKE N'%LOOP JOIN%'
OR m.definition LIKE N'%HASH JOIN%'
OR m.definition LIKE N'%MERGE JOIN%';INDEX syntax permits spacing and parentheses, so no simple LIKE list is complete. Search source files and application-generated SQL as well. Encrypted modules cannot be inspected through this catalog. Record the object, statement, hint, and owner rather than deleting the first match you see.
Include Ad Hoc Text From Query Store
Modules are only part of the workload. Query Store holds query text for captured statements in the current database and aggregates runtime data across plans and intervals. Search query_sql_text for the same hint families, then join query, plan, and runtime views to rank by execution count. Query Store must be enabled and in a healthy read-write state for complete current coverage.
SELECT TOP (50) q.query_id,
SUM(rs.count_executions) AS executions,
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_duration,
qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'%NOLOCK%'
OR qt.query_sql_text LIKE N'%FORCESEEK%'
OR qt.query_sql_text LIKE N'%INDEX%=%'
OR qt.query_sql_text LIKE N'%RECOMPILE%'
OR qt.query_sql_text LIKE N'%MAXDOP%'
OR qt.query_sql_text LIKE N'%LOOP JOIN%'
OR qt.query_sql_text LIKE N'%HASH JOIN%'
OR qt.query_sql_text LIKE N'%MERGE JOIN%'
GROUP BY q.query_id, qt.query_sql_text
ORDER BY executions DESC;Add a bounded runtime interval when comparing a particular incident period. The example ranks all retained data, so its execution count reflects the Query Store retention window, not lifetime activity. A hint inside a comment still matches. Expect the audit queries themselves in the list, because their text holds the same words, and internal StatMan statistics queries too. Read the actual query before labeling it active tuning debt.
Understand What Each Hint Changes
NOLOCK permits dirty reads and other inconsistent results; it is a correctness decision, not free speed. An INDEX hint restricts access paths. FORCESEEK requires a seek, even when a scan would read less. OPTION (RECOMPILE) can improve parameter-specific plans but adds compilation cost and changes plan-cache behavior. MAXDOP caps parallel workers for that statement. LOOP, HASH, and MERGE join hints constrain join strategy and can also affect join order.
I have found a FORCESEEK that helped a small lookup but punished a later range report. The same text ran against a much larger slice of data after the product changed. One successful execution with the old parameter was not enough to keep the hint. Review the full parameter distribution, including empty, typical, and large result sets.
Build a Safe Comparison
Copy the query into a test environment with representative data and the same database compatibility level, indexes, statistics, and relevant settings. Keep the original text and plan. Remove one hint at a time in the test copy. Capture actual execution plans, logical reads, CPU, duration, spills, memory grants, and output rows for several parameter values. Compare correctness before speed; removing NOLOCK can intentionally change results.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run the approved original statement with a representative parameter.
-- Run a copy with one hint removed and the same parameter.
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;These commands are a measurement frame; substitute the real query in a safe test database. A warm-cache run and a cold-cache run answer different questions. Avoid clearing the shared production plan cache to make a benchmark look tidy. Query Store can supply longer-window evidence after a controlled rollout.
Remove One Constraint at a Time
If the no-hint version performs well across the parameter range, change one statement in a reviewed deployment. Keep the previous definition ready for rollback. Track its Query Store query ID and plans, while recognizing that changing text can produce a new ID. Watch p95 duration, CPU, reads, compile time, and regressions after deployment. Do not remove every hint in one release and leave no way to explain a new plan.
A hint can remain when the evidence supports it. Add a comment explaining the symptom, test cases, measurements, and review date. Revisit it after major data growth or version changes. Auditing query hints is successful when the team can tell which overrides are intentional and which ones have become fossils.
Rank by Business Reach as Well as Count
Execution count is an efficient first sorting key, but it is not the whole priority. A query run once in the monthly close can matter more than a cheap lookup run thousands of times. Add total CPU, duration, reads, and affected workflow to the audit record. Query Store aggregates can have several runtime rows for one plan and interval, so compute weighted averages from counts rather than averaging the average values equally.
Look for forced plans and Query Store hints too. They are outside the statement text and can keep a plan constraint in place after the source hint is removed. Check database-scoped configuration and plan guides where applicable. Otherwise a no-hint test can appear unchanged because another control is still active.
Document Each Decision From Auditing Query Hints
For each candidate, write the original reason if known, the tested parameters, the old and new plan IDs, the measured result, and the rollback action. A failed removal test is still valuable evidence. Keep the hint when it protects a known skewed parameter case and the alternatives regress. Schedule its next review after a meaningful data or version change. That turns a forgotten override into an explicit engineering choice.
Related reading on this blog: Applying Query Hints to Views and Avoid Join Hints: SQL in Sixty Seconds #172.
A query hint is not a permanent badge, it is a choice that must pass current measurements.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
