Tuesday, April 5, 2011

Missing Indexes

Have gathered some info about missing indexes.

there are limitations like the below:

1) it doesn't specify order for the columns to be created if you want to create the missing index
2) it can't gather statistics for more than 500 missing index groups
3) doesn't give accurate statistics for inequality predicates
eg. for queries such as this
select * from table1 where col1>33

Now here is how to analyze the missing index DMVs and create indexes for those.

Gather the missing indexes using the query:

select * from sys.dm_db_missing_index_details where database_id=5 and object_id=1779185784

gather the missing index index_handle

select * from sys.dm_db_missing_index_groups where index_handle in
(
select index_handle from sys.dm_db_missing_index_details where database_id=5 and object_id=1779185784
)

you can see the columns in the first query but can get for each index handle group using the below query

select * from sys.dm_db_missing_index_columns(pass the index_handle)

create indexes based on the missing indexes like this

first mention the indexes under "equality" predicate, then under "inequality" and then finally include the columns under "include" category.

No comments:

Post a Comment