Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Index Fragmentation

Posted on 2007-12-05
9
Medium Priority
?
641 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
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

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

Author Comment

by:sfrendo99
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 25

Accepted Solution

by:
imitchie earned 1000 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.
0
 

Author Comment

by:sfrendo99
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
 
       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
ID: 20416303
No it's different. You're using Alter INDEX commands. I am waiting for you to try Drop/Create.
0
 

Author Comment

by:sfrendo99
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??
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20416567
It's the alter index.
0
 
LVL 3

Assisted Solution

by:abhijit_k
abhijit_k earned 1000 total points
ID: 20425808
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 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