| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2004-09-27 : 09:48:45
|
I'm pulling my hair out.There's a job running for ages and because of some logging I made, I see which sql instructions takes so long.If I run the SQL in de SQL Query Analyzer it's done in a few seconds. But when I schedule the code in a job it takes ages.There are no other processes or jobs running. SQL Agent runs with SA rights. Can anyone guess what's happening? Because I don't have a clue.. none... (most of the fields like PersNr have an index)SELECT a1.PersNr, a1.BoNr, a1.StartDate, a1.Type, a1.Code, a1.EndDate, a1.Urgent, a1.VerpleegAdres, a1.VerpleegPC, a1.VerpleegPlaats, a1.MutatieDatum, a1.ZiektePercentage, 'N' as mutatieFROM tblPeopleSoftAbs a1 LEFT JOIN tblPeopleSoftAbs a2 ON a2.PersNr = a1.PersNr AND a2.StartDate = a1.StartDate AND a2.Type = a1.TypeAND a2.BestandsDatum = @OldDateWHERE a1.BestandsDatum = @NewDateAND a2.PersNr IS NULL Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-09-27 : 11:08:02
|
| off the cuff....I'd say the sproc is causing some blocking...My Guess is also that it never comes home...right?Brett8-) |
 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-27 : 11:17:40
|
| It comes home... eventualy (after 16 hours or so).Blocking seems obvious, though there are no other query's, DTS jobs running, and it's an staging Area, so no users on it. It's also only using native data (so no connections to other databases, servers, etc.)Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-09-27 : 12:03:45
|
| is the code in a sproc or directly in the job?Are there any other steps in the job?Just a select in a job wouldn't do anything...what else is it doing?Brett8-) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-28 : 00:27:56
|
| can you provide the other steps before this tsql was invoked? |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-28 : 02:35:33
|
The DTS Job after importing text, runs an SQl Command which fires a stored procedure with some parameters (in the SQL Task I use ? ? ? for parameters.)If I run the DTS job manualy it works fine, if I run the SQL in query analyzer, it works fine. In the SQL Job I run the DTS job with an SQL command like RUNDTS 'servername' etc.Only when the SQL Job runs the DTS job the stored procedure runs ages (and the double processor is working on 50%).SQL 2000 SP3 on a Windows 2003 server (with only SQL running, nothing else)Can you understand my agony?  Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-28 : 02:39:53
|
| It's getting worse...I just checked this morning. Yesterday I decided the 'exclude' DTS as the problem.I run the stored procedure from a 1 step super simple SQl Job now (so not in the DTS job)... running for hours now. If I stop and do it manually... seconds before it's ready.If you look to the original SQL statement... it's a recursive JOIN could it possibly be the problem (I do thousands, and they alwyas work).Every night the're 100.000 new records, maybe indexing isnt' finished?It's a new SQL server installation, could it be it's installed not correctly? I see no other reason.Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-10-01 : 05:50:43
|
| Okee, I finaly pinpointed the problem.If I throm all indices away and build them up again. It works well. It beats me why (when running the jobs by hand they were fast, in a job, the're slow)So, problem solved, I thought.However, after a week, I had to change some code in the stored procedure, after that, it was dead slow again. Rebuilding the indices solved the problem.I am stumped though. The code I changed was only a group by in the stored procedure. It had nothing to do with index what so ever.The point is: I can solve the problem, but this server behaveoir isn't normal, does anyone has this kind of thing before? Or does someone recognize this? Could it be a corrupt SQL server?Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
brogan_a
Starting Member
1 Post |
Posted - 2004-10-07 : 15:03:20
|
| I have the same problem at my site. We have a third party vendor database that we run backend queries against all the time. To pull data out of the system, there are a good number of joins going on. If you run the select query from the query analyzer it takes less than a minute. If you take the exact same query and make a stored procedure out of it, then exec the sproc from query analyzer or crystal reports, etc... The query takes an hour to finished. I have traced the query, and the trace looks exactly the same which ever method you use, except the resources consumed are much higher for the longer running query. Any ideas why or what we can do to get the sproc to run normally? |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-10-07 : 17:34:52
|
| Well, it looked to me that we had a bad server installation (it was a staging area running less than a month), but what helped was removing the indices and put them back on again. After a few days it was slow again (but fast enough in the query analyzer). No I removed the indics all together and it's running ok for a week now.So my two cents are:> we have a corrupt installation (never heard of it though)> Or because of the lot of new records it has something to do with the indexes. Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-07 : 17:42:21
|
| My $0.02:DBCC DBREINDEXTara |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-10-07 : 17:52:12
|
| Hiya Tara,I read about it, and I did it before and after the job, still the same result. That's why I think somehow the installation is corrupt and it doens't explain why it runs fast in query analyzer and slow as part of a job. Or am I mistaken?Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-07 : 17:54:31
|
| Can you launch the job manually during the day so that you can track what is going on through SQL Profiler?Do you have auto update and create statistics turned on?Have you run Performance Monitor while the job is running to determine if there are any hardware bottlenecks?Tara |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-10-07 : 18:02:23
|
| Tara,I will have auto update and create statistics turned on, tomorrow (it's midnight). I'm not very used with profiler, but I couldn't see anything special happening. I run the job on daytime and the only thing I saw was that the processor was working pretty hard. The machine is pretty powerfull, has 2 GB of RAM, nothing else to do than compare 100.000 new records against 100.000 records of the day before.But I will look into 'auto update' and statistics.Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-07 : 18:10:12
|
| Are there any other jobs running at night when this job runs? If you use maintenance plans, then you might have a optimizations job that is causing this slowness. The optimizations job is really just DBCC DBREINDEX. But if these are running at the same time, then this could be the problem. Check the job schedule to cofirm.Tara |
|
|
|
|
SQL runs fast but slow in a job