torn page detection and auto shrink: performance?

Hello everybody,

First: SQL Server 2000 sp3a, HP cluster server, MS 2003 server,
database recovery model simple

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?

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.

Best regards

Matik

Re: torn page detection and auto shrink: performance?

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: torn page detection and auto shrink: performance?

Erland Sommarskog (esquel@sommarskog.se) writes:
> 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.

I got some answers. A MS developer told me that updates should not be
affected at all, as they don't look at the checksums then. The
Checkpoint/Lazy writer processes would be affected, but user threads
should not notice this. My own reaction is that if you are seeing
degradation with torn-page detection, it may be a token of the same
problem that causes your backups to hog the machine.

Another MVP told me this about torn pages:

One thing I think that should be mentioned regardless of the
performance discussion is that he/she needs to not think that since
they have write cache and a Raid that they are safe from torn page's.
I had a system that had a raid backend with cache and all sorts of
"redundancy" built into it and it took one admin moving cables on the san
switch to mess things up and cause torn pages. I'm thankful for the
detection since I didn't lose the whole database or cause more issues and
was able to fix it. So it might be good to mention that just because you
have Hardware doesn't mean your always safe. :)

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

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

Re: torn page detection and auto shrink: performance?

Thnks Erland! As always good tips.

I thinks now I've something to think about, what is going on there :(
I'm really twisted with that a little, what is going on there.

You've mentioned, that the backup shouldn't affect performance ... but
it might affect IO operations (post regarding backups). The problem is,
I've a lot of insert and update operations by the running system.
Regarding to that, if there will be a backup performed, which already
affect disk write procedure, additionaly, there can be not so much
space for this processes running (??).

I hardly doubt (after lecture of posts) that this might be an issue, I
think, that you are booth right, that the reason is somewhere else ...
that's why, I'll play cat and mouse now ...

Thank you very much

Matik

Re: torn page detection and auto shrink: performance?

Matik (marzec@sauron.xo.pl) writes:
> You've mentioned, that the backup shouldn't affect performance ... but
> it might affect IO operations (post regarding backups). The problem is,
> I've a lot of insert and update operations by the running system.
> Regarding to that, if there will be a backup performed, which already
> affect disk write procedure, additionaly, there can be not so much
> space for this processes running (??).

Are you placing the backup on the same disk as where the data or log file
is? That would not be a good idea anyway.

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

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

Re: torn page detection and auto shrink: performance?

Yes Erland, this is what I'm doing. This is not a single HD, it's a
raid controler.
I thougth, it shouldn't be a problem. But in fact, you are right: if
thera are already operations with any device, on one chanel, any other
operation for writing/reading data using the same device (or chanel)
should slower down the first one:)

At the end, everything comes up much more clearly ...

Matik

Re: torn page detection and auto shrink: performance?

Erland,

So I've changed the database model, and it seems to look much better
than it was before. And I have up-to-time recovery possibility.

This has no influance on perfomrmance, what I've noticed, so I'm very
pleased :)

There is just one more question bothering me.

Curently, database size shows 18GB, but the used space shows 8GB.
For a log file, it shows 8GB but the used space is 34MB.

With my understanding, it is better not to shrink these files, because
during running processes, sql server will expand size of the files
anyway.
Shirinking files, will cause that by expanding files, sql server will
need more time, because of generation more io traffic (right?).

But, from the other side, I do not need to keep this files so big!
(This probably remains big anyway from my old model, where I've also
have had not optimized "delete old data" cleaning job).

The question then is: is it better to shring time-to-time, or let say
now, the database files, or leave it just in this way?

Gratings

Matik

Re: torn page detection and auto shrink: performance?

Matik (marzec@sauron.xo.pl) writes:
> So I've changed the database model, and it seems to look much better
> than it was before. And I have up-to-time recovery possibility.
>...
> Curently, database size shows 18GB, but the used space shows 8GB.
> For a log file, it shows 8GB but the used space is 34MB.

Used log space of 34 MB sounds suspiciously small to me, when you at
the same time say that you have point-in-time recovery possibility.
That is, you are running with full (or bulk-logged) recovery. Then
again, 34 MB of used log space is alright if you recently backed up
the transaction long.

> With my understanding, it is better not to shrink these files, because
> during running processes, sql server will expand size of the files
> anyway.
> Shirinking files, will cause that by expanding files, sql server will
> need more time, because of generation more io traffic (right?).
>
> But, from the other side, I do not need to keep this files so big!
> (This probably remains big anyway from my old model, where I've also
> have had not optimized "delete old data" cleaning job).

