AnsweredAssumed Answered

How desfragment tables in CAUIM

Question asked by Miller2012 on Oct 3, 2018
Latest reply on Oct 26, 2018 by Miller2012

Hi everyone.


I am checking my db CAUIM mssql. I am not expert in admin db or in query construction mssql, but I know that with this query i search tables with high level of fragmentation. The rule said that (for key USM tables fragmentation should be less than 30%)


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
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


What query do you recommended to lower the tables with high fragmentation?


Thank for your help.