Check INDEX Status and REBUILD Index, SQL Server

When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.

In my example, I will show how to get the fragmented indexes which have above 20% fragmentation level.

INDEX STATUS check

------------------------------------------

SELECT dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count      

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

------------------------------------------

Now using below SQL, I will create the REBUILD command to Defragment the infected indexes...

SQL to generate Rebuild Command

------------------------------------------

SELECT 

'ALTER INDEX '+dbindexes.[name]+' on '+dbschemas.[name]+'.'+ dbtables.[name]+' REBUILD;',

dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

------------------------------------------