SQL Server Performance

Torn Page Detection Off?

Discussion in 'Analysis Services/Data Warehousing' started by biged123456, Nov 3, 2004.

  1. biged123456 New Member

    I have a 500G data warehouse on SQL2000. It is made up of 4 data files on separate arrays and the TL on a 5th array. The application running against it does 99% reads and some data inserts during the day. At night, large imports are also pulled into the database. We are starting to see higher CPU and disk queuing of late and I was trying to do anything I could to increase performance. We have already gone down the list of indexing tuning, adding HW, etc. I am now thinking about disabling torn page detection. The storage has redundant power, has a backup generator just incase and a battery backup for the write cache on the controller. If I do disable it and I do get a torn page, would a CheckDB find it? If not, how would I ever know?
  2. satya Moderator

    Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery. This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. thomas New Member

    I disable it on my data warehouse - it kept interrupting my load process overnight when TPs were detected. However my mart tables are truncated and re-loaded in their entirety every night - so data loss is not a danger in my situation. I always found that the database had no inconsistencies after a TP was detected either, so I didn't need to know when one had happened.

    Tom Pullen
    DBA, Oxfam GB
  4. satya Moderator

    ... and it will taken care as you have batter backup for write cache.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page