April 15, 2011 at 7:53 am
How many times a week must execute a maintenance plan of 110 GB database ?
I not found an explicit topic whenever I need to do that ... Maybe you can help me.
April 15, 2011 at 7:57 am
Depends. What's in the maintenance plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 11:40 am
I agree. No way to know without discussing the details. A maintenance plan can just backup logs. I'd suggest that should be run many times a day, let alone X*7 number of times a week. What does that plan do?
"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
April 16, 2011 at 1:36 pm
Plan maintenance is run on a production server and only: Check Database Integrity Task, Reorganize Index Task, Rebuild Index Task, Update Statistics Task and Shrink Database Task. I am particularly interested in how many times should I do that? After watching first in my database?
April 16, 2011 at 1:37 pm
Plan maintenance is run on a production server and only: Check Database Integrity Task, Reorganize Index Task, Rebuild Index Task, Update Statistics Task and Shrink Database Task. I am particularly interested in how many times should I do that? After watching first in my database?
April 16, 2011 at 4:15 pm
Shrink is easy. Never.
Rebuild or reorganise (makes no sense to do both) depends on how fast they fragment. I recommend you discard the maint plan and get one of the custom index rebuild scripts available. Maybe the one here: http://sqlfool.com
Database integrity I would recommend you do before every backup.
Speaking of backups, I don't see one... You do have backups???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2011 at 4:08 am
The only one Gail didn't address is Update Stats. You'd be best to make that a part of the index updates. Frequency really depends on your system, but I'd say, on most databases smaller than 500gb, you should be able to run that once a day.
"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
April 17, 2011 at 1:01 pm
sure, but is caught in a separate maintenance plan...
April 17, 2011 at 1:13 pm
to see if I understood well, less than 500gb base once a day is enough?
The system that I have now my whole thing takes 4 hours.
Maintenance plan segments it can take less? I mean to do it segmented over several days?
April 18, 2011 at 5:14 am
As a general rule, yes, but you do need to account for your own individual circumstances, the load on your servers, the number and size of your databases, etc. There is no single, one size fits all formula.
"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
April 18, 2011 at 7:18 am
1) Stop using maintenance plans, ESPECIALLY for index defrag stuff. Go to ola.hallengren.com and get the free suite of maintenance stored procedures there. Fully documented, and they even create the agent jobs you need. READ THE DOCUMENTATION.
2) If you want optimal performance and protection for a 100+GB database, you REALLY need to engage a professional to give your system a review. You were doing things that were REALLY REALLY BAD (like routine shrinking) or counter productive (like both rebuilding and reorganizing indexes) or redundant (updating stats after index rebuild). Your existing plan tells me (nothing personal here) that you do not have the training or experience to do the things that are necessary to get/keep your database optimized or protected.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 18, 2011 at 12:51 pm
ok, I think I understand how it works, I thought I could apply a general rule ...
April 18, 2011 at 12:54 pm
ok, I think I understand how it works, I thought I could apply a general rule ...
April 18, 2011 at 1:10 pm
There are few general rules in SQL unfortunately.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply