Solved

Index Fragmentation

Posted on 2007-12-05
9
620 Views
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
0
Comment
Question by:sfrendo99
  • 4
  • 4
9 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:sfrendo99
Comment Utility
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
 

Author Comment

by:sfrendo99
Comment Utility
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
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:sfrendo99
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
No it's different. You're using Alter INDEX commands. I am waiting for you to try Drop/Create.
0
 

Author Comment

by:sfrendo99
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
It's the alter index.
0
 
LVL 3

Assisted Solution

by:abhijit_k
abhijit_k earned 250 total points
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

12 Experts available now in Live!

Get 1:1 Help Now