1

I'm running the below query every hour to cleanup job history in the sysjobhistory table for all jobs that execute every 10 seconds. The query aims to delete 100k rows of job history that is older than 12 hours for 10 second jobs.

Occasionally, it gets blocked by jobs trying to insert their output to the sysjobhistory table. The blocking continues until the query is killed.

Why are other processes still able to write to the table (and block my query) if the Top 100000 would take a table lock? Would running the query in a transaction prevent it from being blocked?

WITH cte (run_date,run_duration,run_time,start_time,end_time)
 AS (
SELECT TOP 100000
 sh.run_date
, sh.run_duration
, sh.run_time
, CASE sh.run_date
WHEN 0
THEN NULL
ELSE CAST(CAST([run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS start_time
, dateadd(ss, --run_duration

substring(cast(100000000 + run_duration AS NVARCHAR), 2, 4) * 60*60 -- Hours
+ substring(cast(100000000 + run_duration AS NVARCHAR), 6, 2) * 60 -- Minutes
+ substring(cast(100000000 + run_duration AS NVARCHAR), 8, 2)  -- Seconds
,CASE [run_date]
            WHEN 0
                THEN NULL
            ELSE CAST(CAST([run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
            END) end_time
FROM msdb.dbo.sysjobhistory sh
WHERE EXISTS (SELECT 1 FROM msdb.dbo.sysjobs sj WHERE sh.job_id = sj.job_id
AND EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules sjs WHERE sjs.job_id = sj.job_id
AND EXISTS (SELECT 1 FROM msdb.dbo.sysschedules ss WHERE ss.schedule_id = sjs.schedule_id 
AND ss.freq_subday_type = 2
AND ss.freq_subday_interval = 10 ))) 
AND CAST(CAST([run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) < dateadd(hh,-12,getdate())
)
DELETE FROM cte 
2
  • afaik will locking escalate to table lock when more than ~4k records are being touched. If you want to prevent that, you want to delete in chunks which are below that number.
    – Peter
    Commented Jan 8 at 15:39
  • I don't mind if the query locks the table. I'm wondering why it's getting endlessly blocked by inserts if it is taking a table lock.
    – Marcus
    Commented Jan 9 at 6:52

1 Answer 1

0

Couple of suggestions that probably could improve both read and delete performance in your case,

  1. Convert the read query to use join instead of EXISTS, see the cost improvement in below plan comparision. May be you have chosen that approach to perform CTE deletes.

    enter image description here

  2. Update your query to following and see if it improves delete performance,

    • Used temp table, for primary key clustered index delete on instance_id from sysjobhistory for better performance.
    • Used batching to delete 5K per batch and loop through to reduce lock duration/period to unblock other workload on the table.
 -- Create staging temp table
 DROP TABLE IF EXISTS #sysjobhistory_todelete;
 GO
 CREATE TABLE #sysjobhistory_todelete
 (
     instance_id INT PRIMARY KEY CLUSTERED
 );
 GO

 -- Stage data based on criteria
 INSERT INTO #sysjobhistory_todelete
 SELECT DISTINCT
        sh.instance_id
 FROM msdb.dbo.sysjobhistory sh
    JOIN msdb.dbo.sysjobs sj ON sh.job_id = sj.job_id
    JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sj.job_id
    JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
 WHERE ss.freq_subday_type = 2
       AND ss.freq_subday_interval = 10
       AND (
           run_date < CAST(CONVERT(VARCHAR, DATEADD(hh, -12, GETDATE()), 112) AS INT)
           AND run_time < CAST(REPLACE(CONVERT(VARCHAR, DATEADD(hh, -12, GETDATE()), 108), ':', '') AS INT)
       );
 GO

 --Run clustered index deletes in batches
 WHILE (1 = 1)
 BEGIN
     DELETE TOP (5000)
     sjh
     FROM msdb.dbo.sysjobhistory sjh
         JOIN #sysjobhistory_todelete sjhtd
             ON sjh.instance_id = sjhtd.instance_id
 
     IF @@ROWCOUNT = 0
         BREAK;
 END;
 GO
  1. If your deletes are still blocked then the last option I would use is HOLDLOCK to make it serializable,
WHILE (1 = 1)
BEGIN
      DELETE TOP (5000)
      sjh
      FROM msdb.dbo.sysjobhistory sjh WITH (HOLDLOCK)
          JOIN #sysjobhistory_todelete sjhtd
              ON sjh.instance_id = sjhtd.instance_id

      IF @@ROWCOUNT = 0
      BREAK;
END;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.