Recent Execution of Stored Procedure – SQL in Sixty Seconds #118

Recent Execution of Stored Procedure - SQL in Sixty Seconds #118 118-StoredProcedureExecuted-ytcover-500x281 What I enjoy the most is working with different clients on  Comprehensive Database Performance Health Check. Every client is different and what I learn from every organization is different. Recently, I had a very interesting experience with a client who had a database with over thousands of stored procedures. My clients were not sure which SPs are the recent SP and which SP is no longer used. Today we will learn about the recent execution of the stored procedure.

It is very common when people are working on the SQL Server Performance Tuning project to create multiple stored procedures to test variations of the stored procedure to check which works optimally. However, it is equally important to remove the stored procedures which are no longer used. Well, if you are wondering if they impact performance, the answer is dormant stored procedures do not have a direct impact on the SQL Server performance (or not that I know of it).

Here is the quick video where I am reading the data from the SQL Server Cache and list when was the stored procedure last executed as well as how many times it has been executed. Please remember that this data is from the SQL Server cache, hence, if your cache is reset in the recent time or if you have restarted SQL Server services or due to any reason, the procedure cache is removed, it will give not detail that information.

Here is the script which is demonstrated in the video to list the recent execution of the stored procedure.

SELECT  
	SCHEMA_NAME(sysobject.schema_id) SchemaName,
	OBJECT_NAME(stats.object_id) SPName, 
	cached_time, last_execution_time, 
	execution_count,
	total_elapsed_time/execution_count 
			AS avg_elapsed_time
FROM   
	sys.dm_exec_procedure_stats stats
    INNER JOIN sys.objects sysobject 
		ON sysobject.object_id = stats.object_id 
WHERE  
	sysobject.type = 'P'
ORDER BY
	stats.last_execution_time DESC

If you have any idea which you would like me to build the SQL in the Sixty Seconds, please leave a comment and I will do my best to build the same. You may subscribe to my YouTube Channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
Full-Screen Execution Plan – SQL in Sixty Seconds #117
Next Post
Nonclustered Primary Key – SQL in Sixty Seconds #119

Related Posts

10 Comments. Leave new

  • Philip Christiaan van Gass
    September 5, 2020 1:41 pm

    Hi Dave. I get your ‘SQL in sixty seconds’ every day and each time a reference is made to a video and a script, but they are never included in the email; they are missing ! Please explain and rectify.

    Reply
  • Minor change in script to include procedures which never executed after sql server restarted.

    SELECT SCHEMA_NAME(pro.schema_id) SchemaName,
    pro.name SPName,
    cached_time,
    last_execution_time,
    execution_count,
    total_elapsed_time / execution_count AS avg_elapsed_time
    FROM sys.procedures pro
    LEFT JOIN sys.dm_exec_procedure_stats stats
    ON stats.object_id = pro.object_id
    LEFT JOIN sys.objects sysobject
    ON sysobject.object_id = stats.object_id
    ORDER BY stats.last_execution_time DESC;

    Reply
  • Very interesting post Pinal and thanks Sanjay for the update. Very helpful! I’m currently trying to enhance the query to read also the query plan for the procedure and will post the statement later

    Reply
  • Here the enhance query of Sanjay:

    SET NOCOUNT ON;

    SELECT
    SCHEMA_NAME(P.[schema_id]) AS SchemaName
    ,OBJECT_NAME(P.[object_id]) AS ProcedureName
    ,PS.cached_time
    ,PS.last_execution_time
    ,PS.execution_count
    ,(PS.total_elapsed_time / PS.execution_count) / 1000 AS AVG_Elapsed_Milliseconds
    ,QP.query_plan
    FROM
    sys.procedures AS P
    LEFT JOIN sys.dm_exec_procedure_stats AS PS ON P.[object_id] = PS.[object_id]
    LEFT JOIN sys.objects AS B ON PS.[object_id] = B.[object_id]
    LEFT JOIN sys.dm_exec_cached_plans AS CP ON PS.plan_handle = CP.plan_handle
    OUTER APPLY sys.dm_exec_query_plan(PS.plan_handle) AS QP
    WHERE
    P.is_ms_shipped = 0
    ORDER BY
    PS.last_execution_time DESC, SCHEMA_NAME(P.[schema_id]), OBJECT_NAME(P.[object_id]);

    SET NOCOUNT OFF;

    Reply
  • This is helpful. Thank you.

    Reply
  • I was seeing some very strange results on a server hosting multiple databases. Adding a filter on stats.database_ID for the current database cleared things up.

    INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id AND stats.database_id = DB_ID()

    Reply
  • Had to add a clause on the join to sys.objects for a server hosting multiple databases:

    INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id AND stats.database_id = DB_ID()

    Reply

Leave a Reply

Menu