October 28, 2014 at 1:38 am
Hi Team,
I have report processing application hosted on multiple application servers, which reads a row from queue table using updlock, readpast logic. I am experiencing block inside stored procedure in update statement which is inside Begin transaction -commit section. I am looking for help to fix block issue. I am posting my stored procedure which creating block in database which hindering performance. The highlighted portion is causing issue.
CREATE PROCEDURE [dbo].[ReportQueue]
(
@Server char(50),
@Record int OUTPUT,
@RepType int OUTPUT,
@ReportidR int OUTPUT
)
AS
DECLARE @TimeOut table (record int)
DECLARE @RunningJobs table (record int)
DECLARE @OpCoJobs table (opco char(5),jobsrunning int)
DECLARE @PriorityJob table (priority char(30), record int, saveas varchar(100))
DECLARE @Throttle int
DECLARE @ReportID int
DECLARE @PreOK bit
DECLARE @priority char(30)
DECLARE @OpCoLimit int
DECLARE @GoodToGo bit
DECLARE @FoundCount int
SET @Throttle = 1
SET @OpCoLimit = 5
SET @GoodToGo = 0
SET @ReportID = 0
/* Fill in any blank end dates */
UPDATE rptschedule
SET lastrun = CONVERT(datetime,CONVERT(char,DATEADD(year,50,GETUTCDATE()),112))
WHERE ISNULL(lastrun,'1/1/1900') < firstrun
/* Check for hung report processes */
BEGIN TRANSACTION ReportHung
INSERT INTO @TimeOut
SELECT record FROM tblqueue WITH (UpdLock, ReadPast) INNER JOIN tbldef ON tblqueue.reportid = tbldef.reportid
WHERE DATEADD(minute,tbldef.runningtime,[started]) < GETUTCDATE()
AND [started] > '1/1/1900'
AND [server] IS NOT NULL AND [server] <> ' '
INSERT INTO rptdone (scheduleid,reportid,version,saveas,runcode,inisettings,fortime,
startdt,enddt,distribute,priority,server,started,finished,submitted,error,errormsg,official,location,
adhocentity,adhocentitytype,[xml],whois)
SELECT scheduleid,tblqueue.reportid,tblqueue.version,tblqueue.saveas,runcode,inisettings,fortime,
startdt,enddt,distribute,priority,server,started,GETUTCDATE() AS finished,submitted,
1 AS error,
'Timeout Error. The job took longer than ' +
RTRIM(LTRIM(CONVERT(char(7),tbldef.runningtime))) + ' minutes.' AS errormsg,
official,location,adhocentity,adhocentitytype,[xml],whois
FROM tblqueue INNER JOIN tbldef ON tblqueue.reportid = tbldef.reportid
WHERE record IN (SELECT record FROM @TimeOut)
DELETE FROM tblqueue WHERE record IN (SELECT record FROM @TimeOut)
COMMIT TRAN ReportHung
/* Check to see how many jobs are already running on the calling server */
INSERT INTO @RunningJobs (record)
SELECT record FROM tblqueue
WHERE server = @Server
/* If this server can pick up a job, determine which one to pick up */
If @@ROWCOUNT<@Throttle
BEGIN
BEGIN TRANSACTION ReportGrab
/* Determine # Of jobs running per OpCo */
INSERT INTO @OpCoJobs (opco,jobsrunning)
SELECT location,COUNT(*) FROM tblqueue WHERE [server]<>' ' AND [server] IS NOT NULL GROUP BY location
/* Get the highest priority job for OpCo with least # of jobs running */
INSERT INTO @PriorityJob (priority,record,saveas)
SELECT TOP 1 tblqueue.priority,tblqueue.record,tblqueue.saveas
FROM tblqueue WITH (UpdLock, ReadPast) INNER JOIN tbldef ON tblqueue.reportid = tbldef.reportid
LEFT JOIN @OpCoJobs ON tblqueue.location = opco
WHERE (tblqueue.[server] = ' ' OR tblqueue.[server] IS NULL)
AND (onlyserver = ' ' OR onlyserver=@Server OR onlyserver IS NULL)
AND (jobsrunning<@OpCoLimit OR jobsrunning IS NULL)
ORDER BY CASE WHEN tblqueue.reportid=1958 THEN 0 ELSE 1 END,ISNULL(jobsrunning,0),tblqueue.priority,tblqueue.record
SET @FoundCount = @@ROWCOUNT
SELECT @ReportID = MIN(record) FROM @PriorityJob
IF @FoundCount>0
BEGIN
/* Check to make sure that there isn't another job running with the same saveas */
SELECT tblqueue.[server] FROM tblqueue,@PriorityJob pj WHERE tblqueue.saveas=pj.saveas AND [server]<>' ' AND [server] IS NOT NULL
IF @@ROWCOUNT=0
BEGIN
/* Check pre-requisites */
EXEC PreRequisites @ReportID,0,@PreOK OUTPUT
IF @PreOK = 1
SET @GoodToGo = 1
END
IF @GoodToGo = 1
BEGIN
[highlight=#ffff11]/* If it is good to go mark the job as started */
UPDATE tblqueue SET [server] = @Server, [started] = GETUTCDATE()
WHERE ([server]=' ' OR [server] IS NULL)
AND record = @ReportID
IF @@ROWCOUNT=0 DELETE FROM @PriorityJob
PRINT 'Good To Go'[/highlight]
END
ELSE
BEGIN
/* Not good to go - move to bottom of the queue */
SELECT @priority = MAX(priority) FROM tblqueue
UPDATE tblqueue SET [server]='',
priority =
STUFF(@priority,1,8,LEFT(CONVERT(char,CONVERT(int,LEFT(@priority,8))+1),8))
WHERE record = @ReportID
DELETE FROM @PriorityJob
PRINT 'Not Good to Go'
END
END
COMMIT TRANSACTION ReportGrab
END
SET @Record = 0
SET @RepType= 0
SET @ReportidR = 0
SELECT * FROM @PriorityJob
IF @@ROWCOUNT>0 AND @ReportID > 0 AND @ReportID IS NOT NULL
BEGIN
SET @Record = @ReportID
SELECT @ReportidR = tblqueue.reportid, @RepType=charindex('.prg',tbldef.)
FROM tblqueue INNER JOIN tbldef ON tblqueue.reportid = tbldef.reportid
WHERE tblqueue.record=@Record
END
GO
Regards,
Naresh
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply