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.
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.
.
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.
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.