Easy Audit Index Changes

Comments: No Comments
Published on: December 27, 2019

In the previous article, I showed a somewhat elaborate method that could be used should there be a need to audit changes to your indexes.

As I learned many years ago in school, the elaborate or difficult method may not always be the best method. That said, we often need to learn the more elaborate methods in order to better appreciate the simpler, more efficient methods available. This article will help demonstrate a much better method.

Auditing

When looking for an easy method, one of the first technologies to try really should be Extended Events (xevents). Through the use of xevents, we have a large tool set at our disposal. Not only is the tool set rather large, it is also an extremely flexible multi-faceted tool set giving us greater potential to solve problems with simple tools.

Let’s look at how we can solve this problem through the use of xevents.

In the preceding xevent session, I am taking advantage of three events: object_created, object_altered, and object_deleted. These events will only trigger if an index definition is changed, added, or deleted. They will not trigger if the index is defragged (for instance) or the stats for the index happen to be updated (which is an issue for the stats_date method shown in this article). After deploying this session and then creating an index (shown in the following script), I can expect this session to capture a wealth of information.

In the preceding image, we can see that plenty of pertinent information has been captured. The image does not show the person who made the change, but that information is also captured with this session. Should I wish to focus this session to a specific database, that is definitely possible by removing the comment tags from the database_name predicate in the session script I have shared.

Put a bow on it

This article showed an easy 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 Extended Events articles? Check these out!

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

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

page 1 of 1

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

Welcome , today is Friday, January 24, 2020