Index rebuild script

Below is a cursor that generates scripts to rebuild indexes. The script checks for the average fragmentation percentage value and the page count and also checks for the tables with primary keys. Any index that has the avg fragmentation percentage value greater than 25  and if the page count is greater than 1000 and if it is not a heap will be scripted out. Here we have an option to directly execute the script to rebuild the indexes or have it generate the scripts for us to execute. The fillfactor the script uses is 80. This can be modified per the user’s requirement.

DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_NAME(DPS.OBJECT_ID) AS Table_Name,SI.NAME AS IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS
INNER JOIN sysindexes SI
 ON DPS.OBJECT_ID = SI.ID
 AND DPS.INDEX_ID = SI.INDID where DPS.avg_fragmentation_in_percent>25.0 and index_type_desc<>'HEAP' and page_count>1000
ORDER BY DPS.avg_fragmentation_in_percent DESC
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX '+@IndexName+' ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR
(3),@fillfactor) + ')'
print (@sql)
--Exec (@sql) -- uncomment this to perform the index rebuild and comment the above line which is print(@sql)
FETCH NEXT FROM TableCursor INTO @TableName, @IndexName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Hope this helps

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , , , , , , , . Bookmark the permalink.

One Response to Index rebuild script

  1. SELECT ‘ CREATE ‘ +
    CASE
    WHEN I.is_unique = 1 THEN ‘ UNIQUE ‘
    ELSE ”
    END +
    I.type_desc COLLATE DATABASE_DEFAULT + ‘ INDEX ‘ +
    I.name + ‘ ON ‘ +
    SCHEMA_NAME(T.schema_id) + ‘.’ + T.name + ‘ ( ‘ +
    KeyColumns + ‘ ) ‘ +
    ISNULL(‘ INCLUDE (‘ + IncludedColumns + ‘ ) ‘, ”) +
    ISNULL(‘ WHERE ‘ + I.filter_definition, ”) + ‘ WITH ( ‘ +
    CASE
    WHEN I.is_padded = 1 THEN ‘ PAD_INDEX = ON ‘
    ELSE ‘ PAD_INDEX = OFF ‘
    END + ‘,’ +
    ‘FILLFACTOR = ‘ + CONVERT(
    CHAR(5),
    CASE
    WHEN I.fill_factor = 0 THEN 100
    ELSE I.fill_factor
    END
    ) + ‘,’ +
    — default value
    ‘SORT_IN_TEMPDB = OFF ‘ + ‘,’ +
    CASE
    WHEN I.ignore_dup_key = 1 THEN ‘ IGNORE_DUP_KEY = ON ‘
    ELSE ‘ IGNORE_DUP_KEY = OFF ‘
    END + ‘,’ +
    CASE
    WHEN ST.no_recompute = 0 THEN ‘ STATISTICS_NORECOMPUTE = OFF ‘
    ELSE ‘ STATISTICS_NORECOMPUTE = ON ‘
    END + ‘,’ +
    ‘ ONLINE = OFF ‘ + ‘,’ +
    CASE
    WHEN I.allow_row_locks = 1 THEN ‘ ALLOW_ROW_LOCKS = ON ‘
    ELSE ‘ ALLOW_ROW_LOCKS = OFF ‘
    END + ‘,’ +
    CASE
    WHEN I.allow_page_locks = 1 THEN ‘ ALLOW_PAGE_LOCKS = ON ‘
    ELSE ‘ ALLOW_PAGE_LOCKS = OFF ‘
    END + ‘ ) ON [‘ +
    DS.name + ‘ ] ‘ + CHAR(13) + CHAR(10) + ‘ GO’ [CreateIndexScript]
    FROM sys.indexes I
    JOIN sys.tables T
    ON T.object_id = I.object_id
    JOIN sys.sysindexes SI
    ON I.object_id = SI.id
    AND I.index_id = SI.indid
    JOIN (
    SELECT *
    FROM (
    SELECT IC2.object_id,
    IC2.index_id,
    STUFF(
    (
    SELECT ‘ , ‘ + C.name + CASE
    WHEN MAX(CONVERT(INT, IC1.is_descending_key))
    = 1 THEN
    ‘ DESC ‘
    ELSE
    ‘ ASC ‘
    END
    FROM sys.index_columns IC1
    JOIN sys.columns C
    ON C.object_id = IC1.object_id
    AND C.column_id = IC1.column_id
    AND IC1.is_included_column =
    0
    WHERE IC1.object_id = IC2.object_id
    AND IC1.index_id = IC2.index_id
    GROUP BY
    IC1.object_id,
    C.name,
    index_id
    ORDER BY
    MAX(IC1.key_ordinal)
    FOR XML PATH(”)
    ),
    1,
    2,

    ) KeyColumns
    FROM sys.index_columns IC2
    –WHERE IC2.Object_id = object_id(‘Person.Address’) –Comment for all tables
    GROUP BY
    IC2.object_id,
    IC2.index_id
    ) tmp3
    )tmp4
    ON I.object_id = tmp4.object_id
    AND I.Index_id = tmp4.index_id
    JOIN sys.stats ST
    ON ST.object_id = I.object_id
    AND ST.stats_id = I.index_id
    JOIN sys.data_spaces DS
    ON I.data_space_id = DS.data_space_id
    JOIN sys.filegroups FG
    ON I.data_space_id = FG.data_space_id
    LEFT JOIN (
    SELECT *
    FROM (
    SELECT IC2.object_id,
    IC2.index_id,
    STUFF(
    (
    SELECT ‘ , ‘ + C.name
    FROM sys.index_columns IC1
    JOIN sys.columns C
    ON C.object_id = IC1.object_id
    AND C.column_id = IC1.column_id
    AND IC1.is_included_column =
    1
    WHERE IC1.object_id = IC2.object_id
    AND IC1.index_id = IC2.index_id
    GROUP BY
    IC1.object_id,
    C.name,
    index_id
    FOR XML PATH(”)
    ),
    1,
    2,

    ) IncludedColumns
    FROM sys.index_columns IC2
    –WHERE IC2.Object_id = object_id(‘Person.Address’) –Comment for all tables
    GROUP BY
    IC2.object_id,
    IC2.index_id
    ) tmp1
    WHERE IncludedColumns IS NOT NULL
    ) tmp2
    ON tmp2.object_id = I.object_id
    AND tmp2.index_id = I.index_id
    WHERE I.is_primary_key = 0
    AND I.is_unique_constraint = 0
    –AND I.Object_id = object_id(‘Person.Address’) –Comment for all tables
    –AND I.name = ‘IX_Address_PostalCode’ –comment for all indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s