Identifying Columns to Create Index via T-SQL

To identify columns on which you need to create indexes for fine tuning query performances you can run the T-SQL statement below;

SELECT OBJECT_NAME(OBJECT_ID),
mid.included_columns,
mid.statement,
mic.column_name,
mid.equality_columns,
mid.inequality_columns,
mic.column_usage
FROM sys.dm_db_missing_index_details mid
CROSS apply sys.dm_db_missing_index_columns(mid.index_handle) mic
ORDER BY mid.index_handle

The DMV used above may have some limitations. It should not be used to fix your index configuration. I would suggest to make use of the Database Tuning Advisor for that one. Though it is a big help when you are fine tuning a query performance.

To learn more about the missing index DMV, visit http://technet.microsoft.com/en-us/library/ms345485(v=sql.105).aspx

 

 

About these ads

4 thoughts on “Identifying Columns to Create Index via T-SQL

  1. This is very fascinating, You are a very skilled blogger.

    I’ve joined your feed and look ahead to looking for extra of your excellent
    post. Additionally, I’ve shared your web site in my social networks

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