SQL: Watching for Mid Page Splits
Date: 2014-06-25 11:11:07
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.
COUNT(1) AS NumberOfSplits
WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
WHEN N'LCX_CLUSTERED' THEN N'Clustered'
END) AS [SplitType]
WHERE Operation = 'LOP_DELETE_SPLIT'
GROUP BY AllocUnitName, Context
ORDER BY NumberOfSplits DESC