<\/p>\n
After some time, we can query this session to find the source of page split problem. The first challenge is to find the database where the page splits are happening.<\/p>\n
To read the information in the files we need to use the DMF sys.fn_xe_file_target_read_file<\/b>, but the information will be returned in XML format, so we need to use XML methods to retrieve the information from inside the XML document.<\/p>\n
The query will be this:<\/p>\n
with qry as\r\n (select\r\n -- Retrieve the database_id from inside the XML document\r\ntheNodes.event_data.value('(data[@name=\"database_id\"]\/value)[1]','int') as database_id\r\n from\r\n (select convert(xml,event_data) event_data -- convert the text field to XML\r\n from\r\n-- reads the information in the event files\r\nsys.fn_xe_file_target_read_file('c:\\xel\\badsplits*.xel', NULL, NULL, NULL)) theData\r\n cross apply theData.event_data.nodes('\/\/event') theNodes(event_data) )\r\nselect db_name(database_id),count(*) as total from qry\r\ngroup by db_name(database_id) -- group the result by database\r\norder by total desc<\/pre>\n![\"pagesplit1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/03\/pagesplit1.png\")
<\/a><\/p>\nNow that we know the database with most page splits, we need to identify the objects that are causing the problem. The XE event only returns the allocation_unit_id<\/b>, so we need to convert the allocation_unit_id<\/b> to the object name that owns these pages.<\/p>\n
The query to convert the allocation_unit_id to the object name needs to run on the database that contains the allocation units and we will filter the result from XE for this specific database. We 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, that means data row pages and row overflow pages.<\/p>\n
We need to add sys.partitions to the query, join the container_id field of sys.allocation_units with hobt_id field in sys.partitions. Finally we add sys.objects to the query, joining with object_id field from sys.partitions and get the name from sys.objects.<\/p>\n
with qry as\r\n (select\r\ntheNodes.event_data.value('(data[@name=\"database_id\"]\/value)[1]','int') as database_id,\r\ntheNodes.event_data.value('(data[@name=\"alloc_unit_id\"]\/value)[1]','varchar(30)') as alloc_unit_id,\r\ntheNodes.event_data.value('(data[@name=\"context\"]\/text)[1]','varchar(30)') as context\r\n from\r\n (select convert(xml,event_data) event_data\r\n from\r\n sys.fn_xe_file_target_read_file('c:\\xel\\badsplits*.xel', NULL, NULL, NULL)) theData\r\n cross apply theData.event_data.nodes('\/\/event') theNodes(event_data) )\r\nselect name,context,count(*) as total -- The count of splits by objects\r\n from qry,sys.allocation_units au, sys.partitions p, sys.objects ob\r\nwhere qry.alloc_unit_id=au.allocation_unit_id\r\n and au.container_id=p.hobt_id and p.object_id=ob.object_id\r\n and (au.type=1 or au.type=3) and\r\n db_name(database_id)='MDW' -- Filter by the database\r\ngroup by name,context -- group by object name and context\r\norder by name<\/pre>\n![\"pagesplit2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/03\/pagesplit2.png\")
<\/a><\/p>\nNow that we know the table that’s causing most of page splits, we can analyze the indexes of this table to solve the problem.<\/p>\n
Although this solution can identify page split problems, we can’t let this session stay started for a long time because we are capturing transaction log activities, this kind of capture can be too intensive for the server. We can, however, use Redgate SQL Monitor<\/a> to point to us when there is a page split problem in the server and only then start this session for a short period to identify the source of the problem.<\/p>\nSQL Monitor can create analysis graphs based on several performance counters. In this case we should use ‘page splits\/sec’ and ‘page splits\/batch requests’.<\/p>\n
We should create a baseline to identify the regular values of these counters in our environment, so when we notice these values increasing we can use the XE session to identify the source of the page split problem.<\/p>\n