Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rockmoose
SQL Natt Alfen
3279 Posts |
 Posted - 2006-12-08 : 03:50:29
|
| Hi,We have one sp that accesses a linked server.If we execute it from the "New Query Window" in SSMS, it runs in a couple of seconds.If we execute the same proc as a step in a Sql Server Agent Job, it is incredibly slow, in fact it does not seem to finish. And it eats up a lot of CPU.The linked server is a SQL2000 server.We set it up "collation compatible"We use the same user in both scenarios.Has anyone else seen this kind of problem before?rockmoose |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 04:17:08
|
| Are you sure that it is the SProc that is slow?Maybe change the STEP in the job to Log the Start/End too?INSERT INTO MyTable(MyTime, MyDescription) SELECT GetDate(), 'Job Start'EXEC dbo.MySProcINSERT INTO MyTable(MyTime, MyDescription) SELECT GetDate(), 'Job End'that type of thing ...Kristen |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-08 : 08:26:12
|
Thanks Kristen.I tried that, it confirmed that it is indeed the proc that stalls when executed as a job-step.It contains a rather nasty view that joins 3 linked tables in a mix of right and left joins. :[I rewrote the view and now it works.The bad thing is this:I now have 2 versions of the proc, 1 using the old view, and another one using the new optimized view.From the "New Query Window" both execute in 3-5 seconds.As a job step, the optimized version works (3-5 seconds), the old version does not work at all!I remember we had problems with this particular job a couple of months ago, but then setting the linked server to "collation compatible" solved the problem.cheers all!rockmoosePS.What's the new acronym for "New Query Window" in SSMS?, as QA is no more  |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-12-08 : 08:35:28
|
| Did you try recompiling the old view? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 08:37:25
|
| +1 to recompile+1 to QA as the "generic term" for the New Fangled Thingie |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-08 : 10:09:38
|
Thanks for the excellent suggestion  I already did alter view/proc on all the views and the proc. That should have taken care of the recompile...I was going to recreate the linked server as well, but did not find any obvious way of scripting the mapped logins.So I went down the path of rewriting the sql of the original view (you don't want to see it).When executing in QA the statement is a SQL:Batch, from the Job it's a RPC.And who knows what kind of plans are generated and what things are going on under the hood when it comes to linked servers.They work well - basically - but sometimes they just don't rock the way one wants or expects.And they are extremely collation sensitive, imo.< "+1 to QA as the "generic term" for the New Fangled Thingie"What about all the newbies, won't they get confused?rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 11:07:21
|
| "What about all the newbies, won't they get confused?"And this is a problem is it?!! |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-08 : 11:28:13
|
quote:
Originally posted by Kristen
"What about all the newbies, won't they get confused?"And this is a problem is it?!!
We can dismiss it over a beer rockmoose |
|
|
|
|
|
|
|
|
QA is fast, Job is slow