Sql-server – sp_Blitz says “Page Verification Not Optimal” on MDS database

master-data-servicessp-blitzsql server

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?

Best Answer

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.

Related Question