Post reply

How expensive are my page checksums

  • July 6, 2011 at 5:53 am

    #240870

    Hi,

    I have a server running SQL Server 2005 SP3 Standard Edition. This server experiences performance issues from time to time which are usually caused by processes taking >90% of the CPU, usually sqlservr.exe

    I have noticed that 11/27 of my databases have the page_verify_option set to checksum. Doing some reading it seems that the checksum option can incur additional CPU overhead, so I am considering switching these back to torn_page.

    I have a reliable I/O subsystem, so am comfortable with using torn page detection, but I was wondering if there was a way of telling how long the page checksums are currently taking, and how much CPU overhead they are causing. Then I would be able to guesstimate what sort of performance increase I should be seeing when I switch to torn page.

    Any info or links to documentation to help me with this would be greatly appreciated.

    Thanks,

    Bodsda

  • July 6, 2011 at 6:22 am

    #1348865

    Hi,

    The extra CPU usage is around 5% - 10% overall I believe. Paul Randal did a blog on the performance impact of turning on Checksum from torn pages.

  • July 6, 2011 at 6:58 am

    #1348899

    Don't. Seriously.

    Tune the queries that are causing the high CPU. You'll get a far better return on that than dropping back to a less reliable page verification. Bear in mind, even if you do switch to torn page, the pages that have checksums will keep those checksums until they are written back to disk. So depending how much writing you do vs reading, it could be some time before the checksums are gone.

    It is very hard to max out a modern CPU unless you have seriously inefficient queries. I've before now dropped a server's average CPU usage by more than 20% just with a couple days of query tuning work.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 6, 2011 at 7:40 am

    #1348955

    Unfortunately, I have no control at all over the queries on this server. All of the databases are from third party providers that we have a managed service with, so I am not allowed to tinker with things they have written.

    Bodsda

  • July 6, 2011 at 10:56 am

    #1349136

    I thought it was 1-2%, which Paul doesn't dispute here: http://www.sqlservercentral.com/Forums/Topic663009-266-1.aspx

    I do agree with Gail. If you cannot tune the queries, analyze them and send back information and suggestions to the third party providers, either about better queries or better indexing.

  • July 6, 2011 at 11:29 am

    #1349171

    Steve Jones - SSC Editor (7/6/2011)

    If you cannot tune the queries, analyze them and send back information and suggestions to the third party providers, either about better queries or better indexing.

    Definitely. If the vendor has written crap code that's killing your server then the vendor is responsible for fixing their mess.

    These may help you narrow down what you send back to the vendor for fixing.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 7, 2011 at 1:58 am

    #1349462

    Ah that's the post I was looking for, did look on Pauls website for that link.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply