5

I've run sp_blitz (version 45) on our test database server. It complains about

Database [MDS] has TORN_PAGE_DETECTION for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

But this is a database from Microsoft.
Is it wise to change the Page Verification to CheckSum?

Or should we wait for Microsoft to release a new version of MDS?

3
  • @Brent: well, it's more a question on what to do on databases that Microsoft provide. Should you expect your ISV to use and follow their own best practice? Or do you fix it yourself and hope the best? Jan 8, 2016 at 13:37
  • There's two ways ISVs handle this: either they give you the ability to do it, and expect you to, OR they give you a locked-down database and don't allow you to change anything lest you lose support. They both have their own pros and cons. I blogged about it here: brentozar.com/archive/2014/12/whos-allowed-add-indexes-isv-apps
    – Brent Ozar
    Jan 8, 2016 at 13:42
  • 1
    it's 2023, using MS SQL 2022, and still MDS page_verify_option_desc is set to TORN_PAGE_DETECTION, it seems that there is no change
    – Mandana
    Jul 15 at 12:39

1 Answer 1

11

I would suggest you follow what the sp_blitz recommended. You can change the option to CHEKSUM. Checksum might be able to detect more problem that torn_page.

Torn page allows you to detect whether page was successfully written to disk or not. It would not check what is inconsistency inside the page. While checksum performs more thorough checks. Having said all this you must know there is no replacement of good backup and you must (if possible) run dbcc checkdb every day.

What Paul said, quoting from This SQLServercentral link

The difference is 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?.

Following is what BOL document has to say

Consider the following important points when you use the PAGE_VERIFY option: • The default is CHECKSUM.

• When a user or system database is upgraded to SQL Server 2005 or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM

In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008 and later versions, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation SQL Server, the default value remains NONE. The option can be modified. We recommend that you use CHECKSUM for the tempdb database.

NOTE: If you enable page checksum it would not be enabled for entire pages of the databases. There is no process as such which puts this option for all pages. When a certain page would be read in memory, changed, and written back to disk checksum would then be enabled for that page. So you would have to perform operation like index rebuild to bring as much page as possible in memory. Paul Randal has Myth section related to Checksum please read it. Databases that are created on SQL Server 2005 and 2008 will have page checksums enabled automatically unless user changes the setting in the model database.

2
  • I think I will follow your recommendation as it is not unlikely that the next version of MDS will use CheckSum. I hope it does not break anything... Jan 8, 2016 at 14:32
  • No it would not break anything. That is internal feature to check consistency it has nothing related to Vendor specific thing
    – Shanky
    Jan 8, 2016 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.