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 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?

share|improve this question
    
What version of SQL Server are you using? –  Aaron Bertrand Mar 10 '13 at 3:40
add comment

2 Answers

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.

share|improve this answer
add comment

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.

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.