Tuesday, May 25, 2010

[Tips] HOW TO find page splits by reading the transaction log

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

There are two tricks in here. The first is using ::fn_dblog (undocumented function) to read the transaction log for a database.
The second is pulling out the delete messages involved in a page split and using that to determine where the splits are occurring.


select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

No comments: