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 responses to “Identifying Columns to Create Index via T-SQL

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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server

Coding Tips

We will improve your knowledge

SQL Studies

My SQL Studies

It's Hemlock Time

We don't make sense. It's innate.

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Microsoft BI consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: