Normally, if I’m doing some general health checks – I’ll issue a trace with some designated conditions- depending on the application. For example, it may be looking at Duration of queries on a specific database. This will give me some hints at what to analyse.

Once identifying the relevant queries - I would investigate Execution Plans, statistics, fragmentation. All fairly straightforward stuff.

Recently I implemented a Trace, using the Scan Event. This is part of the Scans Event Category which produces event classes related to scanning tables and indexes.

The Scans Event category has two Event classes. Started Event Class and Stopped Event Class.

As the name suggests the Started Event Class is triggered when a scan of a table o index starts and the Stopped Event Class is triggered when the Scan stops. It is possible to combine the Scan event with the statements captured in one of the TSQL Event Category classes.

I found the Transaction ID column - ID of the transaction if the statement was run within a transaction – useful as I was able to run one trace focused on the Scan events and cross reference with output from the TSQL events.

Scans of tables aren’t always bad. In fact, sometimes the Query Optimizer will suggest to do a scan, if it decides that there aren’t sufficient unique values in a column. The cost-based optimizer will calculate resources required - and make the relevant decision.

But, if the DBA has designed indices based on potential queries – then Scans may suggest a number of problems such as: index design, statistics out of date, data sets have changed since the original design. The Scan Event is a useful tool to use.

Republished from http://www.sqlserver-dba.com.