January 31, 2011 | SQL Server

Capturing index operations using a DDL trigger

Today on twitter the following question came up on the #sqlhelp hash tag, from DaveH0ward:

Is there a DMV that can tell me the last time an index was rebuilt? SQL 2008

My initial response:

I don't believe so, you'd have to be monitoring for that … perhaps a DDL trigger capturing ALTER_INDEX?

Then I remembered that the default trace in SQL Server (as long as it is enabled) will capture these events. My follow-up response:

You can get it from the default trace, blog post forthcoming

So here is that blog post. Let's see how useful the default trace actually is.  I've created a simple table with a clustered index, and run several commands against it, including ALTER INDEX … REORGANIZE and ALTER INDEX … REBUILD.  Then I wanted to check for event 164 [Object:Altered] in the default trace.  So I used the following query to get the default trace id, then determine where the default trace file is located, then finally query the trace data in table form:

 DECLARE @filename NVARCHAR(4000);
 
SELECT @filename = REVERSE(SUBSTRING(REVERSE([path]),
    CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
	FROM sys.traces WHERE is_default = 1; 
SELECT TextData, LoginName, StartTime, DatabaseID, ObjectID, IndexID
   FROM sys.fn_trace_gettable(@filename, DEFAULT)
   WHERE EventClass = 164
   AND DatabaseID > 4
   AND ObjectID IS NOT NULL
   AND IndexID IS NOT NULL
   ORDER BY StartTime DESC;

Results:

 

It appears that with the default trace, you can tell who altered your indexes and when, but you can't tell what they did exactly, since TextData is NULL (did they defragment the index, or rebuild it?).  And you will be drowned by other activity if you expand the search to also include create object events (e.g. CREATE INDEX, including WITH DROP_EXISTING, which can often implicate a simple rebuild using different syntax).  Now I'm leaning toward my original response as a more robust solution, as I can capture surrounding information as well (such as the actual SQL that was executed).

So first, let's create a table somewhere that can log the information we're interested in (I often have a utility database called "dba"):

 CREATE DATABASE dba;
GO
USE dba;
GO
CREATE TABLE dbo.IndexEvents
(
   EventDate    DATETIME,
   HostName     NVARCHAR(128),
   LoginName    NVARCHAR(128),
   AppName      NVARCHAR(128),
   DatabaseName NVARCHAR(255),
   SchemaName   NVARCHAR(128),
   ObjectName   NVARCHAR(255),
   IndexName    NVARCHAR(255),
   OriginalTSQL NVARCHAR(MAX)
);
GO

Now, we need to create a DDL trigger that is going to capture create and alter index events (it is nice that we can get more granular than object here).  I am going to create the trigger at the server level, and then have a conditional inside that does not bother logging events in system databases.  You can do this differently, of course.  You can change the conditional to exclude or include whatever user databases you wish.  Or you can create the trigger at the database scope (just change "ON ALL SERVER" to "ON DATABASE"), creating it in each database you want to monitor – and if you want it to be included by default in all new databases (not restored or attached databases), you can add the trigger to the model database.  Note that to capture the index name, the property from EVENTDATA() is called "ObjectName" and the property for the name of the table / view is actually "TargetObjectName"…

 CREATE TRIGGER [IndexEventAudit]
   ON ALL SERVER
   FOR ALTER_INDEX, CREATE_INDEX
AS
BEGIN
   SET NOCOUNT ON;
 
   IF DB_ID() > 4
   BEGIN
 
       DECLARE
           @event  XML,
           @host   NVARCHAR(128),
           @login  NVARCHAR(128),
           @app    NVARCHAR(128),
           @spid   INT;
 
       SELECT
           @spid   = @@SPID,
           @event  = EVENTDATA();
 
       SELECT
           @login  = MAX(login_name),
           @host   = MAX([host_name]),
           @app    = MAX([program_name])
       FROM
           sys.dm_exec_sessions
       WHERE
           session_id = @spid;
 
       INSERT dba.dbo.IndexEvents
       (
           EventDate,
           HostName,
           LoginName,
           AppName,
           DatabaseName,
           SchemaName,
           ObjectName,
           IndexName,
           OriginalTSQL
       )
       SELECT
           CURRENT_TIMESTAMP,
           @host,
           @login,
           @app,
           @event.value('(/EVENT_INSTANCE/DatabaseName)[1]',     'nvarchar(128)'),
           @event.value('(/EVENT_INSTANCE/SchemaName)[1]',       'nvarchar(128)'),
           @event.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(128)'),
           @event.value('(/EVENT_INSTANCE/ObjectName)[1]',       'nvarchar(128)'),
           @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]',      'nvarchar(MAX)');
   END
END
GO

Now if I repeat my index commands, I can then run a query to see that they have all been captured:

 SELECT
   EventDate, 
   [Index] = DatabaseName + '.' 
       + SchemaName + '.' 
       + ObjectName + '.' 
       + IndexName,
   OriginalTSQL
FROM dba.dbo.IndexEvents
ORDER BY EventDate DESC;

Results:

 

Of course it can be very easy to filter against the OriginalTSQL column for rebuild vs. defrag operations, and also to weed out create operations.  In fact you could do that by adding a column that indicated the index operation type, populating it by first parsing the EVENTDATA() within the trigger.  I'll leave that as an exercise for the reader.

Note that if you use the old-style DBCC index operations (DBCC DBREINDEX, DBCC INDEXDEFRAG), these events will not be captured by either DDL triggers or by the default trace. Since SQL Server 2005 first introduced the default trace, it has captured DBCC events using EventClass 116 (Audit DBCC Event) but, in SQL Server 2008 R2 at least, these specific events are no longer captured – I assume because the commands should no longer be used in favor of their ALTER INDEX replacements.  The default trace will, for example, show EventClass 115 for a DBCC CHECKDB command.

 

8 comments on this post

    • David Howard - February 1, 2011, 6:43 PM

      Nice post, I originally asked the question because we were trying to verify a claim that some indexes had been recently rebuilt.  Your first query against the default trace showed that at least the index was altered in the expected time frame, but like you said, it doesn't show the operation that took place.  It's a shame that the TextData field is null, especially when that same field is populated correctly in a user defined trace when an index is rebuilt. Think this should be a MS connect item?

    • AaronBertrand - February 1, 2011, 6:55 PM

      My feeling is that between extended events and DDL triggers they're not going to invest any effort in improving trace data here…

    • Claire - February 4, 2011, 12:17 AM

      Very cool. Just wondering–what are the benefits of having a conditional inside that does not bother logging events in system databases? Is it just faster?

    • AaronBertrand - February 4, 2011, 12:19 AM

      That is mostly to prevent junk from tempdb filling up your log table.  I guess if you are concerned about folks rebuilding indexes on tables in system databases, you should leave the conditional out.  🙂

    • unclebiguns - February 17, 2011, 7:01 AM

      I wonder if the BigIntData1 Column tells you anything.  It has data in it, but I can't find any documentation as to what it means.

    • Anon Texas - May 6, 2016, 4:30 PM

      Great information.
      One thing if I can add. In our case, the default trace file name that can be queried end up having serial numbers on it. Like 'Log_3426.trc'.
      And sys.fn_trace_gettable doesn't seem to expect it. It only reads data from the last trace.
      So I always have to manipulate the file name to remove the number part, and make it 'Log.trc'.

    • AaronBertrand - May 6, 2016, 4:57 PM

      Thanks Anon Texas, I changed the code above to follow the better example for getting the trace file location here:
      /blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx

    • Anon Texas - May 6, 2016, 6:19 PM

      Nice way to get the file name! Thank you again for the good things you do for us.

Comments are closed.