SSMS v22 Query Hint Recommendation Tool: A Practical Guide for SQL Server DBAs

---

Performance tuning in SQL Server has traditionally meant digging through execution plans, adding indexes, or rewriting queries to nudge the optimizer toward better behavior. SSMS v22 introduced something new to the toolkit: the Query Hint Recommendation Tool. It gives DBAs and developers a structured way to experiment with optimizer hints, compare performance outcomes, and apply improvements without restructuring T-SQL.

This is not a replacement for deep tuning expertise. Query hints have real risks and their effectiveness depends heavily on hardware, workload, and parameter distribution. But for teams with limited resources or time, the tool lowers the barrier to experimentation and makes hint testing measurable rather than guesswork.

1 Getting Started: How to Launch the Tool Beginner

The Query Hint Recommendation Tool is available in SSMS v22 and later. To use it, open a query window in SSMS, highlight the query you want to analyze, and go to Tools → Query Hint Recommendation Tool. A pane slides out on the right side of the screen.

The key setting to understand before running it is the Maximum Tuning Time, which defaults to 300 seconds. This controls how long SSMS will spend executing the query with different hint combinations to find improvements. If the query you are analyzing already takes several seconds to run, increase this value to give the tool enough time to test multiple combinations meaningfully. A query that runs for 30 seconds needs more than 300 seconds of tuning time if the tool is going to try five or six combinations.

2 How It Works Behind the Scenes Beginner

The tool executes the query multiple times, testing different hint combinations in sequence. For each combination it measures actual elapsed time and compares it to the baseline run without hints. Combinations that show no improvement are skipped automatically. The tool surfaces the combinations that produced the best measured results.

Once a suggestion appears in the tool pane, right-clicking it and selecting Append Hint to Query places the hint block at the end of the query in the editor. From that point you can run the hinted version yourself, compare execution plans, and decide whether to apply the hint permanently.

The tool works entirely within SSMS and does not require any changes to the database or any stored objects. The hints it tests are standard T-SQL query hints documented by Microsoft. Nothing proprietary is being added to the query.

3 Example Hints and What They Do Intermediate

The tool generates standard T-SQL hint syntax. Understanding what the common suggestions mean helps you evaluate whether to trust and apply them.

-- Example 1: Complex hint combination
OPTION (MERGE JOIN,
        CONCAT UNION,
        USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'),
        USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

-- MERGE JOIN: forces the optimizer to use merge join strategy
-- CONCAT UNION: uses concatenation for UNION operations
-- ENABLE_PARALLEL_PLAN_PREFERENCE: allows parallel execution plans
-- DISABLE_OPTIMIZER_ROWGOAL: removes row goal optimization
--   (row goal is where the optimizer assumes TOP or EXISTS only
--    needs the first few rows, which can hurt full-scan scenarios)

-- Example 2: Simpler single-hint suggestion
OPTION (LOOP JOIN, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

-- Example 3: Parallelism hint alone
OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

Hint	What It Does	When It Helps
MERGE JOIN	Forces merge join over nested loop or hash join	Large sorted inputs, joining on indexed columns
LOOP JOIN	Forces nested loop join	Small outer input, highly selective inner lookup
ENABLE_PARALLEL_PLAN_PREFERENCE	Encourages the optimizer to choose parallel plans	CPU-heavy operations on large data sets
DISABLE_OPTIMIZER_ROWGOAL	Removes row goal optimization from the plan	Queries returning full result sets where row goal causes scans to be underestimated
RECOMPILE	Forces recompile on every execution	Parameter sniffing problems, but adds CPU overhead per execution

4 Real Performance Impact Beginner

The tool can produce meaningful improvements on the right queries. A heavy reporting query running consistently around 22 seconds dropped to approximately 8 seconds after the tool suggested a parallel plan preference hint combined with a join strategy change. The execution plan shifted to a parallel strategy and changed join operators without any modification to the T-SQL logic itself.

In another case the single hint OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) alone produced a further improvement by allowing the optimizer to use parallelism it had previously avoided. Sometimes one hint is more effective than a complex combination.

The results are query-specific. The same hint that cuts 22 seconds to 8 seconds on one query may have no effect or a negative effect on a different query running on different data. Always measure rather than assume, and always validate in a representative environment before deploying to production.

5 The Risks of Query Hints Intermediate

Query hints override the optimizer’s decisions. The optimizer normally makes good decisions based on statistics, hardware, and workload. When you force a hint you are telling it to ignore some of that context. This can produce excellent results in testing and poor results in production for several reasons.

• Hardware differences. A parallel plan hint that works well on a 32-core production server may behave differently on a 4-core test server, or vice versa.
• Parameter sensitivity. A hint optimized for one set of parameter values may produce a bad plan for other values. Test with the full range of parameters your queries actually receive.
• Data distribution changes. As data grows and distributions change, a forced hint that was optimal six months ago may no longer be optimal. Hints do not adapt the way the optimizer does.
• Edition differences. Some parallel plan behavior differs between Standard and Enterprise Edition. A hint validated on Enterprise may behave differently on Standard.

6 Step-by-Step Workshop: From Baseline to Validated Improvement Advanced

This workshop covers the complete workflow from identifying a slow query through validating a hint improvement in production.

Step 1: Find a candidate query

Use Query Store or DMVs to identify queries with high average duration or resource consumption. These are your best candidates for hint testing because the potential gain is largest where the current cost is highest.

-- Find top 5 queries by average elapsed time using DMVs
SELECT TOP 5
    qs.total_elapsed_time / qs.execution_count  AS AvgElapsedUs,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    LEFT(qt.text, 300)                          AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgElapsedUs DESC;

-- Or use Query Store for richer history
SELECT TOP 10
    qsqt.query_sql_text,
    ROUND(qsrs.avg_duration / 1000.0, 2)        AS AvgDurationMs,
    ROUND(qsrs.avg_logical_io_reads, 0)         AS AvgLogicalReads,
    qsrs.count_executions
FROM sys.query_store_runtime_stats              qsrs
JOIN sys.query_store_plan                       qsp  ON qsp.plan_id        = qsrs.plan_id
JOIN sys.query_store_query                      qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text                 qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsrs.last_execution_time >= DATEADD(DAY, -7, GETDATE())
ORDER BY qsrs.avg_duration DESC;

Step 2: Capture the baseline

Run the query without any hints and record the metrics. You need these numbers to evaluate whether the hint actually helped.

-- Capture baseline metrics before hint testing
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run your query here (no hints)

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Record from the Messages tab:
-- SQL Server Execution Times: CPU time, elapsed time
-- Table 'YourTable': logical reads, physical reads

-- Also capture the actual execution plan (Ctrl+M in SSMS)
-- Save it as a .sqlplan file for before/after comparison

Step 3: Run the Query Hint Recommendation Tool

Highlight the query in the editor and go to Tools → Query Hint Recommendation Tool. Set the Maximum Tuning Time to at least three times the baseline query duration. For a 10 second query set the tuning time to at least 60 seconds to allow meaningful comparison runs.

Step 4: Review and apply suggestions

The tool pane shows suggested hint combinations ranked by measured improvement. Review what each suggestion is recommending before applying it. Right-click the best suggestion and select Append Hint to Query. The hint block appears at the end of your query.

-- Example of what the tool might append:
-- Your original query...
SELECT CustomerID, OrderDate, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID, OrderDate
OPTION (MERGE JOIN, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
-- Tool appended the OPTION clause above

Step 5: Compare results

-- Run with hints and capture the same metrics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Hinted query here

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Compare:
-- Did elapsed time decrease?
-- Did CPU time decrease or increase significantly?
-- Did logical reads change?
-- Does the execution plan show a different strategy?
-- Are estimated vs actual rows still close?

Step 6: Test with multiple parameter values

If the query accepts parameters, test the hint with the full range of values it receives in production. A hint that works for one customer ID may not work for another where data distribution is different.

-- Test the hinted version with multiple parameter values
EXEC YourStoredProcedure @CustomerID = 11000;  -- small customer
EXEC YourStoredProcedure @CustomerID = 11001;  -- large customer
EXEC YourStoredProcedure @CustomerID = 99999;  -- edge case

-- For each run capture elapsed time and confirm the hint
-- consistently helps rather than just for one parameter value

Step 7: Validate in production with Query Store

Deploy the hinted query through your normal release process. Enable Query Store on the database if not already enabled and monitor the query over the following week. Compare runtime statistics from the days before and after deployment.

-- After deployment: check if the hinted query is stable in production
SELECT
    qsp.plan_id,
    qsp.is_forced_plan,
    qsrs.count_executions,
    ROUND(qsrs.avg_duration       / 1000.0, 2) AS AvgDurationMs,
    ROUND(qsrs.avg_logical_io_reads, 0)        AS AvgLogicalReads,
    qsrs.last_execution_time
FROM sys.query_store_runtime_stats              qsrs
JOIN sys.query_store_plan                       qsp  ON qsp.plan_id        = qsrs.plan_id
JOIN sys.query_store_query                      qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text                 qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsqt.query_sql_text LIKE '%YourQueryText%'
ORDER BY qsrs.last_execution_time DESC;

References

• Microsoft Learn: SQL Server Management Studio (SSMS)
• Microsoft Docs: Query Hints (Transact-SQL)
• Microsoft Docs: Query Store Hints
• Microsoft Docs: Monitoring Performance by Using the Query Store
• SQLYARD: A Beginner-to-Advanced Guide to Query Store in SQL Server
• SQLYARD: Query Store Complete Screen-by-Screen Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide