|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.
When one page becomes full and a new page is needed, this is reported as a page split. But this 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:\xel\badsplits.xel’ )
— Start the session
ALTER EVENT SESSION [Blocked]
ON SERVERSTATE = START;
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:
AS ( SELECT
— Retrieve the database_id from inside the XML document
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
(‘c:\xel\badsplits*.xel’,NULL, NULL, NULL)) theData
CROSS APPLY theData.event_data.nodes
(‘//event’) theNodes ( event_data ))
SELECT DB_NAME(database_id) ,COUNT(*) AS total
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
FROM (SELECT CONVERT(XML,event_data) event_data
(‘c:\xel\badsplits*.xel’, NULL, NULL, NULL)) theData
CROSS APPLY theData.event_data.nodes(‘//event’)
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, you can analyze its indexes to solve the problem.
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.
Again thanks Red Gate . and do like the post if you find it useful ..