SQL Server – Query to Find Unused Stored Procedures

performancequery-performancestored-procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries.

When I run the following query, the results show the query was created in 2003, and last updated over a year ago. 

select d.name, p.name, e.last_execution_time, p.create_date, p.modify_date
from sys.DM_exec_procedure_stats as e
  right join sys.databases as d on d.database_id = e.database_id
  right join sys.procedures as p on p.object_id = e.object_id
WHERE is_ms_shipped = 0
order by e.last_execution_time asc, p.modify_date desc, p.create_date desc

What am I missing? Why am I seeing create procedure calls nonstop while the procedures are seemingly very old and unmodified. As a reference, the DB has 700+ stored procedures, while being only 6,5GB in size, and the query above shows that 675 of them have not been used since the last reboot (unless I'm misinterpreting what last_executed = NULL means).

Best Answer

Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying.

Executions of each individual statement is also recorded, in sys.dm_exec_query_stats, and you can retrieve the exact text of what has been executed, by CROSS APPLY sys.dm_exec_sql_text(sql_handle) and applying the start and end offsets from the sys.dm_exec_query_stats DMV.

This is the code I use to identify what queries on my server are placing quite a load on the database in terms of IO, Duration and CPU.

It only works on SQL Server 2008+ though (unless you exclude the plan_hash and query_hash), but gives a nice indication of where I should start to look in terms of refactoring.

Each individual query within a stored procedure has the performance recorded (providing it completes and does not timeout)

SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,DB_NAME(dest.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(dest.objectid, dest.dbid) AS SchemaName
    ,OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_elapsed_time) AS SumDuration
    ,CAST(100.0 * SUM(total_elapsed_time)
                / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
    ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
                / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext      
    ,deqs.query_plan_hash AS QueryPlanHash                                    
  FROM sys.dm_exec_query_stats AS deqs 
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE()) 
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases  
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')  
  GROUP BY 
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, 
          deqs.query_hash, dbid, dest.objectid, deqs.query_hash, deqs.query_plan_hash
Related Question