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

Question

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.

Answer 1

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.

• Ola Hallengren - SQL Server Maintenance Solution
• SQL Fool - Index Defrag Script

Answer 2

Quote from "Microsoft SQL Server 2000 Index Defragmentation Best Practices":

"Fragmentation affects disk I/O. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server. Use the page count reported by DBCC SHOWCONTIG to get an idea of the size of the indexes (each page is 8 KB in size). Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

So this kind of answers your question and backs Mark's and Aaron's answers.

You can find good information about index fragmentation in the following articles from Brent Ozar:

• Index Fragmentation Findings: Part 1, The Basics
• Index Fragmentation Findings: Part 2, Size Matters

Also..an ocean of great info about indexes in general (also about fragmentation issues) can be found on Kimberly Tripp's blog.

Answer 3

This is not meant to answer your question, but it will never fit in a comment. You can build this script dynamically without having to copy & paste the output into another window. Taking into account that there is absolutely no reason to REORGANIZE and then REBUILD:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER INDEX all ON ' + name + ' REBUILD;
    ' FROM sys.tables;

PRINT @sql; -- to see the first 8,000 characters and make sure it checks out
-- EXEC sp_executesql @sql;

Answer 4

I think that to rebuild all the indexes you can run a more elegant solution, try with

 EXEC sp_MSforeachtable ' ALTER INDEX ALL ON ? REBUILD'