Nearly two years ago Kalen Delaney blogged about Splitting a page into multiple pages, showing how page splits occur inside of SQL Server.  Following her blog post, Michael Zilberstein wrote a post, Monitoring Page Splits with Extended Events, that showed how to see the sqlserver.page_split Events using Extended Events.  Eladio Rincón also blogged about Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits, but not in relation to Kalen’s blog post.  Both of these blog posts demonstrate how to get the sqlserver.page_split Events, but as discussed in the comments section of Michael Zilberstein’s blog post, the Event fires for all page splits and Adam Machanic and I talked after Eladio’s blog post and opened a connect item to have the sqlserver.page_split Event extended in the product so that you know what kind of split is actually occurring.

https://connect.microsoft.com/SQLServer/feedback/details/388482/sql-server-extended-events-page-split-event-additions

The CTP1 release of Denali has significant changes to the sqlserver.page_split Event, that makes it easier to find the splitting object as well the type of split that is occurring.  Before we look at that, I am going to show the code required to get the object and index information from SQL Server 2008, which is based on Adam’s comments to use sys.dm_os_buffer_descriptors.  For the examples in this blog post I am going use Kalen’s multipage split example from her blog post referenced above.

	-- Create the table 
	USE tempdb;
	GO
	SET NOCOUNT ON
	GO
	IF EXISTS (SELECT * FROM sys.tables
	            WHERE name = 'split_page')
	    DROP TABLE split_page;
	GO
	CREATE TABLE split_page 
	(id INT IDENTITY(0,2) PRIMARY KEY,
	id2 bigint DEFAULT 0,
	data1 VARCHAR(33) NULL, 
	data2 VARCHAR(8000) NULL);
	GO
	-- fill page until no more rows fit
	INSERT INTO split_page DEFAULT VALUES;
	GO 385
	-- verify that there is only one data page 
	DBCC IND(tempdb, split_page, -1);
	-- Create MonitorPageSplits Extended Event Session 
	IF (SELECT 1 FROM sys.server_event_sessions WHERE name = 'MonitorPageSplits') IS NOT NULL 
	   DROP EVENT SESSION MonitorPageSplits ON SERVER 
	GO 
	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.ring_buffer 
	WITH(MAX_DISPATCH_LATENCY = 1 SECONDS)
	GO 
	-- Start the MonitorPageSplits Event Session 
	ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start; 
	GO 
	-- Now insert one more row, this time filling the VARCHARs to the maximum length. 
	SET IDENTITY_INSERT split_page  ON;
	GO
	INSERT INTO split_page (id, id2, data1, data2)
	      SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000);
	GO
	SET IDENTITY_INSERT split_page  OFF;
	GO 
	ALTER EVENT SESSION MonitorPageSplits ON SERVER 
	DROP EVENT sqlserver.page_split; 
	GO 
	-- Wait to allow dispatch to complete
	WAITFOR DELAY '00:00:01.000' 
	GO
	SELECT oTab.*
	  , p.OBJECT_ID
	  , p.index_id
	  , OBJECT_NAME(p.OBJECT_ID)
	  , i.name
	FROM
	(
	SELECT 
	    XEvent            = XEvent.query('.') 
	  , time              = XEvent.value('(@timestamp)[1]','datetime') 
	  , FILE_ID           = XEvent.value('(data[@name=''file_id'']/value)[1]','int') 
	  , page_id           = XEvent.value('(data[@name=''page_id'']/value)[1]','int') 
	  , database_id       = XEvent.value('(action[@name=''database_id'']/value)[1]','int') 
	  , sql_text          = XEvent.value('(action[@name=''sql_text'']/value)[1]','varchar(max)') 
	FROM 
	( 
	   SELECT CAST(target_data AS XML) AS target_data 
	   FROM sys.dm_xe_session_targets xst 
	   JOIN sys.dm_xe_sessions xs ON xs.address = xst.event_session_address 
	   WHERE xs.name = 'MonitorPageSplits' 
	) AS tab (target_data) 
	CROSS APPLY target_data.nodes('/RingBufferTarget/event') AS EventNodes(XEvent) 
	) AS oTab
	LEFT JOIN sys.dm_os_buffer_descriptors AS obd
	   ON obd.database_id = oTab.database_id
	       AND obd.FILE_ID = oTab.FILE_ID
	       AND obd.page_id = oTab.page_id
	LEFT JOIN sys.allocation_units au
	   ON au.allocation_unit_id = obd.allocation_unit_id
	LEFT JOIN sys.partitions p 
	   ON p.partition_id = au.container_id  
	LEFT JOIN sys.indexes i
	   ON p.OBJECT_ID = i.OBJECT_ID
	       AND p.index_id = i.index_id
	-- verify that there is only one data page 
	DBCC IND(tempdb, split_page, -1);
	
	

The above code creates a table in tempdb, loads one page of data in it exactly as in Kalen’s blog post, and then creates an Event Session for the sqlserver.page_split Event in tempdb, that also collects the sqlserver.database_id and sqlserver.sql_text actions when the Event fires.  After triggering the page split, it drops the Event from the Event Session and then uses WAITFOR DELAY to allow the events to be buffered to the package0.ring_buffer Target.  Then it shreds the XML and joins to the DMV’s to get the object and index names.  The output of running the above script in SQL Server 2008 should be similar to the following, showing 10 split events and 10 additional pages in the database table.

 An XEvent a Day (27 of 31)   The Future   Tracking Page Splits in SQL Server Denali CTP1   image thumb

Note that the only two columns returned by the sqlserver.page_split Event are the file_id and page_id.  In SQL Server Denali CTP1, the sqlserver.page_split event now has a much larger Event payload associated with it.  It now returns the file_id, page_id, database_id (as a part of the event, not requiring an action), rowset_id, splitOperation, new_page_file_id, and the new_page_page_id associated with the page_split Event.   This makes the Event much more useful and allows it to be used without having to query the buffer descriptors to find the object association.  The following demo is identical to the demo for SQL Server 2008 listed above with the exception of that the XQuery is slightly different (a requirement to pull the new information from the XML).

	-- Create the table 
	USE tempdb;
	GO
	SET NOCOUNT ON
	GO
	IF EXISTS (SELECT * FROM sys.tables
	            WHERE name = 'split_page')
	    DROP TABLE split_page;
	GO
	CREATE TABLE split_page 
	(id INT IDENTITY(0,2) PRIMARY KEY,
	id2 bigint DEFAULT 0,
	data1 VARCHAR(33) NULL, 
	data2 VARCHAR(8000) NULL);
	GO
	-- fill page until no more rows fit
	INSERT INTO split_page DEFAULT VALUES;
	GO 385
	-- verify that there is only one data page 
	DBCC IND(tempdb, split_page, -1);
	-- Create MonitorPageSplits Extended Event Session 
	IF (SELECT 1 FROM sys.server_event_sessions WHERE name = 'MonitorPageSplits') IS NOT NULL 
	   DROP EVENT SESSION MonitorPageSplits ON SERVER 
	GO 
	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.ring_buffer 
	WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
	GO 
	-- Start the MonitorPageSplits Event Session 
	ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start; 
	GO 
	-- Now insert one more row, this time filling the VARCHARs to the maximum length. 
	SET IDENTITY_INSERT split_page  ON;
	GO
	INSERT INTO split_page (id, id2, data1, data2)
	      SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000);
	GO
	SET IDENTITY_INSERT split_page  OFF;
	GO 
	ALTER EVENT SESSION MonitorPageSplits ON SERVER 
	DROP EVENT sqlserver.page_split; 
	
	GO
	SELECT 
	    event_time         = XEvent.value('(@timestamp)[1]','datetime') 
	  , orig_file_id      = XEvent.value('(data[@name=''file_id'']/value)[1]','int') 
	  , orig_page_id      = XEvent.value('(data[@name=''page_id'']/value)[1]','int') 
	  , database_id           = XEvent.value('(data[@name=''database_id'']/value)[1]','int') 
	  , OBJECT_ID         = p.OBJECT_ID
	  , index_id          = p.index_id
	  , OBJECT_NAME           = OBJECT_NAME(p.OBJECT_ID)
	  , index_name            = i.name
	  , rowset_id         = XEvent.value('(data[@name=''rowset_id'']/value)[1]','bigint') 
	  , splitOperation        = XEvent.value('(data[@name=''splitOperation'']/text)[1]','varchar(255)') 
	  , new_page_file_id  = XEvent.value('(data[@name=''new_page_file_id'']/value)[1]','int') 
	  , new_page_page_id  = XEvent.value('(data[@name=''new_page_page_id'']/value)[1]','int') 
	  , sql_text          = XEvent.value('(action[@name=''sql_text'']/value)[1]','varchar(max)') 
	FROM 
	( 
	   SELECT CAST(target_data AS XML) AS target_data 
	   FROM sys.dm_xe_session_targets xst 
	   JOIN sys.dm_xe_sessions xs ON xs.address = xst.event_session_address 
	   WHERE xs.name = 'MonitorPageSplits' 
	) AS tab (target_data) 
	CROSS APPLY target_data.nodes('/RingBufferTarget/event') AS EventNodes(XEvent) 
	LEFT JOIN sys.allocation_units au
	   ON au.container_id = XEvent.value('(data[@name=''rowset_id'']/value)[1]','bigint') 
	LEFT JOIN sys.partitions p 
	   ON p.partition_id = au.container_id  
	LEFT JOIN sys.indexes i
	   ON p.OBJECT_ID = i.OBJECT_ID
	       AND p.index_id = i.index_id
	-- View the Page allocations 
	DBCC IND(tempdb, split_page, -1);
	

If you run the above demo the output should be similar to the below (if you click on the picture, it will open up larger).  One thing that should become immediately obvious is that the same demo in Denali is doing 1/3rd of the page splits that occur in SQL Server 2008. 

An XEvent a Day (27 of 31)   The Future   Tracking Page Splits in SQL Server Denali CTP1   image3 thumb

The old_page_id and new_page_id tell where the page originated and moved to, and the splitOperation tells the type of split.  In this case only two of the type of splits are occurring; SPLIT_FOR_ROOT_NODE which occurs when the first page allocated is split into multiple pages, and SPLIT_FOR_INSERT which occurs as the inserts continue and the pages are split to accommodate the data.  There are a number of additional split operations that exist in SQL Server Denali CTP1 including, SPLIT_FOR_DELETE, SPLIT_FOR_GHOST, SPLIT_FOR_INTERNAL_NODE, and SPLIT_FOR_UPDATE.  I’ve tried to figure out how to correlate the output from DBCC IND with the data held in the Event Session for page splits to correlate the old_page_id and new_page_id to identify problematic splits, but haven’t finalized validation of my tests yet (hopefully I can finish this work and I’ll write an update to this blog post showing how to do this at some point in the near future).  One item that I have noted in my testing is that mid-page splits generally generate multiple sqlserver.page_split Events in the same operation, similar to the demonstrations used in this example, where as end-page splits for identity and sequential GUID inserts do not.  I am not certain that this is a valid conclusion to come to at this point and have further testing to do to investigate page splits more.