Index Fragmentation

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
sfrendo99Asked:
Who is Participating?
 
imitchieCommented:
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.
0
 
imitchieCommented:
If you don't want fragmentation at all, and you don't care about performance going to bits, pick one index and do this

DROP INDEX Table.X
CREATE INDEX X on Table(Y) WITH FILLFACTOR=100

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.
0
 
sfrendo99Author Commented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sfrendo99Author Commented:
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.
0
 
sfrendo99Author Commented:
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
 
       select
 
        [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])
 
       select
 
        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
 
              select
 
               @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(o.name), @SchemaName = quotename(s.name)
 
              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
 
       end
 
 
 
       -- Ending Index Fragmentation
 
       SELECT
 
        [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
 
       select
 
        [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'
 
        end
 
       ,[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;
 
end

Open in new window

0
 
imitchieCommented:
No it's different. You're using Alter INDEX commands. I am waiting for you to try Drop/Create.
0
 
sfrendo99Author Commented:
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??
0
 
imitchieCommented:
It's the alter index.
0
 
abhijit_kCommented:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Try the same for Reindexing for all Indexes in Databases. i Hope the owners of the table are DBO. for User Db.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.