DEV Community

Michael Kennedy
Michael Kennedy

Posted on

2 1

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And finally, update statistics by running this

EXEC sp_updatestats
Enter fullscreen mode Exit fullscreen mode

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.

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

The discussion has been locked. New comments can't be added.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free