7

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?

  • 1
    What version of SQL Server are you using? – Aaron Bertrand Mar 10 '13 at 3:40
7

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.

4

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.

0

Augmenting @Aaron Bertrand's post by adding in general query index optimization (and auto-generating the DDL for the new indicies) as per @Rory's answer here: https://stackoverflow.com/a/26060183/4228193:

sp_updatestats
go
SELECT 
  CONVERT (decimal (28,1), (
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) + 
    migs.avg_total_system_cost * migs.avg_system_impact * (migs.system_seeks + migs.system_scans) +
    COALESCE(SPs.total_elapsed_time*1.0/ 
      290/ --based on 'SELECT total_elapsed_time/(DATEDIFF(second, start_time, GETDATE())+1.0) FROM sys.dm_exec_requests' for my personal install of SQL Server
      1000/ --otherwise seems closer to ms; total seconds of execution to get here
      425 -- the approximate relationship between avg_total_user_cost and the average of total_elapsed_time for a stored procedure on my system
    , 0) * (migs.avg_user_impact + migs.avg_system_impact)
  )) AS improvement_measure,
  COALESCE(QUOTENAME(DB_NAME(SPs.database_id))+'.','') +
    COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(SPs.[object_id]))+'.','') +
    COALESCE(QUOTENAME(OBJECT_NAME(SPs.[object_id])),CAST(SPs.[object_id] as nvarchar(128)),'N/A') as [ProcedureAppliesTo], 
  '  CREATE INDEX
       [missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + ']
     ON ' + mid.statement + '
       (' + ISNULL (mid.equality_columns,'') +
            CASE WHEN mid.equality_columns IS NOT NULL
                  AND mid.inequality_columns IS NOT NULL 
              THEN ','
              ELSE ''
            END +
            ISNULL(mid.inequality_columns, '') + '
       )' + 
     ISNULL(' INCLUDE (' + mid.included_columns + ')', '') as [create_index_statement],
  COALESCE(QUOTENAME(SPs.referenced_schema_name)+'.',QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id))+'.','') +
    COALESCE(QUOTENAME(SPs.referenced_entity_name),QUOTENAME(OBJECT_NAME(mid.object_id)),'') as [TableName],
  SPs.exec_count,
  SPs.total_elapsed_time*1.0/ 
    290/ --based onselect total_elapsed_time/(DATEDIFF(second, start_time, GETDATE())+1.0) from sys.dm_exec_requests 
    1000/ --otherwise seems closer to ms
    SPs.exec_count as [avg_time_sp],
  SPs.total_elapsed_time*1.0/ 
    290/ --based onselect total_elapsed_time/(DATEDIFF(second, start_time, GETDATE())+1.0) from sys.dm_exec_requests 
    1000/ --otherwise seems closer to ms
    SPs.exec_count/
    425 as avg_total_user_cost_sp,
  SPs.cacheobjtype,
  SPs.objtype,
  CASE WHEN migs.last_user_seek>migs.last_system_seek THEN migs.last_user_seek ELSE COALESCE(migs.last_system_seek,migs.last_user_seek) END as last_seek,
  CASE WHEN migs.last_user_scan>migs.last_system_scan THEN migs.last_user_scan ELSE COALESCE(migs.last_system_scan,migs.last_user_scan) END as last_scan,
  SPs.last_execution_timestamp,
  CONVERT(varchar, getdate(), 121) AS this_query_time_of_exec
FROM
  sys.dm_db_missing_index_details AS mid
INNER JOIN sys.dm_db_missing_index_groups AS mig
  ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as migs
  ON migs.group_handle = mig.index_group_handle
LEFT JOIN (
  SELECT
    CASE WHEN SUM(eps.execution_count)>=SUM(cp.usecounts) THEN SUM(eps.execution_count) ELSE COALESCE(SUM(cp.usecounts),SUM(eps.execution_count)) END as exec_count,
    SUM(eps.total_elapsed_time) as total_elapsed_time,
    MAX(cp.cacheobjtype) as cacheobjtype,
    MAX(cp.objtype) as objtype,
    MAX(eps.last_execution_time) as last_execution_timestamp,
    re.referenced_entity_name,
    re.referenced_schema_name,
    re.referenced_id,
    eps.database_id,
    eps.[object_id]
  FROM
    sys.dm_exec_procedure_stats AS eps
  INNER JOIN sys.dm_exec_cached_plans as cp
    ON cp.plan_handle=eps.plan_handle
  CROSS APPLY sys.dm_sql_referenced_entities (
    COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(eps.[object_id]))+'.','') +
      COALESCE(QUOTENAME(OBJECT_NAME(eps.[object_id])),CAST(eps.[object_id] as nvarchar(128)),''),
    'OBJECT'
  ) AS re
  GROUP BY
    re.referenced_entity_name,
    re.referenced_schema_name,
    re.referenced_id,
    eps.database_id,
    eps.[object_id]
) as SPs
  ON mid.[object_id] = SPs.referenced_id

Depending on the specifics of your SQL installation you may have to adjust the "constants" 290 and 425, which appear 2x and 3x respectively.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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