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.
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
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.
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.
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.
Last Update: 2017-10-06
About the author
View all my tips