When Was That Index Modified?

Kendra Little looks at index creation and modification dates:

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?

That said, Kendra has several methods for answering the question of when a particular index was created.

Related Posts

Query Store Indexes

Arthur Daniels shows what you can learn from the indexes on Query Store tables: It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. […]

Read More

Gotchas When Indexing Partitioned Tables

Andrew Pruski gives us a couple of considerations when creating indexes on partitioned tables in SQL Server: Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not. But also […]

Read More


December 2016
« Nov Jan »