Database Reindexing (MSSQL)

There are a number of ways to reindex a database. This is my preferred method.

First, execute the below to check the fragmentation level of the active database:

declare @fragmentation float

set @fragmentation = 20

SELECT 
    S.name as [Schema],
    T.name as [Table],
    I.name as [Index],
    DDIPS.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN 
    sys.tables T with (nolock) 
on 
(
    T.object_id = DDIPS.object_id
)
INNER JOIN 
    sys.schemas S with (nolock) 
on 
(
    T.schema_id = S.schema_id
)
INNER JOIN 
    sys.indexes I with (nolock) 
ON 
(
    I.object_id = DDIPS.object_id AND 
    DDIPS.index_id = I.index_id
)
WHERE 
    DDIPS.database_id = DB_ID() and 
    I.name is not null AND 
    DDIPS.avg_fragmentation_in_percent > @fragmentation
ORDER BY 
    DDIPS.avg_fragmentation_in_percent desc

Run the database reindex by executing the following:

declare @fragmentation float

set @fragmentation = 20

declare @schema_name sysname,
        @table_name sysname,
        @index_name sysname

DECLARE cursor_tables 
    CURSOR FOR 
        SELECT 
            S.name as 'Schema',
            T.name as 'Table',
            I.name as 'Index'
        FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
        INNER JOIN sys.tables T with (nolock) on (T.object_id = DDIPS.object_id)
        INNER JOIN sys.schemas S with (nolock) on (T.schema_id = S.schema_id)
        INNER JOIN sys.indexes I with (nolock) ON (I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id)
        WHERE 
            DDIPS.database_id = DB_ID() and 
            I.name is not null AND 
            DDIPS.avg_fragmentation_in_percent > @fragmentation
        ORDER BY 
            DDIPS.avg_fragmentation_in_percent desc

open cursor_tables

fetch next from cursor_tables
into @schema_name, @table_name, @index_name

while @@fetch_status = 0
begin
    exec
    (
        '
            ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
            REBUILD WITH(FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)
        '
    )

    exec
    (
        '
            ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
            REORGANIZE
        '
    )

    fetch next from cursor_tables
    into @schema_name, @table_name, @index_name
end

close cursor_tables
deallocate cursor_tables

And finally, update statistics by running this

EXEC sp_updatestats

Article created mostly for personal reference; there are of course a few different ways in which these scripts could be improved, but when providing them to clients the simplest solution is often the best.

Blog

Database Reindexing (MSSQL)

Michael Kennedy

Join Our Newsletter. No Spam, Only the good stuff.

Related