Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index Fragmentation and Performance Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 6:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 1,375, Visits: 2,659
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
Post #1341188
Posted Tuesday, August 7, 2012 7:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1341260
Posted Wednesday, August 8, 2012 1:31 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
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

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1342171
Posted Wednesday, August 8, 2012 2:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:21 PM
Points: 185, Visits: 917
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.



Alex Suprun
Post #1342240
Posted Wednesday, August 8, 2012 10:20 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
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.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1342372
Posted Friday, August 10, 2012 4:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 1,375, Visits: 2,659
Thanks Guys!!
Post #1343272
Posted Friday, August 10, 2012 10:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:21 PM
Points: 185, Visits: 917
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.



Alex Suprun
Post #1343522
Posted Friday, August 10, 2012 11:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 42,425, Visits: 35,484
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1343568
Posted Friday, August 10, 2012 1:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 1,375, Visits: 2,659
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
Post #1343638
Posted Friday, August 10, 2012 1:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 42,425, Visits: 35,484
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1343643
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse