4
\$\begingroup\$

I was given the task of finding those unused procs/functions in our SQL 2012 database.

Initially my 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)
|improve this question
\$\endgroup\$
  • \$\begingroup\$ Do you want to find unused ones or there also are dead objects that you want to get rid of? Also, how many stored procedures you approximately have? \$\endgroup\$ – Evaldas Buinauskas Dec 29 '17 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.