Audit Index Changes

This article shows 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.

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.