Question regarding SQL blocking of jobs

[inutelinside]

I'm running two jobs in our production environment.

The first job just selects records in the SQL Database while the second job updates records in the SQL Database. Both jobs run for at least 5 hours because of the amount of data involved. There is an overlap in their schedule that is why when the second job commences it stops because it is being blocked by the first job.

Is there a way I could modify my first job (the job that selects records) so that it will no block the second job? Thank you very much.

 

[JayKusch]

Are you using the NOLOCK option on each of the tables you are querying w/ your SELECT statement(s)???

This may help!

Thanks

J. Kusch

 

[Catadmin]

If you know for a fact that the jobs overlap, and are going to continue to overlap, couldn't you just reschedule one of them to start either earlier or later?

Try this, of course, if Jay's suggestion doesn't help you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[inutelinside]

I cannot do anything about the schedule. It seems that my back is against the wall. Below is my script. Can this still be optimized? Thank you very much. Note: The script below processes millions of records...




DECLARE @tblSubTot table (request_ID varchar(9), request_total money)
INSERT @tblSubTot
SELECT tblSourceUsage.request_id, SUM(tblSourceUsage.cost) AS request_total
FROM dbo.erwinrequest_erwin_tbl tblRequest WITH (NOLOCK) LEFT OUTER JOIN dbo.source_usage_erwin_TBL tblSourceUsage WITH (NOLOCK)
ON tblSourceUsage.request_id = tblRequest.request_id
WHERE tblRequest.request_date >= '9/1/2003' and tblRequest.request_ID <> 0
GROUP BY tblSourceUsage.request_id

DECLARE @tblSubTot2 table (personnel_ID varchar(9), totals money, request_ID varchar(5))
INSERT @tblSubTot2
SELECT tblAssigned.personnel_ID, ((tblTime.hours_worked1 + tblTime.hours_worked2 + tblTime.hours_worked3 + tblTime.hours_worked4
+ tblTime.hours_worked5 + tblTime.hours_worked6 + tblTime.hours_worked7 + tblTime.hours_worked8
+ tblTime.hours_worked9 + tblTime.hours_worked10 + tblTime.hours_worked11 + tblTime.hours_worked12
+ tblTime.hours_worked13 + tblTime.hours_worked14 + tblTime.hours_worked15 + tblTime.hours_worked16) *
tblAssigned.researcher_rate) AS TotalCostUSDollars, tblAssigned.request_ID
FROM dbo.assigned_to_request_erwin_TBL tblAssigned WITH (NOLOCK)
RIGHT OUTER JOIN dbo.time_sheet_erwin_TBL tblTime WITH (NOLOCK)
ON tblAssigned.request_ID = tblTime.request_ID
JOIN dbo.erwinrequest_erwin_tbl tblRequest WITH (NOLOCK)
ON tblTime.request_ID = tblRequest.request_ID
WHERE tblAssigned.personnel_ID = tblTime.personnel_ID
AND tblAssigned.personnel_ID <> ''
AND tblRequest.request_date >= '9/1/2003'


INSERT @tblSubTot2
SELECT tblRequest.originalRep_ID, ((tblTime.hours_worked1 + tblTime.hours_worked2 + tblTime.hours_worked3 + tblTime.hours_worked4
+ tblTime.hours_worked5 + tblTime.hours_worked6 + tblTime.hours_worked7 + tblTime.hours_worked8
+ tblTime.hours_worked9 + tblTime.hours_worked10 + tblTime.hours_worked11 + tblTime.hours_worked12
+ tblTime.hours_worked13 + tblTime.hours_worked14 + tblTime.hours_worked15 + tblTime.hours_worked16) *
tblRequest.originalRep_rate) AS TotalCostUSDollars, tblRequest.request_ID
FROM erwinRequest_erwin_TBL tblRequest WITH (NOLOCK) RIGHT OUTER JOIN dbo.time_sheet_erwin_TBL tblTime WITH (NOLOCK)
ON tblRequest.request_ID = tblTime.request_ID
JOIN dbo.erwinrequest_erwin_tbl WITH (NOLOCK)
ON tblTime.request_ID = tblRequest.request_ID
WHERE tblRequest.originalRep_ID = tblTime.personnel_ID AND personnel_ID <> ''
AND tblRequest.request_date >= '9/1/2003'
AND NOT EXISTS (SELECT tblSubTot2.personnel_ID
FROM @tblSubTot2 tblSubTot2
WHERE tblRequest.originalRep_ID = tblSubTot2.personnel_ID )

DECLARE @tblSubTot3 table (totals money, request_ID varchar(5))
INSERT @tblSubTot3
SELECT SUM(totals), request_ID
FROM @tblSubTot2
GROUP BY request_ID


SELECT LTRIM(RTRIM(b.customer_fname)) + ' ' +
LTRIM(RTRIM(b.customer_initial)) + ' ' +
LTRIM(RTRIM(b.customer_lname)) as 'CUSTOMER',
a.request_id as 'REQUEST ID',
LTRIM(RTRIM(CAST(DATEPART(mm, a.request_date) AS char(2)))) + '/' +
LTRIM(RTRIM(CAST(DATEPART(dd, a.request_date) AS char(2))))+ '/' +
LTRIM(RTRIM(CAST(DATEPART(yyyy, a.request_date)AS char(4)))) AS 'REQUEST DATE',
b.level_name as 'CUSTOMER LEVEL',
LTRIM(RTRIM(c.requestedfor_fname)) + ' ' +
LTRIM(RTRIM(c.requestedfor_initial)) + ' ' +
LTRIM(RTRIM(c.requestedfor_lname)) as 'REQUESTED FOR',
c.level_name as 'REQUESTED FOR LEVEL',
d.first_name + ' ' + d.middle_initial + ' ' + d.last_name as 'RESEARCHER',
e.JobCDDescr as 'population-CUSTOMER LEVEL',
e.GeographicUnitDescription as 'population-CUSTOMER GEOGRAPHY',
e.SESLevel2Description as 'population - OPERATING GROUP',
e.CompetencyGroupDescription as 'population - CAPABILITY GROUP',
e.BusinessOrgDescription as 'population - WORKFORCE',
a.chargeable_time_spent_total AS 'HOURS BILLED',
CAST (ROUND(i.totals,0) AS FLOAT) *
CAST (ROUND(f.conversion_factor,0) AS FLOAT) AS 'COST IN LOCAL CURRENCY',
g.currency_name AS 'CURRENCY TYPE',
h.request_total AS 'CONTENT CHARGES'
FROM erwinRequest_erwin_TBL a WITH (NOLOCK) JOIN customer_erwin_TBL b WITH (NOLOCK) ON a.customer_ID = b.customer_ID
JOIN requested_for_erwin_TBL c WITH (NOLOCK) ON a.requestedfor_ID = c.requestedfor_ID
LEFT JOIN person_KRIS_TBL d WITH (NOLOCK) ON a.originalRep_ID = d.personnel_ID
LEFT JOIN population e WITH (NOLOCK) ON e.personnelnumber = b.personnel_id
LEFT JOIN office_KRIS_TBL f WITH (NOLOCK) ON a.office_ID = f.office_ID
LEFT JOIN currency_KRIS_TBL g WITH (NOLOCK) ON f.currency_ID = g.currency_ID
LEFT JOIN @tblSubTot h ON a.request_ID = h.request_ID
LEFT JOIN @tblSubTot3 i ON a.request_ID = i.request_ID
WHERE a.request_date >= '9/1/2003' and a.request_ID <> ''
ORDER BY a.request_ID, a.request_date

 

[Catadmin]

Check your Estimated Execution plan to see if that can be optimized any further. The only thing I see off the bat is your last two joins don't use the NoLock hint. Look for anything which says Index Scan or Table Scan. Those are bad and will give you an idea where to start.

At this point, I would recommend running SQL profiler while these jobs are running. Sounds to me like they are competing for Processor threads. Are there any other things running (Exchange, a print server, etc.) on this server which might be stealing CPU time during these jobs?

Also, run a Windows trace to check Processor QUEUE length, RAM availability and check for any I/O issues.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???