Solved

Slow response from Job when executing a store procedure

Posted on 2010-11-11
16
374 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 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.

760 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

20 Experts available now in Live!

Get 1:1 Help Now