0

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?

1
  • 3
    Are you sure you are running this query from the database where the procedure exists? This is a common mistake, since some sys. catalog views and DMVs span databases, people think that all views span databases. If you run this from the right database you could also simplify to WHERE ps.database_id = DB_ID();. Dec 15, 2016 at 3:21

1 Answer 1

0

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.

0

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