Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Should FULLSCAN be the default? Consider making it optional #37

Open
tmenier opened this issue Oct 20, 2023 · 0 comments
Open

Should FULLSCAN be the default? Consider making it optional #37

tmenier opened this issue Oct 20, 2023 · 0 comments

Comments

@tmenier
Copy link

tmenier commented Oct 20, 2023

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant