Test

The following script extends the work of John Huang and his excellent blog post about parallel SQL Execution using the SQL Agent: https://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. 

 

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

 

 DECLARE @Commands AS Commands;
Insert Into @Commands Values (‘Select 352*23’)
Insert Into @Commands Values ('Select 123')
exec RunParallelAndWaitTillallDone @Commands
GO

 

 

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

 

create procedure CreateTask (@TaskName sysname, @Handle uniqueidentifier output, @TaskBody nvarchar(max))

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 @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 function dbo.TaskStatus(@handle uniqueidentifier)

returns bit

as

begin

return case when exists(select 1 from msdb.dbo.sysjobs where job_id = @handle) then 1 else 0 end

end

GO

CREATE TYPE Commands AS TABLE

( Command varchar(Max));

GO

CREATE Procedure RunParallelAndWaitTillallDone @cmds Commands READONLY

as

begin

set nocount on

declare @SynchronizationTable table(Handle uniqueidentifier primary key)

declare @cmd varchar(max)

declare @handle uniqueidentifier

declare c cursor for Select Command from @cmds

open c

fetch next from c into @cmd

while @@FETCH_STATUS = 0

begin

exec CreateTask 'MyTest', @handle output, @cmd

insert into @SynchronizationTable values(@handle)

fetch next from c into @cmd

end

close c

deallocate c

while (1=1)

begin

Delete from @SynchronizationTable where dbo.TaskStatus(@handle) = 0

if not exists (Select * from @SynchronizationTable)

break;

waitfor delay '00:00:01'

end

Select ‘DONE!'

End