Geek Answers Handbook

Accurately measure the effectiveness of a stored procedure

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

I'm going to start trying to optimize the monster stored procedure, and to correctly determine if my settings make sense, I need to compare something before and after.

My ideas so far:

  • Looking at the execution time of the query SQL Management Studio: not very accurate, but very convenient.
  • Adding timers to a stored procedure and printing elapsed time: adding a debugging code such as stinks.
  • Using SQL Server Profiler, add filters designed only for my stored procedure. This is my best option.

Any other options?

+5
source share
4 answers

Profiler is the most reliable method. You can also use SET STATISTICS IO ONand SET STATISTICS TIME ON, but they do not include the full influence of scalar UDFs.

You can also enable the "enable client statistics" option in SSMS to get an overview of the performance of the last 10 launches.

+6
source

In DMV dm_exec_query_stats

detailed performance information is available,
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 aggregate performance data and the amount of execution in a cached expression.

You can use DBCC FREEPROCCACHEto reset counters (do not run this on a production system, as it will clear all cached query plans).

You can get query plans for each operator by expanding 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

, SP , - - .

+8

/ . SQL-, .

+1

More articles:


All Articles