Create's SQL Simulated Workers

CreateWorkers.sql

DECLARE @Process VARCHAR(1) = 'C'

--CREATE DATABASE SimulatedWorkers;
--GO
--DROP TABLE SimulatedWorkers.dbo.Jobs;
--GO
--CREATE TABLE SimulatedWorkers.dbo.Jobs (
--JobId int PRIMARY KEY IDENTITY(1,1), JobCommand VARCHAR(max), JobStatus VARCHAR(1), LockedByWorker uniqueidentifier, JobOutput VARCHAR(max)
--)
--GO
	
DECLARE @cpu_count int = (SELECT cpu_count FROM sys.dm_os_sys_info)
PRINT 'CPU COUNT'
PRINT @cpu_count
DECLARE @subthreads int = (SELECT CEILING(1.5*@cpu_count))
PRINT 'THREADS'
PRINT @subthreads
PRINT '----------------------------------------------'
PRINT 'CREATING OR DELETING WORKERS'
--Create the threads to handle the workload

IF @Process = 'C'
BEGIN
		EXEC msdb.dbo.sp_add_job @job_name = 'Simulated Process Controller',@start_step_id =1
		--We now create the jobs that execute the outstanding workers	
		EXEC msdb.dbo.sp_add_jobstep
		@job_name = 'Simulated Process Controller',
		@step_name = N'Check and execute available job',
		@subsystem = N'TSQL',
		@database_name = N'SimulatedWorkers',
		@command = N'EXEC dbo.JobController' 
		EXEC msdb.dbo.sp_add_jobserver @job_name = 'Simulated Process Controller', @server_name = N'(local)'
END

IF @Process = 'D'
BEGIN 
EXEC msdb.dbo.sp_delete_job @job_name = 'Simulated Process Controller'
END 

DECLARE @x int = 1
WHILE (@x <= @subthreads)
	BEGIN

	PRINT @x
	DECLARE @job_name VARCHAR(255) = 'Simulated Process'+CONVERT(VARCHAR,@x)

	IF @Process = 'C'
		BEGIN
		--C for Create Workers
		EXEC msdb.dbo.sp_add_job @job_name = @job_name,@start_step_id =1
		--We now create the jobs that execute the outstanding workers	
		EXEC msdb.dbo.sp_add_jobstep
		@job_name = @job_name,
		@step_name = N'Check and execute available job',
		@subsystem = N'TSQL',
		@database_name = N'SimulatedWorkers',
		@command = N'EXEC dbo.ExecuteJob' 
		EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)'
		END

	IF @Process = 'D'
		BEGIN
		--D for Delete
		EXEC msdb.dbo.sp_delete_job @job_name = @job_name
		END

	SET @x = @x + 1
	END

ExecuteJob.sql

USE [SimulatedWorkers]
GO
/****** Object:  StoredProcedure [dbo].[ExecuteJob]    Script Date: 8/8/2014 12:04:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Rodney Hawkins
-- Create date: 2014
-- Description:	Simulates a process, allowing stored procedures to be parallized where they were previously not.
-- =============================================
ALTER PROCEDURE [dbo].[ExecuteJob]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	start:
	DECLARE @ThreadGuid uniqueidentifier
	SET @ThreadGuid = NEWID()

    --We first check for a job.
    --Use NEWID() to ensure two Simulated Processes do not fight over the same Job.
	DECLARE @job_id int = (SELECT top 1 JobId FROM dbo.Jobs where JobStatus = 'W' ORDER BY NEWID())
	IF @job_id is null
		BEGIN
		RAISERROR ('There are no jobs to process, so this job process is exiting.', -- Message text.
           16, -- Severity.
           1 -- State.
           );
		END
	--Try and lock the Job_Id so that it is ours
	UPDATE dbo.Jobs SET JobStatus = 'R',LockedByWorker=@ThreadGuid where JobId = @job_id and JobStatus = 'W'
	--Ensure that the Job is actually locked by the worker or continue to the next step to be reallocated a task
	DECLARE @allocated_job_thread uniqueidentifier = (SELECT LockedByWorker FROM dbo.Jobs where JobId = @job_id)
	IF (@allocated_job_thread = @ThreadGuid)
		BEGIN
		DECLARE @attempts int = 0
		--This thread has been allocated the job, so we now execute it.
		attempt:
		PRINT 'A job has been allocated'
		PRINT @job_id
		DECLARE @sql_command NVARCHAR(max) = (SELECT JobCommand FROM dbo.Jobs where JobId = @job_id)
		BEGIN TRY
			EXEC sp_executesql @sql_command
			UPDATE dbo.Jobs SET JobStatus = 'C' where JobId = @job_id
		END TRY
		BEGIN CATCH
			PRINT ERROR_MESSAGE()
			SET @attempts = @attempts + 1
			IF (@attempts > 5)
				BEGIN
				UPDATE dbo.Jobs SET JobStatus = 'F' where JobId = @job_id
				END
			ELSE
				BEGIN
				GOTO attempt --Yeah GOTO!
				END
		END CATCH 
		END
	
	WAITFOR DELAY '00:00:01'
	GOTO start
END

JobController.sql

USE [SimulatedWorkers]
GO
/****** Object:  StoredProcedure [dbo].[JobController]    Script Date: 8/8/2014 12:05:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Rodney Hawkins
-- Create date: 25 July 2014
-- Description:	Check's if there are jobs available and if there are jobs available,
-- start's workers to execute them.
-- =============================================
ALTER PROCEDURE [dbo].[JobController]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @cpu_count int = (SELECT cpu_count FROM sys.dm_os_sys_info)
	DECLARE @threads int = (SELECT CEILING(1.5*@cpu_count))

    DECLARE @jobs_requiring_threads int = (SELECT COUNT(*) FROM dbo.Jobs where JobStatus = 'W')

		IF (@jobs_requiring_threads = 0)
		BEGIN
		RAISERROR ('There are no jobs to process, so this controller job is exiting.', -- Message text.
           16, -- Severity.
           1 -- State.
           );
		END

	IF (@jobs_requiring_threads > @threads)
		SET @jobs_requiring_threads = @threads

	DECLARE @x int = 1
	WHILE (@x <= @jobs_requiring_threads)
		BEGIN
		DECLARE @job_name VARCHAR(20) = 'Simulated Process'+CONVERT(VARCHAR,@x)
		EXEC msdb.dbo.sp_start_job @job_name
		SET @x = @x+1
		END

END