Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 238

This information resides in the sys.dm_db_missing_index_group_stats DMV.
After finding out the possible improvement, it is better to see which table, which columns
need which index. Therefore, even the suggested query has been given in the field
Create_Index_Syntax , by using the following DMVs:
F sys.dm_db_missing_index_group_stats
F sys.dm_db_missing_index_details
These two DMVs contain all information regarding the column(s) of the table, that need an
index, so a CREATE INDEX syntax would be created from these two DMVs. But, to get the
relationship between these two DMVs, the sys.dm_db_missing_index_groups DMV is
the only way, as it contains Index_Group_Handle , which makes join possible with sys.
dm_db_missing_index_group_stats , and the Index_Handle field, which makes join
possible with sys.dm_db_missing_index_details .
The list of indexes created by the preceding DMVs is just a piece of advice regarding which
indexes are missing and need to be created. Finally it's up to you, based on your requirement,
whether to create the index or not. You need to check whether the table name and column
has any selectivity, and then decide whether or not to create it. More indexes on a table might
improve the performance of your SELECT statement, but it will harm other DML statements.
So, it is always advisable to make the decision manually rather than leaving everything
to DMVs.
There's more...
These DMVs can keep information for a maximum of 500 indexes, and the information is lost
whenever SQL Services restarts. Once again, this list of 500 missing indexes is a suggestion
only; apply your expertise to find whether it is really worth creating the index or not.
How to find unused indexes
By this time, it is crystal clear that an index can boost up performance, but it comes at a price.
Indexes need space in your desk to accommodate their own B-Tree and get updated each time
a DML statement gets executed, so it is a good idea to check for any unused indexes in every
business cycle.
Getting ready
Before executing the query to find the unused index, remember that we are going to use a
sys.dm_db_index_usage_stats dynamic management view that removes all the data
at every restart of a SQL Server instance and starts collecting data from scratch again.