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.
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"?)
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 STATISTICScall on a single, particularly huge stat. This made me wonder what the effect would be of droppingWITH FULLSCANand 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:
My question is, would be it appropriate to default to leaving
WITH FULLSCANout 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!