Fragmentation and ALTER INDEX REBUILD/REORGANIZE not resolving it

  • 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?

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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