Tracking stored procedure usage

Question

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?

Answer 1

You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance:

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc

This will give you the usecounts of the stored procedures that are cached pertaining to SomeDB.

Note: the plan cache contains the execution plans. This retention of these plans has many factors involved. Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.

BOL Reference about the Plan Cache

Answer 2

You can have a look at this as well as it contains info of last_execution_time of every stored procedure.

    SELECT DB_NAME(database_id)
    ,OBJECT_NAME(object_id,database_id)
    ,cached_time
    ,last_execution_time
    ,execution_count
FROM sys.dm_exec_procedure_stats

Answer 3

I use the two queries below to track SP execution times. When run via an agent job at the proper intervals this captures all executions in a single table before items are removed from the cache.

You must create the historical table UtilTrackSP and create an agent job (Part 1).

Part 2, this query will show Stored procedures that have not been executed, by comparing data in UtilTrackSP to SP's from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

/*
* Part 1
*/
/*

Query uses dm_exec_procedure_stats (which tracks last execution time in procedure cache) to report last execution time and saves data to
UtilTrackSP table for historical purposes. For SP's that have already been recorded in UtilTrackSP, the last execution time is updated.
For SP's that do not exist in UtilTrackSP a new record is added.

Run this via a SQL server agent job at set intervals to capture date from dm_exec_procedure_stats before removed from cache.
the interval depends on how long items remain in cache.

*/
DECLARE @object_id INT -- SP object_id, used as IDENTIFIER for each SP
DECLARE @dbname varchar(25) -- database name
DECLARE @SPName VARCHAR(250) -- SP name
DECLARE @LastExecTime DateTime -- Last excution time
DECLARE @LastUpDateTime DateTime -- Last time UtilTrackSP was updated
--Remove temp table is exists
IF OBJECT_ID('tempdb..#TempSP') IS NOT NULL
 DROP TABLE #TempSP
-- truncate table UtilTrackSP
-- Create table to store SP usage in active cache
CREATE TABLE #TempSP
(
   idx INT IDENTITY,
   object_id int,
   dbname varchar(25),
   SPName VARCHAR(250),
   LastExecTime Datetime
)
-- insert SP usage in active cache to TempDatabaseNames
INSERT INTO #TempSP (object_id, dbname, SPName, LastExecTime)
SELECT
s.object_id, 
DB_NAME(d.database_id) AS [Database],
OBJECT_NAME(object_id, s.database_id) 'proc name',
s.last_execution_time
FROM sys.dm_exec_procedure_stats AS s 
join sys.databases d on d.database_id = s.database_id
where DB_NAME(d.database_id) in ('DatabaseA','DatabaseB','DatabaseC')
-- uncomment below to see content of TempSP table 
-- select * from #TempSP
-- 
declare @i int
declare @cnt int
declare @id varchar(20) 
select @i = min(idx) - 1, @cnt = max(idx) from #TempSP
-- Display the names of the databases to backup
print 'inserting records'
while @i < @cnt
begin
     select @i = @i + 1
end
--
select @i = min(idx) - 1, @cnt = max(idx) from #TempSP
while @i < @cnt
begin
     select @i = @i + 1
 select @object_id = object_id, @dbname = dbname, @SPName = SPName, @LastExecTime =  LastExecTime 
 from #TempSP where idx = @i  


print 'object_id: ' + CAST(@object_id AS varchar(50)); 
print 'dbname: ' + @dbname 
print 'SPName: ' + @SPName

print 'LastExecTime: ' + CAST(@LastExecTime AS varchar(50))
-- Begin insert/update record
-- determine if there is an existing entry for the active SP
select @LastUpDateTime = LastExecTime from [YourDB].[dbo].[UtilTrackSP] where object_id = @object_id
select * from [YourDB].[dbo].[UtilTrackSP] where object_id = @object_id
IF @@ROWCOUNT = 0 BEGIN -- If there isn't an entry then insert the entry
insert into [YourDB].[dbo].[UtilTrackSP] VALUES (@object_id, @dbname, @SPName, @LastExecTime, getdate())

END
ELSE IF (@LastUpDateTime <> @LastExecTime) BEGIN -- If there is an existing entry and the last execute time has changed then record the change (LastExecTime & LastUpDateTime)
Update [YourDB].[dbo].[UtilTrackSP] set LastExecTime = @LastExecTime, LastUpDateTime = getdate() where object_id = @object_id

END
-- End insert/update record

END
--Remove temp table is exists
IF OBJECT_ID('tempdb..#TempSP') IS NOT NULL
 DROP TABLE #TempSP
/*

Part 2

*/
/*

Query compares all stored procs (using INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE') to values in tracked SP usage table.
The output is stored procs that have not be executed. These are SP that are canidates for deletion.
One per Database,each combined with UNION clause.

/
SELECT SPECIFIC_CATALOG, SPECIFIC_NAME  ,SPECIFIC_CATALOG + '_' + SPECIFIC_NAME as lookup
  FROM DatabaseA.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' and Not exists (select  from YourDB.dbo.UtilTrackSP where [dbname] + '' + [SPName] = SPECIFIC_CATALOG + '' + SPECIFIC_NAME)
 union
SELECT SPECIFIC_CATALOG, SPECIFIC_NAME ,SPECIFIC_CATALOG + '' + SPECIFIC_NAME as lookup
  FROM DatabaseB.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' and Not exists (select * from YourDB.dbo.UtilTrackSP where [dbname] + '' + [SPName] = SPECIFIC_CATALOG + '_' + SPECIFIC_NAME)
union
SELECT SPECIFIC_CATALOG, SPECIFIC_NAME ,SPECIFIC_CATALOG + '' + SPECIFIC_NAME as lookup
  FROM DatabaseC.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' and Not exists (select * from YourDB.dbo.UtilTrackSP where [dbname] + '' + [SPName] = SPECIFIC_CATALOG + '_' + SPECIFIC_NAME)

Answer 4

I just use the userconfigurables objets :

1. Place this code in firts line of your procedure :

   DECLARE @procevent sysname = (SELECT name FROM sys.objects WHERE object_id = @@PROCID); EXEC sp_trace_generateevent @eventid = 82, @userinfo = @procevent, @userdata = NULL;

2. create a profiler trace for UserConfigurable0 into a file

   You will have in the profiler trace all the executions with startDate and the name of the procedure.

Prefer to store profiler trace into files with a rollover...

3. read the profiler traces with the following query :

   SELECT * FROM sys.fn_trace_gettable('C:\ProfilerTraces\UserTrace.trc', -1)

Just replace "C:\ProfilerTraces\UserTrace.trc" by the first tracefile with directory