Solved

Index Fragmentation

Posted on 2007-12-05
9
629 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 25

Accepted Solution

by:
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.
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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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