Script for rebuilding and reindexing the fragmented index?

Asked 6/7, 2009 at 12:55 Answered 6/7, 2009 at 12:55

Can anyone provide the script for rebuilding and re-indexing the fragmented index when 'avg_fragmentation_in_percent' exceeds certain limits (better if cursor is not used)?

Duel answered 6/7, 2009 at 12:55 Comment(3)

Why not? Anyone can choose to make any question they ask a Wiki question. It avoids losing rep points. – Hamlani 6/7, 2009 at 13:9

@Savitha: which part is the problem? I'm not immediately sure how you'd do the operation with a cursor. Are you seeking a script to detect the percentage and then rebuild? Have you checked the manual for options? What are the available statements? Do you have to determine the index details? Is there an ALTER INDEX statement? – Hamlani 6/7, 2009 at 13:18

@Jonathan Leffler, but this a good legit question with little risk of getting down votes. fewer people view wikis and/or want to answer for zero points. – Symbolic 6/7, 2009 at 13:39

To rebuild use:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD

or to reorganize use:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REORGANIZE

Reorganizing should be used at lower (<30%) fragmentations but only rebuilding (which is heavier to the database) cuts the fragmentation down to 0%.
For further information see https://msdn.microsoft.com/en-us/library/ms189858.aspx

Symbolic answered 6/7, 2009 at 12:55 Comment(0)

Two solutions: One simple and one more advanced.

Introduction

There are two solutions available to you depending on the severity of your issue

Replace with your own values, as follows:

Replace XXXMYINDEXXXX with the name of an index.
Replace XXXMYTABLEXXX with the name of a table.
Replace XXXDATABASENAMEXXX with the name of a database.

Solution 1. Indexing

Rebuild all indexes for a table in offline mode

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD

Rebuild one specified index for a table in offline mode

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD

Solution 2. Fragmentation

Fragmentation is an issue in tables that regularly have entries both added and removed.

Check fragmentation percentage

SELECT  
    ips.[index_id] ,
    idx.[name] ,
    ips.[avg_fragmentation_in_percent]
FROM    
    sys.dm_db_index_physical_stats(DB_ID(N'XXXMYDATABASEXXX'), OBJECT_ID(N'XXXMYTABLEXXX'), NULL, NULL, NULL) AS [ips]
    INNER JOIN sys.indexes AS [idx] ON [ips].[object_id] = [idx].[object_id] AND [ips].[index_id] = [idx].[index_id]

Fragmentation 5..30%

If the fragmentation value is greater than 5%, but less than 30% then it is worth reorganising indexes.

Reorganise all indexes for a table

ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE

Reorganise one specified index for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE

Fragmentation 30%+

If the fragmentation value is 30% or greater then it is worth rebuilding then indexes in online mode.

Rebuild all indexes in online mode for a table

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Rebuild one specified index in online mode for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Prewar answered 6/7, 2009 at 12:55 Comment(2)

It appears that there is no WITH (ONLINE = ON) for the REORGANIZE method – Louettalough 17/8, 2018 at 15:0

REORGANIZE is always ONLINE. REBUILD has ONLINE option only available in Enterprise Edition – Stricken 2/7, 2020 at 13:3

Query for REBUILD/REORGANIZE Indexes

30%> Rebuild
5%=> Reorganize
5%< do nothing

Query:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + QUOTENAME(ind.name)  + ' ON ' +  QUOTENAME(OBJECT_SCHEMA_NAME( ind.OBJECT_ID)) + '.' +QUOTENAME(object_name(ind.object_id)) + 
CASE    WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' 
        WHEN indexstats.avg_fragmentation_in_percent>=5 THEN ' REORGANIZE '
        ELSE NULL END as [SQLQuery]  -- if <5 not required, so no query needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id 
    AND ind.index_id = indexstats.index_id 
WHERE 
--indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent 
ind.Name is not null 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Output

