New Feature in AimBetter Query Console
We are pleased to tell you about a great new feature included in the AimBetter monitor that will tremendously assist administrators by alerting them to any query that is performing a query plan that may need attention.
Users of AimBetter console are already familiar with real-time ability to detect missing or corrupted indexes and flag the query as needing investigation.
Our new ability extends that scope dramatically to cover six additional conditions that Optimizer may identify. Our new feature now covers the conditions in the manner illustrated in this picture, and the individual plan conditions are briefly explained below.
As can be seen, there is a sign on the top-level view whenever a running plan has one of these conditions, and the exact condition is identified in the detailed description of the query status.
This example illustrates a simultaneous complex SQL query (producing a cluster index scan) and an OS anti-virus scan diagnostic.
The six new conditions monitored are an extension of our exiting detection of missing indexes.:
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.
