Home > Functions and DMVs, Joins, SQL Queries, SQL Server, T-SQL > Identify the columns on which the indexes needs to be created / missing indexes in a table.

Identify the columns on which the indexes needs to be created / missing indexes in a table.

Most often identifying the columns for which the indexes needs to be created is a difficult job. Often we land up creating indexes on columns that may not be used very often. Even worse is not creating indexes on the columns that may be used very often. sometimes it becomes difficult to identify the queries that run the most and the columns on which it runs. I normally use a query as below to get the list of columns for which indexes are missing.

SELECT DET.*, ST.*
FROM    SYS.DM_DB_MISSING_INDEX_GROUP_STATS ST
INNER JOIN
SYS.DM_DB_MISSING_INDEX_GROUPS GRP
ON GRP.INDEX_GROUP_HANDLE = ST.GROUP_HANDLE
INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS DET  ON DET.INDEX_HANDLE = GRP.INDEX_HANDLE
WHERE DATABASE_ID = DB_ID(‘MYDB’)

You can use the columns mentioned in “Equality_columns, included_columns” to build the indexes. I advice you to go with the values in “AVG_USER_IMPACT ,USER_SEEKS, AVG_TOTAL_USER_COST” to take the decision on whether to create index or not on a particular column. It is also not advisable to create index on each and every column.

Hope this was of some help to you. Let me know if the post was useful or not and also if you have something similar :).

Advertisements
  1. No comments yet.
  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: