Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found on the internet all say that if you have a low page count defragmenting will not do anything. I have also seen answers of "It can only go so low" when the percentage is below 10%

However I have table with 3 indexes, all 3 indexes has a page count > 1900 and the fragmentation is all above 80%.

Here is the query I ran.

select 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  FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, null)

SELECT b.name, c.name, a.* FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, 'DETAILED') a
inner join sys.tables b on a.object_id = b.object_id
inner join sys.indexes c on a.object_id = c.object_id and a.index_id = c.index_id

print 'Rebuilding'
Alter index all on aahaDXavailables rebuild with (online = off)
print 'Done Rebuilding'

SELECT b.name, c.name, a.* FROM sys.dm_db_index_physical_stats (DB_ID(N'i101600'), 78675378, null, null, 'DETAILED') a
inner join sys.tables b on a.object_id = b.object_id
inner join sys.indexes c on a.object_id = c.object_id and a.index_id = c.index_id

select count(*) as Rows from aahaDXavailables

and it's results


+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+
| 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 |
+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+
|          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           0 | 87.7104377104377             |           2640 | 1.125                      |       2970 |
|          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 82.0746132848044             |           1853 | 1.18618456556935           |       2198 |
|          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 99.1295442908346             |           1952 | 1.00051229508197           |       1953 |
+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
|       name       |             name             | 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 |
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 82.0746132848044             |           1853 | 1.18618456556935           |       2198 | 64.311304670126                |       327022 |                  0 |                          0 |                       33 |                       33 |                       33 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           1 | 100                          |             18 | 1                          |         18 | 61.8304423029405               |         2198 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 9.09315542377069               |           18 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 99.1295442908346             |           1952 | 1.00051229508197           |       1953 | 72.3821349147517               |       327022 |                  0 |                          0 |                       33 |                       33 |                       33 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           1 | 100                          |             16 | 1                          |         16 | 61.805819125278                |         1953 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 8.08005930318755               |           16 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           0 | 87.7104377104377             |           2640 | 1.125                      |       2970 | 80.2371756856931               |       327022 |                  0 |                          0 |                       57 |                       57 |                       57 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           1 | 94.4444444444444             |             18 | 1                          |         18 | 50.9389671361502               |         2970 |                  0 |                          0 |                       23 |                       23 |                       23 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 5.53496417099086               |           18 |                  0 |                          0 |                       23 |                       23 |                       23 | NULL                   |
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
Rebuilding
Done Rebuilding
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
|       name       |             name             | 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 |
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 82.0746132848044             |           1853 | 1.18618456556935           |       2198 | 64.311304670126                |       327022 |                  0 |                          0 |                       33 |                       33 |                       33 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           1 | 100                          |             18 | 1                          |         18 | 61.8304423029405               |         2198 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_dxGUID   |          32 |  78675378 |        2 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 9.09315542377069               |           18 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           0 | 99.1295442908346             |           1952 | 1.00051229508197           |       1953 | 72.3821349147517               |       327022 |                  0 |                          0 |                       33 |                       33 |                       33 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           1 | 100                          |             16 | 1                          |         16 | 61.805819125278                |         1953 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | IX_aahaDXavailables_itemGUID |          32 |  78675378 |        3 |                1 | NONCLUSTERED INDEX | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 8.08005930318755               |           16 |                  0 |                          0 |                       39 |                       39 |                       39 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           0 | 87.7104377104377             |           2640 | 1.125                      |       2970 | 80.2371756856931               |       327022 |                  0 |                          0 |                       57 |                       57 |                       57 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           1 | 94.4444444444444             |             18 | 1                          |         18 | 50.9389671361502               |         2970 |                  0 |                          0 |                       23 |                       23 |                       23 | NULL                   |
| aahaDXavailables | PK_aahaDXavailables          |          32 |  78675378 |        1 |                1 | CLUSTERED INDEX    | IN_ROW_DATA          |           3 |           2 | 0                            |              1 | 1                          |          1 | 5.53496417099086               |           18 |                  0 |                          0 |                       23 |                       23 |                       23 | NULL                   |
+------------------+------------------------------+-------------+-----------+----------+------------------+--------------------+----------------------+-------------+-------------+------------------------------+----------------+----------------------------+------------+--------------------------------+--------------+--------------------+----------------------------+--------------------------+--------------------------+--------------------------+------------------------+
+--------+
|  Rows  |
+--------+
| 327022 |
+--------+

Why are my indexes not getting defragmented?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

When you rebuild the index set MAXDOP(1). This will only use 1 CPU and although it will take longer you will have much lower fragmentation.

I suspect that currently, where the index is "fairly small" the operation is being over parellelsed. Each thread used will cause additional fragmentation, as each thread orders its own pages only, and for smaller indexes this can be more noticeable.

share|improve this answer

Small indexes will often not get defragmented as there is basically no point. With the object being that small there is basically no benefit to moving the pages around.

share|improve this answer
    
Why is this considered small? I have a index > 1900 pages in size. And from everything I can find anything bigger than 1000 is considered "not small" –  Scott Chamberlain Oct 9 '12 at 4:08
    
1000 pages is 7.8 Megs. That's small. –  mrdenny Oct 9 '12 at 17:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.