44

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.

0

4 Answers 4

38

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.

0
21

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:

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

13

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;
1
  • Aaron, thanks for pointing out the dynamic sql, I'm well aware of dynamic sql, I wasn't going to automate the solution till it worked. Others reading this should probably be aware though. Sep 3, 2011 at 1:34
-2

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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