How can you find the SQL Server jobs that start simultaneously?

SQL Agent jobs starting simultaneously.

Why should you care about them?

Multiple SQL Server Agent jobs are configured to start simultaneously, which will not perform as fast as if jobs would not overlap.

Your SQL Server may be under heavy load for short bursts of time.

By the way, this check is a part of our SQL Server Health Check service.

How can I find the SQL Server jobs that start simultaneously?

You can use the script below to list all Agent SQL Server jobs starting at precisely at the same time.

  1. SELECT j.name As JobName,
  2.  j.description AS JobDescription,
  3.  a.start_execution_date AS JobStartExecutionDate
  4. FROM msdb.dbo.sysjobs j
  5. INNER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id
  6. WHERE j.enabled = 1 AND a.start_execution_date IN
  7. (SELECT start_execution_date
  8. FROM msdb.dbo.sysjobactivity
  9. GROUP BY start_execution_date HAVING COUNT(*) > 1)
Figure 1 -SQL Server Agent Jobs starting Simultaneously.

How to fix them?

  1. Spread job execution tweaking the job schedules.

More information

Microsoft – dbo.sysjobactivity.

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *