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:
;WITH p as ( SELECT [path] = REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) + N'log.trc' FROM (SELECT REVERSE([path]) FROM sys.traces WHERE is_default = 1) s(p) ) SELECT t.TextData, t.LoginName, t.StartTime, t.DatabaseID, t.ObjectID, t.IndexID FROM sys.fn_trace_gettable(p.[path], DEFAULT) AS t WHERE t.EventClass = 164 AND t.DatabaseID > 4 AND t.ObjectID IS NOT NULL AND t.IndexID IS NOT NULL ORDER BY t.StartTime DESC;
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)', 'nvarchar(128)'), @event.value('(/EVENT_INSTANCE/SchemaName)', 'nvarchar(128)'), @event.value('(/EVENT_INSTANCE/TargetObjectName)', 'nvarchar(128)'), @event.value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(128)'), @event.value('(/EVENT_INSTANCE/TSQLCommand)', '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;
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.
Nice way to get the file name! Thank you again for the good things you do for us.
Thanks Anon Texas, I changed the code above to follow the better example for getting the trace file location here:
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'.
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.
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. 🙂
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?
My feeling is that between extended events and DDL triggers they're not going to invest any effort in improving trace data here…
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?