SQL Server Performance Forum – Threads Archive

Scheduled job performance in first run

Subject: Scheduled job performance in First Run I am facing the problem with Job performance it is taking hours to get completed <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />. Requirement description: Requirement was to create a job which can run every week and can archive the old record (older then 365 days) into an archive database and after successful archival it deletes those from the live database. Implementation Description: I have created a scheduled job which calls "MASTER" stored procedure putting number of days as input. MASTER Stored Procedure in turn calls two more stored procedures "ARCHIVE" and "PURGE" in a single transaction. ARCHIVE Stored procedure archives the data from LIVE_DATABSE to ARCHIVE_DATABASE then PURGE stored procedure Deletes that data from Live Database. Problem Description: The job runs fine with small number of records. Say if we need to archive/purge only 100 records it takes only few seconds. But our requirement is to archive/purge at least 40000 records in every run. So I took a dump from the live environment and create a database on testing environment and made the changes and started the job. First run for 41000 records: job dint get completed in 13 hours so I cancelled the job run. Second run for 2500 records: job gets completed in 12 mins and 30 secs. Third run for 11000 records: job gets completed in 1 min and 30 secs. Fourth run 41000 records: Job gets completed in 1 min and 47 secs. Fifth run 113000: job gets completed in 4 mins and 30 secs. I thought there might be some transient network problem in first run so ignore that statistic and considered the testing from second run. But to confirm this I again did the testing. I restored the dump again First run for 29000 records: job dint get completed in 3 hours so I cancelled the job run. Second run for 2600 records: job gets completed in 22 mins and 30 secs. Third run for 27000 records: job gets completed in 2 min and 30 secs. Fourth run 41000 records: Job gets completed in 1 min and 47 secs. I restored the dump again I manually deleted 2600 records (considering that problem is with these records) from the database and tried to run the job with 29000 records: Job dint get complete in 2 hours as CPU utilization reaches up to 100% so I cancelled the job. It indicates that the problem doesn#%92t lies in those records. On the same restored database I run ARCHIVE_APPROVAL_HISTORY stored procedure and then I run the PURGE_APPROVAL_HISTORY stored procedure to archive-purge 29000 records. First one took only 43 secs and second one took only 2 mins. I restored the dump again I directly ran the Master stored procedure for 29000 records. It got stuck and got completed in 4 hours. I restored the dump again And I ran the ARCHIVE and PURGE Stored procedure one after other both got completed in 1 min and 1 min 20 sec respectively. It concludes that whenever I am running ARCHIVE and PURGE Stored procedure separate then it not taking time but when running together in a single transaction from MASTER stored procedure its taking time. But strangely when I run job for first time on freshly restored database it is taking time and second time onwards it#%92s running very fast. Time to time I checked blocking using SP_who2 command and never get any blocking. I have also tried SET NOCOUNT ON but it is not making any difference

————- JOB SCRIPT————-
DECLARE @RC int
DECLARE @ErrorCode int
DECLARE @ErrorDescription varchar(200)
DECLARE @noofdaysforApproval int
SET @ErrorCode = NULL
SET @ErrorDescription = NULL
SET @noofdaysforApproval =185
EXEC @RC = [WFWASUAT].[dbo].[Master_Archive_Purge_Approval_History] @ErrorCode OUTPUT , @ErrorDescription OUTPUT , @noofdaysforApproval
————- Strored Procedures—————-
/*
Name: Master_Archive_Purge_Approval_History
Description: To archive and then purge Approval & Audit History from Application Database
Author: Sanjeev K Khare
Date: 05-OCT-2006
Version Log :
*/ CREATE PROCEDURE [dbo].[Master_Archive_Purge_Approval_History]
@ErrorCode int OUTPUT,
@ErrorDescription varchar(200) OUTPUT,
@noofdaysforApproval int
AS
BEGIN TRAN — Archiving approval history
EXEC SP_Archive_Approval_History @@ERROR, @ErrorDescription OUTPUT, @noofdaysforApproval
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN
print ‘Error in Archiving Approval Data’
GOTO HANDLE_ERROR
END
— Purging Approval History
EXEC SP_Purge_Approval_History @@ERROR, @ErrorDescription OUTPUT, @noofdaysforApproval
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN
print ‘Error in Purging Approval History’
GOTO HANDLE_ERROR
END COMMIT TRAN RETURN 0 HANDLE_ERROR:
ROLLBACK TRAN —–
EXEC SP_GetMessage @@ERROR, @ErrorDescription OUTPUT
RETURN @ErrorCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO /*
Name: SP_Archive_Approval_History
Description: To Archive Approval History from Application Database
Author: Sanjeev K Khare
Modification Log: V1.0
*/ CREATE PROCEDURE [dbo].[SP_Archive_Approval_History]
@ErrorCode int OUTPUT,
@ErrorDescription varchar(200) OUTPUT,
@noofdays int
AS
–DECLARE @noofdays int
DECLARE @WorkflowID varchar(100)
DECLARE @moddate datetime
DECLARE @currdate datetime SET @currdate = getDate() /* StatusIDStatusName
=======================
1PROCESS
2APPROVED
3REJECTED
4STARTED
5CANCELLED
6REMINDER
7AFTER EVERY APPROVAL
8AFTER ALL APPROVALS
10RESTARTED
11WF_PROCESS
12HOLD */
–NEW LOGIC STARTS HERE
— Insert workflowid into HISTORY_TEMP table
DELETE from history_temp
INSERT INTO history_temp select distinct WORKFLOWID from history where statusid in (2,3,5,10) AND DATEDIFF(day, HISTORY.MODIFIEDDATE,@currdate)>@noofdays SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END BEGIN
/*– Insert workflowid into HISTORY_TEMP table
INSERT INTO history_temp select * from history where statusid in (2,3,5,10) AND DATEDIFF(day, HISTORY.MODIFIEDDATE,@currdate)>@noofdays*/ /*INSERT INTO CMARCHIVE.dbo.WF_APPROVER_SUBSTITUTE SELECT * FROM WF_Approver_Substitute WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.WF_APPROVER_SUBSTITUTE Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.WF_APPROVER_SUBSTITUTE SELECT A.* FROM WF_Approver_Substitute A, History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.WF_APPROVER_SUBSTITUTE After Insert ‘ + CAST(getDate() as varchar) SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.History SELECT * FROM History WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.History Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.History SELECT A.* FROM History A, History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.History After Insert ‘ + CAST(getDate() as varchar)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.WF_Transaction_Approvers SELECT * FROM WF_Transaction_Approvers WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.WF_Transaction_Approvers Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.WF_Transaction_Approvers SELECT A.* FROM WF_Transaction_Approvers A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.WF_Transaction_Approvers After Insert ‘ + CAST(getDate() as varchar)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.WF_Transaction_Groups SELECT * FROM WF_Transaction_Groups WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.WF_Transaction_Groups Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.WF_Transaction_Groups SELECT A.* FROM WF_Transaction_Groups A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.WF_Transaction_Groups After Insert ‘ + CAST(getDate() as varchar)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.WF_Payment_Transaction SELECT * FROM WF_Payment_Transaction WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.WF_Payment_Transaction Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.WF_Payment_Transaction SELECT A.* FROM WF_Payment_Transaction A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.WF_Payment_Transaction After Insert ‘ + CAST(getDate() as varchar) SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.ModifyDetails SELECT * FROM ModifyDetails WHERE WORKFLOWID IN(SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.ModifyDetails Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.ModifyDetails SELECT A.* FROM ModifyDetails A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.ModifyDetails After Insert ‘ + CAST(getDate() as varchar) SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.WF_Transaction SELECT *,@currdate FROM WF_Transaction WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.WF_Transaction Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.WF_Transaction SELECT A.*,@currdate FROM WF_Transaction A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.WF_Transaction After Insert ‘ + CAST(getDate() as varchar) SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END /*INSERT INTO CMARCHIVE.dbo.Temp_WF_Transaction SELECT * FROM Temp_WF_Transaction WHERE WORKFLOWID IN (SELECT WORKFLOWID from History_temp)*/ –print ‘CMARCHIVE.dbo.Temp_WF_Transaction Before Insert ‘ + CAST(getDate() as varchar)
INSERT INTO CMARCHIVE.dbo.Temp_WF_Transaction SELECT A.* FROM Temp_WF_Transaction A,History_temp B WHERE B.WORKFLOWID = A.WORKFLOWID
–print ‘CMARCHIVE.dbo.Temp_WF_Transaction After Insert ‘ + CAST(getDate() as varchar)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END END –NEW LOGIC ENDS HERE HANDLE_ERROR:
EXEC SP_GetMessage @@ERROR, @ErrorDescription OUTPUT
RETURN @ErrorCode GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO CREATE PROCEDURE [dbo].[SP_Purge_Approval_History]
@ErrorCode int OUTPUT,
@ErrorDescription varchar(200) OUTPUT,
@noofdays int
AS
–DECLARE @noofdays int
DECLARE @WorkflowID varchar(100)
DECLARE @moddate datetime
DECLARE @currdate datetime SET @currdate = getDate()
/* StatusIDStatusName
=======================
1PROCESS
2APPROVED
3REJECTED
4STARTED
5CANCELLED
6REMINDER
7AFTER EVERY APPROVAL
8AFTER ALL APPROVALS
10RESTARTED
11WF_PROCESS
12HOLD */ –BEGIN –dumping all the data to be purged from history to history_temp table
–dumping starts
–INSERT INTO history_temp select * from history where statusid in (2,3,5,10) AND DATEDIFF(day, HISTORY.MODIFIEDDATE,@currdate)>@noofdays
–dumping ends — This insert statment creates a log of all the workflowIds which are getting Purged DELETE FROM WF_Approver_Substitute WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END DELETE FROM History WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp) SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END
DELETE FROM WF_Transaction_Approvers WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END DELETE FROM WF_Transaction_Groups WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END DELETE FROM WF_Payment_Transaction WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END DELETE FROM ModifyDetails WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END DELETE FROM WF_Transaction WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN
GOTO HANDLE_ERROR
END DELETE FROM Temp_WF_Transaction WHERE WorkFlowID IN (SELECT WORKFLOWID from History_temp)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode != 0
BEGIN GOTO HANDLE_ERROR
END
–END
HANDLE_ERROR:
—-ROLLBACK TRAN —–not required bujagonda
EXEC SP_GetMessage @@ERROR, @ErrorDescription OUTPUT
RETURN @ErrorCode GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I don’t have time to look closely at your code, but do you have appropriate indexes, and are the indexes being rebuilt often? Also, are you using the full or simple recovery model? If you are using full, you might consider switching to simple, run your job, and then switch back to full, then do your full backup. This way, you reduce logging and save on disk IO, helping to boost performance. ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com

Thanks Brad for your suggestion, Database recovery model is SIMPLE only.As you suggested I have rebuilt the indexes of all the 8 tables using DBCC DBREINDEX(‘TableName’,”,90). But the first job run performance is still poor.Its taking hours and CPU utilization is reaching up to 100%. But very next run for same number of records is getting completed within 1 min and few seconds <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.

]]>