CODEWITHSUNDEEP
sql-serversql-server-2000locking
Jon Wilson
Jon Wilson

Reputation: 776

SQL procedure running time widely divergent

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?

Upvotes: 2

Views: 166

Answers (2)

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

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

Upvotes: 2

Related Questions