Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aap
Starting Member
3 Posts |
 Posted - 2008-10-16 : 05:44:16
|
| Hello,I work as an application support. But I also have a responsibilty of the DB. So, right now I have a problem with my optimization job.For the information, my database size is about 16 GB and I have some scheduled job that needed by the application and some of it is for the maintenance. Here the jobs are :1. Integrity Check every Sunday at 00:00 and takes 7 minutes2. Optimization every Sunday at 00:30 and takes 1 hour 10 minutes3. DB Backup everyday at 03:00 and takes 20 minutes4. Application job at 01:00 and takes 30 minutesI also have a cleanup job for clean up some of table that accessed frequently by the application.My problem is why the optimization job take so long while my other database with the size 8 GB only take 25 minutes with the same job, the same setting and the same parameter ? And because of this condition, there are some impact :- the fourth job (Application job) overlap with the optimization job. It makes the application job running longer than usually (Monday through Saturday, when the optimization job doesn't running). - my database looks like busy so the database don't response my other communication (for example, application that send the data). I can say that when the job running, so it looks like the database can't retrieve data from others application.And I have some question :1. Is it allow to make some job run parallel in SQL Server especially Maintenance Plans Job ? (so maybe there's some job will overlap)2. What is your suggestion for the sequence of the maintenance plan jobs (integrity, backup, optimization, etc) ?For Your Information that my application is online application, so it will access the database for 24 hours in a day.Thanks for your attention, and please your help. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-16 : 09:04:17
|
| Instead of running maintenance plan for defrag index job, you should run script which checks the fragmentation level of index and decide whether to defrag or reorganize.This ways it runs less time. Also make your application job run at 3:00 AM instead so it doesn't overlap.You can use Tara's script for optimization job which is smart enough to detect that. |
 |
|
|
|
|
|
|
|
Optimization Maintenance Plan