December 29, 2011 at 10:44 am
Hey all, got some questions/concerns around index fragmentation.
I posted a similar article over on the MS technet, but figured I'd stir the pot here too. The problem I ran into was troubleshooting a sporadically slow singleton lookup on a Clustered Index in a table with about 8 million rows, which is a separate issue I may need to submit for help. That aside, during that troubleshooting I noticed fragmentation on the Unique Clustered Index (it's a VARCHAR(20)), and then noticed the fragmentation in other indexes on this table. See sys.dm_db_index_physical_stats and DBCC SHOWCONTIG results below.
[font="Courier New"]SELECT
substring(OBJECT_NAME(i.object_id),1,30) AS TableName,
substring(i.name,1,40) AS TableIndexName,
i.index_id, phystat.index_level,
phystat.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE
OBJECT_NAME(i.object_id) = 'CONSUMERS'
TableName TableIndexName index_id index_level avg_fragmentation_in_percent
CONSUMERS UNI2K_CONSUMERS 1 0 0.154827346202469
CONSUMERS UNI2K_CONSUMERS 1 1 35.2941176470588
CONSUMERS UNI2K_CONSUMERS 1 2 0
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 0 0.336078590685822
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 1 100
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 2 0
CONSUMERS UNI1K_CONSUMERS 3 0 0.156451316031658
CONSUMERS UNI1K_CONSUMERS 3 1 61.1510791366906
CONSUMERS UNI1K_CONSUMERS 3 2 0
CONSUMERS IDX1_CONSUMERS 4 0 0.215271389144434
CONSUMERS IDX1_CONSUMERS 4 1 40
CONSUMERS IDX1_CONSUMERS 4 2 100
CONSUMERS IDX1_CONSUMERS 4 3 0
CONSUMERS IDX2_CONSUMERS 5 0 0.222614710968834
CONSUMERS IDX2_CONSUMERS 5 1 38.6281588447653
CONSUMERS IDX2_CONSUMERS 5 2 75
CONSUMERS IDX2_CONSUMERS 5 3 0
(17 row(s) affected)
DBCC SHOWCONTIG('CONSUMERS') WITH ALL_INDEXES
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 70401
- Extents Scanned..............................: 8827
- Extent Switches..............................: 8843
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.51% [8801:8844]
- Logical Scan Fragmentation ..................: 0.15%
- Extent Scan Fragmentation ...................: 23.76%
- Avg. Bytes Free per Page.....................: 47.2
- Avg. Page Density (full).....................: 99.42%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 27077
- Extents Scanned..............................: 3402
- Extent Switches..............................: 3402
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.47% [3385:3403]
- Logical Scan Fragmentation ..................: 0.34%
- Extent Scan Fragmentation ...................: 11.88%
- Avg. Bytes Free per Page.....................: 24.1
- Avg. Page Density (full).....................: 99.70%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 3, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 54330
- Extents Scanned..............................: 6803
- Extent Switches..............................: 6805
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.79% [6792:6806]
- Logical Scan Fragmentation ..................: 0.16%
- Extent Scan Fragmentation ...................: 7.03%
- Avg. Bytes Free per Page.....................: 50.3
- Avg. Page Density (full).....................: 99.38%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 4, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 54350
- Extents Scanned..............................: 6808
- Extent Switches..............................: 6837
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.36% [6794:6838]
- Logical Scan Fragmentation ..................: 0.22%
- Extent Scan Fragmentation ...................: 7.17%
- Avg. Bytes Free per Page.....................: 53.2
- Avg. Page Density (full).....................: 99.34%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 54354
- Extents Scanned..............................: 6804
- Extent Switches..............................: 6846
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.24% [6795:6847]
- Logical Scan Fragmentation ..................: 0.22%
- Extent Scan Fragmentation ...................: 7.13%
- Avg. Bytes Free per Page.....................: 53.8
- Avg. Page Density (full).....................: 99.33%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/font]
This fragmentation I found shocking because I reorg nightly and have a weekly rebuild task running that I set up through the Maintenance Plan wizard (which I've verified has been running). So I attempted to reorg these manually (especially index ID: 1) and to my shock the fragmentation % did not change at all. I then took the SQL provided by the Maintenance Plan for rebuilding the indexes and found that after running that it didn't change the fragementation % at all either (commands run shown below).
ALTER INDEX [IDX1_CONSUMERS] ON [dbo].[CONSUMERS] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )
GO
ALTER INDEX [IDX2_CONSUMERS] ON [dbo].[CONSUMERS] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )
GO
ALTER INDEX [UNI1K_CONSUMERS] ON [dbo].[CONSUMERS] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, SORT_IN_TEMPDB = ON )
GO
ALTER INDEX [UNI2K_CONSUMERS] ON [dbo].[CONSUMERS] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, SORT_IN_TEMPDB = ON )
GO
Fragmentation did not change until I performed a "CREATE ... DROP_EXISTING = ON" on the 4 non PK indexes and a manual rebuild of Primary Key offline not specifying any other parameters, which all seemed completely overkill to ensure the defragmentation actually got done. Final sys.dm_db_index_physical_stats and DBCC SHOWCONTIG results below.
[font="Courier New"]SELECT
substring(OBJECT_NAME(i.object_id),1,30) AS TableName,
substring(i.name,1,40) AS TableIndexName,
i.index_id, phystat.index_level,
phystat.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE
OBJECT_NAME(i.object_id) = 'CONSUMERS'
TableName TableIndexName index_id index_level avg_fragmentation_in_percent
CONSUMERS UNI2K_CONSUMERS 1 0 0.0213458562356583
CONSUMERS UNI2K_CONSUMERS 1 1 11.2426035502959
CONSUMERS UNI2K_CONSUMERS 1 2 0
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 0 0.0460971112476951
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 1 14.2857142857143
CONSUMERS PK__CONSUMER__7F6B0B8B286302EC 2 2 0
CONSUMERS UNI1K_CONSUMERS 3 0 0.0225314031431307
CONSUMERS UNI1K_CONSUMERS 3 1 10.6194690265487
CONSUMERS UNI1K_CONSUMERS 3 2 0
CONSUMERS IDX1_CONSUMERS 4 0 0.0225318262045139
CONSUMERS IDX1_CONSUMERS 4 1 10.7296137339056
CONSUMERS IDX1_CONSUMERS 4 2 0
CONSUMERS IDX1_CONSUMERS 4 3 0
CONSUMERS IDX2_CONSUMERS 5 0 0.0225314031431307
CONSUMERS IDX2_CONSUMERS 5 1 12.0171673819742
CONSUMERS IDX2_CONSUMERS 5 2 0
CONSUMERS IDX2_CONSUMERS 5 3 0
(17 row(s) affected)
DBCC SHOWCONTIG('CONSUMERS') WITH ALL_INDEXES
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 56217
- Extents Scanned..............................: 7029
- Extent Switches..............................: 7028
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.99% [7028:7029]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.44%
- Avg. Bytes Free per Page.....................: 32.4
- Avg. Page Density (full).....................: 99.60%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 26032
- Extents Scanned..............................: 3256
- Extent Switches..............................: 3255
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.94% [3254:3256]
- Logical Scan Fragmentation ..................: 0.05%
- Extent Scan Fragmentation ...................: 0.31%
- Avg. Bytes Free per Page.....................: 11.1
- Avg. Page Density (full).....................: 99.86%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 3, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 53259
- Extents Scanned..............................: 6659
- Extent Switches..............................: 6658
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [6658:6659]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.35%
- Avg. Bytes Free per Page.....................: 40.5
- Avg. Page Density (full).....................: 99.50%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 4, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 53258
- Extents Scanned..............................: 6659
- Extent Switches..............................: 6658
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [6658:6659]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.53%
- Avg. Bytes Free per Page.....................: 40.3
- Avg. Page Density (full).....................: 99.50%
DBCC SHOWCONTIG scanning 'CONSUMERS' table...
Table: 'CONSUMERS' (645577338); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 53259
- Extents Scanned..............................: 6659
- Extent Switches..............................: 6658
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [6658:6659]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.59%
- Avg. Bytes Free per Page.....................: 40.5
- Avg. Page Density (full).....................: 99.50%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/font]
For the record, here's the version I'm running:
[font="Courier New"]select @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)[/font]
In summary my question is - why didn't ALTER INDEX ... REBUILD/REORGANIZE modify the index_level 1 fragmentation as reported by sys.dm_db_index_physical_stats, nor would it correct the Extent Scan Fragmentation as reported by DBCC SHOWCONTIG?
December 29, 2011 at 10:51 am
If I understand correctly, you saw one level of fragmentation, ran the rebuild and think that the fragmentation is the same?
You have less than 1% fragmentation in the logical and extent scan frag levels. Are you trying to achieve absolute 0% fragmentation?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2011 at 11:11 am
No, I'm not trying to get to 0% as that's typically not attainable.
[font="Courier New"]Table: 'CONSUMERS' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Extent Scan Fragmentation ...................: 23.76%
TableName TableIndexName index_id index_level avg_fragmentation_in_percent
CONSUMERS UNI2K_CONSUMERS 1 0 0.154827346202469
CONSUMERS UNI2K_CONSUMERS 1 1 35.2941176470588
CONSUMERS UNI2K_CONSUMERS 1 2 0[/font]
DBCC SHOWCONTIG and sys.dm_db_index_physical_stats give different ways of looking at fragmentation, and my understanding is that DBCC SHOWCONTIG is being depricated and sys.dm_db_index_physical_stats is more accurate in 2008 anyway. However, according to Microsoft:
Extent Fragmentation
This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.
The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable. All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. For more information about how to analyze the degree of fragmentation in an index, see Reorganizing and Rebuilding Indexes.
I sourced that from directly from the MSDN article on sys.dm_db_index_physical_stats (Transact-SQL) which can be found here: http://msdn.microsoft.com/en-us/library/ms188917.aspx
I was looking at a value of 35 according to sys.dm_db_index_physical_stats. It wasn't until I did a CREATE .. WITH DROP_EXISTING that I was able to get that number to budge, ALTER INDEX REBUILD/REORGANIZE would not effect it.
December 29, 2011 at 11:22 am
That 35% fragmentation is in a non-leaf level of your index, a level that has much fewer pages than the leaf and will be much less affected by fragmentation anyway (likely to be in memory, unlikely to be scanned).
Hence this is very much something not to be too concerned about.
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
December 29, 2011 at 11:25 am
p.s. Singleton lookups are the one thing that won't be affected by fragmentation at all.
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
December 29, 2011 at 11:43 am
GilaMonster (12/29/2011)
p.s. Singleton lookups are the one thing that won't be affected by fragmentation at all.
Appreciate that tip. You know how it goes though, you're investigating a problem and you look at all of the usual suspects in your process of elimination you get completely sidetracked with something else that smells fishy but you're not sure what to do with.
December 29, 2011 at 11:50 am
GilaMonster (12/29/2011)
That 35% fragmentation is in a non-leaf level of your index, a level that has much fewer pages than the leaf and will be much less affected by fragmentation anyway (likely to be in memory, unlikely to be scanned).Hence this is very much something not to be too concerned about.
It's confusing because everything I've read doesn't distinguish between index_level 0 or any other level. Two follow up questions:
1. Are you saying that so long as index_level 0 is in good shape I should ignore the rest?
2. Why didn't ALTER INDEX REBUILD/REORGANIZE seem to touch anything other than index_level 0 in this case?
December 29, 2011 at 11:53 am
1) Kinda...
Fragmentation affects large range scans from disk. Emphasis large and disk. The intermediate levels (anything other than 0) tend to be much smaller than the leaf level, and far more likely to be in cache rather than just on disk. Hence they are far less of a concern (not completely ignore, but less worry)
2) Reorganise (afaik) only works on the leaf level. Rebuild should touch all, but it's going to have more of an impact on the leaf (which is larger) than on the others.
p.s. Could you please edit your earlier posts and take out the massive lines of -------- which are causing the scrolling?
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
December 29, 2011 at 11:59 am
p.s. Could you please edit your earlier posts and take out the massive lines of -------- which are causing the scrolling?
Strange, it's not scrolling on my Chrome browser. But not a problem. Those were simply the SQL output which were space aligned from SSMS but the forums look like they compressed the redundant spaces out before presenting the final HTML. Should be done in a sec.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply