none
Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008

    Question

  • I understand that indexs when rebuilt should show less fragmentation.

    In AW, Before and after I rebuild all indexes, index freagmentation show the same results as follows

    TABLE_NAME INDEX_NAME FRAGMENT_PERCENT

    Employee AK_Employee_LoginID 66

    ,6666666666667

    Am I missing something
    Thanks

    Sunday, February 14, 2010 8:38 PM

Answers

  • First thing that come in my mind

    table in question is a heap (without having a clustered index)

    There are few other reasons from:

    http://www.sqlskills.com/blogs/paul/post/Conference-Questions-Pot-Pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations.aspx

    Why doesn't performing an index rebuild alter the fragmentation

    There isn't an index - either DBCC DBREINDEX or ALTER INDEX ... REBUILD are being run on a table that only has a heap, and so the (extent) fragmentation of the heap isn't changing because there's no way to rebuild a heap (except by the heavily NOT recommended method of creating and then dropping a clustered index).

    The index is too small. An index with only a handful of pages may not show any changes in fragmentation because all the pages are single, mixed pages (see my previous post on extent types for more info) and so rebuilding the index does nothing.

    The workload and schema are such that by the time the rebuild has finished and the fragmentation calculation has been done again, the index is already getting fragmented again.

    The Extent Scan Fragmentation result from DBCC SHOWCONTIG is being used to gauge fragmentation for an index stored in a filegroup with multiple files. The Extent Scan Fragmentation in DBCC SHOWCONTIG does not cope with multiple files (as is documented in Books Online) and so the value may even go UP in some cases

    Sunday, February 14, 2010 9:04 PM

All replies

  • First thing that come in my mind

    table in question is a heap (without having a clustered index)

    There are few other reasons from:

    http://www.sqlskills.com/blogs/paul/post/Conference-Questions-Pot-Pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations.aspx

    Why doesn't performing an index rebuild alter the fragmentation

    There isn't an index - either DBCC DBREINDEX or ALTER INDEX ... REBUILD are being run on a table that only has a heap, and so the (extent) fragmentation of the heap isn't changing because there's no way to rebuild a heap (except by the heavily NOT recommended method of creating and then dropping a clustered index).

    The index is too small. An index with only a handful of pages may not show any changes in fragmentation because all the pages are single, mixed pages (see my previous post on extent types for more info) and so rebuilding the index does nothing.

    The workload and schema are such that by the time the rebuild has finished and the fragmentation calculation has been done again, the index is already getting fragmented again.

    The Extent Scan Fragmentation result from DBCC SHOWCONTIG is being used to gauge fragmentation for an index stored in a filegroup with multiple files. The Extent Scan Fragmentation in DBCC SHOWCONTIG does not cope with multiple files (as is documented in Books Online) and so the value may even go UP in some cases

    Sunday, February 14, 2010 9:04 PM
  • My bet would be too few pages. Judging by that value, I would guess that there are only 3 or 6 pages... Fragmentaiotn isn't relevant until you have about some 1000 pages or so, less don't care...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, February 15, 2010 12:19 PM