SQL Server Performance Forum – Threads Archive

Torn Page Detection Off?

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?

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.

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

… 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.

]]>