Most Executed Stored Procedure?

Question

We created so many inefficient stored procedure in our application, we always postpone to make it more efficient until we have serious problem with database performance.

Now, I am thinking to fix it one by one order by most often executed stored procedure.

What is the best way to figure out which stored procedure is the most executed?

Is there a script that can show which stored procedure is the most executed?

Answer 1

Typical situation..

Run the performance analyzer And then start your application set some filters to capture high number of reads/writes/scans etc..

Answer 2

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

Answer 3

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, click on Organize Columns...
• Click on TextData, and move it up to the "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.

Answer 4

Another fantastic tool is Microsoft's Performance Dashboard. link text