Keeping track of your SQL Server index “inventory”

Something’s always bugged me when it comes to managing indexes in SQL Server; keeping track of when I created or dropped them.

You can already check just about everything you could want to know about indexes, via handy DMV’s.  Need to figure out how much your indexes are being used? Take a look at sys.dm_db_index_usage_stats.  Or how about which indexes you might need to add to improve performance? Easy, just query sys.dm_db_missing_index_group_stats! You get the idea…

But what about the create date of an index, or when it was last rebuilt, or even when you dropped an index? For this you need to roll up your sleeves and roll your own solution.

How to skin this cat?

There are a few ways we can do this.

Scheduled job

The first way I used to do this was to just have a scheduled job running fairly regularly (like every 15 – 30 minutes) which checked for any changes to indexes in the database since the last time it ran. Any new ones would be added to the table, changes would be recorded, and dropped indexes would noted as such.  In fact, I used a version of Kimberly Tripp’s “improved sp_helpindex” to gather and store the index information in a nice format (i.e. with separate columns for included columns, compression, etc).

This is what the “guts” of the proc look like, just to give you an idea:

DECLARE TimelyTables CURSOR FAST_FORWARD FOR
		SELECT	DISTINCT 
				QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) AS TableName,
				st.[object_id]
		FROM	sys.tables AS st
		JOIN	sys.schemas AS ss ON st.[schema_id] = ss.[schema_id]
		WHERE	st.is_ms_shipped = 0;

	OPEN TimelyTables;
	FETCH NEXT FROM TimelyTables INTO @TableName, @ObjectId;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		TRUNCATE TABLE #tt_Indexes;

		RAISERROR('Table: %s (%i)',10,1,@TableName,@ObjectId);

		INSERT	#tt_Indexes
		EXEC	dbo.sp_helpindex2 @TableName; 

		IF @@ROWCOUNT > 0 
		BEGIN 
			INSERT	#index_history ([object_id], table_name, index_id, is_disabled, index_name, index_description, index_keys, included_columns, filter_definition, [compression]) 
			SELECT	@ObjectId, @TableName, t.index_id, t.is_disabled, t.index_name, t.index_description, t.index_keys, t.included_columns, t.filter_definition, t.[compression] 
			FROM	#tt_Indexes AS t;  
		END 

		FETCH NEXT FROM TimelyTables INTO @TableName, @ObjectId;
	END

Outside of this loop you can then do your MERGE comparison between “current” indexes, and what was recorded in the “index history” table from the previous run.

DDL trigger

DDL triggers are nothing new, but they can be very useful for auditing schema and login changes, etc. So it makes sense that this is ideally suited to creating an inventory of your indexes (if not all database objects that you might be interested in).  In fact, you can even quite easily create your own poor-man’s source control system, but that’s a different kettle of fish.

The idea behind DDL triggers is that you specify which ‘events‘ you want them to fire for at a database or server level.  In my case, working with Azure, I’m only interested in database level events.  In fact, in this case I’m only interested in recording the details of any CREATE INDEX, ALTER INDEX, or DROP INDEX statements.  Which looks like this:

CREATE TRIGGER trg_IndexChangeLog ON DATABASE 
    FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX 
AS 
...

Now we just insert the EVENTDATA data into our logging table, like so:

IF OBJECT_ID('dba.IndexChangeLog') IS NOT NULL 
BEGIN
	DECLARE @data XML; 
	SET @data = EVENTDATA(); 

	INSERT	dba.IndexChangeLog(eventtype, objectname, objecttype, sqlcommand, loginname) 
	VALUES	( 
			@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
			@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
			@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
			@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
			@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 
			); 
END;

EVENTDATA is basically an XML document that contains the important bits you might want to record for auditing purposes… like who ran what, when.  This is what’s available:

<EVENT_INSTANCE>
    <EventType>event </EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name </ServerName>
    <LoginName>login </LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Best of both worlds?

The above two methods are ones that I’ve already used successfully and have experience with – and each one offer pro’s and con’s.  I like the detail and “query-ability” of the data I get from the scheduled job solution, but the DDL trigger is simpler and doesn’t rely on scheduled jobs running.  The trigger also clearly has a big advantage in that it’s going to pick up the actual event as it happens, whereas the job may miss stuff between executions. This may or may not be important to you.

There may be a nice way of combining the two though.  Using Event Notifications, or DDL triggers to insert a payload onto a Service Broker queue, you could have a live and asynchronous system which gathers more detail than what’s available in the EVENTDATA.  I.e. you could have an activated procedure on the end of the SB queue which uses the index name to populate additional details, for example. Let me know in the comments if you give this a go, or if you can see any gotchas.

Cheers,
Dave