Torn Page Detection

I have a production database that processes 600 transaction/sec. and 300
concurrent users.  This is a SQL Server 2000 Enterprise Edition database in 
a
cluster server with Windows 2003 server.  This is database size is
approximately 300 GB.

Please give me the positives and negatives that go along with this parameter
setting.

Thank You,

Hi,

Positive:-

Allows SQL Server to detect incomplete I/O operations caused normally by
power failures or other system outages. So you can go ahead
and solve the issue immediately

Negative:-

Slight performance bottle neck.  In certain high performance apps (more than
thousands transactions / sec)
they may turn it off to get every last bit of performance. Most of the cases
there will not be any issue.

Thanks
Hari
SQL Server MVP



"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:2C0D2DF5-9BA5-4F59-A1AB- C4D19D55D004@microso
ft.com...
>
> I have a production database that processes 600 transaction/sec. and 300
> concurrent users.  This is a SQL Server 2000 Enterprise Edition database
> in a
> cluster server with Windows 2003 server.  This is database size is
> approximately 300 GB.
>
> Please give me the positives and negatives that go along with this
> parameter
> setting.
>
> Thank You,

Definitely turn it on in your case.

--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix

This posting is provided "AS IS" with no warranties, and confers no rights.

"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message
news:e9UOCljYFHA.3188@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> Positive:-
>
> Allows SQL Server to detect incomplete I/O operations caused normally by
> power failures or other system outages. So you can go ahead
> and solve the issue immediately
>
> Negative:-
>
> Slight performance bottle neck.  In certain high performance apps (more
> than thousands transactions / sec)
> they may turn it off to get every last bit of performance. Most of the
> cases there will not be any issue.
>
> Thanks
> Hari
> SQL Server MVP
>
>
>
> "Joe K." <Joe K.@discussions.microsoft.com> wrote in message
> news:2C0D2DF5-9BA5-4F59-A1AB- C4D19D55D004@microso
ft.com... 
>
>

The MS recommendation is to leave torn page detection turned on....

I also recommend it, although there is a slight processor cost, it is very
nominal..

Torn pages will be detected at DBCC time anyway... Turning on this option
means that torn pages will be detected immediately on first use instead of
having to wait for a DBCC.

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:2C0D2DF5-9BA5-4F59-A1AB- C4D19D55D004@microso
ft.com...
>
> I have a production database that processes 600 transaction/sec. and 300
> concurrent users.  This is a SQL Server 2000 Enterprise Edition database
> in a
> cluster server with Windows 2003 server.  This is database size is
> approximately 300 GB.
>
> Please give me the positives and negatives that go along with this
> parameter
> setting.
>
> Thank You,