Home > Functions and DMVs, Joins, SQL Queries, SQL Server, T-SQL > Calculating the value of proposed indexes and analysing the impact.

Calculating the value of proposed indexes and analysing the impact.

September 24, 2009 Leave a comment Go to comments

Take this as a continuation of the post for identifying columns on which indexes are missing

The most difficult decision to make is which of the indexes proposed by the sys.dm_db_missing_index can provide the most benefit. Applying some basic calculations/ numerical comparison based on the records retrieved can help you acheive this task. The following examples shows a sample code you can use :

SELECT USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) AS IMPACT,*
FROM
 SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS GRPSTATS  WITH(NOLOCK)
INNER JOIN 
SYS.DM_DB_MISSING_INDEX_GROUPS  AS GROUPS WITH(NOLOCK)
ON GRPSTATS.GROUP_HANDLE = GROUPS.INDEX_GROUP_HANDLE
INNER JOIN 
SYS.DM_DB_MISSING_INDEX_DETAILS  AS  DETAILS WITH(NOLOCK)
ON GROUPS.INDEX_HANDLE = DETAILS.INDEX_HANDLE
ORDER BY 1

On operational systems, values above 5,000 indicate indexes that should be evaluated for creation.When the value passes 10,000, you generally have an index that can provide a significant performance improvement for read operations.

This code is only for read activity,you may have to redesign it for other maintenance operations as well.

Advertisements
  1. Trevor
    October 4, 2012 at 7:11 pm

    What if your impact is 108776448.398057. You think I need to look at that? ; )

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: