Parallel Execution of multiple queries with Logging

<= Simple Parallel Execution without Logging

In my previous post I created a simple procedure to run multiple queries in parallel.
It was very simple and easy to use, but for more advanced production processes it is too simple.
In order to satisfy additional user requirements I added some functionality to that procedure.

Here is the new edition I have:

CREATE PROC sp_Parallel_Execution

@SQLCommand NVARCHAR(4000),

@QueueLen INT = 100,

@delete_level INT = 3,

@LogQuery INT = 1

/****************************************************************************************

Name: sp_Parallel_Execution

Description: Allows parallel execution of multiple queries using SQL Server Agent.

Parameters:

@SQLCommand - SQL Server command to execute.

@QueueLen - Limit of jobs in execution queue (default is 100)

@delete_level Options:

3 - Job will be deleted after execution (default);

2 - Job will be deleted in case of failure;

1 - Job will be deleted in case of success;

0 - Job won't be deleted.

@LogQuery Options:

0 - Executed SQL Command won't be included in the log;

1 - Executed SQL Command will be included in the log only in case of an error (default);

2 - Executed SQL Command will be included in the log.

Author: Slava Murygin

www.slavasql.blogspot.com/2019/02/parallel-execution-of-multiple-queries.html

****************************************************************************************/

AS

SET NOCOUNT ON

DECLARE @DBName sysname = DB_NAME();

DECLARE @SQL NVARCHAR(4000);

/* Check for current queue */

IF @QueueLen < ( 

 SELECT COUNT(a.queued_date)

 FROM msdb.dbo.sysjobs as j WITH (NOLOCK)

 INNER JOIN msdb.dbo.sysjobactivity as a WITH (NOLOCK)

 ON j.job_id = a.job_id

 WHERE j.name like 'Parallel_Execution_Command_%'

  and a.queued_date is NOT null

)

BEGIN

  RAISERROR('Can''t execute a query. SQL Agent queue length is bigger than current threshold %d.',16,1, @QueueLen);

  RETURN -1;

END

/* Generate new job */

DECLARE @CommandName sysname = 'Parallel_Execution_Command_'

 + REPLACE(CAST(NEWID() as NVARCHAR(36)),'-','_')

 + '_' + CONVERT(NVARCHAR(150), HASHBYTES ('SHA2_512', @SQLCommand), 1);

EXEC msdb.dbo.sp_add_job @job_name = @CommandName, @delete_level=@delete_level;

/* Generate first job's step with code execution */

SET @SQL = 'DECLARE @SQL NVARCHAR(4000), @Message VARCHAR(8000), @IsError TINYINT = 0, @Start_Dt DATETIME = GetDAte();' + CHAR(10)

 + 'BEGIN TRY' + CHAR(10)

 + ' SET @SQL = ''' + REPLACE(@SQLCommand,'''','''''') + ''';' + CHAR(10)

 + ' EXEC sp_executesql @SQL;' + CHAR(10)

 + 'END TRY' + CHAR(10)

 + 'BEGIN CATCH' + CHAR(10)

 + ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;' + CHAR(10)

+ ' SELECT @IsError = 1, @Message = ' + CHAR(10)

+ ' ''Error Number: "'' + CAST(ERROR_NUMBER() AS VARCHAR) + ''"'' + CHAR(10)' + CHAR(10)

+ ' + ''In Line #'' + CAST(ERROR_LINE() AS VARCHAR) + CHAR(10)' + CHAR(10)

+ ' + ''Message: "'' + ERROR_MESSAGE() + ''"'';' + CHAR(10)

+ 'END CATCH ' + CHAR(10)

+ 'IF OBJECT_ID(''dbo.tbl_Parallel_Execution_Log'') is Not Null' + CHAR(10)

+ 'BEGIN' + CHAR(10)

+ ' INSERT INTO dbo.tbl_Parallel_Execution_Log(Start_Dt, IsError, Message, SQLQuery)' + CHAR(10)

+ ' SELECT @Start_Dt, @IsError, @Message, '

+ CASE @LogQuery WHEN 0 THEN 'NULL' WHEN 2 THEN '@SQL' ELSE 'CASE @IsError WHEN 1 THEN @SQL END' END + CHAR(10)

+ 'END' + CHAR(10);

/*

Create a table for logging

CREATE TABLE tbl_Parallel_Execution_Log(ID INT IDENTITY(1,1), Start_Dt DATETIME, Finish_Dt DATETIME DEFAULT GetDAte(), IsError TINYINT, Message VARCHAR(8000), SQLQuery NVARCHAR(4000));

*/

EXEC msdb.dbo.sp_add_jobstep @job_name = @CommandName, @step_name = N'Parallel_Execution_Command_1', @subsystem = N'TSQL', 

 @database_name = @DBName, @command = @SQL;

/* Adding job to the server and executing it */

EXEC msdb.dbo.sp_add_jobserver @job_name = @CommandName;

EXEC msdb.dbo.sp_start_job @job_name = @CommandName;

RETURN 0;

GO

The major changes I've made:
1. Added execution logging functionality. If table "tbl_Parallel_Execution_Log" is present in current database then execution will be automatically logged. By default it won't log executed query, but you can still record it if you specify "@LogQuery" parameter as "2". Log table definition is included into stored procedure just in case:

CREATE TABLE tbl_Parallel_Execution_Log(

       ID INT IDENTITY(1,1),

       Start_Dt DATETIME,

       Finish_Dt DATETIME DEFAULT GetDAte(),

       IsError TINYINT,

       Message VARCHAR(8000),

       SQLQuery NVARCHAR(4000)

);

2. By Steve Ledridge's advise I've added SQL Agent Job deletion not as a separate step, but as job's property. Now by controlling "@delete_level" parameter you have a flexibility to delete a job on failure or success. However, with logging functionality there is very limited number of cases when you'd need to research failed job.

Enjoy!

Disclosure:

1. The stored procedure is provided "as is" and at your own risk. Do not run it in production before the comprehensive testing in Dev environment.
2. That method is very insecure. Whoever has rights to run that procedure can make a damage to your whole SQL Server.
3. Because SQL Agent job is self-deleting, you won't see any execution results of it. That means you have to do your own error handling. The most preferred way to wrap all executed queries inside of hard coded stored procedures with their own error handling. Later on I'll come up with more comprehensive version of that procedure to provide error handling and execution check.