Index Create and Last Modified Dates in SQL Server

In many environments, it’s useful to know exactly when an index was created or modified.

Did that last code release help performance, or hurt it? It’s really helpful to know exactly when the code was deployed to prove that your change made something better. Or that you might need to roll it back.

It takes a little preparation to track changes to your indexes, but it’s easy to implement.

SQL Server doesn’t really track index create or modification date by default

I say “really”, because SQL Server’s default trace captures things like index create and alter commands. However, the default trace rolls over pretty quickly on most active servers, and it’s rare that you’re looking up the creation date for an index you created five minutes ago.

I think it’s fine that SQL Server doesn’t permanently store the creation date and modification date for most indexes, because not everyone wants this information – so why not make the default as lightweight as possible?

You can get create date and last modified date for the table – but that’s usually not enough

SQL Server stores when tables were created and modified in the sys.objects DMV. But most of us create and drop indexes long after the table creation time. And lots of things count as a modification in this context, including adding columns, or just rebuilding an index.

If the index is a Primary Key or Unique Index / Unique Constraint, you can get the create date

For a few indexes, you can find the creation date with a simple query – because these indexes get their own row in sys.objects. You can use a query like this to find when they were created:

But if you’re not looking for the create date of a Primary Key, unique index, or unique constraint, you’re out of luck.

Track index creation and modifications with a DDL Trigger

DDL triggers are the simplest way to keep track of when indexes are created and dropped. This is a very easy way to record the exact time indexes are created and modified.

There are a few things to know about DDL triggers, though.

DDL Triggers are tightly coupled with the executing code

Mostly, the folks who have a bad time with DDL triggers are DBAs who implement them in production without testing them extensively, and without talking to their developers. They create a trigger that works fine for themselves in a simple test, but may fail for someone else– commonly because of a security issue.

The bad news is that when the DDL trigger fails, it takes down the calling command as well. Not everyone is so happy when their table or index create suddenly fails in production with a weird error message.

To make sure you have a good time with DDL Triggers to track index changes:

1. Put them in every environment, including development / text
2. Treat them like any index related code change - check it into source code
3. Use change control
4. Talk to your coworkers – and maybe mention that logging the exact time when DDL occurs is useful for troubleshooting and showing positive improvements, instead of making it all about policing

Sample DDL trigger code

Here’s some simple, database level code. Make sure to check out the links at the end of this article for more samples elsewhere, too!

This sample works just for one database. (Scroll to the bottom of the post for more samples on other blogs!)

This code sample…

• Creates a schema and a table to track create and alter for tables and indexes. I did this because dropping a table is going to drop all the indexes as well.
• Uses row compression by default on the table. Maybe you don’t want the CPU hit on row compression, or it doesn’t work on your version/edition of SQL Server, though – feel free to change it.
• Stores target_object_name - if you’re creating or modifying an index, this is the object name. If you’re creating or modifying a table, this is null.
• Stores object_name - if you’re creating or modifying an index, this is the index name
• Stores new_object_name - this gets populated if you use sp_rename on a table or index
• Has the DDL Trigger execute as dbo, and uses ORIGINAL_LOGIN() to get the login of the caller. This is to reduce risk of the DDL trigger making a schema change fail because it’s being run by an account that doesn’t have permission to write to the table you’re using for logging.

Here’s a gist of the sample code:

Want to see the DDL Trigger fail?

Just run this code after creating the table, then the trigger:

You can’t get around that error by putting a try/catch in the DDL trigger. You going to have to ditch the DDL trigger to drop that table.

An alternative: Event Notifications

If you don’t like the “tight coupling” of a DDL trigger, you can use Event Notifications to record the exact same events.

The downside is that Event Notifications are a bit more complex to set up and manage. Event Notifications use Service Broker, so you need to become comfortable with the different parts of Service Broker, and managing having it on. It’s not bad, it’s just a bit more learning to do, and it’s another feature in use that you need to keep an eye out for when it comes to patching and management.

If you go the Event Notifications route, I highly recommend that you configure the Event Notifications in their own database, and record all the index changes there. That way, if you’re troubleshooting something and you want to turn off the logging temporarily, you can disable Service Broker just on that database where you’re doing the Event Notifications. (Enabling Service Broker requires an exclusive database lock.)

More code samples

Aaron Bertrand wrote a similar post on capturing index operations in this great article. His sample DDL trigger is for all user databases and for create and alter events only, because he was looking for any index rebuild operations per the question.

Aaron wrote another post on using DDL triggers to capture even more types of changes here.