Speed Up SQL Agent Jobs by Preventing Simultaneous Runs

SQL agent job B steps Title

I’ve seen the time it takes a SQL agent job to complete on a resource-limited system increase tenfold when running alongside another job compared to when it’s run individually. I’ve also seen several jobs run simultaneously without any hiccups. Parallel Processing is a good thing, and your goal when scheduling SQL agent jobs should be to run what you can in parallel unless you find that doing so negatively impacts the overall performance of your jobs. If your job performance is suffering and you find that multiple jobs running simultaneously is part of the reason why, then you’ll find the following method a great tool to add to your SQL Server belt.

Analysis

First thing’s first, do some research on your jobs. I like to start my SQL agent job analysis with the small tool SQLjobvis, which gives a nice graphical presentation of jobs, and which I cover in more detail here. Figure out what jobs overlap and what jobs could potentially overlap if they run long. In other words, identify which of your jobs might benefit by setting them to make sure they don’t run simultaneously with other jobs.

After you’ve identified potential SQL agent jobs that could benefit from running all by themselves, you should test them out. Run jobs that overlap individually, and see if they run significantly faster than under normal conditions. Run jobs that could potentially overlap in parallel and see if they run significantly slower.

As part of this identification process, it’s helpful to know what the SQL agent jobs are doing. Does one job pin the CPU’s on your system, slowing down anything else that tries to run? What tables are the jobs hitting – is there potential for one job to lock a table that would block other tasks? Do you have jobs that max out your network pipe’s throughput as they transfer data from one system to another? How is your storage configured – are your database operations being limited by the speed of your I/O?

Setup

OK, so let’s say you’ve identified that jobs “Job A” and “Job B” have problems running in parallel. Job B is started after job A, and you want to make sure that job B does not start until job A has completed.

First, let’s create a little T-SQL stored procedure to help us identify our job run states:

-- =============================================================================
-- Author:		 Ben Moore
-- Create date:	 2012-11-09
-- Description:	 Check if passed in @JobName is currently running, returning
--   'No' if it is not.  If it is running, check at @CheckInterval intervals
--    up to a @MaxWait total wait time.  If the job is still running after
--    the maximum wait time, return 'Yes' and raise an error.
-- =============================================================================
ALTER PROCEDURE [dbo].[usp_JobRunningCheck] --'Test Job - Is job running 1', '00:02', '00:00:10'
	@JobName SYSNAME = 'Test - Is job running 1' -- The name of a SQL agent job to check.
	,@MaxWait DATETIME = '01:00' -- The Max time to check if the job is still running before failing.
	,@CheckInterval DATETIME = '00:05' -- How often to check on if the job is still running.
AS
------------------------
BEGIN --Stored Procedure
	SET NOCOUNT ON;	-- Prevents extra result sets from effecting speed of real SELECTs.
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Sets WITH(NOLOCK) globally for all tables in this session.

	DECLARE @StartTime DATETIME = GetDate();
	DECLARE @JobExists BIT = 'false';

	----------------------------------------
	WHILE GETDATE() < (@StartTime + @MaxWait)
	BEGIN --While loop
		IF NOT EXISTS (
				SELECT 1
				FROM msdb.dbo.sysjobs_view job
				INNER JOIN msdb.dbo.sysjobactivity activity
					ON job.job_id = activity.job_id
				WHERE activity.run_Requested_date IS NOT NULL
					AND activity.stop_execution_date IS NULL
					AND job.NAME = @JobName
				)
		BEGIN
			-- If job does not exist, break out of the loop.
			SET @JobExists = 'false';

			BREAK;
		END
		ELSE
		BEGIN
			-- If job does exist, wait and continue looping.
			SET @JobExists = 'true';

			WAITFOR DELAY @CheckInterval
		END
	END --While loop

	----------------------------------------
	-- Output the results of the job check time loop.
	IF @JobExists = 'true'
	BEGIN
		-- Indicate that the job is running.
		PRINT 'Yes';

		-- Raise an error so that this will register as a failed step in a SQL agent job.
		RAISERROR (
				'This procedure failed on purpose because the job "%s" is currently running'
				,11
				,0
				,@JobName
				);
	END
	ELSE
	BEGIN
		-- Indicate the job is not running.
		PRINT 'No';
	END
END --Stored Procedure

You run the stored procedure like this:
[dbo].[usp_JobRunningCheck] ‘Job A’, ’00:02′, ’00:00:10′

In this example, we are checking if the SQL agent job named ‘Job A’ is running. We will check this job for a maximum of 2 minutes in 10 second intervals. If the job is not running, the stored procedure will return ‘No’. If the job is still running after 2 minutes of checking, the stored procedure will return ‘Yes’ and will throw an error.

So how do we use this in our SQL agent jobs? It’s pretty simple really. Let’s continue with our scenario that we don’t want job B to start until job A has completed. In this case we’d open up our “Job B” in SQL agent, go to Steps, and add a New step. We’ll call this step “Check if ‘Job A’ is currently running”. Set up the step like the following image, calling the T-SQL script we created earlier:

SQL agent job currently running check

Then, make sure your new step is at the top of the step list and is run first.

SQL agent job B steps

That’s all there is to it.  Now, when job B is started, the first thing it’s going to do is make sure job A is completed before it runs. If job A never completes within the max check time you set up, then job B will fail without ever attempting it’s real work. This is likely what you want, because if job A is running much longer than you expect it to there’s likely a problem that you want to investigate before you go trying to run job B.

Realistically, many of the SQL agent jobs I have used this technique on run anywhere from 15 minutes to an hour, so I tend to set these up to check in 5 minute intervals for a max time of 2 to 3 hours.  If a job runs beyond that time, I know there’s something I need to look into.  I might have two or three ‘check’ steps in my jobs, checking against different jobs, and the script provided lets me use different check intervals and max check times as needed for each of my job checks.

Conclusion

You never know when a SQL agent job is going to run a little long and throw a wrench into your well laid out schedule. Now you have a method in your tool belt you can use to prevent specific jobs from running in parallel with other jobs. If you find one of your jobs suffering from bad performance, give this a try and see if it helps, and feel free to share and let me know how it worked for you.

Leave a Reply

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