Solved

Slow response from Job when executing a store procedure

Posted on 2010-11-11
16
383 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 29

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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How does this SELECT query work 11 99
SQL Login 17 37
SQL Select Statement 2 20
T-SQL:  I Want "Summary"--Not "Detail" 6 19
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

947 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now