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!
Comments
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