New Feature in AimBetter Query Console

We are pleased to be able to tell you about a great new feature which is now included in the AimBetter monitor that will tremendously assist administrators by alerting to any query that is performing a query plan that may need attention.

Users of AimBetter console are already familiar with its ability in real time to detect missing or corrupted indexes and to 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.

In this example, we are illustrating a simultaneous complex SQL query (producing a cluster index scan) and OS anti virus scan  diagnostic.

The six new conditions monitored are an extension of our exiting detection of missing indexes.:

TABLE SCAN

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 in order 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 strategy used by SQL Server 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 of all retrieved rows in ascending or descending order.

INDEX SCAN / SEEK WITH CONVERT_IMPLICIT

This happen 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 these flags are being raised by the actual plan 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 job of the administrator much simpler.