we are facing problem with Job performance it is taking hours to get completed :
Requirement was to create a job which can run every week and can archive the old records (older then 365 days i.e. around 40000 Records) into an archive database and after Successful archival it deletes those from the live database.
I have created a scheduled job which calls "MASTER_SP" stored procedure; MASTER_SP Stored Procedure in turn calls two more stored procedures "ARCHIVE_SP" and "PURGE_SP" in a single transaction. ARCHIVE_SP Stored procedure archives the data from LIVE_DATABSE to ARCHIVE_DATABASE then PURGE_SP stored procedure Deletes that data from LIVE_DATABSE
We took a database dump from the live environment and restored on testing environment, made changes to job and started the job for 40000 records(first run), job dint get Completed in 13 hours so I cancelled the job run.
Then we tried with these tests and here are the statistics
Second run 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.
Sixth run 283000: job gets completed in 23 mins.
We are facing the problem when job is run for first time on the restored database with huge number of records (i.e. around 40000 Records), CPU utilization is reaching to 100% on this machine, Here SQLSERVER process is utilizing CPU a lot.
On the same restored database If we run the job with less number of records say 2500, then this job(i.e 2500 Records) gets completes in 12 minutes, and rest of the successive job runs(i.e. for 40000,113000, 283000) gets completed without any problem. statistics are shown above
Can anyone say the exact reason why it creates problem on first run, please come up with some suggestions, so that i can implement them and move code into production.
VIEWS ON THIS POST
Monday 26th November 2012