how to fix fragmented indexes in sql server?

Question

I want to defrag tables in sql server, I used this code to know the fragmented indexes:

sELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
 indexstats.avg_fragmentation_in_percent as Fragmentation, 
 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() ORDER BY    indexstats.avg_fragmentation_in_percent
 desc

after i have the results i used

 DBCC INDEXDEFRAG([MAIL SYSTEMS],EmpMail)

to defragmented index and then reuse the first code to show results it shows the same before deframentation and no pages was deleted? what's wrong with that and how can i fix indexes?

[`REBUILD/REORGANIZE`](https://technet.microsoft.com/en-us/library/ms188388%28v=sql.105%29.aspx)? — Lukasz Szozda, Dec 26 '15 at 10:58
`DBCC INDEXFRAG` is outdated. Better to use `ALTER INDEX` with `REBUILD` or `REORGANIZE` — SQL Police, Dec 26 '15 at 11:04

score 0 · Answer 1 · answered Dec 26 '15 at 13:16

Try this code, This will defragment your all indexes based on the fragmentation level.

SELECT CASE
         WHEN indexstats.avg_fragmentation_in_percent > 5
              AND indexstats.avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX [' + ind.NAME + '] ON ['
                                                                     + Object_name(ind.OBJECT_ID)
                                                                     + '] REORGANIZE ; '
         ELSE 'ALTER INDEX [' + ind.NAME + '] ON ['
              + Object_name(ind.OBJECT_ID) + '] REBUILD; '
       END,
       'GO'
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 > 10
       AND ind.NAME IS NOT NULL
ORDER  BY indexstats.avg_fragmentation_in_percent DESC

In a result of this query I get many sql commands (strings) which is only commands to copy and execute by ourself or it was already executed? — maniootek, Dec 23 '19 at 12:34
I tired it but after execution of these commands, base query still return same value as before. Should I update anything else? — maniootek, Jan 02 '20 at 21:23

score 0 · Answer 2 · answered Jul 17 '20 at 00:19

Here is a modified version of StackUser's script that directly executes the generated commands and additionally prints nice info messages about processed indexes and their fragmentation status:

DECLARE @sql VARCHAR(MAX) =
(
    SELECT CASE
        WHEN indexstats.avg_fragmentation_in_percent > 5
         AND indexstats.avg_fragmentation_in_percent <= 30
        THEN
            'PRINT ''Reorganizing index [' + ind.name + '] ON [' + OBJECT_NAME(ind.object_id) + '] with ' +
            CAST(indexstats.avg_fragmentation_in_percent AS VARCHAR(10)) + ' % fragmentation ...''; ' +
            'ALTER INDEX [' + ind.name + '] ON [' + OBJECT_NAME(ind.object_id) + '] REORGANIZE ; '
        ELSE
            'PRINT ''Rebuilding index [' + ind.name + '] ON [' + OBJECT_NAME(ind.object_id) + '] with ' +
            CAST(indexstats.avg_fragmentation_in_percent AS VARCHAR(10)) + ' % fragmentation ...''; ' +
            'ALTER INDEX [' + ind.name + '] ON [' + OBJECT_NAME(ind.object_id) + '] REBUILD; '
        END
    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 > 10 AND ind.name IS NOT NULL
    ORDER BY indexstats.avg_fragmentation_in_percent DESC
    FOR XML PATH('')
);
EXEC (@sql)

Also, there is a similar script from MSDN: https://learn.microsoft.com/de-de/sql/t-sql/database-console-commands/dbcc-indexdefrag-transact-sql?view=sql-server-ver15

how to fix fragmented indexes in sql server?

2 Answers2