How to find and monitor it.
Page splits is something common. When a page is full and want to insert/update and there is no space, then a new page is allocated (split) .
Some splits are good – the ones that are in inserting in a sequential way, like identity. Here is allocated a new page at the end of the list. Because of this, it’s a good split
Some are not good – like mid insert or an update to full page. For this , we need to add/update the record in a mid list, but no available space. For this, we allocate a new page at the end of the list and move to this page, half of records.Because of this , the hierarchy it’s in a disorder manner and voila = fragmentation.
When a page it’s split , in transaction log, a information it’s inserted : LOP_DELETE_SPLIT
To monitor this, you can search/read the transaction log with fn_dblog
A better way is to use extended events and and look for the event sql_server.transaction_log and filter for an operation LOP_DELETE_SPLIT , which is value =11
CREATE EVENT SESSION [BadBadSplits]
ON SERVER
ADD EVENT sqlserver.transaction_log(
WHERE operation = 11 — LOP_DELETE_SPLIT
)
ADD TARGET package0.event_file
(SET filename=N’D:\xe\badbadMyDog.xel’)
GO
Start the extended event:
ALTER EVENT SESSION [BadBadSplits]
ON SERVER
STATE = start;
GO
S
Sql Server 