What is the way to fix indexes in SQL server?

Question

I need to defrag tables in my sql server, I have used the following 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 got 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 defragmentation and no pages were deleted? 

Please tell me how can I fix this?

Answer 1

Please try below code, it 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

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.