We are pleased to introduce a powerful new feature in the AimBetter monitor that significantly helps administrators identify queries with execution plans that may require attention.
AimBetter users are already familiar with the platform’s real-time ability to detect missing or corrupted indexes and flag queries that should be investigated.
This new enhancement greatly expands those capabilities by monitoring six additional execution plan conditions identified by the SQL Server Optimizer. The feature now detects and highlights these conditions, as illustrated in the image below, with a brief explanation of each condition provided afterward.
As shown, a visual indicator appears in the top-level view whenever a running query plan contains one of these conditions, while the specific issue is clearly identified in the detailed query status information.
The example below demonstrates a complex SQL query generating a Clustered Index Scan alongside an operating system anti-virus scan diagnostic.
The six newly monitored conditions extend our existing missing-index detection capabilities:
In
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 that is stored in the tempdb database so it can re-use it later in the query.
HASH MATCH
Hash Match is a SQL Server strategy to join two tables together using the hash bucket and algorithm approach, allowing 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 an input parameter that is 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).
Summary
The most important thing to remember is that the actual plan is raising these flags in real-time. The query that is running at the time that these flags are seen is the one being executed. Showing on the console that the query is running with one or more of these conditions will make the administrator’s job much simpler.
