CHECKSUM and Tempdb

PLEASE NOTE THAT THE HYPERLINK HEREIN MAY BE BROKEN.

You may recall that starting with SQL Server 2005, you have an option available to enable CHECKSUM on the user databases.  For details, please refer tohttp://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx.  In fact, any new database created in SQL2005 has CHECKSUM enabled automatically but it does not happen to databases that are upgraded from previous versions of SQL Server. For the upgraded databases from SQL Server versions earlier than SQL Server2005, you will need to enable CHECKSUM explicitly using ALTER DATABASE command. Enabling CHECKSUM is critical and it allows SQL Server to detect the corruption in the IO path (e.g. a disk malfunction) when the page is read as part of query or when you run DBCC CHECKDB command. While this has been very useful to our customers, there was one missing link and that was that SQL Server did not allow enabling CHECKSUM on tempdb. This provided a window where a page corrupted due to mis-behaving disk found its way into user database even if you had enabled CHECKSUM on the databse. Here is one such scenario

PLEASE

Scenario:: bulk import the data into a temp table for staging purposes and then move it to the user database. The user database has CHECKSUM enabled, so the new page, when written to the disk, will have checksum computed. But guess what is missing? If the tempdb disk corrupts the pages in tempdb, SQL Server will have no way of knowing that the page was corrupted and it will go to user database without detection. Yes, when the page is subsequently read, depending upon what the corruption was, the SQL Server may detect it or may not. For example if a bit flip happened for the integer value, it will go undetected.

With CHECKSUM available on tempdb starting with SQL2008, you can finally close this window.  You can use the following command

ALTER DATASE tempdb set PAGE_VERIFY CHECKSUM

For new installs of SQL Server 2008, all tempdbs will have CHECKSUM enabled by default. You can always disable it using ALTER DATABASE command but we don't recommend it. For databases upgraded to SQL Server 2008, you will need to explicitly enable CHECKSUM on the tempdb. We measured the performance impact of enabling CHECKSUM in tempdb and the impact is very low (<2% of CPU) which is similar to what you would expect in user database. Since the CHECKSUM is only computed when page is written to the disk, the added point is that there is no 'checkpoint' in tempdb, so a page in tempdb is written to disk ONLY under memory pressure. So you may not see as many CHECKSUM calculations in tempdb.