Three months ago, I presented a session at the special Rosh HaShana user group meeting. The session was about Monitoring Page Splits. I talked about the difference between a “real” page split (mid-page split) and a page allocation (end-page split). I demonstrated that all the tools available by SQL Server to monitor page splits actually monitor both types (mid-page and end-page), and there is no way to monitor only “real” page splits, which are a common concern for DBAs.
I then showed that there is one undocumented and unsupported way to achieve this goal, which is by looking at the transaction log using the “sys.fn_dblog” function and tracking the “LOP_DELETE_SPLIT” operation. This is the only indication in SQL Server for mid-page splits. I also showed that the same is true for SQL Server 2012.
After the presentation, Ami Levin posted a question about it in the MVP forum and connected between me and Jonathan Kehayias. Jonathan suggested a much better solution, which is still based on the “LOP_DELETE_SPLIT” operation in the transaction log. But instead of searching for it in the log using “sys.fn_dblog”, he suggested to monitor it with Extended Events using the “sqlserver.transaction_log” event, which is a new event in SQL Server 2012.
There are several advantages to this approach over the “sys.fn_dblog” approach:
The “sqlserver.transaction_log” event is fired as the log record is created, and the relevant events are put in memory. This is much more efficient than reading the transaction log from the log file.
With Extended Events, you can control the timeframe in which you would like to monitor page splits by starting and stopping the event session. With “sys.fn_dblog”, the amount of log records you’ll read is determined by the last time the log was truncated (for example, by a CHECKPOINT operation or by a log backup operation).
The Extended Events infrastructure lets you correlate between transaction log records and the operations that caused these records to be created. This is a very powerful tool, and I plan to write about it more in the near future.
While the content of the transaction log still remains undocumented, Extended Events is a well-documented and supported mechanism. The “sys.fn_dblog” function isn’t.
Jonathan has just written a great post describing his solution. You can read it here.
I would like to thank Jonathan for sharing his solution with me (and with everyone, actually), and I would like to thank Ami for making the connection.