January 07, 2011

Find the Most Used Stored Procedures in SQL Server




In one of my earlier posts, I had mentioned about some Dynamic Management Views (DMV) for SQL Server Performance and Tuning. Out of these DMV’s, I find the sys.dm_exec_query_stats very useful. This DMV returns aggregate performance statistics for cached query plans.

One of the suggested methods to get information of the most executed code is to create a trace or use a tool that does that, and then query the results. However since SQL Server caches information over time, you can extract such information using the sys.dm_exec_query_stats

Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

-- Query by SQLServerCurry.com
SELECT TOP 3 dest.text, deqs.execution_count,
deqs.total_worker_time, dest.objectid
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) dest
ORDER BY deqs.execution_count desc

Top Stored Procedures

As you can see, the DMV extracts the 3 most used stored procedure based on its execution count.

Note: If you execute this query on a live databases, the results may be inaccurate in the first run. The BOL says, “The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.” Hence is is advised to run the same query 2 or 3 times.

You can also see some more uses of the sys.dm_exec_query_stats DMV over here Find the Most Time Consuming Code in your SQL Server Database and Find Unused Objects in your SQL Server Database


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "Find the Most Used Stored Procedures in SQL Server"
  1. darnok said...
    September 1, 2011 at 5:24 AM

    That should work on sql 2005 ?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions