How can I reliably track stored procedure ( and functions ) use over time?

Question

I have a lot of stored procedures and functions in database, many are not used any more and I want to remove them. How can I find them reliably ?

I have seen answer that suggests looking into plan cache. That's great start but there is note that retention of these plans has many factors involved. What are those factors ? I would not like to delete procedure of function just because plan cache is missing ( for instance: does option recompile delete plan ? ).

I have also considered using profiler ( running it for 2 months does not sound like a good idea ) or perhaps even making some script to change all procedures to write when they are being used ( that would not help with functions though ).

If it is not possible to find them this way, is there another way? Do you know of any other tool, besides profiler ? ( Log analysis would be too slow as I would need to go over 2 months of data ).

I need this data to be reliable as possible.

Answer 1

I would create a polling job and auditing table to track this information, much like I do currently with space usage in my environment. This seems fairly straightforward and will not be as high impact as running a continuous trace. While the frequency of your polling would depend largely on your procedure cache churn, even executing a snapshot every few minutes would be fairly lightweight.

Auditing Table

CREATE TABLE sproc_tracking
(sproc_name sysname
,last_execution_time datetime
,CONSTRAINT pk_sproc_tracking PRIMARY KEY CLUSTERED (sproc_name));

Auditing MERGE Statement (usable since we're talking SQL 2008)

MERGE INTO sproc_tracking
USING (SELECT p.name, max(ps.last_execution_time) last_time
    FROM sys.dm_exec_procedure_stats ps
    JOIN sys.procedures AS p ON (ps.object_id = p.object_id)
    GROUP BY p.name
) AS sproc_stats(name,last_execution_time)
ON (sproc_stats.name = sproc_tracking.sproc_name)
WHEN MATCHED THEN
    UPDATE SET last_execution_time=sproc_stats.last_execution_time
WHEN NOT MATCHED THEN
    INSERT (sproc_name,last_execution_time)
    VALUES (sproc_stats.name,sproc_stats.last_execution_time);

Once you create the table, I would place the auditing statement in a SQL Agent Job to execute periodically based on your cache churn. Probably a good place to start, if your system is reasonably active, is every 15 minutes.

Gotchas

• Since this is still based on procedure cache, any stored procedures that are created WITH RECOMPILE will not be gathered no matter what.
• This process will need to execute/collect within the context of your specific database. If you need multiple databases, you'll need to adapt the process to execute in each one across the whole server. There are a variety of ways to do this.

Answer 2

SELECT p.name, s.last_execution_time
    FROM sys.dm_exec_procedure_stats AS s
    INNER JOIN sys.procedures AS p
        ON s.[object_id] = p.[object_id]
    ORDER BY s.last_execution_time desc;