January 30, 2009 at 11:14 am
I have a table of 39,842,660 rows and clustered index on the primary key.
Here is the information I get from sys.dm_db_index_physical_stats on the clustered idx (fill factor is 80):
Leaf level
page_count: 793395
avg_fragmentation_in_percent: 0.536680972277365
fragment_count: 4569
avg_fragment_size_in_pages: 173.647406434668
Level 1
page_count: 1284
avg_fragmentation_in_percent: 1.16822429906542
fragment_count: 41
avg_fragment_size_in_pages: 31.3170731707317
Levels 2 and 3 are very small (number of pages less than 10).
I'm concerned about the high number of fragments and low fragment size, in the leaf level especially.
So far, my automated re-indexing procedure has not picked up this aspect of fragmentation and has instead focused only on the avg_fragmentation_in_percent values, which, clearly, are very low in this case.
I think I have been missing this side of fragmentation all along and should modify my scripts to include the information on fragments.
Can someone give me some input?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 30, 2009 at 12:34 pm
You may be fine, but it depends on the workload.
Because SQL is reading an extent at a time from disk, you wouldn't want the average fragment size to be much below 8. At the same time if you are using the index for range scans of say 500 pages at a time then you would ideally have as few fragments as possible in that range ... your level 1 index would have to go through around 17 fragments on average under those circumstances.
January 30, 2009 at 12:42 pm
matt stockham (1/30/2009)
You may be fine, but it depends on the workload.Because SQL is reading an extent at a time from disk, you wouldn't want the average fragment size to be much below 8. At the same time if you are using the index for range scans of say 500 pages at a time then you would ideally have as few fragments as possible in that range ... your level 1 index would have to go through around 17 fragments on average under those circumstances.
Thank you.
I just read that a performance improvement is unlikely for avg_fragment_size_in_pages > 32 pages (256 KB), but, intuitively, I tend to agree it would depend on workload (from INSIDE MS SQL SERVER 2005: THE STORAGE ENGINE, Kalen Delaney, Solid Quality Learning, page 321).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 2, 2009 at 6:35 am
Every system is different, but I personally wouldn't sweat fragmentation until it at least got as high as 20% and probably I wouldn't do a rebuild until I saw fragmentation over 40%. Those are general rules of thumb and some systems behave differently, but as far as an analysis and threshold to begin evaluating the system and it's needs, that's where I'd start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 7:51 am
Grant Fritchey (2/2/2009)
Every system is different, but I personally wouldn't sweat fragmentation until it at least got as high as 20% and probably I wouldn't do a rebuild until I saw fragmentation over 40%. Those are general rules of thumb and some systems behave differently, but as far as an analysis and threshold to begin evaluating the system and it's needs, that's where I'd start.
Yes, these are more or less my standards as well. It's just that I was trying to troubleshoot a performance problem and was wondering about this.
Looks like I need to focus elsewhere.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply