Heavily fragmented indexes can degrade query performance and cause applications
to respond slowly. Reorganizing or rebuilding of indexes will fix this
issue. However, this is a very resource intensive process. Therefore identify
and only rebuild the indexes that need to be rebuilt is important.
Following criteria can be used to determine which indexes to rebuild,
which indexes to reorganize, and which indexes to leave alone.
- REBUILD index : if fragmentation is > 30% and number of pages > 1000
- REORGANIZE index : if fragmentation is > 10 % but < 30% and number of pages > 1000
We can use system function sys.dm_db_index_physical_stats
to find out fragmentation information of indexes like:
- avg_fragmentation_in_percent: The percent of logical fragmentation (out-of-order pages in the index).
- fragment_count: The number of fragments (physically consecutive leaf pages) in the index.
- avg_fragment_size_in_pages: Average number of pages in one fragment in an index.
Following is a complete SQL script copied from http://www.sqlmusings.com/2009/03/15/a-more-effective-selective-index-rebuildreorganize-strategy/
for the above purpose. Setting “report_only = 1” of the script will
only analyze the database without updating.
-- Ensure
a USE <databasename> statement has been
executed first.
SET NOCOUNT ON
--
adapted from "Rebuild or reorganize indexes (with configuration)"
from MSDN Books Online
--
(http://msdn.microsoft.com/en-us/library/ms188917.aspx)
--
=======================================================
-- ||
Configuration variables:
-- || -
10 is an arbitrary decision point at which to reorganize indexes.
-- || -
30 is an arbitrary decision point at which to
-- || switch
from reorganizing, to rebuilding.
-- || - 0
is the default fill factor. Set this to a
-- || a
value from 1 to 99, if needed.
--
=======================================================
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh
= 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 80
DECLARE @report_only bit SET @report_only = 1
-- added (DS) : page_count_thresh is
used to check how many pages the current table uses
DECLARE @page_count_thresh smallint SET @page_count_thresh = 1000
--
Variables required for processing.
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions
bigint
DECLARE @frag
float
DECLARE @page_count int
DECLARE @command
nvarchar(4000)
DECLARE @intentions
nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag
float,
page_count int
)
--
Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats
function and
--
convert object and index IDs to names.
INSERT INTO
@table_var
SELECT
[object_id] AS
objectid,
[index_id] AS
indexid,
[partition_number] AS
partitionnum,
[avg_fragmentation_in_percent]
AS frag,
[page_count] AS page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh
AND
page_count >
@page_count_thresh
AND
index_id >
0
--
Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
-- Open
the cursor.
OPEN partitions
-- Loop
through the partitions.
WHILE (1=1) BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @page_count
IF @@FETCH_STATUS <
0 BREAK
SELECT
@objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM
sys.objects AS
o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id] = @objectid
SELECT
@indexname = QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SELECT
@partitioncount = count (*)
FROM
sys.partitions WITH
(NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
-- Build the
required statement dynamically based on options and index stats.
SET @intentions =
@schemaname + N'.' +
@objectname + N'.' +
@indexname + N':' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N'
FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
N' PAGE COUNT:
' +
CAST(@page_count
AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'
OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER
INDEX ' + @indexname +
N' ON '
+ @schemaname
+ N'.' + @objectname
+
N'
REORGANIZE; ' +
N' UPDATE
STATISTICS ' + @schemaname + N'.' + @objectname +
N' '
+ @indexname
+ ';'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'
OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER
INDEX ' + @indexname +
N' ON '
+ @schemaname
+ N'.' + @objectname +
N' REBUILD'
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N'
PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N'
PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor
> 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N' FILL
FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N' WITH
(FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
-- Execute
determined operation, or report intentions
IF @report_only = 0 BEGIN
SET @intentions = @intentions + N' EXECUTING: ' + @command
PRINT @intentions
EXEC (@command)
END ELSE BEGIN
PRINT @intentions
END
PRINT @command
END
-- Close
and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
GO
Rebuilding an index can be executed online or offline. Reorganizing an
index is always executed online: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
