SQLServerPedia

From SQL Server 2005 onwards, CHECKSUM is available as a PAGE_VERIFY option at the database level. All databases created in SQL Server 2005 & above will have CHECKSUM enabled by default. Even tempdb in SQL Server 2008 has this option enabled. It is recommended by MSFT to change the PAGE_VERIFY option of databases to CHECKSUM when migrating from earlier versions to SQL Server 2005 and above.

Why should I enable PAGE CHECKSUM?

CHECKSUM has a robust algorithm compared to TORN_PAGE_DETECTION option which was the default in previous versions. Database pages can get corrupted outside the scope of SQL Server and CHECKSUMS are a great way to detect corruption caused by IO subsystems. When a dirty database page is written to disk, SQL Server computes the CHECKSUM of the contents of the page and stamps the value in the page header just before the page is written to disk. When that page is read back into memory, SQL Server re-computes the CHECKSUM and if that value doesn’t match with the value stamped on the header then someone else wrote to this page outside the scope of SQL Server. By enabling CHECKSUMs one should be able to isolate the IO subsystem as the root cause of corruption.

The key to un-covering corruption above is, only if the page is read back from the disk. What happens if the page is *NOT* read back for a week or say a month. Corruption will be your house guest and you may not notice it and there is a potential chance that you may end up with loosing data.

Why should I use BACKUP CHECKSUM?

When BACKUPs are taken with the CHECKSUM option, SQL Server will verify and re-compute the CHECKSUM/ TORN_PAGE_DETECTION of all pages and gives you extra protection that PAGE CHECKSUMS can’t handle. This comes very handy if the PAGE_VERIFY is set to CHECKSUM or TORN_PAGE_DETECTION. Even if both the options are turned off then BACKUP CHECKSUM will still compute a combined CHECKSUM of all pages and stamp the value on the backup media. This can be used to re-compute the CHECKSUM before restoring to make sure the backup itself is intact.

Impact of using PAGE CHECKSUM:

Linchi Shea did some testing on this topic a while back and I strongly recommend you to visit his work at Performance impact of enabling page checksum and default trace. As per Linchi and MSFT, the total cpu cost of enabling PAGE CHECKSUM is very small and mostly in the range of 1-2%.

Impact of using BACKUP CHECKSUM:

BOL mentions there is a performance penalty for enabling the BACKUP CHECKSUM and here is a quote from BOL.
“ When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup. Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system. ”

I was curious to measure the impact of the BACKUP CHECKSUM and went ahead with 6 different options listed below with my testing. SQL Server 2005 with Service Pack 2 is used and I measured the performance using SQL Profiler. I also did similar testing on SQL Server 2008 with SP1 with almost identical results on small databases. I wish I had better hardware on SQL Server 2008 instance.

1) Database with PAGE_VERIFY set to NONE & use BACKUP WITH CHECKSUM
2) Database with PAGE_VERIFY set to NONE & use BACKUP WITH OUT CHECKSUM
3) Database with PAGE_VERIFY set to TORN_PAGE_DETECTION & use BACKUP WITH CHECKSUM
4) Database with PAGE_VERIFY set to TORN_PAGE_DETECTION & use BACKUP WITH OUT CHECKSUM
5) Database with PAGE_VERIFY set to CHECKSUM & use BACKUP WITH CHECKSUM
6) Database with PAGE_VERIFY set to CHECKSUM & use BACKUP WITH OUT CHECKSUM

Environment:

SQL Server 2005 SP2 64 bit Developer Edition.
4 Processors, 8 GB RAM, RAID 5

Testing:
Each data point has been run 3 times and the average of them is considered. CPUTime and Duration are measured for every test using Profiler. A new database is created with the right PAGE_VERIFY option and data is populated for every test. These tests are done for databases with 4, 8, 16, 32 GB.

The results were startling to me and I didn’t expected the results.

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to NONE (least recommended)

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to TORN_PAGE_DETECTION

BACKUP WITH CHECKSUM vs BACKUP when PAGE_VERIFY is set to CHECKSUM

BACKUP CHECKSUM when PAGE_VERIFY is set to NONE, TORN_PAGE_DETECTION & CHECKSUM

From the above results, the least expensive method is when PAGE_VERIFY is set to CHECKSUM and the backups are taken with CHECKSUM option and the most expensive is when TORN_PAGE_DETECTION is set for PAGE_VERIFY.

So, Do I recommend adding CHECKSUM option while taking the backups? Definitely if you have cpu cycles to spare then I would highly recommend adding this option [CHECKSUM] to the BACKUP or if you don’t have enough confidence on your IO subsystem. In this post I tried to show the actual cost of adding this option so that one can take an informed decision about the added cost. Also, note that BACKUP CHECKSUM is *NOT* a replacement to run the full-fledged DBCC CHECKDB which does a lot more checking to identify corruption.

Acknowledgements:
Performance Impact of Enabling Page Checksum and Default Trace
Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors
How do I know if my earlier backups used CHECKSUM?
Checksum in SQL2005