SQL Server 2008 – Why is Database Still Fragmented After Rebuilding and Reindexing?

fragmentationindexsql-server-2008

I have a database which I tried to defragment all the tables at once by running this T-SQL:

SELECT 
        'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
        'ALTER INDEX all ON ' + name + ' REBUILD;'
    FROM sys.tables

And then copying and pasting the output to a new query window and running that. I got no errors, but I still have fragmentation. I tried running both commands separately too and still have fragmentation. Note: I have been made aware that REORGANIZE is unnecessary by Aaron, and I'm aware I could use dynamic sql to automate this.

I ran this to determine I still have fragmentation:

SELECT * FROM 
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) 
WHERE avg_fragmentation_in_percent > 0

And I got:

database_id object_id   index_id    partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count  avg_page_space_used_in_percent  record_count    ghost_record_count  version_ghost_record_count  min_record_size_in_bytes    max_record_size_in_bytes    avg_record_size_in_bytes    forwarded_record_count  compressed_page_count
85  171147655   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   36.3636363636364    5   2.2 11  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  421576540   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   75  7   1.14285714285714    8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  965578478   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   14.7058823529412    6   5.66666666666667    34  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1061578820  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   40  4   1.25    5   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1109578991  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   30.7692307692308    5   2.6 13  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1205579333  2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   50  5   1.6 8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1493580359  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   50  6   1.66666666666667    10  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

I know I am missing something real basic, but I don't know what.

Best Answer

The tables are tiny. The page counts in your tables are:

11, 8, 6, 5, 13, 8, 10

They occupy 480kb in total. There is quite literally nothing to defrag.

Edit: This warrants a little more explanation.

A new table or index is usually allocated it's first 8 pages from a mixed, rather than uniform extent. So, it's possible for each of the first 8 pages to be allocated from different mixed extents. A table or index consuming 8 pages could therefore have 8 fragments, 1 on each of 8 different mixed extents.

The more widely used defrag scripts (a couple of examples linked below) tend to exclude small tables because of this. IIRC, <500 pages is in one or both of them. At these sizes, there is very little benefit to defragmenting and the fragmentation figures are potentially skewed by the mixed extent allocations.

Related Question