Posted by Erland Sommarskog on 02/17/06 00:58

Matik (marzec@sauron.xo.pl) writes:
> Torn page detection:
> When I have this option turned on, processes conected with updating and
> writing in database, was MUCH more slower, than if I've this option
> turned off.
>
> It seams to me, that when this option is turned on, the sqlserver is
> much more slower by any operation connected with writing, than without
> this option.
>
> This option is required for ensurance, that the data are written
> properly on the media.
> My question is:
> if I have my database running under cluster, with RAID hd system, which
> contains write cache, with additional checking, that the data will be
> written correctly, is it necessary for me to keep this option turned
> on?

My gut feeling is that torn-page detection should be such a
performance hog, but that is just a feeling. I have never played
with it, so I've posted a question to my MVP mates, to see if thety
have anything to add.

> Auto shrink:
> When it is turned on, does it affect performance?
> Personaly, I don't think so, but somebody is insysting on it, and I
> would like to have an information from independent source about that.

There are rarely any reasons to have auto-shrink on. And yes, it
affects performance. The shrinking takes power of the machine, and
may incur looking. But not only that, things that have shrunk may
grow again, and auto-grow can be painful.

Generally, while databases in SQL Server grow dynamically, for a well-
tuned system it's best to plan ahead, and grow the database during
maintenenace windows. Auto-grow during production hours should be
avoided.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]