February 10, 2010 at 2:55 am
hi,
i have executed the missing indexes DMV Query and it output as
columnname column_usage
Status_IndEQUALITY
Foramted_CityEQUALITY
State_IDEQUALITY
City_IDINCLUDE
how can i use this information to create indexes?
plz help me.
🙂
February 10, 2010 at 5:26 am
This means that the server had a query and that query could have benefited from an index that is based on the columns Status_ind, , Formated_City and State_ID. It also recommends that you’ll add the column City_ID as an included column (most chances are that this would make it a covering index). This doesn’t mean that you should automatically create such an index. You should take into consideration other factors such as how many times the query that would benefit from this index ran? How much would the query will be improved? How many data modifications do you have on this table? Can a narrower index (index that is based only on some of those columns) be sufficient?
If you decide to create an index that is based on the server recommendation remember that the order of the columns in the recommendation is meaningless and that you should decide of the columns’ order according to there selectivity.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 10, 2010 at 7:10 am
My number one reccommendation is to make sure the index doesn't already exist (almost).
many times I end up adding or including 1 column to an existing index to cover whatever query generates the missing index counter.
In the MS Press MCITP books they had a very clever index advantage that would show you how valuable the index would be for select statements. It helps to weigh the impact and compare relative impacts for all the missing indexes.
user_seeks * avg_total_user_cost * (avg_user_impact *0.01) as 'index advantage'
at 10-15,000, an index should be considered.
It works pretty well for me, but I also consider all the same things that Adi mentions about the impact.
Oh, and I calculate the space the new index or index column would consume on large tables.
Cheers!!
Craig Outcalt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply