SSMS v22 Query Hint Recommendation Tool: A New Era of Query Optimization

Introduction

For years, performance tuning in SQL Server has meant digging through execution plans, adding indexes, or rewriting queries to coax the optimizer into better behavior. With SQL Server Management Studio (SSMS) v22 Preview 3, Microsoft is introducing something new: the Query Hint Recommendation Tool.

This feature lets you quickly experiment with optimizer hints, compare performance outcomes, and apply improvements without restructuring your entire T-SQL. It’s not a silver bullet, but it can speed up tuning work, especially in environments where developer time is limited.

---

Getting Started with the Query Hint Recommendation Tool

To launch the tool in SSMS v22:

1. Highlight the query you want to analyze.
2. Go to Tools → Query Hint Recommendation Tool.
3. A pane will slide out on the right side of your screen.

The Maximum Tuning Time defaults to 300 seconds. That value controls how long SSMS will spend trying different combinations of hints. If your baseline query already runs for several seconds or minutes, increasing this value gives the tool more breathing room to find improvements.

Reference: Microsoft Learn – SSMS Documentation

---

How It Works Behind the Scenes

The tool executes your query multiple times behind the scenes, testing different hints in various combinations. If it finds options it doesn’t expect to improve performance, it skips them automatically.

Here’s an example of what you might see:

OPTION (MERGE JOIN, CONCAT UNION,
    USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'),
    USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

This sample tells the optimizer to:

• Favor merge joins over other join types,
• Allow parallel plans,
• Use concatenation unions, and
• Disable row goal behavior, which can lead to overly narrow estimates.

A simpler example could be:

OPTION (LOOP JOIN, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

You can right-click the suggestion in the tool’s pane and select Append Hint to Query, and SSMS will place the hint block at the end of your statement.

Reference: Microsoft Docs – Query Hints

---

Real Performance Gains in Action

When I tested the tool with a heavy reporting query, the unhinted version consistently ran around 22 seconds. After applying the suggested hints, the same query dropped to around 8 seconds. The execution plan showed it pushed to parallel execution and changed join strategies—without touching the T-SQL logic itself.

In another case, using just a single hint:

OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

The execution time improved even further. This hint alone let the optimizer take advantage of parallelism, shaving more time off without forcing a more complex hint strategy.

---

A Word of Caution About Query Hints

Hints can be powerful—but also dangerous if misused. Their effectiveness depends on:

• Hardware and CPU topology
• SQL Server edition and version
• Parameter sensitivity
• Differences between test and production workloads

A combination that works beautifully in a test environment can backfire in production. Always validate before deploying, and ideally pair this with Query Store to track performance regressions.

Reference: Query Store Documentation

---

Why This Matters for Teams

Not every organization has a full-time performance tuning specialist or the time to rewrite complex T-SQL. The Query Hint Recommendation Tool lowers the barrier.

• Hints can be applied and removed quickly.
• The impact is measurable through actual query runs.
• It encourages experimentation without long rewrite cycles.

For teams with limited resources, this can translate into faster wins and better query performance without deep optimizer expertise.

---

AI Assistance in SSMS

SSMS v22 also integrates with GitHub Copilot. Copilot can offer performance suggestions, but right now, they’re fairly simple. The Hint Recommendation Tool, however, provides something tangible and testable.

Used together, they can give both experienced DBAs and newer developers more leverage to troubleshoot slow queries quickly.

---

Workshop: From Novice to Expert

This step-by-step walkthrough will help you use the new tool effectively.

Step 1: Identify a Slow Query

Use Query Store or DMVs to find high-duration or resource-intensive queries.

SELECT TOP 5 
    qs.total_elapsed_time / qs.execution_count AS AvgElapsedMs,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgElapsedMs DESC;

Step 2: Run a Baseline

Execute the query without hints and document:

• Elapsed time
• CPU time
• Logical reads
• Execution plan details

Step 3: Launch the Tool

Highlight the query in SSMS and run the Query Hint Recommendation Tool. Extend the tuning time if the query is long-running.

Step 4: Review and Apply Suggestions

Example suggestion:

OPTION (MERGE JOIN, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

Append the hints and rerun the query.

Step 5: Compare Results

Check if elapsed time, CPU usage, and plan shape improved. If they did, note the change.

Step 6: Validate in Production

Deploy with standard release and monitoring practices. Use Query Store or Extended Events to confirm stability over time.

Step 7: Iterate and Fine-Tune

Try individual hints or combinations like:

• USE HINT('DISABLE_OPTIMIZER_ROWGOAL')
• USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')
• LOOP JOIN or MERGE JOIN

Document which options yield stable improvements.

---

Summary

The Query Hint Recommendation Tool in SSMS v22 is a practical step forward for performance tuning. It gives DBAs and developers a structured way to experiment with hints and improve query performance without rewriting entire queries.

It won’t replace deep tuning expertise, but it bridges the gap between novice and expert by making tuning more approachable and measurable.

---

References

• Microsoft Learn – SQL Server Management Studio
• Microsoft Learn – Query Hints (Transact-SQL)
• Microsoft Learn – Query Store
• SQL Server 2025 Preview Notes and SSMS v22 Preview Documentation