I am either off in my thinking or I don't understand the documentation. Just for clarity, the documentation on this management view uses the phrase "since it was last compiled" but does not clarify if that is the same as cached time. My ultimate goal is to retire an undocumented and outmoded database. I have already dealt with external dependencies to tables in the database and now I working with procedures and functions. I thought I could use this view [dm_exec_proedure_stats] to help discover what is being used and modify any callers. One of the procedures, a trivial one that returns output samples for all the different datetime convert constants, seems to be called thousands of times a day. I came to this conclusion using this code:
SELECT DB_NAME() as DBName
, oo.[Name] as ProcedureName
, SCHEMA_NAME(oo.schema_id) as SchemaName
, sm.is_recompiled as isRecompiled
, sp.modify_date as Modify_date
, st.cached_time as cached_time
, st.last_execution_time as Last_exec_Time
, st.execution_count as execution_ct
, st.total_elapsed_time as TotExecTime
FROM sys.sql_modules sm
LEFT JOIN sys.objects oo
on oo.object_id = sm.object_id
LEFT JOIN Sys.procedures sp
on sp.object_id = sm.object_id
LEFT JOIN sys.dm_exec_procedure_stats st
on st.object_id = sm.object_id
WHERE NOT oo.type IN ('tr','v','fn', 'tf', 'if')
When I ran a trace, which I am admittedly rusty at, it would not show any activity. To try and resolve this, I added the following code to the opening of the stored procedure which inserts every execution into a logging table.
INSERT INTO IT_SERVICE.dbo.DBA_ProcTrace
( ProcName, HostName, ProgramName, nt_domain, nt_userName, net_address, loginName, ProcSpid)
SELECT Object_name(@@PROCID), Hostname, program_name, nt_domain, nt_username, net_address, loginame, spid FROM Master.dbo.sysprocesses where spid = @@SPID
I then ran the data collection query [first above] which yielded this result (isolated to the proc of interest)
DBName ProcedureName SchemaName isRecompiled Modify_date cached_time Last_exec_Time execution_ct TotExecTime
@dbn usp_ADMIN_Show_DateFormats dbo 0 2023-08-23 09:50:28.420 2023-08-23 09:49:14.357 2023-08-23 10:17:43.797 168 172036
Then I executed the first query a half an hour later I get this result.
DBName ProcedureName SchemaName isRecompiled Modify_date cached_time Last_exec_Time execution_ct TotExecTime
DW_TBEI usp_ADMIN_Show_DateFormats dbo 0 2023-08-23 09:50:28.420 2023-08-23 09:49:14.357 2023-08-23 10:41:32.130 333 280012
Perhaps my understanding of execution count is overly simplistic but I would have expected a query of my logging table to have 165 records for this procedure (333-168). To my surprise there is none. This remained the pattern through to now. The execution count keeps increasing however there are no entries in the logging table.
Can anyone tell me where i have failed logically or mechanically? Backing up to what my original purpose was, I am looking for the least invasive way of discovering which procedures are used with the bonus of capturing the caller in some fashion.
EDIT It seemed useful to Restate the query I mentioned in my question Incorporating the corrections from @MartinSmith.
SELECT DB_NAME() as DBName
, oo.[Name] as ProcedureName
, SCHEMA_NAME(oo.schema_id) as SchemaName
, sm.is_recompiled as isRecompiled
, sp.modify_date as Modify_date
, st.cached_time as cached_time
, st.last_execution_time as Last_exec_Time
, st.execution_count as execution_ct
, st.total_elapsed_time as TotExecTime
FROM sys.sql_modules sm
LEFT JOIN sys.objects oo
on oo.object_id = sm.object_id
LEFT JOIN Sys.procedures sp
on sp.object_id = sm.object_id
LEFT JOIN sys.dm_exec_procedure_stats st
on st.object_id = sm.object_id
and st.database_id = DB_ID(DB_NAME())
WHERE NOT oo.type IN ('tr','v','fn', 'tf', 'if')
AND NOT cached_time IS NULL