t find a "maintenance plans"
newsgroup for SQL Server . . .
About 3 w
s ago I created maintenance plans on four databases: master, msdb and two production databases
(I'll call them DB1 and DB2). I included:
1) update statistics used by the query optimizer
2) remove unused space from database files
3) check database integrity - include indexes - attempt to repair minor prob
lems
4) perform step 3 before backing up database and transaction log
5) backup database with verify
6) backup transaction log with verify
We run a nightly process wherein text files are downloaded from an AS/400 an
d fed into DB1.
This process runs on Tuesday through Saturday mornings, starts at 3:00 AM an
d finishes (under normal circumstances)
at about 5:30 AM. I scheduled the maintenance plans so that none of them ran
during this time.
As soon as the plans were in place and executing, I noticed a significant sl
owdown
in the abovementioned 2 hour download/import process. It started running at
least 1 hour longer;
on some days it would take up to 2 hours longer. I immediately disabled the
plan for DB1 -
no performance improvement whatsoever. Long story short - I finally disabled
all four plans,
and execution time was back to 2 hours again.
I will be doing some testing over the next few w
s; enabling parts of the plans on master and msdb,
but in the meantime I needed to know if anyone out there has experienced thi
s type of behavior and if so,
how you resolved the issue.
We're running SQL Server version 7.0 SP2 (7.00.842)
Note: the maintenance plans were created using the Enterprise Manager versio
n that came with SQL Server 2000;
don't know if that makes a difference.
Let me know if you need additional information about the issue.
And, thanks in advance for any assistance you can offer.
CarlI suspect that your culprit is step 2) remove unecessary space from database
s.
If your are using DBCC SHRINKFILE to compact your database and log files,
when the import job runs those jobs will need to grow in order to accommodat
e
the new data (especially the log file, if your recovery model is Full). If
the server has to grow the files during the import, it will definitely slow
things down.
Try removing that step from your plan and see what happens.
"Carl Imthurn" wrote:
> My apologies if this is not the right group for this question, but I could
n't find a "maintenance plans"
> newsgroup for SQL Server . . .
> About 3 w
s ago I created maintenance plans on four databases: master, msdb and two production databases
> (I'll call them DB1 and DB2). I included:
> 1) update statistics used by the query optimizer
> 2) remove unused space from database files
> 3) check database integrity - include indexes - attempt to repair minor pr
oblems
> 4) perform step 3 before backing up database and transaction log
> 5) backup database with verify
> 6) backup transaction log with verify
> We run a nightly process wherein text files are downloaded from an AS/400
and fed into DB1.
> This process runs on Tuesday through Saturday mornings, starts at 3:00 AM
and finishes (under normal circumstances)
> at about 5:30 AM. I scheduled the maintenance plans so that none of them r
an during this time.
> As soon as the plans were in place and executing, I noticed a significant
slowdown
> in the abovementioned 2? hour download/import process. It started running
at least 1 hour longer;
> on some days it would take up to 2 hours longer. I immediately disabled th
e plan for DB1 -
> no performance improvement whatsoever. Long story short - I finally disabl
ed all four plans,
> and execution time was back to 2? hours again.
> I will be doing some testing over the next few w
s; enabling parts of the plans on master and msdb,
> but in the meantime I needed to know if anyone out there has experienced t
his type of behavior and if so,
> how you resolved the issue.
> We're running SQL Server version 7.0 SP2 (7.00.842)
> Note: the maintenance plans were created using the Enterprise Manager vers
ion that came with SQL Server 2000;
> don't know if that makes a difference.
> Let me know if you need additional information about the issue.
> And, thanks in advance for any assistance you can offer.
> Carl
>|||Sorry, let me translate my post into English:
If your are using DBCC SHRINKFILE to compact your database (mdf) and log
(ldf) files, when the import job runs those files will need to grow in order
to accommodate the new data (especially the log file, if your recovery model
is Full). If
the server has to grow the files during the import, it will definitely slow
things down.
Try removing that step from your plan and see what happens.
"Mark Williams" wrote:
> I suspect that your culprit is step 2) remove unecessary space from databa
ses.
> If your are using DBCC SHRINKFILE to compact your database and log files,
> when the import job runs those jobs will need to grow in order to accommod
ate
> the new data (especially the log file, if your recovery model is Full). If
> the server has to grow the files during the import, it will definitely slo
w
> things down.
> Try removing that step from your plan and see what happens.
> --
> "Carl Imthurn" wrote:
>|||Thanks Mark -- I will check that out and see what happens.
And, thanks for the English translation ;-)
Carl
Mark Williams wrote:
> Sorry, let me translate my post into English:
> If your are using DBCC SHRINKFILE to compact your database (mdf) and log
> (ldf) files, when the import job runs those files will need to grow in ord
er
> to accommodate the new data (especially the log file, if your recovery mod
el
> is Full). If
> the server has to grow the files during the import, it will definitely slo
w
> things down.
> Try removing that step from your plan and see what happens.
>|||An alternative approach to the troubleshooting efforts: Disable all
maintenance plans, then add one very simple maintenance plan. Make it for
DB1 and have it do only one thing (back up the database); don't have it do
anything else. See what happens. If life is good, then proceed to add
additional steps and maintenance plans (one step at a time of course) until
the problem reappears. If it reappears right away with the simple (backup
only) maintenance plan on DB1, then kill that maintenance plan and do an
equivalent but for master, or model, or DB2 or whatever other database.
Whatever you do, the suggestion I'm making here is to start small and simple
and incrementally add complexity until it breaks. Upgrading to SQL Server
2000 probably wouldn't hurt anything...
Once you get that all figured out you might take your consultant to task for
not setting up a maintenance plan and testing it to begin with : )
-HTH
"Carl Imthurn" <nospam@.all.com> wrote in message
news:%23EzkzSALGHA.3264@.TK2MSFTNGP11.phx.gbl...
> My apologies if this is not the right group for this question, but I
> couldn't find a "maintenance plans"
> newsgroup for SQL Server . . .
> About 3 w
s ago I created maintenance plans on four databases: master,> msdb and two production databases
> (I'll call them DB1 and DB2). I included:
> 1) update statistics used by the query optimizer
> 2) remove unused space from database files
> 3) check database integrity - include indexes - attempt to repair minor
> problems
> 4) perform step 3 before backing up database and transaction log
> 5) backup database with verify
> 6) backup transaction log with verify
> We run a nightly process wherein text files are downloaded from an AS/400
> and fed into DB1.
> This process runs on Tuesday through Saturday mornings, starts at 3:00 AM
> and finishes (under normal circumstances)
> at about 5:30 AM. I scheduled the maintenance plans so that none of them
> ran during this time.
> As soon as the plans were in place and executing, I noticed a significant
> slowdown
> in the abovementioned 2 hour download/import process. It started running
> at least 1 hour longer;
> on some days it would take up to 2 hours longer. I immediately disabled
> the plan for DB1 -
> no performance improvement whatsoever. Long story short - I finally
> disabled all four plans,
> and execution time was back to 2 hours again.
> I will be doing some testing over the next few w
s; enabling parts of> the plans on master and msdb,
> but in the meantime I needed to know if anyone out there has experienced
> this type of behavior and if so,
> how you resolved the issue.
> We're running SQL Server version 7.0 SP2 (7.00.842)
> Note: the maintenance plans were created using the Enterprise Manager
> version that came with SQL Server 2000;
> don't know if that makes a difference.
> Let me know if you need additional information about the issue.
> And, thanks in advance for any assistance you can offer.
> Carl
>
