SQL: Watching for Mid Page Splits
Author: jason
Date: 2014-06-25 11:11:07
Category: Musing

I have a couple SQL servers that have, what I think, are high Page Splits per second, as monitored by Perfmon. I took the below code, made a #Temp table and then periodically through-out the day ran the script to gather up what indexes/tables were splitting the most.

Here are 2 excellent articles on tracking down Page Splits, specifically Mid Page Splits, the worst kind... Part of the fix is rebuilding the indexes with a Fill Factor of 90, or 80, or 70 depending on your environment. And you may need to set the PAD_Index as ON.

http://www.sqlballs.com/2012/08/how-to-find-bad-page-splits.html
http://www.sqlskills.com/blogs/paul/tracking-page-splits-using-the-transaction-log/
http://dbamohsin.wordpress.com/tag/pad_index/
Code

SELECT
COUNT(1) AS NumberOfSplits
,AllocUnitName
,Context
,(CASE [Context]
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
ELSE N'Non-Leaf'
END) AS [SplitType]
FROM fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_DELETE_SPLIT'
GROUP BY AllocUnitName, Context
ORDER BY NumberOfSplits DESC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009