Page Splits

  • I've got an SQL server where my avg. page splits per second is 3-4. However there is process that runs twice a day when this process is running my page splits go to 20-25.

    My question: Is this a high number for page splits or not.

  • There is no hard and fast answer to whether it is problemattic or not. It really depends a lot on what the impact of the page splitting is. If it creates waits or blocking/locking delays in your database then it may be problemattic. Unfortunately, finding out which specific object/index is getting the page split is not so intuitive. You can find the count of splits by object for the active portion of the transaction log with fn_dblog()

    SQL 2005

    select parsename([AllocUnitName], 3) [Schema],

    parsename([AllocUnitName], 2) [TableName],

    parsename([AllocUnitName], 1) [Index],

    count([Current LSN])

    from ::fn_dblog(null, null)

    where Operation = N'LOP_DELETE_SPLIT'

    group by parsename([AllocUnitName], 3), parsename([AllocUnitName], 2), parsename([AllocUnitName], 1)

    SQL 2000

    -- http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx

    select [Object Name], [Index Name], count([Current LSN])

    from ::fn_dblog(null, null)

    where Operation = N'LOP_DELETE_SPLIT'

    group by [Object Name], [Index Name]

    The examples above are based on Greg Linwoods blog post whose address is the comment on the SQL 2000 version. I just changed it to work for SQL 2005.

    Somethings to consider when looking at page splits:

    1. How many/how often are they occuring?

    2. What is the actual impact to the system when they occur?

    3. What is the residual impact to the system when they occur? (index fragmentation)

    Once you consider the above, the way you go about fixing it is to change the fillfactor on the index in question and leave more freespace at the leaf level by rebuilding the index with the new fillfactor.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Thanks, Jonathan

    Here's the data I'm getting back.

    What I'm wondering is what does it mean when theres a "(0)" in the index name column.

    dbo.GroupAttribs^BODY^BOM (1074102867) (0) 69

    dbo.GroupAttribs^BODY^PRODUCTION_FAULTS (0) (0)51

    dbo.GroupAttribs^BODY^TRACK_POINTS (1554(0) (0)32

    dbo.GroupAttribs^ORDER^BROADCAST_STATUS_(0) (0)4

    dbo.Items (2089058478) (0)5

    dbo.TrkAttributes (2105058535) (0)274

    dbo.TrkAttributes (2105058535) IX_TrkAttributes (2)147

    dbo.GroupAttribs^BODY^BOM (1074102867)PIX_GroupAttribs^BODY^BOM (1)1

    dbo.GroupAttribs^BODY^TRACK_POINTS (1554PIX_GroPIX_GroupAttribs^BODY^TRACK_POINTS (1)1

    dbo.GroupAttribs^ORDER^BOM (1650104919)PIX_GroupAttribs^ORDER^BOM (1)4

    dbo.GroupAttribs^BODY^BOM (1074102867)PK_GroupAttribs^BODY^BOM (2)1

    dbo.GroupAttribs^BODY^PRODUCTION_FAULTS PK_GroupAttrPK_GroupAttribs^BODY^PRODUCTION_FAULTS (2)2

    dbo.GroupAttribs^ORDER^BOM (1650104919)PK_GroupAttribs^ORDER^BOM (2)1

    dbo.TrkAttributes (2105058535)PK_TrkAttributes (1)2

    dbo.GroupAttribs^BODY^BOM (1074102867)UIX_GroupAttribs^BODY^BOM (4)48

    dbo.GroupAttribs^BODY^PRODUCTION_FAULTS UIX_GroupAttUIX_GroupAttribs^BODY^PRODUCTION_FAULTS (3)18

    dbo.GroupAttribs^BODY^TRACK_POINTS (1554UIX_GroUIX_GroupAttribs^BODY^TRACK_POINTS (3)18

    dbo.GroupAttribs^ORDER^BOM (1650104919)UIX_GroupAttribs^ORDER^BOM (4)6

    dbo.GroupAttribs^ORDER^BROADCAST_STATUS_UIX_GroupAttribs^ORDUIX_GroupAttribs^ORDER^BROADCAST_STATUS_HISTORY (3)5

    dbo.GroupAttribs^BODY^BOM (1074102867)UIX_PART_NUMBER (3)49

  • mark.hall (11/3/2008)


    I've got an SQL server where my avg. page splits per second is 3-4. However there is process that runs twice a day when this process is running my page splits go to 20-25.

    My question: Is this a high number for page splits or not.

    I'd say "Yes" especially if it's on a single table. It could indicate that you've selected the wrong clustered index for an OLTP table. It may also indicate that you should be feeding a staging table that will be used to update a larger storage table later. Also, you may be able to reduce the number of page splits by reducing the FILL FACTOR on your clustered index to something in the range of 60-70 depending on the size of the rows in the table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mark.hall (11/3/2008)


    Thanks, Jonathan

    Here's the data I'm getting back.

    What I'm wondering is what does it mean when theres a "(0)" in the index name column.

    Mark, from what I can tell in a SQL 2000 VM, it is the clustered index. I base this on the value of the Context column returned by the TVF in a SELECT * query. Lowering your fill factor would probably not be a bad idea on the high splitting indexes to allow more leaf level space to exist in the index. Be careful how far you go with this, as this will cause you to have to do more reads from the database to get the same amount of data since the pages maintain freespace in them to prevent splitting.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Thanks Jeff/Jonathan,

    I won't get a change to try the changes till next week.

    But, I'll let you know the results.

    Thanks again for all your help.

    Mark

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

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