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

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