3

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.

Jon Seigel
  • 16,922
  • 6
  • 45
  • 85
jcolebrand
  • 6,376
  • 4
  • 43
  • 67

1 Answers1

4

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

SQLRockstar
  • 6,355
  • 27
  • 48