SQL Server Auto update statistics - and why I dont advocate for it.
SQL Server has long provided functionality called Auto Update Statistics (AUS). While I did not write the product, I've used it for a long, long time. Auto Update Statistics, in my opinion, exists so that statistics functionality exists at all in the face of limited management. For small environments and completely consistent data distribution, this mostly works. However, I wouldn't be writing about how something just works and doesn't need attention if there weren't a meaningful, necessary alternative.
First, it's widely known that DBAs should manage statistics updates. Depending on many things, you may opt to do statistics update with FULLSCAN or with sampling. Many also know that index rebuilds also update statistics as, in effect, the index rebuild event recreates the table, so the histogram simply must align with that. If you're regularly updating statistics, it must seem like auto update statistics is unnecessary. Well, yes, it can be.
Second, statistics can become aged. While not quite like milk, statistics age due to change. There is no right or wrong answer here - statistics "damage" occur when DML takes place (insert/update/delete). As data distribution evolves, the optimiser and its plans may not be as effective as they can be. In fact, they could be completely and totally wrong.
Third, plans. Statistics are the foundation of plans. It's what helps the engine determine if seeks or scans are more appropriate. If you don't update stats, plans would (necessarily) re-evaluate. There are many other triggers for plans to rebuild but we're talking about stats actions here. When you manually update stats, the affected plans are invalidated and must be rebuilt. This can be tragic; your next plan build could be based on some edge case parameter (parameter sniffing!) and moreso, could be based on poor sampling.
Fourth, the sampling. AUS uses a canned method (I wouldn't and didn't call it an algorythm, it ain't) where 0.1% of records are sampled. In a scenario where data is perfectly linear (1-1,000,000,000), this is actually perfect and efficient. Realistically, the world doesn't work that way. While an IDENTITY column might get close, everything else has a tendency to be wild and all over the place as time rolls on. Really ugly sampling can totally result in a query running perfectly at 11:23:45.111 and taking forever at 11:23:45.222. While my datetime logic is a bit snarky, this can and does totally happen.
Finally, the frequency. AUS has a canned method for executing AUS (2016+): MIN(500+(0.20*[records]),SQRT(1000*[records])). So every table will have a different threshold that you cannot change. Before 2016, it was just 500 rows plus 20% of the table.
So what to do?
There's been an update but it's up to you to take advantage. As of SQL Server 2016 with some CU back in 2017, you can now influence AUS behavior per object. There is no magical, default setting. Instead, you must manually update any affected (or every) object and include "PERSIST_SAMPLE_PERCENT = ON". This will cause AUS to use your sample percentage going forward. Even if you update manually with FULLSCAN or some other sampling later on, unless you specify PERSIST_SAMPLE_PERCENT = ON during an update event, that stored value will not change.
Ok, cowboys, don't get ahead of yourselves. This doesn't mean you should pick some non-zero number and blast a new sampling percentage. This doesn't mean you should blindly turn off AUS and just run Ola Hallengren's script with whatever defaults provided or that you've set. You need to think about this and you need to use it based on what you observe in your various applications and databases.
Perhaps in a future update I'll cover using XE to trap AUS events so that you can better tell that AUS took place and, honestly, use that to determine unwanted plan regression and performance penalties.
For more explanation, see:
Comments
Post a Comment