Let's take the data and log files separately.

If you have a data file of 18 GB and only 8 GB used, and you don't
expect any particular growth, you could shrink the file to 10 GB.
One thing to keep in mind here is the size of your biggest table.
There should be enough free space to permit reindexing of this table.
(I believe that when a table is reindexed it is essentially moved to
new fresh pages, but I have not investigated this in detail, and I
may be wrong on this.)

But if you expect the database to continue to grow, then there is little
reason to shrink it.

If you do shrink your data file, you should then reindex all tables
to defragment them, as shrinking often leads to serious fragmentation.

For the log file, you need to find the peak size of the log file. 34 MB
in use may be now, but if you run some heavy maintenance job, you may find
that 2GB of the log is used. (Reindexing for example. But run reindexing
with bulk-logged recovery, that reduces the log growth considerably.

8GB log for the aame amount of data sounds a bit excessive, but it
depends a lot on the operations you do on your data. I guess one way
to find out the peak size, is to keep an eye on the size of the log
backups, particularly after you have performed some large update, such
a reindexing job.



> The question then is: is it better to shring time-to-time, or let say
> now, the database files, or leave it just in this way?
>
> Gratings
>
> Matik
>



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

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

Re: torn page detection and auto shrink: performance?

Erland,

I've changed to full model (not bulk).
I'm backuping transaction log each 15 minutes (maybe thats why the used
space is so small).
Transaction log backups are aprox. 36MB big. I saw sometimes, its
around 100MB. But in general, they are not exceeding 36MB.

I'm expecting the database to growth, but not much. Maybe about
two-four additional GB's.

My reindexing runs once a week, on sunday. So the shrink (which is not
turned on yet).
What I've observed, since I've changed that, the database is running
much more stable than before it.
Seams to be much faster.

Each transaction log backup takes around 1-5 Seconds. Full Backup now,
takes only 10-12 Minutes togethet with optimisation.
Removing old data, which is done each 10 minutes, takes around 10-20
seconds.
And it all seams to be much better now:)

Regards

Matik

Re: torn page detection and auto shrink: performance?

Are procedures with this frequency typical?!

We usually perform operations like these daily at most. If you are
keeping the backups on the same raid as the real data, I think you have
some room to improve your recovery model.

Then again, if your setup is this serious, you could implement some
sort of live backup server...or is that what you have?

Re: torn page detection and auto shrink: performance?

Matik (marzec@sauron.xo.pl) writes:
> I've changed to full model (not bulk).
> I'm backuping transaction log each 15 minutes (maybe thats why the used
> space is so small).
> Transaction log backups are aprox. 36MB big. I saw sometimes, its
> around 100MB. But in general, they are not exceeding 36MB.

Seems like you should be able to shrink the log quite a bit. I would
settle for 1GB, although you might be tempted to set it to 500 MB, but
I would not go any lower than this.




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

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

Re: torn page detection and auto shrink: performance?

figital (mharen@gmail.com) writes:
> Are procedures with this frequency typical?!

Backing up the log every 15 minutes? Yes, I would expect many shops to
this.

> We usually perform operations like these daily at most.

And many shops do it that way. It all depends on your needs.

I recall many years ago, one of our customers had a fire io a disk
cabinet, and I went there to run DBCC to see if the database was in
shape. It wasn't, but what was worse, when I restored a backup that
also gave me a corrupted database, although it was different errors.
I think we had to go back a week to find a good backup. The customer
was quite nervous and eager to know when we had the database back up.
But that was for the read access, so that they could answer customers
on the phone. The fact that they had to re-register transactions for a
week was no big deal. This being a financial institution, they had
their transacion log on paper so to speak.



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

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

Re: torn page detection and auto shrink: performance?

Hey figital,

It is not a live backup server (not sure what You mean with that).
The thing now is, that performing this kind of backup takes short time.
In other case, it will take much more longer.
Also, this is a production database, where the information are changing
very fast (for example, some tracking informaction, which are rewriten
each 30 seconds for one point ... and there is a LOT of this points:)

Truly said, since I've changed this, I'm out of my problems. The system
is running stable, and I'm out of the problems which I had before.

There is one more thing which I might to consider: implemeting a second
raid controler, that the backups and database it self will be on a
diferent phisical device (suggested before by this thread).

Matik

Re: torn page detection and auto shrink: performance?

Erland,

Thank you! I think, I will do this like you said. Just for testing, it
will not harm. Then, I can whatch it during the week, and see, will it
have any influance on performance, or processes at all.

Greatings

Matik