Statistics!!?! Argghhhh!
Had to get that out of my system.
No fancy graphs today, not even any code to share. Gotta get these thoughts into bytes, though or this will become just another of the blog posts I should write but won't.
I believe performance & resource utilization gains should offset the cost of statistics & index maintenance. And I love models! So I need a model for stats 'potential benefit' to decide when and how to update, and a model for 'benefit realized' so the choice can be evaluated afterward. This post is part of my journey to those destinations.
Models don't need to be perfect... but they should be useful.
"Essentially, all models are wrong. But some are useful."
page 424
Empirical Model Building and Response Surfaces
Box, G.E.P. & Draper, N.R.
page 424
Empirical Model Building and Response Surfaces
Box, G.E.P. & Draper, N.R.
Auto stats updates in SQL Server 2008 R2 and beyond are triggered based on rules involving colmodctr, a column modification counter for the leading column of the stats key. Great - secret knowledge! But colmodctr is only available (afaik) via DAC. Bummer! I want to use DAC as sparingly in SQL Server as I use kdb in UNIX. It's not a wise way to regularly model maintenance on a tool that is restricted to that degree, imo.
Enter a new dmv - Sys.dm_db_stats_properties! And it has a mysterious column 'modification_counter'. Based on its description, it sounds like an equivalent of colmodctr - or at least a very closely correlated proxy. Awesome!
sys.dm_db_stats_properties (Transact-SQL)
http://bit.ly/1nFu92u
But... it's introduced in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1. That leaves out a good number of the instances I care about, until upgrades that haven't been scheduled yet. Bogus!!!
But wait... I remember something. Kinda. When I first read about mitigating the 'ascending key problem' with trace flags 2389 and 2390, it kinda seemed like the writers were implying that SQL Server always kept track of the 'brand' for a stats key - whether or not it treated keys differently based on brand.
I remember testing trace flags 2389 & 2390. When I used trace flag 2388 at the session level and called dbcc show_statistics for the stats, the brand was revealed. I seem to remember that stats update date was there, too. And inserts and deletes since update! Hmmm...
Ok... I just confirmed that memory. No fancy cut-n-pastes today, sorry. Blogging from my phone. Left as an exercise for the reader I guess :-)
But, what if trace flags 2389 and 2390 aren't in place? Hot dog!!
Trace flag 2388 reveals the brand in dbcc 'show_statistics' even without 2389 a& 2390! But even more exciting... last stats update, inserts, deletes are there, too!
So inserts and deletes since last stats update can be my colmodctr proxy.... even if the instance is SQL Server 2008. R2 before SP2, or SQL Server 2012 SP1 and thus missing 'modification_counter' from Sys.dm_db_stats_properties.
I kinda like getting inserts and deletes separately anyway... since the resulting changes to underlying data may profile differently for deletes compared to inserts.
An astute reader may think: why bother when lots of interesting similar information can be grabbed from
sys.dm_db_index_operational_stats?
Trouble is, most of the databases I care about have 20,000 or more indexes. And they are busy databases.
That means the following passage disqualifies sys.dm_db_index_operational_stats as my colmodctr proxy, because I don't want holes in my model based on cache eviction:
"The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available."
No comments:
Post a Comment