Expose index creation date in sys.indexes
Index creation date is very important for DBAs for many tasks including
1) DBA created indexes and should be reverted. There's no change management in place
2) Indexes created by commercial application that the DBA needs to track
Upvotes: 23<=-=Oct 19 2015 2:55PM=-=>
We recently had discussion about dropping unused indexes and during the discussion, a question came up about few indexes on whether they were created recently or did they exists for a while and I have no easy way to answer that question.
There is almost 4 entries of the same topic :
1 - Expose index creation date in sys.indexes
2 - Report Index creation time and modification time
3 - Add create_date and modify_date to sys.indexes
4 - Add sys.indexes create_date and modify_date fields
Adding the creation and last rebuild date would be a very important feature in the implementation of indexing campaigns. This would make it possible to decide whether an index should be deleted or not. Currently the view sys. DM _ db_index_usage_stats allows to know whether an index is used or not (User_seeks, User_scans, User_lookups...). But as we do not know if it was created recently or exists since a long time, this information is, in fact, of no value!