By Justin Dearing


2011-09-02 20:57:15 8 Comments

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.

3 comments

@Marian 2011-09-05 10:45:00

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.

@Mark Storey-Smith 2011-09-02 23:44:25

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.

@Justin Dearing 2011-09-03 00:26:13

Ok, that is satisfactory unless someone else has a better answer I will mark yours as correct.

@Aaron Bertrand 2011-09-03 00:42:24

+1 Agreed with Mark. Worry about fragmentation when you actually have some data. :-)

@Thomas Stringer 2011-09-03 12:40:08

I completely grasp what you arre saying. But just out of sheer curiosity, is this because the db engine just can't defrag such few pages? I mean, there must be a reason for this.

@Aaron Bertrand 2011-09-03 17:34:07

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.

@Thomas Stringer 2011-09-03 17:50:05

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.

@Kyle Williamson 2015-12-01 15:55:25

Is there any point to having an index on tables this small? Should they be removed if the size of the table will never increase past 8 pages?

@Po-ta-toe 2016-11-17 08:59:02

@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.

@Aaron Bertrand 2011-09-03 00:47:02

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;

@Justin Dearing 2011-09-03 01:34:48

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.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] Database after restore process still display Restoring

1 Answered Questions

5 Answered Questions

2 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] why SELECT is still using CPU & DiskIO?

Sponsored Content