SQL Server 2008 (SSQA.net)

The best option is to run a resource intensive or typical workload on SQL Server by accessing volatile database, whereby it will be easy to retrieve information about missing indexes by querying the dynamic management objects listed below that are stored in the master database. By default the user needs VIEW SERVER STATE permission or any permission that implies the VIEW SERVER STATE permission to query these DMVs.

The first DMV used by feature is sys.dm_db_missing_index_group_stats TSQL and by default the information is updated by every query execution, not by every query compilation or recompilation. As the behaviour of optimizer is usage statistics are persisted or kept only until SQL Server services are restarted, so it is a responsibility of DBA to check it periodically make backup copies of the missing index information if they want to keep the usage statistics after server recycling. To put this in an example, here is what BOL refers:

--Find the 10 missing indexes with the highest anticipated improvement for user queries

SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

--To determin further on which missing indexes comprise a particular missing index group, and displays their column details. For the sake of this example, the missing index group handle is 24.

SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 24;

Next on the list to add is sys.dm_db_missing_index_groups  TSQL can be used when a query is optimized by the query optimizer, and as referred above this is not persisted. Further it is easy to determine which missing index groups a particular missing index is part of it, you can query the sys.dm_db_missing_index_groups dynamic management view by equijoining it with sys.dm_db_missing_index_details based on the index_handle column.

As referred on above about sys.dm_db_missing_index_details TSQL gets you detailed information about missing indexes, excluding spatial indexes. The next function to add these DMVs is sys.dm_db_missing_index_columns  TSQL,as it refers this gets you the database table columns that are missing an index, excluding spatial indexes. Again BOL clearly defines that the output from the sys.dm_db_missing_index_columns dynamic management function can be used by any tool that can read the missing index information that corresponds to an index_handle, process the information, and convert it into CREATE INDEX DDL statements that implement the missing index. To put that in example see below TSQL code:

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO
SELECT mig.*, statement AS table_name,
    column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;
GO

It simple easy to get information using DMV and functions of SQL Server master database, if the SQL Server services are restarted frequently then make sure you put a process in practice to store these details in a table for historic purpose as the missing indexes feature consists of the following components:

• A set of dynamic management objects that can be queried to return information about missing indexes.
• The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing. 

In next one I will cover on how quickly you can obtain this information using SQL Server Management Studio and its limitations, keep watching this space!