Fixing SCOM DB Fragmentation issue

Posted by Shashi Bhushan on Aug 29, 2016 in SCOM 2012, SQL

Sometimes, when I am using a development or test environment and I run into a situation where SCOM is taking more time to generate the report, SCOM console slowness or the queries in SQL is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”.

Fragmentation is an element that has been critical to SQL Server performance for many years. As data is modified in a database, the database and its indexes become fragmented. As indexes become fragmented, ordered data retrieval becomes less efficient and reduces database performance.

Here is a simple query that will list every index on every table in your 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.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Output will be like below:

However in our scenario we are not bothered for the fragmented indexes having less than 30% fragmentation and page count less than 1000.

So, below query will list the fragmented indexes having >30% fragmentation with page count > 1000.

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

Now we can see only those tables for which we need to rebuild the index.

Below is the query to manually rebuild the index for fragmented table.

You need to run this query for each table.

Alter Index “INDEX NAME”
ON “TABLE NAME”
REBUILD;
GO

EXAMPLE:

Alter Index idx_PerformanceData_14_PerformanceSourceInternalId
ON PerformanceData_14
REBUILD;
GO

Once the index rebuild is done for all the tables, run below query again to check the fragmentation status:

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

Now we can see we don’t have any fragmented index tables having fragmentation > 30% with page count > 1000.

After index rebuild, the DB performance should increase and should eliminate the issues like delay in report generation, slow SCOM console, etc..

We can create a job in SQL to check and resolve the fragmentation periodically /weekly. I will cover that topic in my next post.

Also, I have explained how we can monitor and send an alert for index fragmentation through SCOM in below post:

Monitor SQL Server Index Fragmentation in SCOM