Find the Most Used Stored Procedures in SQL Server

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

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.