Considerations for Updated Statistics

Up-to-date statistics makes life easier for SQL Server. Estimated rows in plans will be closer to the actual rows and SQL Server will not have to make as many guesses when building query plans. Think back to when you were in school. It was easier to take a test when you had a cheat sheet with you. You would (hopefully) earn a better score and finish your test sooner than you would on your own. The same goes for SQL Server. Having updated statistics makes it easier to formulate an accurate plan and make those choices more quickly.

But are you doing too little (or too much) to manage statistics?

Don’t Wait, Act Now

When should you consider updating stats manually? I’ve ran into issues with slow queries where the quick fix was updating stats. That does not mean that updating stats should be something you run each time you encounter a slow query. While there is a debate about whether updating stats causes locking (from what I’ve seen this is a myth), Microsoft recommends against updating stats too often due to the “performance tradeoff between improving query plans and the time it takes to recompile queries.“

It can sometimes be worth proactively updating stats manually after significant data changes. For example, was a large chunk of data recently inserted that’s going to be queried right away? Rather than wait around, it may be worth updating statistics sooner than later so that SQL Server can generate better plans now.

You can’t manage it all which is why it’s important to have AUTO_UPDATE_STATISTICS turned on. When enabled, AUTO_UPDATE_STATISTICS will do it’s best to keep statistics updated as data changes. Depending on object size, it typically takes about 500 changes or 20% of the table changing before AUTO_UPDATE_STATISTICS considers statistics outdated. That’s usually going to be good enough for acceptable performance.

Goldilocks Stats

We’ve discussed a few reactive situations where there can be benefits to updating statistics. What’s the sweet spot the rest of the time if you have an “Update Stats” maintenance plan or job? It depends. Start by updating statistics weekly and adjust as necessary. You may find it better to update every few days. Perhaps every couple weeks or monthly is best. If you’re blindly updating statistics every night, you could be making SQL Server do extra work that’s doing more harm than good for performance.

Thanks for reading and Happy New Year!