SQL Server Page Verify CHECKSUM vs NONE Performance

By: Simon Liew   |   Updated: 2017-10-06   |   Comments (1)   |   Related: > Database Configurations

Problem

An application developer asked if changing a user database Page Verify setting to NONE from CHECKSUM can boost performance for INSERTs, UPDATEs and DELETEs, because SQL Server does not need to perform overhead associated with page checksum calculation. In this tip we take a closer look to see the impact.

Solution

Page Verify is a database option which defines the SQL Server mechanism of verifying page consistency when the page is written to disk and when it is read again from disk.

To get the properties for a database, right click on the database name in SSMS and select Properties. The go to the Options page and scroll through the options to find the following as shown below.

Page Verify NONE is not and never has been associated as a performance boost option in SQL Server. It is highly recommended to set a database to use Page Verify CHECKSUM, which is also the default setting for databases on SQL Server 2008 and higher.

But if you wish to compare query performance between Page Verify CHECKSUM vs. NONE, then the section below outlines a test simulation to benchmark the two database options.

Performance Benchmark – SQL Server Page Verify CHECKSUM vs. NONE

For the performance benchmark purposes, we will create a test database with the script below.

-- First Block of Code
USE master
GO
DROP DATABASE IF EXISTS [PageVerifyTest]
GO
CREATE DATABASE [PageVerifyTest]
GO
ALTER DATABASE [PageVerifyTest] MODIFY FILE ( NAME = N'PageVerifyTest', SIZE = 2GB )
GO
ALTER DATABASE [PageVerifyTest] MODIFY FILE ( NAME = N'PageVerifyTest_log', SIZE = 2GB )
GO
ALTER DATABASE [PageVerifyTest] SET RECOVERY SIMPLE;
GO
ALTER DATABASE [PageVerifyTest] SET PAGE_VERIFY CHECKSUM  
GO

The database Page Verify will be initially set to CHECKSUM, which is the default database configuration option. The SQL Server version used for this benchmark testing is SQL Server 2016 Developer Edition on Service Pack 1. SQL Server maximum memory is set to 500 MB. The test machine used in this benchmark has 4 CPU cores on local attached Solid State Disks with no other workload running during the test.

After the database has been created, we will execute the script below to benchmark the performance of INSERT and UPDATE queries when the database Page Verify option is set to CHECKSUM.  An explicit transaction is wrapped around the INSERT and UPDATE operation, so the wait bottleneck is not on the log flush performing small transactions.

The script will insert and update 1 million rows in the benchmark process. Approximately 1.1 GB of database data space is occupied, hence the initial database size created is 2 GB.

-- Second Block of Code
USE [PageVerifyTest]
GO

CREATE TABLE dbo.mssqltips (increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(1000) DEFAULT 'a')
GO

CREATE UNIQUE CLUSTERED INDEX CIX_mssqltips_increment ON dbo.mssqltips (increment)
GO

SET NOCOUNT ON;
DECLARE @counter INT = 1;
BEGIN TRAN
WHILE  @counter <= 1000000
   BEGIN
      INSERT INTO dbo.mssqltips (increment, randomGUID, randomValue) 
      VALUES (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)

      SET @counter += 1
   END;
COMMIT TRAN;

DECLARE @increment INT, @randomGUID uniqueidentifier, @randomValue INT

DECLARE update_cursor CURSOR FOR   
SELECT increment, randomGUID, randomValue
FROM dbo.mssqltips

OPEN update_cursor  
FETCH NEXT FROM update_cursor INTO @increment, @randomGUID, @randomValue  

BEGIN TRAN
WHILE @@FETCH_STATUS = 0
   BEGIN  
      UPDATE dbo.mssqltips SET 
      @randomGUID = NEWID()
      , randomValue = ABS(CHECKSUM(NewId())) % 1400000000
      WHERE increment = @increment

      FETCH NEXT FROM update_cursor INTO @increment, @randomGUID, @randomValue    
   END  

CLOSE update_cursor  
DEALLOCATE update_cursor;
COMMIT TRAN;
GO 

Test Process

The test scripts contain 2 blocks of code.

The first block of code creates a [PageVerifyTest] database and the Page Verify option is set with the last T-SQL command of the script.

Page Verify = CHECKSUM

The first test will be to use CHECKSUM.  This is the very last line in the first block of code.

ALTER DATABASE [PageVerifyTest] SET PAGE_VERIFY CHECKSUM
GO

Page Verify = NONE

To test Page Verify option NONE, the last T-SQL command in the first block of code is replaced with the code below and then both blocks of code can be run again.

ALTER DATABASE [PageVerifyTest] SET PAGE_VERIFY NONE
GO

The second block of code INSERTs and UPDATEs rows in the database.

Benchmark Results

The test scripts are executed 10 times for each Page Verify option (CHECKSUM then NONE).

Each test run involves dropping and recreating the [PageVerifyTest] database with the appropriate database Page Verify option.  It then measures the execution duration of the second block of code which inserts 1 million rows and then updates each of the 1 million rows.

The execution duration is captured in seconds and the results are shown below:

The test result shows an average duration difference of 0.4 seconds between Page Verify CHECKSUM vs. NONE. The result does not indicate a noticeable performance difference between CHECKSUM vs. NONE.

Conclusion

If your database is struggling with performance, you should spend time to troubleshoot the issue and take the appropriate action. Setting the database Page Verify option to NONE will not make queries perform any noticeably faster and it is also against Microsoft's best practice recommendation.

You should feel comfortable setting your database Page Verify option to CHECKSUM. If you still doubt if the CHECKSUM protection is worth the insignificant overhead, my next tip will highlight a scenario where undetected corruption can occur unnoticeable when Page Verify is left at NONE.

Next Steps

• Set the PAGE_VERIFY Database Option to CHECKSUM
• CHECKSUM and Tempdb
• Checksum in SQL2005

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips