Why index REBUILD does not reduce index fragmentatation with a page count > 1000?

Question

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?

Answer 1

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.

Answer 2

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.