Using ::fn_dblog() to identify which indexes are experiencing page splits
I picked up an interesting tip from Gert Drapers (MS) in the private MVP forum this week which was helpful to me so I'll post it here in case it's useful to anyone else.
I was trying to identify which specific indexes are suffering excessive page splits in a customer database but the monitoring toolset didn't offer quite the help I needed.
SQL Profiler's events didn't seem to cover it at all & the Perfmon only has the SQL Server:Access Methods\Page Splits/sec counter. That counter is useful for telling you how many Page Splits are occuring on a specific server but offers no information on which database they're occuring in, let alone which index.
So I asked the question in the MVP private newsgroup & Gert posted this solution:
[i]
You can do this:
use YourDB
go
select [Object Name], [Index Name]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
This operation indicates the deletion of the rows that are moved because of a page split.
[/i]
This clever solution is reading the transaction log, looking for log records which reveal page split events, & includes the index name in the output. I altered it slightly to give me an aggregated view of how many such log records exist per index & have since wrapped it into a utility which gets run prior to transaction log backups & database backups & stuffs the results into a maintenance table. Very handy for identifying which tables are being hit hard by page split events.
My query looks more like this:
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]