Setting Up Some Basic Maintenance Tasks

Question

Setting Up Some Basic Maintenance Tasks

Sonny Childs

SSC-Addicted

Points: 411
More actions
June 11, 2015 at 7:22 am

#318942

Like many database administrators, I've inherited our DBA responsibilities de facto due to no one else knowing anything about it.
I'd like to set up some basic maintenance tasks to help keep our databases running smoothly. Here's what I have so far:
Backups:
- Full backups every night
- Backups older than 7 days are removed
- Check weekly to ensure backups are doing their thing and transaction log isn't becoming a walrus
Heaps and Indexes:
- Rebuild heaps >40% fragmentation weekly
-- Heaps are in-place due to this being an application database I did not build. Not sure if I am allowed to add clustered indexes
- Indexes at < 5% fragmentation ignored
- Indexes 5-30% will be reorganized if able, otherwise rebuilt weekly
- Indexes > 30% will be rebuilt weekly
Statistics:
- Update all stats on objects that have been modified
This will mostly be done weekly during off-hours on Sunday. I'm leveraging Hallengren's SQL Server Maintenance Solution stored procedures, and I am planning to eventually automate all of this with either jobs or Maintenance Plans.
Any suggestions or tips? What I have so far is based on a lot of research from different sources, but it seems like there's no 'silver bullet' for DB tuning.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Absolutely no silver bullet. That basically looks OK. You may find that statistics updates weekly might be a little light. Depending on the size of your system and the load, I'd probably try to get those done daily if I could.

The biggest suggestion I'd make is to ensure that your backups meet the service level agreements you set with the business. Also, that you practice restores so that you know how to do those before there's an emergency.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sonny Childs SSC-Addicted Points: 411 More actions · Answer 2

Thanks for the reply.

How would you filter out pertinent statistics so that only those which really need to be updated are affected?

I've ran successful backups before, and luckily our SQL box is imaged every 15 minutes, so I can always restore from that if there is a catastrophic failure.

Also, the DB average size is around 350GB, so I'm not dealing with a huge system.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

There's no automated mechanism I'm aware of. You gather metrics about query behaviors and over time identify those that are causing you pain. Identify the fact that stats are causing problems there and then you isolate those statistics as needing more attention. It's an organic process, not a scientific one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

There are actually two places that provide good "hints" as to whether or not stats need to be updated. Please see the following article for both.

http://www.jasonstrate.com/2012/10/what-happened-to-rowmodctr-from-sysindexes/

Of course, MS had to deprecate the one that is easiest to use. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Here's one more that uses a slightly different approach for stats.

http://raresql.com/tag/sys-sysindexes/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sonny Childs SSC-Addicted Points: 411 More actions · Answer 6

Sonny Childs

SSC-Addicted

Points: 411

June 12, 2015 at 6:43 am

#1805005

Thanks, Jeff, I'll be sure to look into these.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 7

Are any of your databases in the FULL or BULK LOGGED recovery model? Because if they are you need to ensure that transaction log backups are in your backup strategy. I only saw full backups which will not allow the transaction log to flush.

Joie Andrew
"Since 1982"