September 14, 2010 at 9:40 am
Good morning ladies and gents,
I am trying to setup a maintenance plan on my companies production database. After some reading I have managed to create more questions than answers regarding indexes and the most effective way to handle them. When the db was first setup the maintenance plan was set to:
9:00pm Backup Database -> History Cleanup
9:00pm Rebuild Index Task -> Update Statistics -> Check Database Integrity
4AM and 9P:00M T-Log Backup
Now I may be a new DBA but I am pretty sure that all 3 of these tasks should not be taking place at the same time right? I am thinking the order should be:
9:00pm: Check Integrity -> Rebuild Index Task -> Update Statistics -> Check Database Integrity
12:30pm: Backup Database -> History Cleanup
Every Hour on the Hour: T-Log Backup
Unfortunately the T-Log was only set to backup twice a day, subsequently the backups were HUGE. I am trying to get the sizes of my t-log backups down to a manageable size and i figure backing up the t-log more frequent would keep the size down tremendously.
But my main question is whether I should be using REBUILD or REORGANIZE during my 'nightly' index task. From what I have read reorganize will reorder my index pages and rebuild will drop the indexes and recreate them. Is one more stable or efficient than the other??? Should rebuild only be used in extreme circumstances??
Any help or advice would be greatly appreciated!!!
Antony
September 14, 2010 at 9:50 am
Why do you need to maintain your indexes so frequently ? I usually do ours every couple of months on the busy databases.
However, I do log backups about every 15-30 minutes.
September 14, 2010 at 9:59 am
You know I actually have no idea, the job was just originally setup to run that way. What I do notice is that every time the t-log backup runs after the rebuild my backups grows to about 30gb..... when the database is only 22gb.
I have already backed up and shrank the t-log and then set the backups to run more frequently to truncate the data. The majority of .trn's sit at 665KB. accept for when the index is being rebuilt which makes me think this job is not working so well.... hence my attempt at re-configuring the maintenance plan.
Do you suppose I should just do my reindexing manually or do a reorganize instead?
September 14, 2010 at 10:13 am
I would rebuild based on fragmentation, doing only those tables that need it. Typically I've rebuild once a week, on weekends, and that's worked well. I have rebuilt some tables more often, usually if they are getting lots of inserts and we want to maintain a certain fillfactor to handle loads/changes.
Here's a script that you might use:http://qa.sqlservercentral.com/scripts/Maintenance/62695/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 14, 2010 at 10:38 am
I build manual scripts to rebuild/reorg indexes. For the larger ones, I do a t-log backup in between to keep log size manageable.
September 14, 2010 at 10:44 am
Thanks for the information guys! I will start doing this manually via script from now on. I will begin by breaking down the script I have been linked and go from there.
Cheers!
September 14, 2010 at 11:33 am
Assuming there's no stress on the system, running a procedure to rebuild/reorganize the indexes once a day, based on the level of fragmentation & number of pages, is fine. We've been operating that way for quite a while with no issues.
But yeah, I'd split up the operations so that they're not all running at the same time. You will get some cross-over (for example, a log backup runs at the same time as some other process, no big deal) but having everything kick off at the same time is a poor choice and you will see a bigger impact on the system because of it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 14, 2010 at 2:10 pm
There is no need to perform two integrity checks - perform your integrity check first, then backup the database.
I would have a separate plan (or sub-plan) that only performs the history cleanup. No need to tie this to your backups, as this task will only remove the backup history. I create a separate plan - schedule it for 12:01am daily and set it to maintain 6 months of history. Note: this requires that you un-check the option 'Limit size of job history log' in SQL Server Agent History. If you don't disable that option, it will take precedence over your history cleanup task.
For my systems, I start with this general outline for maintenance.
Plan: History Cleanup
Task: History Cleanup Task
Schedule: daily @ 12:01am
Plan: System Database Maintenance
Tasks: Integrity Check -> Backup System Databases -> Maintenance Cleanup Task
Schedule: daily (usually around 2am, but depends on the system)
Backup retention: as many days as possible
Plan: User Database Maintenance
Tasks: Integrity Check -> Backup User Databases -> Maintenance Cleanup Task
Schedule: daily
Backup retention: as many days as possible
Sub-Plan: User Database Maintenance.Weekly
Tasks: Rebuild Indexes and Update Statistics
Schedule: weekly
Plan: User TLog Maintenance
Tasks: Backup Transaction Logs (user databases)
Schedule: at least every hour, if not more frequent
You can put all of the above in a single plan - with sub-plans for each type. However, I find having separate plans works better. Especially when looking at the history - which can be a problem if the transaction log backups are in the same plan as your other tasks.
Whether or not I use a script to rebuild indexes/update statistics depends upon the maintenance window and performance requirements. If I can afford the time, I just rebuild them all and update all with full scan. If not, I'll implement a smart-reindexing scheme and only update statistics that need to be updated.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply