{"id":4065,"date":"2013-02-07T11:43:27","date_gmt":"2013-02-07T19:43:27","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4065"},"modified":"2017-07-26T10:32:27","modified_gmt":"2017-07-26T17:32:27","slug":"tracking-page-splits-using-the-transaction-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/tracking-page-splits-using-the-transaction-log\/","title":{"rendered":"Tracking page splits using the transaction log"},"content":{"rendered":"

Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats<\/em>\u00a0DMV (see\u00a0here<\/a>\u00a0for how that works)\u00a0against all the indexes in your databases.\u00a0Today this came up multiple times, both in class and in email, so it’s time to bubble this blog post up to the top of the list.<\/p>\n

You might think this is easy, as there’s a page split counter in sys.dm_db_index_operational_stats<\/em> and in the Access Methods perfmon object. However, neither of these distinguish between ‘good’ splits and ‘nasty’ splits, which are my terms :-). A ‘nasty’ split is what we think of a just a page split – a data or index page having to split into two pages to make space for a record to be inserted or an existing record to expand. A ‘good’ split is what the Storage Engine calls adding a page on the right-hand side of the index leaf level as part of inserting new records in an ascending key index (e.g. a clustered index with a bigint identity column as the cluster key).<\/p>\n

This is a really annoying as it makes both these methods of tracking page splits essentially useless.<\/p>\n

If you’re running SQL Server 2012 or later, the solution is to use Extended Events, based on the new sqlserver.transaction_log<\/em> event. Jonathan wrote a great post here<\/a> that gives you the Extended Events sessions to use. Be careful of doing this on a product system though as there’s a lot of overhead from using that event.<\/p>\n

If you’re not running SQL Server 2012 or later, read on.<\/p>\n

Before the sqlserver.transaction_log<\/em> event was added, there was (and still is) the sqlserver.page_split<\/em> event but that does not distinguish between ‘good’ splits and ‘nasty’ splits either, so some post processing is involved (essentially reading the page referenced in the event to see if it really split or not).<\/p>\n

So what’s the answer?<\/p>\n

Scanning the log for page splits<\/h2>\n

The easiest way to proactively see page splits occurring is to look in the transaction log. Whenever a page splits, an LOP_DELETE_SPLIT<\/em> log record is generated so querying the transaction log can let you know what’s going on.<\/p>\n

Some simple code to do this is:<\/p>\n

\r\nSELECT\r\n    [AllocUnitName] AS N'Index',\r\n    (CASE [Context]\r\n        WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'\r\n        WHEN N'LCX_CLUSTERED' THEN N'Clustered'\r\n        ELSE N'Non-Leaf'\r\n    END) AS [SplitType],\r\n    COUNT (1) AS [SplitCount]\r\nFROM\r\n    fn_dblog (NULL, NULL)\r\nWHERE\r\n    [Operation] = N'LOP_DELETE_SPLIT'\r\nGROUP BY [AllocUnitName], [Context];\r\nGO\r\n<\/pre>\n
However, I don’t recommend doing this, for two reasons:<\/p>\n
    \n
  1. Running\u00a0fn_dblog<\/em>\u00a0will cause read I\/Os on the transaction log, which can cause performance issues, especially if you’re running the scanner regularly and it happens to coincide with a log backup, for instance.<\/span><\/li>\n
  2. Log clearing is disabled while\u00a0fn_dblog<\/em> is running, so on a system with a large amount of log to scan, this could interrupt the ability of the log to clear and cause log growth.<\/li>\n<\/ol>\n
    If you’re running in the full or bulk-logged recovery model, I recommend scanning your log backups for page splits instead of your actual log. If you’re only running in the simple recovery model, and you *really* want to run the script regularly, you’re going to have to run the script just before each checkpoint operation clears the log. But still, be careful you don’t interrupt the log clearing process.<\/p>\n
    Scanning a log backup for page splits<\/h2>\n
    There are two options for this, using the fn_dump_dblog<\/em> function I blogged about here<\/a>:<\/p>\n