Slow response from Job when executing a store procedure

Good Day

I cant understand why my sql server 2000 job takes +-6min to execute a store procedure from  a step.

If I run the procedure threw query analyser it takes 30seconds.

What could be the problem.
henryreynoldsAsked:
Who is Participating?
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
Mmmm...Using the above script, attempt generating a second job with a different name and see if the problem perdures.  Simply edit the script and replace all occurrences of 'FLEX TEST' by 'FLEX TEST 2'
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
My first thought is that you might want to update the statistics on the database, especially if you don't have statistics updating as part of a maintenance plan.
At a non-critical time, you can try a 'sp_updatestats' while using the database in question.

0
 
henryreynoldsAuthor Commented:
I am running the job on a none production database, the are currently no activity on the database
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HumpdyCommented:
try recreating the stored procedure WITH RECOMPILE
0
 
subhashpuniaCommented:
Does the sp take 30 seconds for first time in qa?
Are you running the sp in qa with same parameter values that are passed by job?
Is the job running at the time when server is more busy?

0
 
deightonConnect With a Mentor progCommented:
try something like

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

but you only need to do that once to rebuild the execution plan

http://articles.techrepublic.com.com/5100-10878_11-5662581.html
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<What could be the problem.>>

> Another step taking 5.5min
> An msdb corruption
> A slow Active Directory for the account running the job.
0
 
henryreynoldsAuthor Commented:
Hi

I have tried "with recompile" but still the same. In query analyser 53sec and in Job 11min.

I want to mention that I am using a cursor.

There are no other jobs or activity running on the pc,
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please post the job script.  If you are on 2005 and above, you should be able to generate it.  Also who owns the job ? I mean what login/user authenticates it?
0
 
henryreynoldsAuthor Commented:
Hi Here is the script

sa is the owner
-- Script generated on 11/12/2010 12:48 PM
-- By: DTPOSTOFFDEV01\post_admin
-- Server: (local)

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'FLEX TEST')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''FLEX TEST'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'FLEX TEST' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'FLEX TEST', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step1', @command = N'exec dt_Get_TEB_FlexCube_Settlement_Transactions', @database_name = N'Post_Back_Office', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:

Open in new window

0
 
HumpdyCommented:
can you change the sql agent service account to use the same a/c you're using in Query mode
and see what happens
0
 
henryreynoldsAuthor Commented:
okay I will try
0
 
henryreynoldsAuthor Commented:
I tried bove no change. Could it be because of the cursor
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please change the database in the job step to 'master' instead of 'Post_Back_Office' and change the call to 'exec Post_Back_Office..dt_Get_TEB_FlexCube_Settlement_Transactions'

Make sure the Agent account has proper credentials to launch all features within the procedure.  It may well be that one of the subprocesses fails.but there is no way to make sure of it without seeing what the proc does.  Please post the script of the 'dt_Get_TEB_FlexCube_Settlement_Transactions' procedure

Also

<<can you change the sql agent service account to use the same a/c you're using in Query mode
and see what happens>>
The user accounts usually do not have proper credentials to run the Agent.  
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I tried bove no change. Could it be because of the cursor>>
Please post the proc script...
0
 
mastooCommented:
It is rare but we have had a bad execution plan in cache.  Execution plan's are per connection properties so you can be getting a different plan from the job as opposed to query analyzer.  The easy way to test/fix this is to run a dbcc freeproccache.  Be aware this acts on the entire server, so there might be some minor momentary hit on performance as it will be clearing all cached plans.  If you feel comfortable doing so, run this then try the agent job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.