Poll Results: How Often You’re Running Maintenance Tasks
Last week, I asked y’all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:
A few thoughts:
You’re typically taking full backups daily or weekly. I’m actually surprised by how many folks do weekly backups! That kinda scares me because if the backups take so long that you can’t do ’em overnight, then you’re probably gonna be horrified by restore times when things go wrong. You’ll have to do that big long full restore, plus the most recent diff. If you’re in that situation, you really wanna start checking out storage snapshot backups.
You’re doing index maintenance less frequently. Compare the backup and index maintenance responses, and weekly/monthly/less have higher responses than they did on the full backups answer. That’s good to hear! Fragmentation isn’t a problem that you want to tackle daily.
A lot of folks are doing daily stats updates. And right here, this is the problem that prompted this entire poll. I see a surprising number of people who do daily statistics updates, AND who complain about an unstable plan cache that constantly experiences parameter sniffing. The more you update stats, the more you’re freeing the plan cache, and you’re causing your own parameter sniffing issues by making the plan cache so unstable.
If I could give you one piece of advice, it would be to check for corruption more frequently than you do statistics updates. People don’t get fired for out-of-date stats: they get fired for losing data.
17 Comments. Leave new
I found the reindex and update stats choices a little limiting and potentially misleading from a results standpoint. For example, if one uses Ola’s maintenance scripts and puts in high values for fragmentation and values other than just updates happened but a minimum threshold, one could have jobs “run daily” or “run weekly” but in reality the update/reindex frequency may be less frequent than the responses.
Thanks for everything you do for the community.
Cheers
When do the jobs run?
That’s what your answer should have been.
I know this is going to sound brutal, but the answer really is that simple. You’re overthinking it. Cheers!
My answers were when the actual jobs run. What actually happens within those jobs is where my comments were directed.
Cheers
I completely understand your scenario, I have a number of customers that require different schedules. In my case, the lowest frequency I have is daily, so I answered daily – scope of the job doesn’t matter.
Holy moly, I didn’t realize that stats updates have an important downside. I was mostly just using them as an alternative to running Index ReOrg because of the line: “Reorganizing/Rebuilding indexes is the most expensive way to Update Statistics.”
Thanks for this insight Brent!
We truncate almost all tables and reimport all table data each night. After the import is complete, we update all statistics. Even with the truncate and reimport, day to day the data hardly changes (think customer data). During the day SQL is run to generate data for scheduled and ad hoc reports. Without updating the statistics, I am told that the reports run slow. I don’t really know because this is the way it has always been done. What do you think?
I can’t really do consulting here in the blog post comments, but feel free to click Consulting at the top of the site for personalized production help. Hope that’s fair!
The question about full backups was not worded the best. I wasn’t sure how to answer it. We do weekly full backups and on the off days, we take differential backups (as well as log backups every 15 minutes). I answered daily full backups but some people may not have answered the same as me.
Exactly. We do weekly full backups, daily differentials and logs every 15-60 minutes depending on the expected RPO.
I’m going to be brutally honest here: if you do weekly full backups, and someone asks you when you do full backups, the answer is once a week.
Differential backups are not full backups. Period. Full stop.
If you doubt my answer, try doing a database restore using a differential backup and not a full backup.
I’m surprised by the number of people still doing even weekly index maintenance
Reindexing is rarely performed on any of my systems, there are some extreme fragmentation cases where it does occur IF NEEDED. Stats are updated daily, but it does not blindly update all stats in the entire database. The process updates each individual stat, calculates the percent based upon table size, number of rows, and number of modified rows.
As with any system, while I typically agree with the general guidance, there are always fringe scenarios where you need to adjust your plans based on the need of the system/customer/business/budget (and no those are not the same things).
Like humans, database systems can be very diverse; just because I’ve worked on one database, doesn’t mean I know the maintenance schedule that works for every database.
I’ve managed hundreds of servers and thousands of databases for multiple companies throughout my career. While I might use the same tools (First Responders Kit, Ola’s maintenance solution, etc.) and guiding principles every time, the maintenance schedule I end up with is rarely ever the same, but it always works for the system/customer/business/budget.
Brent, I would disagree with your statement about daily full backups:
“because if the backups take so long that you can’t do ’em overnight.”
From my perspective, it’s not that we can’t perform them overnight, but rather that we don’t want to do them daily due to the increased storage consumption they would require. That’s why we opt for a weekly full backup combined with daily differential backups.
The poll couldn´t capture scenarios like “we want to use option X, but are running option Y currently.” It might be interesting in the future to explore cases where people aren’t implementing their ideal solution. I hope this makes sense.
Thanks, as always, for your effort running this blog!
[…] Brent Ozar runs a poll: […]
I can’t believe how many DBAs haven’t been fired for losing data. I can’t tell you how many times data has been lost and I get stuck with doing more work as a developer because DBAs cannot do a restore. I don’t know if anyplace I have ever worked if a devastating disaster happened could restore a system in a timely manner. I have asked if that kind of fire drill has ever been done and no DBA I have ever met has ever told me they have done such in their careers.
I cannot tell you how many times I have seen databases get dropped in the middle of the day. Tables disappear. In 2021 while working for a federal government we lost an entire year of billing data. I saw entire year’s worth of coding lost because the System Administrators didn’t even do a backup.
WHAT I CAN’T SAY IS WHAT I WOULD LIKE DO TO A dba THE NEXT TIME I LOSE DATA. I am tired of hearing complaints about queries that were written a decade or more before I started working for the company and then hearing why databases cannot be maintained or backed up.
In an ideal world, those suggestions would always work. But the reality is that every environment has its quirks. Where I work, we deal with a pretty chaotic system, full of highly complex and dynamic views. If I skip fragmentation maintenance, the system starts choking badly the very next day. Since views don’t reuse execution plans, in the end, running UPDATE STATS actually performs better— so basically, everything works the opposite of what you’d expect in that ideal environment, haha.