Index defragmentation

  • I think I'm encountering a fundamental gap in my understanding of index fragmentation, and I'm hoping you smart people can shine a little light. I run the following query:

    SELECT stat.object_id, stat.index_id, stat.index_type_desc, stat.avg_fragmentation_in_percent,

    ob.name

    FROM sys.dm_db_index_physical_stats (DB_ID(N'CE_DATA'), NULL, NULL, NULL , 'DETAILED') stat

    INNER JOIN sys.objects ob

    ON stat.object_id = ob.object_id

    WHERE stat.avg_fragmentation_in_percent > 0

    ORDER BY ob.name

    My understanding is that this will show me the fragmentation levels for my indexes (at least the ones that are fragmented). There are a whole bunch, with avg_fragmentation_in_percent values ranging from 33.3 to 87.5. I assume my goal is to get those values as low as possible, but so far nothing I've done has had any effect on them.

    I've tried running...

    ALTER INDEX ALL ON TableName

    REBUILD

    ...on each table. When I run the first query, the results are identical. I've also tried running Tara Kizer's isp_ALTER_INDEX script, and again the results are identical.

    I've even dropped an index entirely, run the first script to confirm it no longer appears in the list, and then recreated it. When I re-ran the first script, the index I just created appeared with its previous fragmentation value.

    So either I'm misinterpreting the results of the first query, or I don't have something set right, or I'm just a clueless newb. Any guidance would be greatly appreciated.

    Thanks!

    Ron Moses

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • How big are the tables in question? How many pages? (you can see that from the full output of index physical stats)

    My guess is that the tables are very small and for very small tables fragmentation doesn't mean much and, due to the way the first 20 or so pages are allocated, the fragmentation % may be higher than expected.

    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
  • All our tables are relatively small -- the biggest has about 266,000 rows. So you think I shouldn't be too worried about it then? I appreciate the feedback.

    Thanks!

    Ron Moses

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • I wouldn't call 200000 rows small. 🙂

    Check the number of pages in the table. Don't worry too much about tables with < 100 pages.

    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
  • I was thinking in terms of giant terabyte dbs, I guess. 🙂 Anyway, no index has a page_count higher than 21 from what I can see in sys.dm_db_index_physical_stats.

    Thanks for the advice!

    Ron Moses

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply