How to find last execution time of stored procedure in Azure SQL

atennison 0
Viewed 50

There are few unwanted and unused stored procedures in our database and I want to find the last execution time to find if we still need them.

I have almost tried all the coding but nothing seemed to work. The problem is there is no common object_id between these two catalog views, sys.objects and sys.dm_pdw_nodes_exec_procedure_stats for the code to work.

I am using azure synapse. i have tried the below code to find the last execution time of SP but i am not getting any results.

SELECT o.name, ps.last_execution_time 
FROM sys.dm_exec_procedure_stats ps 
INNER JOIN sys.objects o ON ps.object_id = o.object_id 
WHERE DB_NAME(ps.database_id) = '' 
ORDER BY ps.last_execution_time DESC
azure-sql-database stored-procedures sql
1 Answers

In Azure Synapse Analytics we use sys.dm_pdw_nodes_exec_procedure_stats instead of sys.dm_exec_procedure_stats

Query

SELECT o.name,
ps.last_execution_time
FROM sys.objects o
INNER  JOIN sys.dm_pdw_nodes_exec_procedure_stats ps
ON o.object_id = ps.object_id
WHERE  DB_NAME(ps.database_id) = 'demodedi1'
ORDER  BY ps.last_execution_time DESC

Output

enter image description here

PratikLad0
Related