August 7, 2012 at 6:22 am
Hi All
I understand that External Fragmentation exists when the Index pages are not in a logical order for SQL Server to read.
I also understand that Internal Fragmentation is when the Index pages are not filled to capacity.
Given that you will only see performance degradation on an Index with lots of External fragmentation with Range Scan queries
Is it safe to say that Internal fragmentation will never result in a performance degradation?
Thanks
August 7, 2012 at 7:35 am
The less full index pages are, the more of them must be read into memory to do a given search. The fuller they are, the less pages need to be read - so there is a performance hit.
For maximum efficiency, you should use fill factor = 100% - but this only works for read-only tables, otherwise you get page splits.
So for updateable tables you need to find a balance, & rebuild the indexes from time to time.
August 8, 2012 at 1:31 pm
you should specify the fill-factor for an index based on the number of inserts,updates on the index key(s) and the size of the index key(s) the more inserts on the index the less the fill-factor should be and the bigger the index key perhaps you should choose a smaller fill-factor but for sure there is a trade-off .you should check the external fragmentation over time to come up with a appropriate value for fill-factor
Pooyan
August 8, 2012 at 2:54 pm
pooyan_pdm (8/8/2012)
you should specify the fill-factor for an index based on the number of inserts,
Wrong
updates on the index key(s)
Not only updates on the index key can cause fragmentation
the more inserts on the index the less the fill-factor should be
Wrong again. E.g. an insert into an ever-increasing index will never lead to fragmentation. Or an insert into an index with several static key values won't create fragmentation either.
My advice is if one doesn't fully understand what is fill-factor and the patterns of updating data in the given database then it's better to leave fill-factor to default value, otherwise the harm may outweigh all the benefits.
August 8, 2012 at 10:20 pm
What I said is a general guidline and is true, yes with a ever increasing key the inserts does not cause the fragmentation but generally speaking beacuse most of the times there is no trend in inserting the key values the more the inserts the more page splits and fragmentation . And about the update I don't know what you think of a index key update but it' effects on page splits and fragmentation is the same as another insert.
Pooyan
August 10, 2012 at 4:45 am
Thanks Guys!!
August 10, 2012 at 10:02 am
pooyan_pdm (8/8/2012)
What I said is a general guidline and is true, yes with a ever increasing key the inserts does not cause the fragmentation but generally speaking beacuse most of the times there is no trend in inserting the key values the more the inserts the more page splits and fragmentation . And about the update I don't know what you think of a index key update but it' effects on page splits and fragmentation is the same as another insert.
I've seen the cases where people using general guideline set fill-factors to 70% which instantly leads to performance degradation by 30% while the benefits of doing that are really questionable.
August 10, 2012 at 11:44 am
pooyan_pdm (8/8/2012)
And about the update I don't know what you think of a index key update but it' effects on page splits and fragmentation is the same as another insert.
An update can only cause page splits in an index if it increases the size of the columns used as key or include. That's null-> non-null or increasing the amount of data in a variable length column.
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
August 10, 2012 at 1:36 pm
GilaMonster (8/10/2012)
pooyan_pdm (8/8/2012)
And about the update I don't know what you think of a index key update but it' effects on page splits and fragmentation is the same as another insert.An update can only cause page splits in an index if it increases the size of the columns used as key or include. That's null-> non-null or increasing the amount of data in a variable length column.
Thanks
Regarding tracking page-splits, is this represented by the leaf_allocation_count in the sys.dm_db_index_operational_stats DMV?
Thanks
August 10, 2012 at 1:43 pm
No. That's literally the number of pages that were allocated, middle or end. Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.
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
August 10, 2012 at 2:22 pm
GilaMonster (8/10/2012)
pooyan_pdm (8/8/2012)
And about the update I don't know what you think of a index key update but it' effects on page splits and fragmentation is the same as another insert.An update can only cause page splits in an index if it increases the size of the columns used as key or include. That's null-> non-null or increasing the amount of data in a variable length column.
What I meant was not an update to the index key would lead to splitting the same page the index key resides in,but because the key value might change to any other value it could be moved to another page and cause a split to that page .lets say the key value is 10 and the page the key resides in includes keys with values from 1 to 20.if the value 10 is changed to 100 and there's not enough room in the page the key with value 100 should resides in, that page could split just like an INSERT
Pooyan
August 12, 2012 at 11:19 am
GilaMonster (8/10/2012)
No. That's literally the number of pages that were allocated, middle or end. Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.
Thanks
So the only way I can track page splits is using the pagesplits/sec counter?
If I check the page count of an Index, rebuild the index and a week later check the page count again, would that give me an idea of the page splits that have occured?
Thanks
August 12, 2012 at 12:31 pm
SQLSACT (8/12/2012)
GilaMonster (8/10/2012)
No. That's literally the number of pages that were allocated, middle or end. Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.So the only way I can track page splits is using the pagesplits/sec counter?
Not in 2008. That counter is the number of mid-index and end-index splits combined (it's just the allocation of new pages actually). Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.
If I check the page count of an Index, rebuild the index and a week later check the page count again, would that give me an idea of the page splits that have occured?
No, because that counter counts the number of pages that get allocated to the index. The difference between the two in a week will just be the number of new pages that were allocated to the index during that week, not the number of mid-index page splits (which are the harmful ones)
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
August 12, 2012 at 12:43 pm
GilaMonster (8/12/2012)
SQLSACT (8/12/2012)
GilaMonster (8/10/2012)
No. That's literally the number of pages that were allocated, middle or end. Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.So the only way I can track page splits is using the pagesplits/sec counter?
Not in 2008. That counter is the number of mid-index and end-index splits combined (it's just the allocation of new pages actually). Tracking mid-index page splits (which are the harmful ones) is near-impossible until SQL 2012.
If I check the page count of an Index, rebuild the index and a week later check the page count again, would that give me an idea of the page splits that have occured?
No, because that counter counts the number of pages that get allocated to the index. The difference between the two in a week will just be the number of new pages that were allocated to the index during that week, not the number of mid-index page splits (which are the harmful ones)
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply