SystemCenter

All about System Center Articles and Tutorials

Fixing SCOM DB Fragmentation issue

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

Fixing SCOM DB Fragmentation issue

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:

1

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.

2

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

3

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.

4

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

Submit a Comment

Your email address will not be published. Required fields are marked *