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');

 

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

5 Responses to Looking For Unused Stored Procedures In SQL Server 2008

  1. Dukagjin says:

    Why you are commented (SQL 2008 only) …I think that it would be better if the code will work also in SQL 2005, except when you have to use the clauses that are not available in SQL 2005!Anyway, I found nice post here, and very professional!Thank you!

  2. SeanH says:

    Would be great to have the SQL Azure version of this.

  3. Sundari Sinha says:

    This is a great post!
    I have followed this script to create an SP that would return all the unused SPs by databse on a particular server.
    We have some inhouse DB healthcheck kind of stored procs that use system procedures like sp_foreachtable, sp_autostats and some DMVs. These are scheduled to run daily to update stats or to rebuild indexes or to check fragmentation on the tables. When I run your script to find the unused stored procs the I see the DB health check kind of stored procs in the resultset.

    My question is- whether these kind of procs are not cached by SQL Server? I know that this kind of scripts should be used for diagonaositc purpose only- but for my curiosity sake I wanted to know.

    Thanks!

    Sundari Sinha

  4. Sundari Sinha says:

    Thanks for your reply!
    These healthcheck stored procs are not called WITH RECOMPILE option. Not sure what is causing these procs to not show up in the stats.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s