In my last post I talked about how one could go about troubleshooting an issue in SQL Server, and today I would like to talk about one of the common issues that most DBA’s come across their professional career, Page Splits. When one page becomes full and a new page is needed, this is reported as a page split
Page splits can slow down your queries and increase the amount of I/O needed to retrieve your data. So, how do you monitor and identify the source of page splits, so you can avoid them in your databases?
The short answer is that you can monitor page splits with extended events. There’s an event called page_split. The problem is that there are many kinds of page split. You only need to catch the problematic ones, but the page_split event doesn’t identify this. A page split is a regular operation – it doesn’t necessarily have bad consequences for your queries. The problem occurs with updates and non-sequential inserts, when a row needs to be inserted in the middle of an object’s pages and there’s no space.
SQL Server creates a new page, transfers half of the page data to the new page and writes the row data. This creates page fragmentation, which is very bad for performance. This is also reported as page split. You can find these problematic page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, so we need to use it with caution. Specifically, you need to filter the operation field, looking for the value 11, which records LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server moves rows from one page to another in a ‘bad’ page split.
This event isn’t visible in the Extended Event interface, so we need to create a session using T-SQL
CREATE EVENT SESSION [BadSplits] ON SERVER
ADD EVENT sqlserver.transaction_log (
WHERE operation = 11 -- LOP_DELETE_SPLIT
)
ADD TARGET package0.event_file
-- You need to customize the path
( SET filename = N'C:\xelogs\badsplits.xel' )
GO
-- Start the session
ALTER EVENT SESSION [Blocked]
ON SERVER
STATE = START;
GO
After some time, we can query this session to find the source of a page split. The first challenge is to find the database where the page splits are happening. To read the information in the files, you need to use the DMF sys.fn_xe_file_target_read_file. The information will be returned in XML, so we need to use XML methods to retrieve the information:
WITH qry AS
( SELECT
-- Retrieve the database_id from inside the XML document
theNodes.event_data.value
('(data[@name="database_id"]/value)[1]','int')
AS database_id FROM
(SELECT CONVERT(XML, event_data)
event_data -- convert the text field to XML
FROM -- reads the information in the event files
sys.fn_xe_file_target_read_file
('c:\xelogs\badsplits*.xel',NULL, NULL, NULL)
) theData
CROSS APPLY theData.event_data.nodes
('//event') theNodes ( event_data ))
SELECT DB_NAME(database_id) ,COUNT(*) AS total
FROM qry
GROUP BY DB_NAME(database_id)
-- group the result by database
ORDER BY total DESC
Once you know the database with the most page splits, you need to identify the objects that are causing the problem. This is a bit tricky, but here’s how it works (and there’s a code example at the end).
The event only returns the allocation_unit_id, so you need to convert the allocation_unit_id to the object name that owns these pages. The query to convert the allocation_unit_id to an object name needs to run on the database that contains the allocation units, so we’ll filter the results for this specific database. We now need to join the result with the DMV sys.allocation_units using the allocation_unit_id field. This DMV has three kinds of allocation units but we are interested only in two of them: type 1 and type 3, which represent data row pages and row overflow pages.
Next, we need to add sys.partitions to the query, and join the container_id field of sys.allocation_units with the hobt_id field in sys.partitions. Finally, we add sys.objects to the query, joining with the object_id field from sys.partitions, to get the name from sys.objects:
WITH qry AS
(SELECT
theNodes.event_data.value
('(data[@name="database_id"]/value)[1]','int')
AS database_id,
theNodes.event_data.value
('(data[@name="alloc_unit_id"]/value)[1]','varchar(30)')
AS alloc_unit_id,
theNodes.event_data.value
('(data[@name="context"]/text)[1]','varchar(30)')
AS context
FROM
(SELECT CONVERT(XML,event_data) event_data
FROM
sys.fn_xe_file_target_read_file
('c:\xelogs\badsplits*.xel', NULL, NULL, NULL)) theData
CROSS APPLY theData.event_data.nodes('//event')
theNodes(event_data) )
SELECT name,context,COUNT(*) AS total
-- The count of splits by objects
FROM qry,sys.allocation_units au, sys.partitions p, sys.objects ob
WHERE qry.alloc_unit_id=au.allocation_unit_id
AND au.container_id=p.hobt_id AND p.object_id=ob.object_id
AND (au.type=1 or au.type=3) AND
db_name(database_id)='MDW' -- Filter by the database
GROUP BY name,context -- group by object name and context
ORDER BY name
Now that we know which table is causing the most page splits, we can analyze its indexes to solve the problem.
Please note that although this solution can identify page split problems, you can’t let the session run for a long time, because you’re capturing transaction log activities, which can be too intensive for the server.
My DBA Lounge 