04 July 2012

Percentage of fragmented indexes

by Stuart Ainsworth

This is a general indicator of performance problems. Index fragmentation can affect the rate of return for moderate to large tables. If multiple indexes are fragmented throughout the database, then an index maintenance strategy needs to be evaluated.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

Explanation: This is a general indicator of performance problems. Index fragmentation can affect the rate of return for moderate to large tables. If multiple indexes are fragmented throughout the database, then an index maintenance strategy needs to be evaluated.

Guideline values:The ideal value for this metric is less than 5%; this metric counts the number of moderate to large indexes (page values > 100) with fragmentation levels greater than 5%, and then divides that number by the number of indexes in the database. This percentage may increase throughout the day as data is loaded into your database, but it will shrink when indexes are rebuilt or reorganized. This metric does not identify specific performance problems with a specific index, but should be used to obtain a general idea of how well your index maintenance strategy addresses overall problems.

Possible solutions: If this value indicates an increased amount of index fragmentation throughout your database, you may need to fine-tune your index maintenance plans. Indexes may need to be redesigned or rebuilt more frequently.

More information:

sys.dm_db_index_physical_stats (Transact-SQL)

Reorganize and Rebuild Indexes

Metric definition

Name

Percentage of fragmented indexes

Description

Guideline values: The ideal value for this metric is less than 5%; this metric counts the number of moderate to large indexes (page values > 100) with fragmentation levels greater than 5%, and then divides that number by the number of indexes in the database. This percentage may increase throughout the day as data is loaded into your database, but it will shrink when indexes are rebuilt or reorganized. This metric does not identify specific performance problems with a specific index, but should be used to obtain a general idea of how well your index maintenance strategy addresses overall problems.

More information:

sys.dm_db_index_physical_stats (Transact-SQL)

Reorganize and Rebuild Indexes

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			DECLARE @frag DECIMAL(10, 2) ,
    @tot INT;
  
SELECT  @frag = COUNT(*)
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL,
                                       NULL, 'LIMITED') ps
WHERE   ps.avg_fragmentation_in_percent >= 5
        AND ps.page_count > 100
        AND OBJECTPROPERTY(ps.[object_id], 'IsUserTable') = 1;
 
SELECT  @tot = COUNT(*)
FROM    sys.indexes i
WHERE   OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1;
 
SELECT  @tot = CASE WHEN @tot = 0 THEN 1
                    ELSE @tot
               END;
 
SELECT  CAST(( @frag / @tot ) AS DECIMAL(10, 2)) * 100;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
				
						DECLARE @frag DECIMAL(10, 2) ,
    @tot INT;
  
SELECT  @frag = COUNT(*)
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL,
                                       NULL, 'LIMITED') ps
WHERE   ps.avg_fragmentation_in_percent >= 5
        AND ps.page_count > 100
        AND OBJECTPROPERTY(ps.[object_id], 'IsUserTable') = 1;
 
SELECT  @tot = COUNT(*)
FROM    sys.indexes i
WHERE   OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1;
 
SELECT  @tot = CASE WHEN @tot = 0 THEN 1
                    ELSE @tot
               END;
 
SELECT  CAST(( @frag / @tot ) AS DECIMAL(10, 2)) * 100;

Instances to collect from

Select all

Databases to collect from

Collection frequency

86400

Note: Excessively large databases may not return data within an acceptable time frame, so you may need to adjust the collection frequency of this metric from 1 minute to 5 or 10 minutes.

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Increased % of fragmented indexes

Description

Explanation: This alert is raised when index fragmentation goes above the threshold specified. This is a general indicator of performance problems. Index fragmentation can affect the rate of return for moderate to large tables. If multiple indexes are fragmented throughout the database, then an index maintenance strategy needs to be evaluated.

The ideal value for the metric on which this alert is based is less than 5%; the metric counts the number of moderate to large indexes (page values > 100) with fragmentation levels greater than 5%, and then divides that number by the number of indexes in the database. This percentage may increase throughout the day as data is loaded into your database, but it will shrink when indexes are rebuilt or reorganized. This metric does not identify specific performance problems with a specific index, but should be used to obtain a general idea of how well your index maintenance strategy addresses overall problems.

More information:

sys.dm_db_index_physical_stats (Transact-SQL)

Reorganize and Rebuild Indexes

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	15
Medium:	10
Low:	5

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

27,250 Rate

Click to rate this post!

[Total: 3 Average: 5]

Percentage of fragmented indexes

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics