After reading Kalen Delaney's post about single insert causing 10 page splits, I wanted to see those splits in detail - their order at first place. And in SQL Server 2008 there is a way to trace splits - using new Extended Events infrastructure. Here is simple script that creates the trace and afterwards displays results.
First of all, create and populate table in tempdb as described in the Kalen's post.
CREATE TABLE split_page
(id INT IDENTITY(0,2) PRIMARY KEY,
id2 bigint DEFAULT 0,
data1 VARCHAR(33) NULL,
data2 VARCHAR(8000) NULL);
INSERT INTO split_page DEFAULT VALUES;
Now, let's create and start Extended Events session. The only event we would like to monitor is page_split. On the way we'll capture sql text in order to be sure that it is our insert that caused split.
CREATE EVENT SESSION MonitorPageSplits ON SERVER
ADD EVENT sqlserver.page_split
ACTION (sqlserver.database_id, sqlserver.sql_text)
WHERE sqlserver.database_id = 2
ADD TARGET package0.asynchronous_file_target
filename = N'c:\temp\MonitorPageSplits.etx',
metadatafile = N'c:\temp\MonitorPageSplits.mta'
ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start;
Now execute INSERT command from Kalen's script.
SET IDENTITY_INSERT split_page ON;
INSERT INTO split_page (id, id2, data1, data2)
SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000);
SET IDENTITY_INSERT split_page OFF;
Afterwards we'll close the session and display results.
ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = STOP;
DROP EVENT SESSION MonitorPageSplits ON SERVER;
split.value('(/event/data[@name=''file_id'']/value)','int') AS [file_id],
split.value('(/event/data[@name=''page_id'']/value)','int') AS [page_id],
split.value('(/event[@name=''page_split'']/@timestamp)','datetime') AS [event_time],
split.value('(/event/action[@name=''sql_text'']/value)','varchar(max)') AS [sql_text]
SELECT CAST(event_data AS XML) AS split
FROM sys.fn_xe_file_target_read_file('c:\temp\MonitorPageSplits*.etx', 'c:\temp\MonitorPageSplits*.mta', NULL, NULL)
) AS t
ORDER BY [event_time]
OK, the result is:
So, we see 10 splits, among them 4 splits of page 148, another 3 of page 178 etc. It makes sense. When split occurs, ~half of the data from the old page goes to the new one. So if the new row - the one that caused split - should originally have entered first half of the page, after the split it would still try to enter the old page - not the new one. In our case originally we had 56 rows before the new one (id from 0 to 110 step 2) and 385 - 56 = 329 rows after. I would still expect 3 and not 4 splits of the initial page because (((385 / 2) / 2) / 2) = ~48 < 57 (new row's place). So I have expected that after the third split new row would at last leave the initial page. But I was wrong - don't know whether that's just not strict math or there're other factors I didn't think of.