January 2, 2008 at 3:39 am
Anyone know if there's a DMV that tracks page splits? I need some kind of indication as to number of page splits per object and index over a period of time.
I can get this info out of the tran log, but that's messy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 3:47 am
sys.dm_db_index_operational_stats contains a column "leaf_allocation_count "
which according to BOL means "Cumulative count of leaf-level page allocations in the index or heap. For an index, a page allocation corresponds to a page split."
[font="Verdana"]Markus Bohse[/font]
January 2, 2008 at 3:58 am
check out the page_count in sys.dm_db_index_physical_stats .
"Keep Trying"
January 2, 2008 at 4:04 am
Thanks Markus. Will check that out.
Chirag: I'm not looking for the umber of pages in the index. That's easy. What I'm trying to get an idea of is how often a page splits (half rows moved to a new page and half remaining), because I have far too many page splits occuring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 4:23 am
MarkusB (1/2/2008)
sys.dm_db_index_operational_stats contains a column "leaf_allocation_count "which according to BOL means "Cumulative count of leaf-level page allocations in the index or heap. For an index, a page allocation corresponds to a page split."
I hate disagreeing with the documentation, but this time I have to. I did a quick test.
CREATE TABLE FindingPageSplits (
IN int IDENTITY NOT NULL PRIMARY KEY , -- clustered index
Filler CHAR(2000) -- 4 rows will fit on a page
)
Since the cluster is on an ascending field, inserts won't cause page splits, since all inserts occur at the 'end' of the clustered index.
DECLARE @i INT
SET @i = 0
WHILE (@i<5000) -- 5000 rows at 4 rows per page = 1250 pages in the leaf level.
BEGIN
INSERT INTO FindingPageSplits (Filler) VALUES (' ')
SET @i = @i+1
END
And indeed, a check of the transaction log1 reveals that only 3 page splits occured (Operation = LOP_DELETE_SPLIT), all on non-leaf pages (context = LCX_INDEX_INTERIOR)
If I look at the index operational stats, it gives me a leaf_allocation_count of 1250. Precisely the number of pages in the leaf level (as confirmed by the index physical stats DMV)
I did a second test with the cluster on a uniqueidentifier default newID(), and got 2116 splits according to the log, a leaf allocation count of 2483 and a page count of 2483
(1) SELECT COUNT(*) AS NumberOfSplits, AllocUnitName , Context
FROM fn_dblog(NULL,NULL)
WHERE operation = 'LOP_DELETE_SPLIT'
GROUP BY AllocUnitName, Context
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 5:14 am
Hmmmm,
I must admit you're right.
The information from sys.dm_db_index_operational_stats seems pretty useless and because it's a dynamic view, interpreting the results after a restart is even more of a problem.
There is a performance counter "page splits/sec" but it's only on instance level.
So I think you're method of using the t-log is the most reliable one.
[font="Verdana"]Markus Bohse[/font]
January 2, 2008 at 11:09 pm
Hi
Just my thoughts here and apologies in advance if iam wrong.
Cant we divide the number of rows in a table with the page count to get the page splits
Qouting from your first example
"5000 rows at 4 rows per page = 1250 pages in the leaf level."
(5000/1250) -1.
"Keep Trying"
January 2, 2008 at 11:22 pm
Not what I'm looking for. Dividing the number of rows by the page count will get the average number of rows per page.
What I'm trying to get is the number of times, within a certain time period, that an insert/update causes the page to split.
Page splits on insert occur when a row must be added to a particular page (due to the value of the index key) but there is not enough space on that page to fit the page.
On update, a page split can occur when a variable/null co,umn is updates to contain more data/not null value than it did before, and there is insufficient space on the page for the new row to ft
SQL allocates a new page, takes half the rows on the old page, moves them to the new page, then adds the new page into the index chain.
I have (from perfmon stats) around 300 page splits/sec for most of an average morning. That's way higher than I would like. I'm trying to find the tables (clustered indexes) that are getting the most frequent page splits.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2008 at 6:42 am
Maybe this is not what you want, but did you check the article posted here about the Page viewer?
http://qa.sqlservercentral.com/articles/Product+Reviews/3200/
Maybe it could help. But I am not sure.
-Roy
May 6, 2009 at 11:34 am
For what it's worth, there is now a microsoft connect item for this problem.
May 7, 2009 at 8:44 am
Gail, I think there was a thread on this topic on the MVP forums before you got your award. Try a search there. It may have been last year, so you may need to hit a pretty large archive of threads.
I can't recall for sure but I seem to remember that the concensus was that this information isn't available at this time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 7, 2009 at 9:32 am
Dunno if you noticed, but I asked the question almost a year and a half ago.
What I ended up doing was querying the tran log before each backup, looking for the page split operation and the objectid. It worked well enough, but it was intensive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2009 at 9:45 am
You can query the t-log??
May 7, 2009 at 9:50 am
GilaMonster (5/7/2009)
Dunno if you noticed, but I asked the question almost a year and a half ago.What I ended up doing was querying the tran log before each backup, looking for the page split operation and the objectid. It worked well enough, but it was intensive.
Completely missed that. I had assumed that you had a workaround involving the tlog given your post on that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 7, 2009 at 10:05 am
Lynn Pettis (5/7/2009)
You can query the t-log??
Yup. Undocumented command.
SELECT * FROM fn_dblog(null, null)
The two parameters are starting and ending LSNs. Good luck deciphering it. Tran log's not exactly 'readable'
For SQL 2000, precede the function name with ::
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply