Rank: Administration Groups: Member
Joined: 3/24/2008 Posts: 2,488 Points: 4,640 Location: South Florida USA
|
Display the 50 most used stored procedures in SQL Server http://wiki.lessthandot.com/index.php/Display_the_50_most_used_stored_procedures_in_SQL_ServerCode: SELECT * 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
|