AzureSQL
AzureSQL copied to clipboard
Should FULLSCAN be the default? Consider making it optional
Our nightly run of AzureSQLMaintenance tends to plateau our DTUs at 100% for about 2 hours. We've narrowed down the culprit to an UPDATE STATISTICS
call on a single, particularly huge stat. This made me wonder what the effect would be of dropping WITH FULLSCAN
and allowing the default sampling to be used. I tested this manually and the difference was huge - the update completed in just a few minutes without FULLSCAN.
From the docs:
For most workloads, a full scan isn't required, and default sampling is adequate. However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan. While estimates may become more accurate with a full scan than a sampled scan, complex plans may not substantially benefit.
My question is, would be it appropriate to default to leaving WITH FULLSCAN
out of the generated script (here), and perhaps adding adding a new parameter to opt-in? Or is this considered a best practice in this case? (i.e. would a nightly maintenance script not fall under the category of "most workloads"?)
Thanks in advance!