Sql-server – When is dm_exec_procedure_stats updated

dmvsql serversql-server-2012statistics

I just created a new proc and ran it, but it is not showing up in my query:

SELECT  schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time LastElapsedTime, ps.object_id
FROM    sys.dm_exec_procedure_stats ps JOIN 
        sys.objects o ON ps.object_id = o.object_id 
WHERE   DB_NAME(ps.database_id) = 'MyDatabase'

Should I not expect a new proc to appear in the results of this query immediately after running the proc for the first time?

I've run the proc I created several times now, and I've run sys.dm_exec_procedure_stats several times as well yet it still doesn't appear. I have the assumption that running a stored procedure will always cause a procedure plan to be cached for it if one does not already exist… but wondering if that's a correct assumption now?

Best Answer

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.

NOTE: An initial query of sys.dm_exec_procedure_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Resource

Is sp_recompile or WITH RECOMPILE being used? Those can cause the stored procedure to be removed. Also, it could be in regards to the name of the procedure. This answer talks about that.

Related Question