April 21, 2009 at 8:05 am
Hi
One of my apps has a SQL job that runs every ten minutes doing a pretty big calc affecting multiple tables. It runs in general for about 2 minutes. But occasionally, it will get to a point where it does a large insert into a table (tried temp and permanent.. same problem).. and will just not complete..
The job is killed by another monitoring job after 20 mins. More often than not it will fail the next time and so on.
It's single user and has all the necessary transactions and locking / no locking. If we run a sp_who there are no locks. The offending SPID has a status of SUSPENDED. The job still shows as running until we kill it.
We immediately run the identical proc via query analyser .. and it takes 2 or three minutes. Same data.
Driving us mad as we can't rely on this and I have someone watching this throughout the day.
Any ideas why via the Agent Job it can suspend but never via the query window?
Regards
Jeremy
April 21, 2009 at 9:41 am
Strange, same code? Same server?
April 21, 2009 at 1:10 pm
Is this a stored procedure - and does the stored procedure use parameters? Do you create variables and use the variables in the queries for this procedure?
Just thinking this might have something to do with parameter sniffing. If you use variables and the variables are calculated for each iteration - it could be that for certain values the execution plan it is using is not optimal (read, very very very bad :w00t:).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply