Wednesday, March 26, 2008

SQL Server Storage Engine : CHECKSUM and Tempdb

The most important part of any database is the data inside.  Like anything, there's always a tradeoff between performance and stability.  Better to err on the stability side.

ALTER DATABASE 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.

Note, this is not available in CTP-6 but will be available in RTM bits.

SQL Server Storage Engine : CHECKSUM and Tempdb

No comments: