Index Fragmentation

Posted on 2007-12-05
Last Modified: 2012-06-27
I am having the same problem as question ID: 22059997.  This question was never answered with a correct solution.  I Have about 130 SQL 2005 indexes with fragmentation levels between 30% to 99%.  I have tried to set a maintenance plan to rebuild the indexes with no luck.  I have put the database in single user mode and tried to rebuild the indexes indivudually with no success.  I have tried to rebuild the indexes with a SQL script with the same results.  The level of fragmentation does not even change by a percentage point in either direction.  I have been successful in shrinking the database, just not defragmenting the indexes.

Thanks for any help
Question by:sfrendo99
  • 4
  • 4
LVL 25

Expert Comment

ID: 20415526
If you don't want fragmentation at all, and you don't care about performance going to bits, pick one index and do this


To confirm that you can in fact make it fill completely. It is not the best thing to do to any database, except for 100% read-only tables.

Author Comment

ID: 20415583
From what I read, and I might be wrong, Iindex fragmentation is due to the index pages being out of order.  I am under the impression that reorganizing or rebuilding the index puts all of the pages in the index into correct order or rebuilds the page (s) into a more efficient index.  If you fill every index page up to 100%, I don't see how that would rearrange the pages or the data in the pages to a higher efficiency.

Author Comment

ID: 20415770
This is what I read from microsoft.  Like I said, I'm new to SQL.  It seems to me that reindexing or rebuilding should do something to the fragmentation level

The SQL Server 2005 Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.

In SQL Server 2005 you can remedy index fragmentation by either reorganizing an index or by rebuilding an index.
LVL 25

Accepted Solution

imitchie earned 250 total points
ID: 20416094
When you drop/create, it will allocate a new space for it, so fragmentation of the index pages don't occur. Kind of like disk defrag. Just copying a file around doesn't defrag it.  Add a Reorg page task - very slow.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 20416203
ok, when I go to the properties of the index page and I tell it to rebuild it, isin't that what it is doing, dropping the page and rebuilding it?  When I do that, it finishes the process very quickly but the fragmentation never changes.  I also tried to do this through a script and that had the same effect.  Here is the script that I used.
-- Warning: This is to be used on SQL 2005 server 

-- This SQL script uses SQL 2005 functionality


-- Ensure a USE <databasename> statement has been executed first.

-- Example: USE Altiris

declare @CompatMode int 

