SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure

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 (http://blog.SQLAuthority.com)

About these ads

15 thoughts on “SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure

  1. 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.

  2. Pinal Can you please help run above mentioned query in SQL Server 2000. It seems CROSS APPLY functionality is available in 2005 only.

  3. 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.

  4. 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

  5. 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

  6. 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…

  7. 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

  8. 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

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s