Page Splits by Index

Which indexes are actually splitting? This per-database query scans the transaction log with fn_dblog for LOP_DELETE_SPLIT operations, groups them by allocation unit, and joins back to sys.indexes so you can see the object, index name and current fill factor for every index that has split - sorted by split count. (Requires the log to still contain the split records.)

--Use is per-database

SELECT
	Splits.NumberOfSplits,
	Splits.Context,
	i.object_id,
	o.name as object_name,
	i.index_id,
	i.name as index_name,
	i.fill_factor
FROM
	sys.indexes as i
INNER JOIN sys.objects as o
	ON o.object_id = i.object_id
INNER JOIN (
		SELECT
			COUNT(0) AS NumberOfSplits,
			dblog.AllocUnitName,
			dblog.Context
		FROM
			fn_dblog(NULL, NULL) AS dblog
		WHERE
			dblog.Operation = 'LOP_DELETE_SPLIT'
		GROUP BY
			dblog.AllocUnitName,
			dblog.Context
	) AS Splits
	ON Splits.AllocUnitName = schema_name(o.schema_id) + '.' + object_name(i.object_id) + '.' + i.name
WHERE
	o.is_ms_shipped = 0
ORDER BY
	Splits.NumberOfSplits DESC