Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Turn on CHECKSUM after an upgrade


Turn on CHECKSUM after an upgrade

Author
Message
David BAFFALEUF
David BAFFALEUF
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 712
Comments posted to this topic are about the item Turn on CHECKSUM after an upgrade

David B.
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
A good article to point out the checksum "issue".

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 checksum
4. 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.

Best Regards,
Steve
dma333
dma333
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 27
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...



John Santos
John Santos
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 29
I would like to know more about this. The article does not explain what occurs when this is not enabled, nor does it mention if there is a performance hit, and if so,what is the impact on performance.

Thanks.
BobAtDBS
BobAtDBS
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 356
Add me to the list. What is the best protection, and how much (approximately) of a performance hit are we looking at?


Student of SQL and Golf, Master of Neither
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
when i upgraded our netbackup software i turned on checksum for most databases and didn't see any impact, but didn't do too much of a scientific study

to get the pages converted from TPD, can you rebuild the clustered index on the table? will it work?
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
A few links were found:


http://www.sqlskills.com/blogs/paul/category/Page-Checksums.aspx
"In benchmarking tests with a TPCH workload during SQL Server 2005 development, we measured approx 2% performance degradation as a result of having checksums enabled. "

I cannot get this page to load, but based on the title it seems like it will have pertinent information:
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

Regards,
Steve
Sankar Reddy
Sankar Reddy
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1250
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.

http://sankarreddy.com/2010/03/performance-impact-of-using-backup-checksum-in-sql-server-20052008/


Sankar Reddy | http://SankarReddy.com/
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
Sankar,

Thanks for the clarification. Apologies for any confusion all.

Steve
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for this article, it helped remind me of a step I need to make sure is in place for our next SQL Server upgrade.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search