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.
Continue reading on jasonbrimhall.info.
