CODEWITHSUNDEEP
sql-serverperformancestored-procedures
Jakob Gade
Jakob Gade

Reputation: 12427

Accurately measure performance of stored procedure

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:

Any other options?

Upvotes: 6

Views: 10576

Answers (4)

John Sansom
John Sansom

Reputation: 41899

You want to ensure that you are performing fair tests i.e. comparing like with like. Consider running your tests using a cold cache in order to force your stored procedure execution to be served from the IO Subsystem each time you perform your tests.

Take a look at the system stored procedures DBCC FREEPROCCACHE and DBCC FREESYSTEMCACHE

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 454019

Profiler is the most reliable method. You can also use SET STATISTICS IO ON and SET STATISTICS TIME ON but these don't include the full impact of scalar UDFs.

You can also turn on the "include client statistics" option in SSMS to get an overview of the performance of the last 10 runs.

Upvotes: 8

Ed Harper
Ed Harper

Reputation: 21505

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.

Upvotes: 9

John Pickup
John Pickup

Reputation: 5115

One possible improvement on your timers/debug option is to store the results in a table. In this way you can slice-and-dice the resulting timing data with SQL queries rather than just visually parsing your debug output.

Upvotes: 1

Related Questions