Wednesday, July 18, 2007

Finding missing indexes in SQL Server 2005

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables.

Query: select * from sys.dm_db_missing_index_details
Fields to note: equality_columns, inequality_columns, included_columns, statement

So by querying this DMV and having a look at these 4 columns we can decide on the types of indexes which needs to be created for our tables. Each of this column has a meaning. Either find it yourself :) or drop a line in the comment section.

One important point to keep in mind is the result you see out of running the above DMV is just a 'suggestion' on the way to improve the performance of your query. But its not 100% guaranteed that on creating all these indexes would help.

We cannot access the DMV using accounts which doesn't have permission for 'View Server State'. I would write more on these in upcoming posts.

Hope this helps!

4 comments:

Sergio said...

Hello Vadivel, my name is Sergio Alvarez, I'm a contract recruiter currently engaged with TV Guide in on of their searches. I am trying to reach you in order to talk with you about an opening within the company. I'm sorry that I have to contact you in this way, but I've tried to reach you everywhere, but it seems that I have an old copy of your resume.

You can reply at sergio.alvarez|at| avature.net so we can discuss this opening with you and see if it matches what you consider to be a solid next step in your career.

Thank you Vadivel,
looking forward to your reply

Anonymous said...

wat happened to ur blog enteries sir????

Anonymous said...

looks like he got the position from the guy above..

Vadivel said...

lol no i haven't changed my job :) I was busy with something but since 2 weeks back I have started blogging. Sorry for the delay!