Most Executed Stored Procedure?
Use:
SELECT TOP 10 qt.TEXT AS 'SP Name', SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset - qs.statement_start_offset)/2 END) AS actual_query, 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 = '[your database name]')ORDER BY qs.total_physical_reads DESC
Reference: SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure
Another fantastic tool is Microsoft's Performance Dashboard.
A quick way to accomplish this is to run SQL Profiler, and then "group" your trace by TextData.
- Go into your Trace Properties
- Under the Events Selection tab, clickon Organize Columns...
- Click on TextData, and move it up tothe "Groups" area.
You may also only want to watch specific events, like SQL Batch Completed. If I remember, that will give you durations and other handy stats to group/filter by.