Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
2  
Wow, cool to see sp_Blitz support questions over here on Stack. :-D – Brent Ozar Jan 8 at 12:57
    
@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? – Henrik Staun Poulsen Jan 8 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 at 13:42
up vote 9 down vote accepted

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 changes and written back to disk this option 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 has Myth section related to Checksum please read. Databases that are created on SQL Server 2005 and 2008 will have page checksums enabled automatically unless you change the setting in the model database

share|improve this answer
    
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... – Henrik Staun Poulsen Jan 8 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 at 14:40
    
thank you very much. – Henrik Staun Poulsen Jan 8 at 14:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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