Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
5  
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
2  
You'll want to get intimately acquainted with sys.dm_db_index_usage_stats. –  Jon Seigel Sep 12 '13 at 21:20
add comment

1 Answer

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

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
      ,dec.cacheobjtype, des.dbid, des.text     
      ,deq.query_plan
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
WHERE
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: http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries

share|improve this answer
add comment

Your Answer

 
discard

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.