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)
14 Comments. Leave new
Thanks for the idea. How does this relate to back-up in general? We have been writing on our blog about bare metal restores. Please see . Is there a connection? Would appreciate your comments.
Thx Pinal, really nice script!
I wanted the name of the sproc as well so I just joined to the object table.
SELECT TOP 100
ob.name AS ‘SP Name’,
qt.TEXT AS ‘SP Code’,
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
LEFT OUTER JOIN
sys.objects ob
ON qt.objectid = ob.object_id
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = ‘AdventureWorks’)
ORDER BY
qs.total_worker_time/qs.execution_count DESC
I just ran your query and I don’t get the SP name – they’re all NULL. Further, I have over 350 SPs and the query only returns 309 (when the top 100 is removed) and from the ‘SP Code’ many are duplicated but with different stats.
I want to find which SPs are not used so I can delete them.
qs also has a column for last_execution_time which I thought might be handy but the only date it gave was todays – very unhelpful for finding old, unused SPs.
Any ideas would be appreciated.
Nevermind about the NULL part – I ran against the master db rather than my db. I get the SP name when run on my db. The other sections are still valid though.
Pinal Can you please help run above mentioned query in SQL Server 2000. It seems CROSS APPLY functionality is available in 2005 only.
hi.. is there something like this in sql server 2000? thanks
Great script! Thanks, Mr. Dave :-)
For those SQL 2000 users, the DMVs are only available in 2005, but I am curious if this info can be reported on in 2000.
Mr.dave,
This script shows some error in
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
FYI, I’m using Sql Server 2005
Please suggest something to overcome this error
Dear Alwyn,
Please use Master database
Thanks
Mohit
i ran the above script (both the original and the one shown in the first response that includes the sp name from sys.objects and do not get full results. I took off the ‘top 100’ clause, but only 33 rows were returned
Before the script I added calls to execute 2 stored procedures. Both stored procedures returned records. One of these stored procedures shows up in the statistics displayed, but the other one does not.
Any ideas on how to get all execution statistics.
EXEC [dbo].[Checks_Get] @CHECKS_INDEX = 10000
EXEC [dbo].[Stores_Get] @STORE_INDEX = 50
SELECT –TOP 100
ob.name AS ‘SP Name’,
qt.TEXT AS ‘SP Code’,
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
LEFT OUTER JOIN
sys.objects ob
ON qt.objectid = ob.object_id
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = ‘xxx’)
ORDER BY
qs.total_worker_time/qs.execution_count DESC
The script does not show you the most used stored procedures, it shows the most executed plans, and the number of executions since the last re-compile.
While it is still a nice script, it is not what the title says it is…
Hello Pinal,
What about Top 10 Stored Procedures for the last xx number of days? The goal being finding out the heavy hitters for the past week for example. Thanks in advance!
Michael
Hi ,
How can we reset these stats to see if there has been any changes?
I found it can be done by executing:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But it might cause a big CPU Spike on the server
Hi Pinal,
How to get all unused proc/tables??