How do you measure the performance of a stored procedure?

Question

I'm using a version of SQL Server 2005 that does not support the profiler, trying to figure out how best to compare the performance of two stored procedures. I've run the execution plan for each, but it's not clear to me which of the provided metrics I should be focusing on. Do I go through and add up the various costs? What's the best approach?

Thanks in advance.

Answer 1

Look at this article: Measuring SQL Performance

If you don't want to register to free account, here is a solution 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 recommend to register. It's free.

Answer 2

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

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

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

Answer 3

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

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

Answer 4

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

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

Answer 5

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

Answer 6

if you are using something like

SET SHOWPLAN_ALL ON

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

this might help:

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

Answer 7

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