Identifying Unused Stored Procedures

You can use server side trace (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis.

Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the data is flushed).

You can even schedule a job to capture DMV data to a table to keep it persisted.

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database 
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

Refer to :

  • How to find unused Stored Procedures in SQL Server 2005/2008
  • Identifying Unused Objects in a Database

You can find this question useful, it applies to tables and columns but suggests using a third party tool ApexSQL Clean which can also find unused stored procedures as well as all objects that are not referenced by any other object in database, or in external databases

Disclaimer: I work for ApexSQL as a Support Engineer


If you are on SQL Server 2008+ you can also use extended events with a histogram target. Possibly this would be more light weight than a trace.

AFAIK you would need to create a different session for each database of interest though as I couldn't see any indication that bucketizing on multiple columns was possible. The quick example below filters on database_id=10

CREATE EVENT SESSION [count_module_start_database_10]
ON SERVER
ADD EVENT sqlserver.module_start
(  
        WHERE (source_database_id=10) 
)
ADD TARGET package0.asynchronous_bucketizer
(     SET  filtering_event_name='sqlserver.module_start', 
            source_type=0, 
            source='object_id',
            slots = 10000
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO
ALTER EVENT SESSION [count_module_start_database_10]
ON SERVER
STATE=START

And then after running some stored procedures in that DB a few times and retrieving the data with 

SELECT CAST(target_data as XML) target_data
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
WHERE s.name = 'count_module_start_database_10'

The output is 

<HistogramTarget truncated="0" buckets="16384">
  <Slot count="36">
    <value>1287675635</value>
  </Slot>
  <Slot count="3">
    <value>1271675578</value>
  </Slot>
  <Slot count="2">
    <value>1255675521</value>
  </Slot>
</HistogramTarget>

Showing that the procedure with object_id of 1287675635 was executed 36 times for example. The asynchronous_bucketizer is memory only so it would be best to set up something that polls this every so often and saves to persistent storage.

Tags:

Sql Server

Profiler

Recent Posts