Looking For Unused Stored Procedures In SQL Server 2008

One thing you should periodically do as a “good” DBA, is to look for unused objects in your user databases, to make the database easier to maintain and understand. If you use T-SQL stored procedures, (which I highly recommend), you can use the queries below to come up with a list of stored procedures that are not in your procedure cache.

Depending on how long since your procedure cache has been flushed, and your workload, this can give you a pretty good list to start with. You need to remember that if a SP has WITH RECOMPILE or OPTION(RECOMPILE), it won’t be in the cache.

Also, just because a stored procedure shows up on this list does not mean that you should just blindly delete it. I would search your source code (if possible), and talk to your developers. Then, I would rename the SP (rather than deleting it) in a test environment, and regression test. I like to use a prefix of zzz for the renamed stored procedures, so that they all sort together in the SSMS Object Explorer Window.

 

-- Get list of all SPs in the current database (SQL 2005 and 2008)
    SELECT p.name AS 'SP Name', p.create_date, p.modify_date        
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0
    ORDER BY p.name;
    
    
    -- 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;

You can also use this query to check dependencies (in SQL 2008) on an object:

SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities (‘schemaname.objectname’, 'OBJECT');

Technorati Tags: Finding Dead Stored Procedures

About these ads