Accurately measure performance of stored procedure

Question

What is the best way to accurately measure the performance (time to complete) of a stored procedure?

I’m about to start an attempt to optimize a monster stored procedure, and in order to correctly determine if my tweaks have any effect, I need something to compare the before and after.

My ideas so far:

• Looking a query execution time SQL Management Studio: Not very accurate, but very convenient.
• Adding timers in the stored procedure and printing the elapsed time: Adding debug code like that stinks.
• Using the SQL Server Profiler, adding filters to target just my stored procedure. This is my best option so far.

Any other options?

Answer 1

There's lots of detailed performance information in the DMV dm_exec_query_stats

DECLARE @procname VARCHAR(255)
SET @procname = 'your proc name'

SELECT * FROM sys.dm_exec_query_stats WHERE st.objectid = OBJECT_ID(@procname)

This will give you cumulative performance data and execution counts per cached statement.

You can use DBCC FREEPROCCACHE to reset the counters (don't run this in a production system, since will purge all the cached query plans).

You can get the query plans for each statement by extending this query:

SELECT  SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) [sub_statement]
        ,*, CONVERT(XML, tqp.query_plan)
FROM sys.dm_exec_query_stats qs CROSS APPLY
     sys.dm_exec_sql_text(sql_handle) st CROSS APPLY
     sys.dm_exec_query_plan(plan_handle) qp CROSS APPLY
     sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset  ) tqp
WHERE st.objectid = OBJECT_ID(@procname)
ORDER BY statement_start_offset, execution_count

This will give you pointers about which parts of the SP are performing badly, and - if you include the execution plans - why.