SQL procedure running time widely divergent

Question

I have an application that runs a huge stored procedure on SQL Server 2000. Usually it takes about 1 minute to complete, but occasionally it will take MUCH longer. Just now I ran it three times in a row in my test system. It took 1:12, 1:23, and 55:25. What would cause that behavior? There are other things going on in the database, so I wonder if it has something to do with locks. How can I catch this in the act?

Answer 1

Create a trace and examine it in Profiler. That should at least point towards where the problem lies - in your procedure or elsewhere.

Answer 2

It's probably parameter sniffing: based on the input, Sql Server chose a different query plan.

Another possibility is that a separate query was running at the same time and locked everything up.