How many times we all DBA’s might have wonder which stored procedure is executing most in the database?
I have wondered it often and I have written following small script which gives me answer to my above questions. I am also retrieving few additional data along with the highest used SP names. You can change the name of the database from AdventureWorks to any database which you are curious about. If WHERE clause is completely removed it will give results for all the database.
SELECT TOP 10 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = 'AdventureWorks')
ORDER BY qs.total_physical_reads DESC
Reference : Pinal Dave (https://blog.sqlauthority.com)
