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!
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!
Posts
4 comments:
wat happened to ur blog enteries sir????
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
looks like he got the position from the guy above..
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!
Post a Comment