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?