Statistics in SQL Server, love them or hate them, are essential to good performance. Statistics help to drive execution plan selection and thus can have quite a profound impact. If you have bad stats, you can end up with a very poor plan. Conversely, good stats can help the optimizer in selecting an optimal and high performing plan.
Something that was recently requested of me was a means to help audit these statistics in SQL Server. Why? Well, as it turns out, it could prove very useful in determining who, why, and when statistics might have been changed.
One would think there should be no need to audit the statistics in SQL Server. Alas, you would be gravely wrong. I have seen so many weird things that it makes absolute sense to audit changes. You may be surprised to learn that there is a DBA on staff that is adding, dropping, changing, or updating stats out of cycle without communicating the changes.
How do we go about auditing these changes? Easy Peezee! We need only to employ the use and power of Extended Events (XEvents).
For the sake of posterity, I am also adding this to the MASSIVE collection of Extended Events articles.
XEvents to Audit your Statistics
Here comes the tricky part. Auditing your statistics is not quite as easy as auditing other traditional objects (like tables or indexes). Wait, I just mentioned indexes and stats are tightly coupled to indexes, right? True! Indexes and stats are very tightly coupled. So much so, that stats and indexes use the same identity increment for their id.
So, knowing this object in SQL Server is not treated the same as other objects (like views, functions, or tables), we have our first clue. This clue is that events such as object_altered, object_created, object_deleted are quite enough for this kind of audit. Typically, these events are a good place to start for auditing object changes.
Here is a killer part about those events, they have an object_type specific to statistics. This helps lead to a bit of the mystery in how to audit changes to statistics. It is perfectly acceptable to presume that if there is an object_type, then the XEvent will capture desired events related to that object_type. This just isn’t entirely accurate when dealing with statistics.
Statistics Audits event
Since we can’t capture everything with those three events, we clearly need to find a different event. Thankfully, there is another event that can help us audit stats changes. Sadly, the name doesn’t make it very obvious. The event we will need to add is auto_stats. This event will capture statistics updates and give us various stages ( status ) for the stats update process.
There is a potential event in 2019 that I can’t get to produce anything. That event has no real payload attached to it at the moment. This new event is called synchronized_statistics_update_triggered and should probably just be ignored until a future update to it.
Now, let’s put those events together into a session and look at some potential results.
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'StatsChange' )
DROP EVENT SESSION StatsChange
ON SERVER;
GO
CREATE EVENT SESSION [StatsChange] ON SERVER
ADD EVENT sqlserver.auto_stats (
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 ([database_name]=N'DBA') )
, 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 ( [database_name]=N'DBA') )
, 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]='STATISTICS'
AND [database_name]=N'DBA') )
, 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]='STATISTICS'
AND [database_name]=N'DBA') )
ADD TARGET package0.ring_buffer
(SET max_events_limit = ( 666 )
, max_memory = ( 65536 )
, occurrence_number = ( 3 )
)
,
ADD TARGET package0.event_file
(SET filename = N'StatsChange'
, max_file_size = ( 50 )
, max_rollover_files = ( 6 )
)
WITH ( MAX_MEMORY = 4090 KB
,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 4 SECONDS
, MAX_EVENT_SIZE = 2048 KB
, MEMORY_PARTITION_MODE = PER_NODE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON
);Audit Results
From that session, I have the following sample results.
The preceding image illustrates the auto_stats event and how it captures stats updates. Along with the stats updates, I have the session configured to capture the person that performed the update. Not shown is the sql text related to the update – though that is also captured.
The above image shows the stats that have been altered. In this case, these stats were all altered as a result of the index defrags that occurred. And, due to the tight relationship between stats and indexes, the stats are logged as having been altered.
This last image (above) demonstrates the capture of stats being created. When an object is created, there are multiple phases to the creation. Thus, you will see that there are multiple entries for the stat_deleteme statistic that I created. Each entry just represents a different stage of the creation process.
Put a bow on it
In this article, I have demonstrated a more comprehensive method to audit changes to statistics. This method involves the use of Extended Events. Through XEvents, I can audit many different changes to statistics that may occur.
Go ahead, have a try at it!
Interested in learning more deep technical information? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the fourth article in the 2021 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Statistics Auditing to make you Omniscient first appeared on SQL RNNR.