24

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?

1
  • 4
    You can always look at the plan cache. SQL Server doesn't track this stuff forever because metadata quickly gets large and expensive.
    – JNK
    Apr 13, 2012 at 18:50

4 Answers 4

23

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

4
  • 5
    Remeber this only tells you the sp has been run fairly recently and gives you no information about procs not in here. Things like procs behind quarterly reports are run infrequenty but are being used. So basically, this just gives you a list to research in the applications that hit your db.
    – HLGEM
    Apr 13, 2012 at 19:07
  • 1
    @HLGEM Precisely. I've tried to make that point clear in my answer. Apr 13, 2012 at 19:25
  • Is it possible doing this for a specific datetime range? (e.g. last day/month, etc) ?
    – Jose Parra
    Jun 19, 2018 at 1:29
  • Note that this query will not return a row for a stored procedure that has been altered and not executed after.
    – Axel2D
    May 20, 2019 at 14:52
10

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
4
  • There is on caveat to this tho. These stats are only kept since the last database execution. I am busy writing a job that will archive it to a table once a day. May 28, 2018 at 9:55
  • @earthling42, did you ever write a job that archived it to a table daily?
    – Alex Chung
    Nov 8, 2019 at 14:56
  • @Alex Chung - unfortunately never got around to that. Moved onto non MS-SQL projects shortly after the date of this post. Shouldn't be to hard tho? I'd be happy to try to work it out with you if you are interested. Nov 12, 2019 at 16:18
  • another caveat is that not all procedures are kept into the ring buffer. It depend of memory pressure !
    – SQLpro
    Feb 7 at 14:21
0

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)


0

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.

enter image description here

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

  1. 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

Your Answer

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

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