SQL Server Performance Forum – Threads Archive

Page Splits

I believe page splits can be monitored through Performance monitor. But i was wondering, is there a way, we could tell what specific objects (tables/indexes) are in question? Appreciated any help.
Thanks,
Cali

I don’t think there is any direct way… but I don’t remember any new DMV for this at this time…
But check the following articles to determine and resolve… http://www.sqlmag.com/Article/ArticleID/24382/sql_server_24382.html
http://www.sqlmag.com/Article/ArticleID/20589/sql_server_20589.html
http://www.microsoft.com/technet/technetmag/issues/2007/03/SQLIndex/default.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

You might experiment with this — I’ve used it in SQL Server 2000; it gives a rough number of splits per object from the log:
select getDate() as collected,
[Object Name],
[Index Name],
count([Current LSN]) as countSplits
from ::fn_dblog(null, null)
where Operation = N’LOP_DELETE_SPLIT’
group by [Object Name], [Index Name]
From here: http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx Might not work in 2005

Here’s a version that seems to work in 2005: select getDate() as collected,
[AllocUnitName],
count([Current LSN]) as countSplits
from ::fn_dblog(null, null)
where Operation = N’LOP_DELETE_SPLIT’
group by [AllocUnitName]

Thank you so much for the answer’s. I shall look into this.

]]>