Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Turn on CHECKSUM after an upgrade Expand / Collapse
Author
Message
Posted Monday, July 19, 2010 8:29 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
Comments posted to this topic are about the item Turn on CHECKSUM after an upgrade

David B.
Post #955250
Posted Monday, July 19, 2010 8:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
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
Post #955252
Posted Tuesday, July 20, 2010 6:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 8:22 AM
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...


Post #955499
Posted Tuesday, July 20, 2010 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 29, 2011 3:19 PM
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.
Post #955538
Posted Tuesday, July 20, 2010 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335
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
Post #955552
Posted Tuesday, July 20, 2010 7:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
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?


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #955590
Posted Tuesday, July 20, 2010 7:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
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
Post #955591
Posted Tuesday, July 20, 2010 8:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 382, Visits: 1,177
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/
Post #955635
Posted Tuesday, July 20, 2010 8:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:45 AM
Points: 968, Visits: 971
Sankar,

Thanks for the clarification. Apologies for any confusion all.

Steve
Post #955636
Posted Tuesday, July 20, 2010 10:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #955741
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse