Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert, Select on same table Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 9:26 AM
Points: 13, Visits: 122
I have two jobs running. Job2 is kicked off after 3 mins of start of job1. Job1 has a long running step where it inserts and updates a table. Job2 has a step in which a query like where not exists( select from the same table udated in job1 ). Job2 which takes normally 4 to 5 mins to run, but when it has to run simultaneously with job1, it takes more than 40 minutes.
This process has been running like this since years, no blocking or locking issues are recorded and job2 never had dirty reads. I am investigating the root cause of over running of Job2. I found that job 2 waiting at the same step where it has to select data from the same table. Now I need to know if this is the reason or could be any other. Please advice.
Post #1362676
Posted Friday, September 21, 2012 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:42 PM
Points: 33,278, Visits: 15,447
You are using "same" a few times and it's not clear what you are referring to.

If there is a conflict of resources between the jobs, it could be blocking. It doesn't matter if it worked well in the past, if data size/skew changed, it's possible that the jobs are executing at the same time and slowing each other down.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362751
Posted Tuesday, September 25, 2012 12:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 2,717, Visits: 3,854
if my interpretation of your "same" references, it is highly likely that the "inserts and updates" in job (1) are locking the table. Why not move the steps in job (2) to the end of job (1)'s job. In other words, keep job (2) from starting until job (1) is complete.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1364236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse