6

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.

6
  • 1
    I think it's a duplicate of Tracking stored procedure use.
    – Marian
    May 9, 2013 at 12:33
  • 1
    @Marian It is not, besides that question was not solved.
    – IvanP
    May 9, 2013 at 13:40
  • Arguable, but I would say it is not a duplicate question. The duplicate answer is on procedure stats (which helps). The OP is asking more on the lines of: "when was the last time a proc was used, based on that that I can remove it". May 9, 2013 at 14:19
  • 2
    @StanleyJohns, IvanP The linked question asks "...is there any way to track which stored procedures are being used, or at least when they were last executed?" This question asks "How to find out when stored procedures and funcions were last used" (copied from the title). This is the same question. May 9, 2013 at 14:38
  • 3
    @IvanP I've edited this to make it clearer why the previously-linked dupe doesn't satisfy your use case. Please consider adding some more detail about exactly why you need this data into the body of the question to prevent it being closed as a dupe again.
    – JNK
    May 9, 2013 at 15:00

2 Answers 2

5

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.
1
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;
3
  • 5
    It should be noted that this only covers cached stored procedures. If you have procedures that only run say monthly, or even weekly they very well may not show up. Check it several times over a week and then several times at the end/beginning of a month. May 9, 2013 at 13:09
  • No , this query can bring stored procedure that are altered / Created even 5 years before. May 24, 2013 at 11:26
  • sys.procedures will go back as far as you like, however sys.dm_exec_procedure_stats per BOL "The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view." Unless you have a large enough cache to never be flushed, and never restart the SQL services you will lose data over time. May 24, 2013 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.