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)
