Why is my database still fragmented after I rebuilt and reindexed everything?

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
NUL

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,

• SQL Fool - Index Defrag Script

StackExchange Database Administrators Q#5365, answer score: 39