Monday, 29 April 2013

Parallel Processing Using Jobs

Parallel Processing Using Jobs

We'll create the 'job queue' and fill it with sample 'waitfor delay' commands with variable wait time using rand() function. The jobs will just simulate the work.
create table #job_queue (id int identity(1,1) primary key,exe_proc varchar(255))

declare @create_job nvarchar(max),@db_name sysname,@job_name varchar(255)
declare @val int,@command nvarchar(max),@delay varchar(16),@i int

set @db_name=db_name()

set @i=1
while @i<=100
    begin
        insert #job_queue values('waitfor delay ''''0:0:'+cast(ceiling(rand()*10) as varchar(3))+'''''')
        set @i=@i+1
    end


2. Starting the main loop. Create the SQL for the job first:
while exists(select 1 from #job_queue)
 begin
  select top 1 @val=id,@command=exe_proc from #job_queue
  set @job_name='_insert_base_job_name_'+cast(@val as varchar(3))
  set @create_job='
 
  USE [msdb]
 
  DECLARE @jobId BINARY(16)
  EXEC  msdb.dbo.sp_add_job 
      @job_name='''+@job_name+''',
      @enabled=1,
      @delete_level=3,
      @category_name=N''[Uncategorized (Local)]'',
      @owner_login_name=N''sa''
 
  EXEC msdb.dbo.sp_add_jobserver 
      @job_name='''+@job_name+''', 
      @server_name = N'''+@@servername+'''
 
  EXEC msdb.dbo.sp_add_jobstep 
      @job_name='''+@job_name+''', 
      @step_name=N''execute'',
      @step_id=1,
      @cmdexec_success_code=0,
      @on_success_action=1,
      @on_fail_action=2,
      @os_run_priority=0, @subsystem=N''TSQL'',
      @command='''+@command+''',
      @database_name='''+@db_name+''',
      @flags=0
 
 
  EXEC msdb.dbo.sp_update_job 
      @job_name='''+@job_name+''',
      @enabled=1,
      @start_step_id=1,
      @delete_level=3,
      @category_name=N''[Uncategorized (Local)]'',
      @owner_login_name=N''sa'''


3.Create the job and start it. Then remove the command from the queue:
exec sp_executesql @create_job
 
exec msdb.dbo.sp_start_job @job_name
 
delete top (1) #job_queue
 
print 'starting '+@job_name
4. Monitor number of jobs already running and wait until the number drops under 30 before starting a new one:
while (select count(*) from msdb.dbo.sysjobs_view job
         inner join msdb.dbo.sysjobactivity activity
         on (job.job_id = activity.job_id)
         where run_Requested_date is not null 
         and stop_execution_date is null
         and job.name like  '%_Card360_peter_jobs_%')>=30
  waitfor delay '0:0:2'
 end
 
drop table #job_queue