June 26, 2011 at 1:06 pm
GilaMonster (6/26/2011)
opc.three (6/26/2011)
I always use PerfMon, not sure if it's available in a DMV. If you find a DMV/F please post back 😀Except that the perfmon counter doesn't actjually track page splits. It tracks new pages added to an index (no matter where they are added)
Track fragmentation. There's no current way to track page splits that cause fragmentation.
The counter should still go down when there are less page splits due to not enough room on the page to accommodate mid-index inserts...or am I off the grid here?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2011 at 2:56 pm
The counter does not distinguish between mid-index page splits (that cause data to be moved and result in fragmentation) and end-index page splits that just add a page to the end of the index. The perfmon counter doesn't, the extended event doesn't.
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
June 29, 2011 at 1:03 pm
In your estimation is Page Splits/Sec at all useful to look at within an overall picture (e.g. combined with other perf indicators/counters, et al.) for looking into adjusting index fill factors?
</trying_to_learn>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 1:20 pm
No. It's not going to help at all if determining whether fill factor needs dropping as it makes no distinction between pages allocated in the middle of the index (resulting in expensive row migrations and fragmentation) and pages allocated at the end of the index (causing neither)
As I said earlier, look at the fragmentation.
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
June 29, 2011 at 1:49 pm
I am either being stubborn, or dense, or stubborn and dense, (I have a great talent for both) but I still see value in looking at the counter in this context. Maybe it will click...I will do more reading on it. Thanks Gail.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 4:26 am
I posted a reference to dmv sys.dm_db_index_operational_stats previously (did anyone see that?) this dmv contains a column leaf_allocation_count which has a description of 'For an index, a page allocation corresponds to a page split.'
so is the term page-split a misnomer? Is it in fact including new page allocations not caused by a page split? I would have thought any page split was fragmentation, at least internal fragmentation as you end up with a pointer and a page about half empty. The new page could be the next page but is that more or less likely than a nonconsecutive page?
Agreed if you want to check fragmentation you use sys.dm_db_index_physical_stats but is the above information completely misleading?
---------------------------------------------------------------------
June 30, 2011 at 5:18 am
GilaMonster (6/29/2011)
No. It's not going to help at all if determining whether fill factor needs dropping as it makes no distinction between pages allocated in the middle of the index (resulting in expensive row migrations and fragmentation) and pages allocated at the end of the index (causing neither)As I said earlier, look at the fragmentation.
Interesting to know Gail, thanks.
Carlton.
June 30, 2011 at 6:21 am
opc.three (6/29/2011)
I am either being stubborn, or dense, or stubborn and dense, (I have a great talent for both) but I still see value in looking at the counter in this context.
If you wish, but be careful how you interpret it. I can easily generate a scenario where page splits/sec will be high (very high) but the index fragmentation remains almost 0.
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
June 30, 2011 at 6:25 am
george sibbald (6/30/2011)
so is the term page-split a misnomer? Is it in fact including new page allocations not caused by a page split?
Page split = allocation of a new page because the page is full. It makes NO distinction between a page split in the middle of an index which moves data, generates log and causes fragmentation and a page split at the end of an index that just allocates a new page (which could be the next physical page)
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
June 30, 2011 at 6:36 am
GilaMonster (6/30/2011)
george sibbald (6/30/2011)
so is the term page-split a misnomer? Is it in fact including new page allocations not caused by a page split?Page split = allocation of a new page because the page is full. It makes NO distinction between a page split in the middle of an index which moves data, generates log and causes fragmentation and a page split at the end of an index that just allocates a new page (which could be the next physical page)
Hmmm. well it bloody well should! 🙂
---------------------------------------------------------------------
June 30, 2011 at 6:39 am
Yup, but currently it doesn't, nor does the page splits/sec, nor does the extended event.
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
June 30, 2011 at 6:44 am
GilaMonster (6/30/2011)
Yup, but currently it doesn't, nor does the page splits/sec, nor does the extended event.
well at least I learnt today there are 3 ways to get information that doesn't quite mean what i thought it meant 😀
---------------------------------------------------------------------
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply