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;

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.