May 11, 2011 at 1:29 pm
Hi all
I have a Mirrored SQL Server, 2008 RTM (dont ask) with a 3rd party db on it.
this 3rd party db has a proc that is ran from a SQL Agent job every 2 minutes. the job has ran fine for years in 0-2 seconds.
Recently though, the job will sort of hang and take 10+ minutes (it does complete eventually without error), if we stop the job and run the proc manually, it executes instantly.
Now, i have deadlock monitoring on this server, there are no deadlocks occurring as far as i can tell.
If i check activity monitor, the job proc is running with no wait time and no blocking, its just sitting there, it even says RUNNING.......
Now, the proc in question has no parameters, but it does have some nested procs with parameters, i suspect parameter sniffing perhaps?
But if it is parameter sniffing, why would it execute instantly from within MSSMS and take ages from SQL Server Agent....
Now, here is where it gets interesting, if i run it from MSSMS WITH Execution Plan, then it hangs the same way the SQL Server Agent does?!?!?!?!?!
I got that desperate i even ran it through tuning advisor, even that could not find any missing indexes etc.
All indexes are < 2% fragmented (defragged last night) and statistics updated too.
I'm really at a loss here, any help would be super!
Regards
Jordon
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 11, 2011 at 1:33 pm
Has the job changed?
I would suspect parameter sniffing and alter the proc for that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 11, 2011 at 1:52 pm
hi mate
Na, the job has not changed in any way.
I tried the usual fixes for parameter sniffing, adding RECOMPILE, rewriting to not using the Procs parameters directly, (i.e. passing the value of the parameters to declared variables and using those), but this made no difference. Problem is, this procs call about 15 other procs (got to love 3rd party software).
Will try to step through it and hope the same "slowness" occurs during debugging....
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 11, 2011 at 2:48 pm
jordonpilling (5/11/2011)
Problem is, this procs call about 15 other procs (got to love 3rd party software).
Aw man.....
Nested SP's, you have my sympathy.
Try scheduling the agent job to run 20 minutes from now then run the process in SSMS. If they run at the same duration then my guess is stuff running in the background when the job kicks off? 😀
EDIT:
Crap, I just read your OP. I betcha the nested SP's are hitting the same tables and blocking is occuring. One SP is waiting for the other to release the records in the table while another is waiting etc, etc......
May 11, 2011 at 2:58 pm
if blocking is occurring whilst the job is running, its not being shown in Activity Monitor (“Blocked By” etc).
And typical, my VS2008 debugging is deciding not to hit the breakpoints in the proc.
:crying:
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 11, 2011 at 3:42 pm
Do you have auto create statistics enabled? Is Auto Update Statistics enabled? And finally, is Auto Update Statistics asynchronously enabled?
I'm thinking that maybe the system is running into a situation where it needs to update statistics and that is hanging the job until all of the statistics have been updated.
Just a thought...
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
May 11, 2011 at 10:24 pm
I agree with Warren. When is the Job scheduled to Run ? When are you are you running the Sp manually ? Are the times adjacent ?
May be the Job is taking more time due to the other processes running at that time.
Run a brief Profiler trace and Perfmon Counter Log to find the culprit.
Thank You,
Best Regards,
SQLBuddy
May 12, 2011 at 3:51 am
Hi
We are only running it manually after killing the hung job, therefor they are never running at the same time.
I rebuilt all indexes last night (which included statistics right?) (took 6 hours), will see if it behaves any better today.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 18, 2011 at 1:50 pm
just a bit of an update in case anyone else gets the same problem.
I have not found the root cause yet, but i have found a work around.
Given that the EXEC command executed instantly if executed via SSMS as opposed to several minutes when ran from a SQL Agent job, i decided to run sql profiler to see what options were being set.
Turns out that MSSMS sets the following when opening a new query pane:
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET LOCK_TIMEOUT -1
SET QUERY_GOVERNOR_COST_LIMIT 0
SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
I added these into the SQL Agent job step and hey presto, the SQL Agent job now executes instantly too!
Now, which one of those would explain this bizzareness i have no idea, will start eliminating them to isolate which one it is, what would be your best guess?
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply