Showing posts with label DBA. Show all posts
Showing posts with label DBA. Show all posts

Tuesday, November 20, 2012

How to monitor page split in SQL Server

I had a question when creating or rebuilding a index, the fillfactor value that I set to it is the perfect value for my requirement. I have found a query that use a undocumented function and read the transaction log and identify the indexes that are splitting.



SELECT AllocUnitName,COUNT([AllocUnitName]) [Splits]
FROM ::FN_DBLOG(NULL,NULL)
WHERE Operation=N'LOP_DELETE_SPLIT' AND PARSENAME(AllocUnitName,3)<>'sys'
GROUP BY AllocUnitName





there is another way to find the page split with Extended events. for more details go to Michael Zilberstein's Blog