Post reply

High page splits/sec

  • January 31, 2013 at 10:05 am

    #273647

    Hi,

    I'm getting the following page splits/sec values over the course of a day

    Mean783.2

    Max3452.0

    Min40.2

    And the following batch requests/sec

    Mean435.2

    Max1467.6

    Min108.3

    The page splits are way to high, definitely doesn't fall below 20% of batch requests. I've been looking into the indexes but there nothing really shouting out at me. They get rebuilt every night with fill factors etc depending on fragmentation.

    Can anyone give me suggestions on what to look for so I can pin point the cause of this?

  • January 31, 2013 at 10:31 am

    #1583112

    Are you by any chance using GUIDs as ordered primary keys? This is causing similar issues for us in a legacy system.

  • January 31, 2013 at 2:07 pm

    #1583183

    Not using guids, we are using identity columns

  • January 31, 2013 at 3:13 pm

    #1583208

    What value are you using for fill factor?

  • January 31, 2013 at 3:54 pm

    #1583219

    Hi, most of the time im using 90 sometimes 0

  • February 1, 2013 at 5:27 am

    #1583381

    Anyone else had high page splits?

    Thanks

  • February 1, 2013 at 5:33 am

    #1583385

    Sure, often a lot higher that what you have there.

    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

  • February 1, 2013 at 6:13 am

    #1583408

    GilaMonster (2/1/2013)

    Sure, often a lot higher that what you have there.

    Hi Gila,

    I read somewhere that page splits/sec should not be more then 20% of the batch requests/sec, mine are about 200% more if not greater. I am slowly working through indexes to try resolve this. Could my hard drive RAID setup also impact this? Are there any other things i can look for that may affect page splits?

    Many Thanks

  • February 1, 2013 at 6:26 am

    #1583418

    Page splits are caused by inserts into the middle of indexes and updates that grow the row, nothing else. They're internal to SQL.

    As for thresholds, take them with a grain of salt. If you think the page splits are too high, identify the indexes that are splitting (which is damn hard in SQL 2008 and below, as there's nothing that tracks mid-index page splits per index) and drop the fill factor down, make sure that clustered indexes are, as far as possible, on increasing columns.

    You can't prevent page splits entirely.

    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

  • February 1, 2013 at 7:38 am

    #1583451

    GilaMonster (2/1/2013)

    Page splits are caused by inserts into the middle of indexes and updates that grow the row, nothing else. They're internal to SQL.

    As for thresholds, take them with a grain of salt. If you think the page splits are too high, identify the indexes that are splitting (which is damn hard in SQL 2008 and below, as there's nothing that tracks mid-index page splits per index) and drop the fill factor down, make sure that clustered indexes are, as far as possible, on increasing columns.

    You can't prevent page splits entirely.

    Thanks Gila, I'm not sure if the page splits are too high, but they are 4 times greater then the batch requests so I was worried .. Maybe I have no need to be :ermm:.

    Thanks for the recommendations much appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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