Audit Index Changes

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: December 26, 2019

In the previous article, I showed a quick and dirty method to try and capture the indexes that may have been just created. We all know that in the hectic life of the DBA, it is easy enough to forget the indexes created more than just a few hours in the past.

In that article, I mentioned that the solution provided was far from a complete solution to the entire problem. A bigger part of the picture involves audits to know when and by whom indexes were created, altered or dropped.

Auditing

On many an occasion I have had clients request help with tracking the changes that have occurred to their indexes. The reasons vary slightly but generally boil down to two large categories: 1) to better understand who is making changes and 2) to help ensure proper indexes are not removed due to code promotions (various apps like to wipe out custom indexes which causes performance issues).

With that in mind, the following provides a solution that will help track indexes that have changed (index definition) or that have been added or removed during the audit interval. The interval can be set per your specific requirements. For this specific solution, I have historically just set this to run once a day. The solution is shown below.

To make this solution work, I use several tables and a stored procedure. The stored procedure is scheduled via an agent job and scheduled to your requirements. The tables will stage all of the indexes for all of the databases, then log which have changed or have been added or removed. From there, a simple query to the IndexDefChange table will help me learn of which indexes may deserve some attention.

Put a bow on it

This article showed a method to audit for index changes. The weak link in a solution such as this really boils down to the requirement that the solution needs to be in place before the index change occurs. Otherwise, it would all be for naught.

Interested in more back to basics articles? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the second article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

3 Comments - Leave a comment
  1. Jeff Moden says:

    Thanks for taking the time to post things like this, Jason. Not just at Christmas but throughout the year.

    It this case, would it not be better to create a DDL trigger rather than regularly running a job?

  2. […] Index Audit Changes – 26 December 2019 […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
December 2019
M T W T F S S
« Nov   Jan »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Welcome , today is Saturday, June 6, 2020