Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
The Query Hint Recommendation tool is designed to assist users of SQL Server Management Studio (SSMS) with the task of exploring query hints to improve query performance. Regardless of your role, the Query Hint Recommendation tool can help you become more efficient in finding one or more query hints to improve the performance of a query.
Get started
The Query Hint Recommendation tool works with a single query in the active query editor window, executing the query with different query hints applied.
- Connect to a database in a query editor window.
- Select the entire query that you want the tool to evaluate.
- Open the Query Hint Recommendation tool using Tools > Query Hint Recommendation Tool.
- The tool window opens next to the query editor.
- The following input parameters are required:
- Maximum tuning time: The maximum amount of total time spent trying to find a beneficial query hint, in seconds. The default is 300 seconds (five minutes).
- Minimum improvement percentage: The minimum percentage improvement in query performance that the user is willing to accept. This parameter helps filter out less effective hints and uses the allocated time on hints that can deliver significant improvement. This parameter is specified as a percentage, with a default of 50%, or at least a 2x improvement in elapsed time.
- Select Start to initiate execution of the query with different hints.
| Key | Description |
|---|---|
| 1 | Title bar |
| 2 | Tool window controls, to pin or close the window |
| 3 | Required input parameters (maximum tuning time and minimum improvement percentage) |
| 4 | Folder selection button |
| 5 | Start button (initiate query hint exploration) |
| 6 | Advanced options to configure the space of query hints |
| 7 | Hint recommendation summary |
| 8 | Chart to visualize query duration improvement |
| 9 | Toggle to show or hide the chart |
| 10 | Tabular view of explored query hints |
Log file
The Query Hint Recommendation tool creates a log file that contains detailed information about the evaluation process, including errors, and can be useful for troubleshooting. The log file is stored in the user's %TEMP% folder by default, and can be changed using the folder selection button in the tool window.
Visualizing the tuning process
The Query Hint Recommendation tool includes a chart to provide a visual representation of the tuning process, allowing users to see the performance impact of different query hints in real-time. The chart includes a baseline performance measurement and then overlays the performance of increasingly helpful hints as they're found.
Tabular view of explored hints
The Query Hint Recommendation tool also provides a tabular view of all explored hints. This view includes key information such as the hint text, its performance impact, and whether it was skipped to save tuning time. This view allows users to quickly assess the effectiveness of different hints and make an informed decision about which hint to apply.
| Column | Description |
|---|---|
| Timestamp | The time at which the query was executed with the specified query hint. |
| Test ID | The unique identifier for the test run. |
| Elapsed Time (ms) | Query execution duration with the query hint applied, measured in milliseconds (ms). When the query isn't executed with the specific query hint, the reason for skipping the execution is included. |
| Gain % | The percentage improvement in query execution duration achieved by the query hint. |
| Hint | The query hint applied to the query. |
Skipping query hints
The Query Hint Recommendation tool uses the following criteria to improve tuning efficiency by skipping certain hints.
| Skip Reason | Description |
|---|---|
| Invalid query plan | A valid query plan isn't generated when the query hint is applied to the query. |
| Skipped (No improvement expected) | Using the query hint creates an execution plan that's the same as a previously applied hint, or isn't expected to help compare to the best query hint found so far. |
| Stopped (Exceed the time limit) | Query execution is terminated because its expected duration is higher than the best plan so far. |
Appending a hint to the selected query
The Query Hint Recommendation tool allows users to easily append a hint to the currently selected query in the editor window. This streamlines the process of applying a hint without manually modifying the query text.
- Confirm the query is highlighted in the editor window.
- Right-click on the query hint you want to apply.
- Select Append Hint to Query.
- The query hint is added to the query in the editor.
To persist the query hint beyond execution of the query in the editor, you can apply it as a Query Store hint. For more information, see Query Store hints.
Advanced options
The Query Hint Recommendation tool provides advanced options to customize the space of query hints. These options allow users to configure specific hints to explore, including:
- Plan Space Hints: Hints that control the operators and join order in the query.
- Cardinality Model Hints: Hints that modify the cardinality model used to estimate cardinalities in the execution plan.
- Miscellaneous: Other query hints such as controlling the degree of parallelism etc.
By default, the Query Hint Recommendation tool explores all of the predefined query hints and certain combinations of query hints. The advanced options panel allows the user to customize the space of hints to be explored, such as excluding hint combinations, certain hint categories, or specific hints.
