The one tip that I think the community would benefit from is how to ensure the bit is flipped on and that all pages are read (maybe I read through too fast and didn't see the how).

Per Paul Randal (unfortunately don't have a link handy), a DBCC CHECKDB([dbname]) WITH DATA_PURITY should be ran in order to get the checksum fully implemented after the upgrade from SQL 2000.

With that enabled, errors 823, 824, and 825 will be triggered when there is a potential for disk read/write failures, and I have new SQL Agent alerts set up for each of those errors in order to receive those warnings before they become major issues.

The steps I follow when upgrading a DB:1. Restore the DB to SQL 2005/2008.2. Change compatibility level to 90 or 100.3. Change to page verify checksum4. DBCC CHECKDB with data_purity (to ensure checksum is good to go)5. DBCC UPDATEUSAGE (as a best practice to get the usage data up-to-date after the upgrade) -- sometimes needed to correct issues found by the data_purity check as well.

I'm curious to know if anyone has done an analysis on the impact of CHECKSUM on insert/update/delete performance. There must be some tangible penalty. In the 2000 days, when you wanted to maximize performance, you switched off even torn_page detection, so I'm assuming there's an even more significant penalty when undergoing the scrutiny of CHECKSUM...

S.K. (7/19/2010)Per Paul Randal (unfortunately don't have a link handy), a DBCC CHECKDB([dbname]) WITH DATA_PURITY should be ran in order to get the checksum fully implemented after the upgrade from SQL 2000.

DBCC CHECKDB WITH DATA_PURITY has nothing to do with CHECKSUM. DATA_PURITY will check the values stored in the columns are valid for the data type. For databases created in SQL Server 2005 and above, these are done automatically when you run CHECKDB but for the databases created earlier you have to explicitly flip the bit by running the DATA_PURITY switch in the DBCC CHECKDB command.

When you enable PAGE_CHECKSUM, it doesn't really go after all the data pages and write the checksum. There is no command like that to write the CHECKSUM in all of the pages. One way is to rebuild all indexes and this will cover only the CI, I but heaps are NOT touched. For heaps, you may have to do an in-place update like below.

Update heap set c1 = c1

Disabling CHECKSUM to get some performance benefit is NOT a good idea. There are so many other things you can do that will get lot more bang for the time spent. Please don't do that.

On this related topic, I did some tests on the impact of enabling BACKUP CHECKSUM and teh results were very interesting. I did ask MSFT team on why would this happen and didn't get an answer so far. So, my take away point here, always test even if you happen to know teh answer and take action on the first hand information.