Solved

Slow response from Job when executing a store procedure

Posted on 2010-11-11
16
404 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:henryreynolds
  • 5
  • 5
  • 2
  • +4
16 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 34111852
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
 

Author Comment

by:henryreynolds
ID: 34111882
I am running the job on a none production database, the are currently no activity on the database
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34111886
try recreating the stored procedure WITH RECOMPILE
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 6

Expert Comment

by:subhashpunia
ID: 34111892
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
 
LVL 18

Assisted Solution

by:deighton
deighton earned 125 total points
ID: 34113115
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34114882
<<What could be the problem.>>

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

Author Comment

by:henryreynolds
ID: 34118140
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34119070
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
 

Author Comment

by:henryreynolds
ID: 34119411
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
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 125 total points
ID: 34119511
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34119526
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
 

Author Comment

by:henryreynolds
ID: 34119585
okay I will try
0
 

Author Comment

by:henryreynolds
ID: 34119601
I tried bove no change. Could it be because of the cursor
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34119649
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34119657
<<I tried bove no change. Could it be because of the cursor>>
Please post the proc script...
0
 
LVL 21

Expert Comment

by:mastoo
ID: 34119869
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question