TableName      IndexName            IndexType              avg_fragmentation_in_percent SQLQuery
--------------------------------------------------------------------------------------- ------------------------------------------------------
Table1         PK_Table1            CLUSTERED INDEX        75                           ALTER INDEX [PK_Table1] ON [Table1] REBUILD 
Table1         IX_Table1_col1_col2  NONCLUSTERED INDEX     66,6666666666667             ALTER INDEX [IX_Table1_col1_col2] ON [Table1] REBUILD 
Table2         IX_Table2_           NONCLUSTERED INDEX     10                           ALTER INDEX [IX_Table2_] ON [Table2] REORGANIZE
Table2         IX_Table2_           NONCLUSTERED INDEX     3                            NULL

Gaptoothed answered 6/7, 2009 at 12:55 Comment(0)

Here is the modified script which i took from http://www.foliotek.com/devblog/sql-server-optimization-with-index-rebuilding which i found useful to post here. Although it uses a cursor and i know what is the main problem with cursors it can be easily converted to a cursor-less version.

It is well-documented and you can easily read through it and modify to your needs.

  IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL 
        DROP TABLE tempdb..#work_to_do

BEGIN TRY
--BEGIN TRAN

use yourdbname

-- Ensure a USE  statement has been executed first.

    SET NOCOUNT ON;

    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 @pagecount INT;
    DECLARE @command NVARCHAR(4000);

    DECLARE @page_count_minimum SMALLINT
    SET @page_count_minimum = 50

    DECLARE @fragmentation_minimum FLOAT
    SET @fragmentation_minimum = 30.0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

    SELECT  object_id AS objectid ,
            index_id AS indexid ,
            partition_number AS partitionnum ,
            avg_fragmentation_in_percent AS frag ,
            page_count AS page_count
    INTO    #work_to_do
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
                                           'LIMITED')
    WHERE   avg_fragmentation_in_percent > @fragmentation_minimum
            AND index_id > 0
            AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1
BEGIN
 PRINT 'partitions CURSOR DELETED' ;
    CLOSE partitions
    DEALLOCATE partitions
END
-- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR LOCAL
    FOR
        SELECT  *
        FROM    #work_to_do;

-- Open the cursor.
    OPEN partitions;

-- Loop through the partitions.
    WHILE ( 1 = 1 )
        BEGIN;
            FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

            IF @@FETCH_STATUS < 0
                BREAK;

            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;

            SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                + @schemaname + N'.' + @objectname + N' REBUILD';

            IF @partitioncount > 1
                SET @command = @command + N' PARTITION='
                    + CAST(@partitionnum AS NVARCHAR(10));

            EXEC (@command);
            --print (@command); //uncomment for testing

            PRINT N'Rebuilding index ' + @indexname + ' on table '
                + @objectname;
            PRINT N'  Fragmentation: ' + CAST(@frag AS VARCHAR(15));
            PRINT N'  Page Count:    ' + CAST(@pagecount AS VARCHAR(15));
            PRINT N' ';
        END;

-- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

-- Drop the temporary table.
    DROP TABLE #work_to_do;
--COMMIT TRAN

END TRY
BEGIN CATCH
--ROLLBACK TRAN
    PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH

Creationism answered 6/7, 2009 at 12:55 Comment(1)

Just saw this... Thanks for the affirmation! – Queridas 7/12, 2020 at 14:49

The real answer, in 2016 and 2017, is: Use Ola Hallengren's scripts:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

That is all any of us need to know or bother with, at this point in our mutual evolution.

Delrosario answered 6/7, 2009 at 12:55 Comment(0)

I have found the following script is very good at maintaining indexes, you can have this scheduled to run nightly or whatever other timeframe you wish.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Concertina answered 6/7, 2009 at 12:55 Comment(0)

Introduction

Solution 1. Indexing

Solution 2. Fragmentation

Fragmentation 5..30%

Fragmentation 30%+

Recommended topics

Hot tags