Easy Audit Index Changes

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.

USE master;
GO
-- Create the Event Session
IF EXISTS
(
SELECT
*
FROM sys.server_event_sessions
WHERE name = 'IdxObjectAudit'
)
DROP EVENT SESSION IdxObjectAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [IdxObjectAudit]
ON SERVER
ADD EVENT sqlserver.object_altered
(SET
 collect_database_name = (1)
 ACTION
 (
 sqlserver.sql_text
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.client_hostname
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.database_id
   , sqlserver.database_name
   , sqlserver.username
   , sqlserver.session_nt_username
   , sqlserver.client_app_name
   , sqlserver.session_id
   , sqlserver.context_info
   , sqlserver.client_connection_id
 )
WHERE object_type = 'INDEX'
----AND database_name = 'AdventureWorks2014'
)
  , ADD EVENT sqlserver.object_deleted
(SET
 collect_database_name = (1)
 ACTION
 (
 sqlserver.sql_text
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.client_hostname
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.database_id
   , sqlserver.database_name
   , sqlserver.username
   , sqlserver.session_nt_username
   , sqlserver.client_app_name
   , sqlserver.session_id
   , sqlserver.context_info
   , sqlserver.client_connection_id
 )
WHERE object_type = 'INDEX'
----AND database_name = 'AdventureWorks2014'
)
  , ADD EVENT sqlserver.object_created
(SET
 collect_database_name = (1)
 ACTION
 (
 sqlserver.sql_text
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.client_hostname
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.database_id
   , sqlserver.database_name
   , sqlserver.username
   , sqlserver.session_nt_username
   , sqlserver.client_app_name
   , sqlserver.session_id
   , sqlserver.context_info
   , sqlserver.client_connection_id
 )
WHERE object_type = 'INDEX'
----AND database_name = 'AdventureWorks2014'
)
ADD TARGET package0.event_file
(SET filename = N'C:DatabaseXEIdxObjectAudit', max_file_size = (50), max_rollover_files = (6))
WITH
(
MAX_MEMORY = 4090KB
  , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 4 SECONDS
  , MAX_EVENT_SIZE = 2048KB
  , MEMORY_PARTITION_MODE = PER_NODE
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION IdxObjectAudit ON SERVER STATE = START;
GO

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.

CREATE NONCLUSTERED INDEX IX_TransInvent_ItemIDNumItems
ON [dbo].[tblTrans_Inventory] ([TransI_dtmDateTime])
INCLUDE ([Item_strItemId],[TransI_decActualNoOfItems])
WITH (FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

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.