Hello,

I'm running SQL Server 2005 and we have two databases setup. These
databases are virtually identical, one we call Live is our production
database and the other we call Test and use for testing and training.
Periodically I restore a backup of the Live data to the Test database to
keep the data up to date.

There is a middle-tier application server that has a version of the
application for each database, Live and Test.

I keep the Live db in a Full recovery model and the Test in the Simple
recovery model. What we are noticing is that the Test system is much slower
than the Live system and I can't figure out why. The only real difference
between the two systems is the recovery model. Should the Simple recovery
model perform that much worse than the Full?

For example, the maintenance plan for the Live system consists of:

Runs nightly:
Rebuild Index Task
Update Stats Task
Shrink the DB
Backup the DB
Clean up old DB backup files and Transaction Log backup files

Runs hourly:
Backup of the Trans Logs

Maintenance plan for the Test system consists of:

Runs nightly:
Update Stats Task
Shrink DB
Backup DB
Clean up DB backup files

Trans logs are not backed up.

The maintenance plan for the Live system takes about 1 hour 45 minutes to
complete but the Test maintenance plan takes closer to 3 hours. Why should
the Test DB maintenance plan take so much longer to run than the Live DB
maintenance plan?

Thanks in advance,
Linn

How does the hardware compare between Live and Test? Are both systems
running on identical hardware (including network adapters and connection to
any remote storage for backups via the same speed backbone?).

The Simple recovery model should perform better than either Full or
Bulk-logged. I note that you don't rebuild the indexes on the test database
system - is this because there is no need to? If anything, I would expect
this would slow down the test database maintenance further - am just
interested in the logic behind not doing it.

"Linn Kubler" <lkubler (AT) chartwellwisc2 (DOT) com> wrote



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4671 (20091208) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

The databases are on the same server so the hardware is identical.

I think I stopped rebuilding the indexes for two reasons, one was to try and
shorten the time it takes to run the maintenance and the second was because
there is so little activity on the Test system that I didn't think it was
necessary. And a third is that since I tend to move the Live data into the
Test system about monthly I figured the indexes would already be rebuilt. I
simply restore from a backup.

Does that make sense or am I missing something. I too thought that Simple
should perform better.

Thanks,
Linn

"Will Alber" <junk (AT) crazy-pug (DOT) co.uk> wrote

Linn Kubler (lkubler (AT) chartwellwisc2 (DOT) com) writes:
That's probably overkill. If you rebuild indexes, statistics are updated
too. Not all column statistics, but they are handled by autostats.

Take that out!

Take that out!

Shrinking your database is something you should not under very
exceptional circumstances, like you have just emptied a production
of copy of transaction data to use in test. But in a maintenance
job? Never!

If you are lucky there is nothing to shrink, in which case nothing
happens. If you are unlucky, there is something to shrink, whereupon
fragementation sets in, and all your index rebuilding is useless. And
in the morning, if the database needs to grow for new data, you take
a standstill while its happening.

Whether that is due to the shrinking, I don't know. But the shrinking
is causing real damage to your system. Remove it from your maintenance
plans.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Linn - that eliminates a lot of the possibilities - and I fully
understand why you're not running the rebuild of indexes on the test
system - exactly what I would've expected.

Whilst I wouldn't imagine for one moment that you would see a miraculous
speed increase if you set the test database to be fully logged - if anything
it should decrease in speed - it wouldn't hurt to try this to see whether it
does make an inexplicable difference (without transaction log backups,
though, the log will fill up, so maybe try this later as a last resort!).

How do the individual steps of the maintenance task on the test database
compare to the live, in terms of time taken? I'm wondering whether, as the
test system is not used all that much in comparison to the live system, a
lot of the live data is cached which makes the update stats task less of an
I/O impact as much of the data is in memory rather than on disk.

"Linn Kubler" <lkubler (AT) chartwellwisc2 (DOT) com> wrote



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4671 (20091208) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

I was looking at everything that runs on this SQL server at night and
noticed that the tape backup was running the backups on the SQL server at
the same time the maintenance plan was running on the Test system. On
Tuesday I rescheduled the tape backup to start after the Test maintenance
plan was finished. This shortened the Test maintenance plan run time in
half, it now takes 1 hour 45 minutes as opposed to the 2 hr 55min before.
That helps alot.

Compairing apples to apples the Update Stats take 39:44 on the Live system
and 1:02:29 on the Test system. The Shrink DB took 00:12 on the Live system
and 00:26 on the Test. The backup operation took 18:16 on the Live and
40:24 on Test.

In total the mainenance operation took 1:39:24 on the Live system and
1:44:48 on Test.

Thanks,
Linn

"Will Alber" <junk (AT) crazy-pug (DOT) co.uk> wrote

Linn Kubler (lkubler (AT) chartwellwisc2 (DOT) com) writes:
Again: remove the shrinking from your maintenance plan. It's detrimental
for your system.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx