SCOM DB Fragmentation Issue

Sometimes SCOM environments slowness is occurring because of SQL fragmented indexes.

Fragmentation happens when the logical order of pages in an index does not match the physical order in the data file. Because fragmentation can affect the performance of some queries, you need to monitor the fragmentation level of your indexes and, if required, perform re-organize or rebuild operations on them.

When handling SCOM DB, we are interested only in fragmented indexes with more than 30% fragmentation and a page count with more than 1000

Here is a query that will list indexes on every table in the database, ordered by percentage of index fragmentation.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
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.avg_fragmentation_in_percent > 30 and page_count > 1000
ORDER BY indexstats.avg_fragmentation_in_percent desc

The output will be:

You need to run this query for each table:

Alter index "INDEX NAME"
ON "TABLE NAME"
REBUILD;
GO

In my case from the example above:

Alter Index idx_PerformanceData_14_PerformanceSourceInternalId
 ON PerformanceData_14
 REBUILD;
 GO

After running a rebuild for all of the indexes and tables, run the first query again to check the fragmentation status.

After not having any fragmented index in your environment the DB performance should increase, and you would see fewer delays in console reaction and reports generation.