select @CompatMode = ( 

       select cmptlevel  

       from master.dbo.sysdatabases 

       where dbid = db_id() 



if (@CompatMode = 90) begin

       declare @CurrentRow int;

       declare @MaxRows  int;


       declare @ObjectId int;

       declare @ObjectName nvarchar(130);

       declare @IndexId  int;

       declare @IndexName nvarchar(130);

       declare @PartitionNum bigint;

       declare @Partitions bigint;

       declare @PartitionCount bigint;

       declare @SchemaName nvarchar(130);

       declare @AvgFrag numeric (6,4);

       declare @SQLCommand nvarchar(4000);


       -- Beginning Index Fragmentation


        [Table] = object_name(stats.[object_id])

       ,[Index]= b.[name]

       ,[Avg Fragmentation] = cast(avg_fragmentation_in_percent as numeric (6,4))

       ,[Page Count] = stats.page_count

       into #InsIndexFrag

       from sys.dm_db_index_physical_stats (db_id(), NULL, NULL , NULL, 'LIMITED') AS stats

       join sys.indexes as b on stats.object_id = b.object_id

        and stats.[index_id] = b.[index_id]

       where avg_fragmentation_in_percent > 10.0 and b.index_id > 0;


       create table #DefregList (

               [id] bigint identity (1,1) not null

              ,[ObjectId] int

              ,[IndexId] int

              ,[PartitionNum] int 

              ,[AvgFrag] float



       -- Create temp table to hold needed values

       insert into #DefregList ([ObjectId], [IndexId], [PartitionNum],[AvgFrag])


        ObjectId = object_id 

       ,IndexId = index_id 

       ,[PartitionNum] = partition_number 

       ,[AvgFrag] = avg_fragmentation_in_percent 

       from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED')

       where avg_fragmentation_in_percent > 10.0

         and index_id > 0


       -- Set inc values used in the loop

       select @CurrentRow = 1

       select @MaxRows = (select max([id]) from #DefregList)


       -- Loop through the partitions and defrag or rebuild indexes.

       while (@CurrentRow <= @MaxRows) begin

              -- Set values from the current row


               @ObjectId = [ObjectId]

              ,@IndexId = [IndexId]

              ,@PartitionNum = [PartitionNum]

              ,@AvgFrag = [AvgFrag]

              from #DefregList

              where [id] = @CurrentRow

              -- Set object name, schema name, index id by using the oject id

              select @ObjectName = quotename(, @SchemaName = quotename(

              from sys.objects as o

              join sys.schemas as s on s.schema_id = o.schema_id

              where o.object_id = @ObjectId;


              select @IndexName = quotename(name) from sys.indexes

              where  object_id = @ObjectId and index_id = @IndexId;


              select @PartitionCount = count (*) from sys.partitions

              where object_id = @ObjectId and index_id = @IndexId;


              -- The 30% is an arbitrary decision point at which to 

              -- switch between reorganizing and rebuilding.

              if (@AvgFrag < 30.0)

                     set @SQLCommand = 

                     N'ALTER INDEX ' + @IndexName + 

                     N' ON ' + @SchemaName + N'.' + @ObjectName + N' REORGANIZE';

              if (@AvgFrag >= 30.0)

                     set @SQLCommand = 

                     N'ALTER INDEX ' + @IndexName + 

                     N' ON ' + @SchemaName + N'.' + @ObjectName + N' REBUILD';

              if (@PartitionCount > 1)

                     set @SQLCommand = @SQLCommand + N' PARTITION=' + cast(@PartitionNum as nvarchar(10));


              execute (@SQLCommand);

              print N'Executed: ' + @SQLCommand;


              -- inc current row

              set @CurrentRow = @CurrentRow + 1



       -- Ending Index Fragmentation


        [Table] = object_name(stats.[object_id])

       ,[Index]= b.[name]

       ,[Avg Fragmentation] = cast(avg_fragmentation_in_percent as numeric (6,4))

       ,[Page Count] = stats.page_count

       into #FinIndexFrag

       FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL , NULL, 'LIMITED') AS stats

       JOIN sys.indexes AS b ON stats.object_id = b.object_id

        AND stats.[index_id] = b.[index_id]

       where avg_fragmentation_in_percent > 10.0 AND b.index_id > 0;



       -- Show Fragmentation Change


        [Solution] = isnull(vp.[Name],'NS Core')

       ,[Table Name] = todo.[Table]

       ,[Type] = case cast(dc.[ClassType] as int)

              when '0' then 'User'

              when '1' then 'Event'

              when '2' then 'Common'

              else 'Core'


       ,[Index Name] = todo.[Index]

       ,[Start %] = todo.[Avg Fragmentation]

       ,[End %] = did.[Avg Fragmentation]

       ,[Delta] = todo.[Avg Fragmentation] - did.[Avg Fragmentation]

       ,[Page Start(8KB)] = todo.[Page Count]

       ,[Page End] = did.[Page Count]

       ,[Page Delta] = todo.[Page Count] - did.[Page Count]

       from #InsIndexFrag todo

       join #FinIndexFrag did on todo.[Table]=did.[Table]

        and todo.[Index] = did.[Index]

       left join [DataClass] dc on dc.[DataTableName] = todo.[Table]

                 or dc.[HistoryTableName] = todo.[Table]

       left join vItem vi on vi.[guid] = dc.[guid]

       left join vProduct vp on vp.[guid] = vi.[ProductGuid]

       order by 5 desc, 1, 2;


       -- Drop the temporary table.

       drop table #DefregList;

       drop table #InsIndexFrag;

       drop table #FinIndexFrag;


Open in new window

LVL 25

Expert Comment

ID: 20416303
No it's different. You're using Alter INDEX commands. I am waiting for you to try Drop/Create.

Author Comment

ID: 20416536
I'll give that a try and see if there are different results.  I'll post back the results once I am done.  When I manually tell the index to rebuild, Is that the same as dropping the index or is that the same as using the Alter INDEX command??
LVL 25

Expert Comment

ID: 20416567
It's the alter index.

Assisted Solution

abhijit_k earned 250 total points
ID: 20425808

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 90)"


Try the same for Reindexing for all Indexes in Databases. i Hope the owners of the table are DBO. for User Db.

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now