[ACCEPTED]-How do you measure the performance of a stored procedure?-stored-procedures

Accepted answer
Score: 19

Look at this article: Measuring SQL Performance

If you don't want 3 to register to free account, here is a solution 2 1:

DECLARE @start datetime, @stop datetime
SET @start = GETDATE()
EXEC your_sp
SET @stop = GETDATE()

2nd:

SET STATISTICS TIME ON
EXEC your_sp

3rd:

SET STATISTICS IO ON
EXEC your_sp

Btw, this site has some nice articles. I'd 1 recommend to register. It's free.

Score: 3

The question is what are you optimizing 10 for? Is it for speed or resources used?

If 9 speed, then in the query analyzer I would 8 look at the execution between several runs, make 7 changes and time them again.

If it is resources 6 then I would look through the execution 5 plan. In that case I would start with the 4 worse offenders and work my way down the 3 list. Adding them up will tell you the 2 over all performance, but most cases it 1 is an item or 2 that is the bottle neck.

Score: 2

if you are using something like

SET SHOWPLAN_ALL 3 ON

look at the TotalSubtreeCost column value 2 for the row with the EXE YourProcedureName

this 1 might help:

http://technet.microsoft.com/en-us/library/ms180765.aspx

Score: 1

Like most questions, the answer depends... In 17 the final analysis, the only measure that 16 matters is end-user perception, which can 15 be affected by many things,including not 14 only the stored procedure, but network performance, usage 13 patterns (is the sProc being called 20x/day, or 12 1000x/ second?), etc., - and the sProc 11 may not be the determining factor.

But if 10 the stored procedure is the "piece if the 9 puzzle" that is having the major adverse 8 impact on the end-user perception of some 7 function, then, you have to look at elapsed 6 time to run the stored procedure. But this 5 itself can be affected by numerous underlying 4 metrics, and to do anything about it you 3 need to analyse them all to determine which 2 of them is the major or overriding contributer 1 to the overall stored proc performance.

Score: 0

You could always rig a test harness to call 9 your stored procedures and measure the call 8 times. Unfortunately you're not going to 7 get the details about exactly which parts 6 of the Stored Procedure are causing the 5 slow-down.

You could always run the Stored 4 Procedure by hand in Query Analyzer and 3 measure the results that way as well. The 2 .NET harness just automates the process 1 for you.

Score: 0

The simple low-brow solution is to run them 6 with print statements printing the execution 5 time over the various parts. This won't 4 help if the performance problem is more 3 subtle and found in production only, but 2 if you can reproduce it in your test environment, you 1 should be fine.

Score: 0

One handy technique if you are trying to 9 compare the performance of two procs or 8 statements is to select both blocks of sql 7 in query analyzer and run the query plan. The 6 plan will tell you the cost percentage of 5 each block relative to one another. This 4 is not full proof. I have seen it tell me 3 one was cheaper when it was clearly more 2 expensive when actually ran, but for the 1 most part it is a nice, quick trick.

More Related questions