AnsweredAssumed Answered

Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

Question asked by Miller2012 on Aug 15, 2018
Latest reply on Aug 17, 2018 by Issac08

Hi all.

 

I am not expert in db mssql but I trying checking the level of database fragmentation of CAUIM.

My db is Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - Standard Edition (64-bit).

 

When I execute this command

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

 

Return this tablas with fragmentation very high, according the documentation (UMP (USM) Slow Performance Guide and Troubleshooting Checklist 1.1) the tables fragmentation should be less than 30%.

 

 

I attached the file of all tables with fragmentation if your need see.

 

What can I do with this high fragmentation?

How does I reduce this fragmentation? because it feel certain slow when consulting data in the USM and maybe this is the problem.

 

M.E

Outcomes