[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Slow response from Job when executing a store procedure

Posted on 2010-11-11
16
Medium Priority
?
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 500 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 500 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 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