Run T-SQL in parallel agents jobs and wait till all the jobs are done


The following script extends the work of John Huang and his excellent blog post about parallel SQL Execution using the SQL Agent: http://www.sqlnotes.info/2012/01/04/parallel-task-scheduling-1-jobs/

The extension is a wrapper to obfuscate the logic to wait for all the jobs to be done. This approach can significantly improve the duration of large database operations. E.g you can create multiple Indexes in parallel. 

 

*Last Update(23.5.2016)

 

In the end you can run parallel SQL-Scripts like this:

 
--COMMAND EXECUTION WITH LOGGING
DECLARE @Commands AS Commands;
Insert Into @Commands Values ('SELECT 1/0','Select division by zero')
Insert Into @Commands Values ('Select 12345','no desc')
Insert Into @Commands Values ('waitfor delay ''00:00:04''', 'test Monitoring for 4 second delay')
Insert Into @Commands Values (' USE Locktest
ALTER TABLE [dbo].[test] ADD PRIMARY KEY CLUSTERED
(
    [i] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
','CREATE INDEX ON TEST TABLE')

exec RunParallelAndWaitTillallDone @Commands
GO


Select *, DATEDIFF(second,starttime,endtime) as 'Duration' from Monitor

 

 

Here is the entire Script to create the required Stored Procedures:

--README
--THE SCRIPT CREATES ALL CONTROL PROCEDURES AND A MONITORING TABLE IN MASTER
--DONT USE A "GO" INSIDE A COMMAND THAT IS EXECUTED IN PARALLEL!
--MAKE SURE THE AGENT IS RUNNING!
--MAKE SURE NO JOBS EXIST WITH THE TERM "PARALLEL" IN THEIR JOBNAME.
--IF A COMMAND HAS A SYNTAX ERROR THE CREATION WILL FAIL AND THE AGENT MIGHT END UP IN AN INCONSISTANT STATE
--IN THIS CASE RESTART THE AGENT, REMOVE ALL THE JOBS NAMED "PARALLEL" AND TRY AGAIN.

 

USE [master]
GO


--Drop Procedure RunParallelAndWaitTillallDone
--Drop Type Commands


Drop Table Monitor
GO
CREATE TABLE [dbo].[Monitor](
    [TaskID] [uniqueidentifier] NOT NULL,
    [starttime] [datetime2](7) NULL,
    [endtime] [datetime2](7) NULL,
    [Jobname] [varchar](255) NULL,
    [Error] varchar(max) null
PRIMARY KEY CLUSTERED  ([TaskID] ASC))
GO

Drop Procedure CreateTask
GO
create procedure [dbo].[CreateTask] (@TaskName sysname, @Handle uniqueidentifier output, @TaskBody nvarchar(max),@Descr varchar(255))
as
begin
set nocount on
if isnull(@TaskName, '') = '' -- send error back if there is no task name
begin
raiserror('No task name', 16,1)
return
end

if isnull(@TaskBody, '') = '' -- send error back if there is no task body
begin
raiserror('No task body', 16,1)
return
end
declare @monitoringheader varchar(max) = CONCAT('declare @id uniqueidentifier = NEWID()
Insert into Master.dbo.Monitor Values (@id, getdate(),null,''', @Descr ,''',null)
BEGIN TRY ')

declare @monitoringfooter varchar(max) = ' END TRY
BEGIN CATCH
    UPDATE Master.dbo.Monitor set ERROR = ERROR_MESSAGE() where TaskID = @id
END CATCH;

UPDATE Master.dbo.Monitor set endtime = getdate() where TaskID = @id
'

set @TaskBody = CONCAT(@monitoringheader, @TaskBody, @monitoringfooter);

print @TaskBody

declare @InternalTaskName varchar(128), @DatabaseName sysname
select @InternalTaskName = cast(@@spid as nvarchar(20)) + '-' + @TaskName + '-' + CAST(newid() as varchar(50)), @DatabaseName = DB_NAME(), @Handle = null

begin transaction
exec msdb.dbo.sp_add_job @job_name = @InternalTaskName, @delete_level = 3, @job_id = @Handle output
select @TaskBody = 'set context_info ' + convert(varchar(256), cast(@InternalTaskName as varbinary(128)), 1) +';
       go
       '+ @TaskBody
exec msdb.dbo.sp_add_jobserver @job_id = @Handle, @server_name = '(LOCAL)'
exec msdb.dbo.sp_add_jobstep @job_id = @handle, @step_name = 'Task', @database_name = @DatabaseName, @command = @TaskBody
exec msdb.dbo.sp_start_job @job_id = @handle

commit
end

GO


CREATE TYPE Commands AS TABLE
( Command varchar(Max), Descr varchar(255));

GO

CREATE Procedure [dbo].[RunParallelAndWaitTillallDone] @cmds Commands READONLY
as
begin
set nocount on
declare @cmd varchar(max)
declare @desc varchar(255)
declare @handle uniqueidentifier
--Create Jobs
    declare c cursor for Select Command,Descr from @cmds
    open c
    fetch next from c into @cmd,@desc
    while @@FETCH_STATUS = 0
    begin
    exec CreateTask 'Parallel', @handle output, @cmd,@desc
    fetch next from c into @cmd,@desc
    end
    close c
    deallocate c
--Wait till all Jobs are done
    while (1=1)
    begin
    if not exists (Select * from msdb.[dbo].[sysjobs] where name like '%Parallel%')
        break;
    waitfor delay '00:00:01'
    end
Select 'Done!'
End

Comments (0)

Skip to main content