Finding stored procedures with missing indexes

Question

How to scan for stored proc that need indexes to be created.

Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script or app that can scan to indicate if an index needs to be created?

Answer 1

Having not thought a whole lot about this, and answering just the specific question asked, here is a way to identify all the stored procedures that reference tables that have at some point been identified as candidates for additional indexes. Unfortunately this is a very loose relationship ... I don't believe there is any straightforward way to say "this stored procedure queried this table and could have used this suggested index" unless you started parsing the sql text and fuzzy matching against the columns etc. recommended by the missing index DMVs. This is a start toward that process. That said, I kind of agree with Henry, there are plenty of more direct ways to analyze and address performance problems in stored procedures you have identified. Examining the execution plan, for example, should provide some clues much more intuitively than this round-about method you want to pursue. In any case, here is a query to start you off:

SELECT 
  [Procedure] = QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) 
        + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])), 
  [Table]     = QUOTENAME(r.referenced_schema_name) 
        + '.' + QUOTENAME(r.referenced_entity_name),
  i.equality_columns, 
  i.inequality_columns,
  i.included_columns,
  s.execution_count,
  avg_time = s.total_elapsed_time*1.0/s.execution_count
FROM sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_sql_referenced_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
  + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])), 'OBJECT'
) AS r
INNER JOIN sys.dm_db_missing_index_details AS i
ON i.[object_id] = r.referenced_id
WHERE r.referenced_minor_id = 0;

Note: the DMVs usually only have data since the last restart.

Answer 2

Beginning with SS 2005, there is a DMV called sys.dm_db_missing_index_details. Google that and you'll find some useful scripts.

That said, you may need to look at the problem more holistically - is your hardware adequate (Perfmon counters will help you with this), what else is running at the same time, what is SQL primarily waiting on (Google the DMV sys.dm_os_wait_stats for some great scripts). And you can look at the execution plan of the specific stored procedure to find out where the bottleneck is.