Identify likely candidates for indexing

Everytime that a query is executed by SQL Server and the query optimizer believes that a certain index would improve the query plan it makes records this information in the query plan.  All query plan which exist in the cache are stored in the management view dm_exec_cached_plans.  

You can pull out the 100 most commonly used plans which have potentially missing indexes using the query below.  You'll need to click on the xml to bring up the graphical query plan where the missing index will appear.  

If for some reason you just see the xml when clicking the link save of the xml with the extension .queryplan and double click on it and you should see the graphical representation.

SELECT TOP 100

st.text,

cp.cacheobjtype,

cp.objtype,

DB_NAME(st.dbid)AS DbName,

cp.usecounts,

qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%<MissingIndexes>%'

ORDER BY cp.usecounts DESC