The “Plan Improvement Recommendation” feature for MSSQL Servers is a valuable tool for database administrators seeking to optimize performance. By identifying inefficient query plans in real-time and offering the specific condition that can be improved, it enables prompt resolution of non-optimal query performance, enhancing the overall user experience.
This feature complements the “Missing Index Recommendation” feature by highlighting queries that can be easily optimized. It is available for both currently running activities (live queries) and historical database activity, retaining information for up to 30 days.
When the “Plan Improvement” icon is hovered over, the Optimizer identifies and displays the names of certain conditions, making it easier for administrators to understand and address performance issues, as illustrated below.
The condition to be improved is also displayed in the query’s details. In the following image, there is a query that has a “Clustered Index” condition for plan improvement in addition to an index recommendation. This specific query also has an alert about lack of memory, which can be an important factor in performance.
The filtering query options by “Plan Improvement” or “Missing Index,” helps in the understanding of where to focus and how to improve the database system.
Here is a brief description of the conditions that may be identified by the Optimizer:
TABLE SCAN
A table scan means that every column in every row in a table is being read to find matches for the query’s conditions. If a WHERE condition exists, only those rows that satisfy it are returned.
INDEX SCAN
Index scan means that every column in every row in an index to the table is being read to find matches for the query’s conditions.
INDEX SPOOL
Index spool basically means that the optimizer calculated that it needs to build its own index to satisfy the query because it cannot find a suitable one.
TABLE SPOOL
Table spool means SQL scans the input and places a copy of each row in a hidden spool table stored in the tempdb database so it can be reused later in the query.
HASH MATCH
Hash Match is an SQL Server strategy for joining two tables using the hash bucket and algorithm approach. It allows SQL Server to efficiently perform the required join, union, or aggregation.
SORT
If included in the query, the SORT operator forces sorting all retrieved rows in ascending or descending order.
INDEX SCAN / SEEK WITH CONVERT_IMPLICIT
This happens because the input parameter being used in the WHERE/JOIN clause is of a different type from the actual data type (for example, WHERE is looking to match a VARCHAR with NVARCHAR).
Finally, it’s important to understand that the conditions presented are flagged in real time by the actual plan being executed. Showing on the console that the query is running with one or more of these conditions will shed light into the database activity and make the administrator’s job much simpler.
