Wednesday, February 22, 2012

[tuning] Statistics - when were they created... OR RECENTLY UPDATED?

(update 2013/08/07 - since it shows recent times...)

Courtesy of Nayan Raval & #SQLHelp, which led me to another article ( which documents 2 more undocumented trace flags.

How do you find out when statistics were created?  If it's on an indexed field, when the index was created (crdate in sys.indexes).  But statistics on non-indexed fields?  Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.

DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')

Look for the row with the oldest Updated. The oldest update, depending on how often it's been updated, MAY show you when it was created.

And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated?   STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated.  One search to my blog later, code found, and there we go.

No comments: