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 |
|
atiqraza85
Starting Member
3 Posts |
 Posted - 2010-01-25 : 16:26:55
|
| Hello everyone, I have a stored procedure that loads data in to the Data warehouse on a daily basis. this procedure was normally scheduled by a 'JOB' that would run it daily. however the time involved has been increasing and now it takes up to 4 hours. however if i were to run the stored procedure manually, it takes only 1 minute (which is what is used to take). I deleted the job and created another one, which runs the 'Stored Procedure' and the same problem happens. for some reason when i schedule a JOB in SQL management studio to run a stored procedure it takes hours to run, however i know that the code is correct and should take a minute or two, which is what happens when i run it manually. I can not run it every day manually. is there some reason for this ?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-26 : 00:01:06
|
| is it that job has only one step executing sp or are there any preceding or suceeding steps? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 03:14:04
|
| Perhaps the SProc is not getting recompiled when the Stats for the tables change, or the Stats for the tables are not being updated regularly? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-26 : 08:36:46
|
| Could you capture the Execution Plan of both ways of running the stored procedure i.e 1)through the job 2)through executing in SSMS , and post the plans . Comapre the 2 and make sure both are using the same paths and indicesJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
atiqraza85
Starting Member
3 Posts |
Posted - 2010-01-29 : 14:30:10
|
| How would i be able to capture the execution plan of them ? |
|
|
|
atiqraza85
Starting Member
3 Posts |
Posted - 2010-01-29 : 15:12:46
|
| P.s the job is running only the stored procedure... nothing else.. i even created a new job to run the stored procedure and it is still the same issue. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 02:21:15
|
"How would i be able to capture the execution plan of them ?"SET SHOWPLAN_TEXT ONGO*** YOUR QUERY HERE ***SET SHOWPLAN_TEXT OFFGO |
|
|
|
|
|
|
Stored Procedure not running as a ' SCHEDULED JOB'