Indexes with excessive writes

This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

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

This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database.

If indexes are being updated with new data more often than they are being used in query plans, they can cause performance issues during write-heavy operations (INSERT, UPDATE, and DELETE statements), while adding little or no benefit to read operations.

For more information, see General Index Design Guidelines.

Indexes with excessive writes, 3.9 out of 5 based on 7 ratings

Metric definition

Name

Indexes with excessive writes

Description

This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database.

If indexes are being updated with new data more often than they are being used in query plans, they can cause performance issues during write-heavy operations (INSERT, UPDATE, and DELETE statements), while adding little or no benefit to read operations.

Guideline values: The ideal value for this metric should be 0, but not every bad index should be dropped. There may be some indexes which are used for occasional (but important) queries. This metric should be used to provide insight into the overall indexing strategy.

Possible solutions: If this value indicates the need for further investigation, the following query can help identify indexes that may be candidates for adjustment or elimination:

SELECT  OBJECT_NAME(s.object_id), i.name, i.type_desc

FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )

JOIN sys.indexes i WITH (NOLOCK) ON s.index_id = i.index_id

AND s.object_id = i.object_id

WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )

AND s.index_id > 1

More information:

General Index Design Guidelines

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

60

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

High Write Indexes > 0

Description

This alert is raised when the number of writes in a database index exceeds the number of reads. The following query can help identify indexes that may be candidates for adjustment or elimination:

SELECT  OBJECT_NAME(s.object_id), i.name, i.type_desc

FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )

JOIN sys.indexes i WITH (NOLOCK) ON s.index_id = i.index_id

AND s.object_id = i.object_id

WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )

AND s.index_id > 1

More information:

General Index Design Guidelines

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:
Medium:5
Low:1

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled