I have a database which I tried to defragment all the tables at once by running this T-SQL:

        '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:

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.

  • What errors did you get? Also is there a reason you have it reorganize and rebuild the same thing? – user507 Sep 2 '11 at 22:10
  • Shawn, I apologize for my missing of one word. I got no errors. As to why I ran both commands, I did that after trying each command individually. I updated my questions. – Justin Dearing Sep 2 '11 at 23:24

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.

  • Ok, that is satisfactory unless someone else has a better answer I will mark yours as correct. – Justin Dearing Sep 3 '11 at 0:26
  • 3
    +1 Agreed with Mark. Worry about fragmentation when you actually have some data. :-) – Aaron Bertrand Sep 3 '11 at 0:42
  • 3
    It's not that it can't, but why would it bother? Doing so is going to make little to no impact on I/O - especially since tables this small are almost guaranteed to be in memory anyway. – Aaron Bertrand Sep 3 '11 at 17:34
  • 1
    Just. Seems odd, that's all. Say I am writing an application to check and report on index fragmentation, I would have to add aditional logic to not only test frag percentage, but also the amount of pages so that there aren't false alarms. – Thomas Stringer Sep 3 '11 at 17:50
  • 1
    @KyleWilliamson I had a table with three columns and two rows. There were no indexes on it. Adding a clustered index cut 7 minutes of a 7.5 minute query. – Po-ta-toe Nov 17 '16 at 8:59

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.


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:


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;
  • 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. – Justin Dearing Sep 3 '11 at 1:34

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.