2 formatted code
source | link

E.g.

Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.


    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......
It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table(possibly: IX, IU) before the Job 1 is able to acquire its required locks on the sameusers table. Then Job 1 has to wait.

E.g.

Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

E.g.


    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......
It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table(possibly: IX, IU) before the Job 1 is able to acquire its required locks on the sameusers table. Then Job 1 has to wait.

1
source | link

An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.

E.g.

Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:

SELECT sp.spid,
       sp.kpid,
       sp.blocked,
       sp.hostprocess,
       sp.waittype,
       sp.lastwaittype,
       DB_NAME(sp.dbid),
       sp.cpu,
       sp.physical_io,
       sp.memusage,
       CASE 
            WHEN sp.blocked = 0 THEN sp.spid
            WHEN sp.blocked != 0 THEN blocked
       END               AS chain,
       dest.text
FROM   sys.sysprocesses  AS sp
       OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE  spid > 50
       AND spid <> @@spid
       AND (
               spid IN (SELECT blocked
                        FROM   sys.sysprocesses
                        WHERE  blocked != 0
                               AND spid > 50
                               AND spid != @@spid)
               OR blocked != 0
           )

The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.

Possible solutions:

  • Adding a NOLOCK hint can help, but should be avoided if possible.
  • Then you could also consider optimising the code so that the steps in Job 2 run faster.
  • Changing the schedules of the Job 1 and/or Job 2.