How to decide on Index requirement ? – Use of Dynamic Management Views


To find out the existing indexes, Give the command sp_helpindex

Once a user query is run engine will look for relevant indexes .

If we query the DMV sys.dm_db_missing_index_details , we will be able to get the details of the missing index. This query will return equality_columns, inequality_columns, included_columns and statement.

equality_columns – This will be the columns used in the where condition with an equal operator

inequality_columns – This will give the columns used in the where condition with a not equal to (<>)operator. Also this will include all the columns used with a non equal to operator (eg. <)

Included_columns – This is the columns that needed to put in ‘include’ part of the index, That would improve query performance very much

Statement is the table in which query was executed.

This is a DMV that will give another set of information – sys.dm_db_missing_index_group_stats

The query will return unique_compiles, user_seeks, last_user_seek, avg_total_user_cost and avg_user_impact

unique_compiles- No of compiles taken for the user seek, 1 means only one compilation after the sql server started even if there was many number of user queries

user_seeks – No of user queries after SQL Server start

last_user_seek – Last query date and time

avg_total_user_cost – self explanatory

avg_user_impact – Teh percentage value will say, the amount of improvement you can get if we create index on it

sys.dm_db_missing_index_groups is another DMV which is explained below

This will give index_group_handle and index_handle. Usage is explained in an example later

sys.dm_db_missing_index_columns

This DMV will give the column name and column usage(based on operator used)

SAMPLE QUERY FOR FINDING INDEX REQUIREMENT

SELECT avg_user_impact AS avg_imprvmnt_perc, avg_total_user_cost AS avg_cost_of_query_before_index, ‘CREATE INDEX ix_’ + [statement] + ISNULL(equality_columns, ‘_’) + ISNULL(inequality_columns, ‘_’) + ‘ ON ‘ + [statement] + ‘ (‘ + ISNULL(equality_columns, ‘ ‘) + ISNULL(inequality_columns, ‘ ‘) + ‘)’ + ISNULL(‘ INCLUDE (‘ + included_columns + ‘)’, ”) AS missing_index_cmd FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c ON b.index_group_handle

LIMITATIONS OF DMV

A DMV can store information from a maximum of 500 missing indexes.
Unable to provide recommendations for clustered, indexed views and partitioning.
Once the SQL Server is restarted all the information related to missing indexes is lost. To keep the information for later use, the DBA needs to backup all the data available within all the missing index DMV prior to the restart of SQL Server

– Ref :SQL Server Performance

Advertisements

Comment please...

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