July 27, 2012 at 1:40 pm
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.
July 27, 2012 at 2:05 pm
Try this blog post by Paul Randal, as a starting point.
July 27, 2012 at 3:27 pm
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.
July 27, 2012 at 3:36 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply