Environment
Microsoft SQL Server 2005
Cause
SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data in a table. This also forces changes to the index that have to create new index pages to accommodate the additional data. As pages are added to an index, they use whatever SQL page that is available. This growth can cause the data in the index to become fragmented, that is that the index data becomes physically scattered and out of order. When an index becomes fragmented, performance is diminished.
Additionally over time as index pages split, the B-Tree of the index becomes unbalanced, that is the number of index pages needed to find a specific value is higher for some values than for others thus making some portions of an index a lot slower.
Resolution
SQL Server can remedy index fragmentation by either reorganizing an index or by rebuilding(recommended) an index. Defragmenting (reorganize) an index consolidates all of the index data (or leaf level) into the smallest number of pages possible while still respecting the indexes fill factor and makes sure that those pages are physically in the order as per the index. Defragmentation can be done while the index is actively being used and has minimal overhead to SQL's performance. The Reorganize index option, however does not have the ability to rebuild the indexes non-leaf level pages. Only rebuilding an index causes the index's B-Tree to be rebuilt to ensure that it is balanced. The downside is that unlike reorganizing an index, while rebuilding, the index is unavailable and SQL must retrieve requested data without the use of the index currently being rebuilt.
A general best practice guideline in a typical environment is to schedule a SQL maintenance plan to rebuild the indexes once a week. That maintenance plan is configured to leave 10% free space in the index after the rebuild. Also the same maintenance plan should be configured to update statistics. This maintenance plan should be scheduled to run during low utilization so minimize the impact of an index being unavailable while it is being rebuilt.
See KB40488 for a step by step example.
In some cases, a manual SQL execution is needed to do a one time rebuild or defragmentation of the indexes. In SQL 2005 there is a system function called sys.dm_db_index_physical_stats that can provide fragmentation information for each index. Listed below are a few different SQL queries that will help with analyzing and manually correcting fragmented indexes. Each of them have comments that describe what they are used for.
SQL script to show index fragmentation of a database for greater than 10%:
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
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
order by 1, 2;
This SQL query adds Solutions and dataclass types:
-- SQL script to show index fragmentation of a database for greater than 10%
declare @CompatMode int
select @CompatMode = (
select cmptlevel
from master.dbo.sysdatabases
where dbid = db_id()
)
if (@CompatMode = 90) begin
select
[Solution] = isnull(vp.[Name],'NS Core')
,[Table] = object_name(stats.[object_id])
,[Type] = case cast(dc.[ClassType] as int)
when '0' then 'User'
when '1' then 'Event'
when '2' then 'Common'
else 'Core'
end
,[Index]= b.[name]
,[Avg Fragmentation] = cast(avg_fragmentation_in_percent as numeric (6,4))
,[Page Count] = stats.[page_count]
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') stats
join sys.indexes b on stats.object_id = b.object_id
and stats.[index_id] = b.[index_id]
left join [DataClass] dc on dc.[DataTableName] = object_name(stats.[object_id])
or dc.[HistoryTableName] = object_name(stats.[object_id])
left join vItem vi on vi.[guid] = dc.[guid]
left join vProduct vp on vp.[guid] = vi.[ProductGuid]
where avg_fragmentation_in_percent > 10.0 and b.index_id > 0
and stats.[page_count] > 2
order by 5 desc, 1, 3, 4;
end
-- Shows database fragmentation with a page count variable to narrow the results
-- Also a SQL quick script to defrag one index at a time if needed
declare @PageCount bigint
set @PageCount = 1000
select
[Table] = object_name(stats.[object_id])
,[Index]= si.[name]
,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,4))
,[Page Count] = stats.[page_count]
,[SQL script] = case
when (stats.[avg_fragmentation_in_percent] < = 30)
then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] reorganize'
when (stats.[avg_fragmentation_in_percent] > 30)
then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] rebuild'
end
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as si on stats.object_id = si.object_id
and stats.[index_id] = si.[index_id]
where stats.[avg_fragmentation_in_percent] > 10.0
and si.index_id > 0
and stats.[page_count] >= @PageCount
order by 3 desc, 1, 2;
Note: You can copy the "SQL script" column and run them one at a time.
After you know the degree of fragmentation, you will need to determine the best method to correct the index fragmentation.
Fragmentation of > 10% and <= 30% will use this method:
alter index Index_Name on Table_Name reorganize
Fragmentation of > 30% will use this method.
alter index Index_Name on Table_Name rebuild
The following SQL script can automate the defragmentation and determine the method used. This will also show the change in fragmentation after the defragmentation process.
NOTE: This script is not recommended to be used on a repeated schedule as it will always keep indexes defragmented to the point where they never get rebuilt. Although this does keep Fragmentation cost down, the B-Tree will continue to become more and more unbalanced which will cause some portions of the index to become very slow.
DECLARE @CompatMode INT IF (@CompatMode = 90) BEGIN -- Beginning Index Fragmentation -- Create temp table to hold needed values -- Set inc values used in the loop -- Loop through the partitions and defrag or rebuild indexes. -- Set object name, schema name, index id by using the object id -- The 30% is an arbitrary decision point at which to SELECT -- Show Fragmentation Change -- Drop the temporary table.-- 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
SELECT @CompatMode =
(
SELECT cmptlevel
FROM master.dbo.sysdatabases
WHERE dbid = db_id()
)
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);
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
)
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
SELECT @CurrentRow = 1
SELECT @MaxRows = (SELECT max([id]) FROM #DefregList)
while (@CurrentRow <= @MaxRows) BEGIN
-- Set values FROM the current row
SELECT
@ObjectId = [ObjectId]
,@IndexId = [IndexId]
,@PartitionNum = [PartitionNum]
,@AvgFrag = [AvgFrag]
FROM #DefregList
WHERE [id] = @CurrentRow
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;
-- 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
[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;
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 TABLE #DefregList;
DROP TABLE #InsIndexFrag;
DROP TABLE #FinIndexFrag;
END
Note: You may need to stop the Altiris service before you run the SQL script.