Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I monitor a production database to see if there are table scans happening? And what indexes might fix them...

I'm told that we have adequate indexes but I can't see every code path, and we have too many people touching the codebase (hey, lots of developers can sometimes be a good thing) so what's the most comprehensive way to see how if we've missed indexes based on our production load?

SQL Server 2008 R2, C#/ASP.NET code, NHibernate are the key factors in use.

share|improve this question
yes. there are table scans happening. – swasheck Sep 12 '13 at 19:45
haha, I see I forgot my other question ... doh! – jcolebrand Sep 12 '13 at 19:46
You'll want to get intimately acquainted with sys.dm_db_index_usage_stats. – Jon Seigel Sep 12 '13 at 21:20

If you are looking for missing indexes inside your plan cache, you can try this:

SELECT dec.usecounts, dec.refcounts, dec.objtype
      ,dec.cacheobjtype, des.dbid, des.text     
FROM sys.dm_exec_cached_plans AS dec
     CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
     CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

You can find more info in the plan cache as well, I've listed a few examples here:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.