February 23, 2009 at 2:04 pm
I have seen a lot about checksum and I understand the differences between the checksum and torn_page_detection. I also know that the checksum adds about a 2 percent load on the server. Does anyone have any data on how much of a load the torn_page_detection adds to the database worload? I would be curious to see the tradeoff between the corruption detection methods.
Thanks,
Brian
February 23, 2009 at 2:17 pm
Unmeasurably small - all it's doing is grabbing the first two bits from each of the 16 sectors, storing them in the page header, and then writing an alternating bit pattern into the two bits in each sector. And the reverse when the page is read again. Almost nothing compared to what a page checksum is doing by reading the whole page and adding the contents to a 'checksum'.
However, torn-page detection does not detect corruption within sectors - only when a write fails some way through. On 2005 onwards you should always have page checksums enabled for this extra benefit - 1-2% CPU shouldn't be an issue (otherwise you've got other problems).
Some blog posts around this: How to tell if the IO subsystem is causing corruptions? and Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 8, 2011 at 12:10 am
HI Paul,
That was good aritcal.In My servers database defultly kept in PAGE_VERIFY =NONE ; which is the best option on both
torn_page_detection vs checksum.
Please advise me
Regards
venkat
July 8, 2011 at 1:57 am
Checksum. Detects far more problems than torn page ever could.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2011 at 3:35 am
HI Gail,
Thank you for the relpy.If i change to CHECKSUM suddenly any effects will happen .
Regards
venkat
August 25, 2011 at 12:22 am
Hi kvr,
I would say no.
Maybe a slight increase in CPU consumption (as Paul already mentioned) if you have a system which writes/modifies a lot of pages.
So run your databases all with CHECKSUM enabled and you´re fine.
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply