Comparing Stored Procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Comparing Stored Procedures

Hi All,
I have rewritten the logic in a stored procedure to try and improve its speed. I am now wondering what the best way to compare the 2 procedures is, so i can see if my changes have shown any improvement. The procedure takes on parameter, so my initial idea was to use a curosr and run 1000 different parameters through and check the execution time (by setting show statistics time on)…i didnt realise this would show a separate time for each loop through the cursor, so this approach wont work (also, the execution time for each execution of the SP was between 0 and 1 ms, so not too much help!) Any suggestions about how to compare the performance of the 2 SPs?? Thanks
How about compare both execution plan?
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
Load a text file with alternating calls to each stored procedure. Execute it in Query Analyzer and use Profiler to filter the SPID of the Query Analyzer session. Capture the execution plan, reads, writes, recompiles, and cpu. This should give you a very good idea of the strengths/weaknesses of each. I would have two profiler sessions. One can capture exec plan. One can capture the other items. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for the advice…So is it then a case of manually comparing the profiler output to determine which is best?
Yes. You should be able to tell pretty quickly though which is most efficient. It also might point out some areas you can tune better. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.