2

One of our databases has over 3400 stored procedures and 400 functions. The system has been around since before 2009 and there are many procs/functions that are no longer used. I was given the task of finding those unused procs so they could be archived.

One plan was to use the table sys.dm_exec_procedure_stats

I could set up a job to check the table once/day and then summarize over the course of a month or a quarter.

Two problems with this: it wouldn't catch the functions and wouldn't catch stored procs with NOCOMPILE. So I decided to use extended events instead.
I didn't find the code to accomplish this exactly, so I'm adding it here in case it could save someone else some time.

CREATE EVENT SESSION [CaptureProcNames]
ON SERVER 
    ADD EVENT sqlserver.sp_statement_starting
    (
     SET collect_object_name=(1),
             collect_statement=(0)
     WHERE (
            ([object_type]=(8272) OR [object_type]=(20038)) 
            AND [sqlserver].[database_name]=N'MyDatabase' 
            AND [sqlserver].[is_system]=(0))
    ) 
    ADD TARGET package0.event_file
    (SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.KAIGSTAGING\MSSQL\Log\CaptureProcNames.xel',max_file_size=(250),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

Is there a better way to detect these unused stored procedures?

DForck42
  • 19,789
  • 13
  • 59
  • 84
J Brun
  • 1,246
  • 1
  • 12
  • 18
  • The object_types of 8272 and 20038 represent functions and sprocs. – J Brun Nov 03 '16 at 15:37
  • That's nice, but it's not a question. You're allowed to pose a question and then answer it yourself (although some people dislike it with a passion), but don't stuff the question and the answer together, because now it just looks like a "rate my code" question, which is definitely not in scope. – Jeroen Mostert Nov 03 '16 at 15:41
  • 1
    What @JeroenMostert said. Try your luck at http://codereview.stackexchange.com/ – Jens Nov 03 '16 at 15:49
  • @JeroenMostert The question is "How can I accurately detect all unused functions and stored procedures." The code in the question is what was previously tried. – iamdave Nov 03 '16 at 15:58
  • @iamdave: sorry, I didn't get the impression from the question anywhere that the code doesn't actually work (but I admittedly haven't tried it). I was under the impression a complete solution is being posted. I didn't interpret the question as "how can I accurately detect all unused X" either, really, because the answer to that is, obviously, "define *unused*" (or if you want, "define *accurately*"). – Jeroen Mostert Nov 03 '16 at 16:03
  • No matter how tricky you make these types of detection queries there are going to be misses. There may be procedures that are part of year end or some other types of rarely used code that these types of queries always miss. – Sean Lange Nov 03 '16 at 16:04
  • @JeroenMostert Indeed. I fully agree that it is a broad and vague question, but it is still there. – iamdave Nov 03 '16 at 16:29

0 Answers0