Post reply

Question about Clustered PKs

  • August 9, 2011 at 11:02 am

    #1366079

    Updates that increase the size of the row

    Database or file shrinks.

    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

  • August 9, 2011 at 11:03 am

    #1366080

    Only 1 really likely suspect.

    The application is doing insert into default values. Or something like that.

    This reserves them the next id. After that they run an update filling all the columns. This causes the row to grow and eventually a page split happens.

    Easy fix is to only do the insert once all the fields are known and then avoiding 99% of the updates.

    Lower fill factor may help a little bit here, but that's not the root cause of the issue... and I wouldn't want to have to put 30% FF just to avoid the split.

Viewing 2 posts - 1 through 3 (of 3 total)

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