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.