question

sqlLearner 1 avatar image
sqlLearner 1 asked

Update Statistics

If databases are setup with AUTO UPDATE STATISTICS = TRUE do I really need to run a job each night to update the statistics? Also, will running sp_updatestats 'resample' be sufficient enough to run each night rather than a full scan? I am using SQL 2014
sqlstatistics2014update-stats
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
DenisT avatar image
DenisT answered
You should not use a "sledge hammer" approach to update your statistics. The best way to pragmatically figure out what is outdated and maintain them during your regular maintenance window, ideally! The threshold or to define what outdated means truly depends on your environment. Take into the considerations any index maintenance task. Because if you rebuild an index which also updates statistics with a full scan, then you run your update statistics with resample -- you just shot yourself in the foot. Or vice versa, you run the statistics update with resample, then the rebuild an index with a full scan -- you just did the job twice for no reason. You cannot definitely say, resample is enough! It depends... Also, certain rules apply during statistics updates that invalidate the plans in the plan cache which is not a bad thing when you only pick and choose. But if you update everything, you might be invalidating a lot of plans in your cache, including the good ones, causing lots of recompiles and wasting your CPU. Again, it might be not a big deal in your environment but it's the best practice! Something to think about. You can look into using [Ola's SQL Server Index and Statistics Maintenance][1] or develop your own script based on your own definitions of what outdated means in your case. The AUTO_UPDATE_STATS should be ON regardless. [1]: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.