Anyone know of example/graph how page splits impact performance?

  • Does anyone know of any examples/graphs that describe how page splits can actually effect performance? This is one of those classic "everyone says it's bad, but I can't find any real data on what that actually means in practice" searches for information. I fully understand what a page split is and what the implications of it are, but I can't find where anyone's done analysis on it and posted the data results so it's easy to see the effect on insert/update/query performance and also fragmentation. I have to imagine someone somewhere must have done a case study on this and blogged/posted the results but I can't seem to find anything like that on the topic.

  • Try this blog post by Paul Randal, as a starting point.

    http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Well, here's the thing, perhaps context will help point to why I'm asking. And if there's not a good article on this perhaps I'll do the research myself and post one. What you linked doesn't seem to get me the type of answer I'm looking for.

    Every so often we have an insert or update operation on 2-3 tables that takes far longer than I'd expect, say 300-600ms, when normally it happens in 5ms or less. At peak times we're doing 500 inserts or updates per second. The clustered indexes are never updated mind you, only inserted into. We've gone with the default fill factor (forget if it's 0 or 100, regardless it's the same) for everything. What happens though is when I run the following SQL before our 30 minute log file backup, I'm seeing the 2-3 tables that have occasional long running singleton insert or update operations having maybe a hundred or so page splits:

    Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context

    From fn_dblog(NULL,NULL)

    Where operation = 'LOP_DELETE_SPLIT'

    Group By AllocUnitName, Context

    Order by NumberOfSplits desc

    And I get this result (table names masked obviously):

    [font="Courier New"]NumberOfSplits AllocUnitName Context

    -------------- ---------------------------- -------------------------------

    316 dbo.TABLE1.BTR1_TABLE1 LCX_CLUSTERED

    207 dbo.TABLE2.IDX1_TABLE2 LCX_INDEX_LEAF

    137 dbo.TABLE2.PK_TABLE2 LCX_CLUSTERED

    116 dbo.TABLE2.UNI1K_TABLE2 LCX_INDEX_LEAF[/font]

    What I can't get a clear answer on anywhere is the following:

    1. Is that a lot of page splits?

    2. Could this be a potential cause of the performance issue we have occasionally with singleton inserts or updates?

    Everyone says page splits are bad and cause performance degradation, but I can't find anywhere examples of exactly what that means. Does it mean a single insert that would normally run in 2ms now could potentially take 300ms? I can't find anywhere where there's a case study on what that performance hit actually equates to in the real world.

  • Brian.cs (7/27/2012)


    Does it mean a single insert that would normally run in 2ms now could potentially take 300ms?

    It's within the realms of possible. It's one of those things that's difficult to measure because it's not going have the same effect on all systems

    Consider the difference:

    Insert without page split:

    Insert 1 row.

    Insert with page split:

    Allocate new page

    Insert half the rows from the old page onto the new

    Delete those rows from the old page

    Change the allocation pointers on the previous, next and parent pages.

    You may want to try setting up an extended events session (Jonathan Kehayias's blog) to track long running inserts and page splits and see if you can correlate them (though note that extended events page split event tracks both mid-index page splits and end-index page splits)

    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

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

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