Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Display the 50 most used stored procedures in SQL Server
From Wiki
Sometimes you get asked what are the most executed stored procedures in your database, before SQL Server 2005 this was a big pain in the neck. SQL Server 2005 makes this much easier to find out with the introduction of dynamic management views. The two dynamic management views we will use are sys.dm_exec_query_stats and sys.dm_exec_sql_text.
To get the top 50 most executed statements in stored procedures you would use the following query
- SELECT top 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid,
- (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
- ( (CASE WHEN statement_end_offset = -1
- THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
- ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
- last_execution_time
- FROM sys.dm_exec_query_stats AS s1
- CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
- WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
- and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
- and exists (Select 1 from sys.procedures s
- where s.is_ms_shipped = 0
- and s.name = x.ProcName )
- ORDER BY execution_count DESC
Below is some sample output on my system.
proc count objectid statement last_execution_time ---------------------------------------------------------------------------------------------- usp_AddLast 42757230 517576882 UPDATE dbo.LastSome 2010-04-14 14:15:33.433 usp_AddLast 42757230 517576882 IF EXISTS( SELECT 2010-04-14 14:15:33.433 usp_Update 20290 725577623 update t set pclose 2010-04-14 14:15:33.433 usp_Update 20288 725577623 update t set pclose 2010-04-14 14:15:33.453 usp_GetLast3 3188 501576825 SELECT distinct l.Sy 2010-04-14 14:14:33.350 usp_Historical 168 1029578706 select * from Histor 2010-04-14 14:02:08.190 usp_AddLast 3 517576882 INSERT dbo.Sometable 2010-04-07 08:42:57.040 usp_GetLast2 3 965578478 SELECT l.Symbol, q 2010-04-06 16:14:49.523 usp_Historical2 2 1045578763 select SomeNumber,G 2010-04-14 12:10:48.860 usp_GetLast 1 901578250 SELECT distinct l.Sy 2010-02-12 09:11:59.840
As you can see the first two rows are for the same stored procedure, what if you only want to know the procedure names? You can use the following query for that, I grouped them by name and then used the max count of the statement itself as the execution count, you could also use SUM instead of MAX. If you have a lot of if else conditions then max might not give you the whole picture.
- SELECT top 50 * FROM
- (SELECT OBJECT_NAME(s2.objectid) AS ProcName,
- MAX(execution_count) AS execution_count,s2.objectid,
- MAX(last_execution_time) AS last_execution_time
- FROM sys.dm_exec_query_stats AS s1
- CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
- GROUP BY OBJECT_NAME(s2.objectid),s2.objectid) x
- WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
- AND EXISTS (SELECT 1 FROM sys.procedures s
- WHERE s.is_ms_shipped = 0
- AND s.name = x.ProcName )
- ORDER BY execution_count DESC
Here is the output from this query.
proc count objectid last_execution_time ---------------------------------------------------------------------- usp_AddLast 42667941 517576882 2010-04-14 14:08:48.287 usp_Update 20263 725577623 2010-04-14 14:08:48.307 usp_GetLast3 3180 501576825 2010-04-14 14:07:10.513 usp_Historical 168 1029578706 2010-04-14 14:02:08.190 usp_GetLast2 3 965578478 2010-04-06 16:14:49.523 usp_Historical2 2 1045578763 2010-04-14 12:10:48.860 usp_GetLast 1 901578250 2010-02-12 09:11:59.840
There are some differences between this output and the output from above, this is because I ran the queries about 7 minutes apart so that is why the counts don't match
Contributed by: --SQLDenis 18:52, 14 April 2010 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.