How to execute multiple long running SQL Statements Asynchronously in small chunks

Khorshed Alam, Dhaka

4.90/5 (25 votes)

Feb 7, 2013

CPOL

13 min read

208805

1309

How to execute multiple long running SQL Statement Asynchronously in smaller chunks

Download AsyncSQLScripts.zip - 4.3 KB

Introduction

Completing task Asynchronously in case of long running query processing is very helpful in some scenario. It ensures maximum use of hardware resources as well. In case of non set based modern high level programming language such as C# or Java has versatile facilities, libraries and patterns for asynchronous programming. But what about SET based language such as SQL? There is no straight forward way to execute SQL statements in parallel mode. In SQL Server there some way to do such stuff like using SQL Server Service Broker or through CLR stored procedure. Service Broker actually is a process of sending and receiving messages which can be sent to same or any remote database of another SQL Server instance. Whereas CLR needs different set of programming expertise, it also has some deployment issue. Today I am going to show you the same implementation using SQL Server Agent Job. In this article you also came to know, how huge number of long running SQL statements will be executed in some smaller configurable chunks.

Background

In my recent data ware house project, client has a requirement to validate a table data against some wide range of predefined rules. The table has more than 150 columns. Each column's data need to be validated with these rules. Let us be familiar with some of them.

SL#	Column	Rule
1	Customer DOB	DOB Must be a valid date
		DOB must be less than the purchase date.
		The date format should be MM-DD-YYYY
2	Credit Card Number	Card Number should not be an Empty value
		Should be a valid Card Number
		Should be a valid Card Type
		Expiry Date must be within a range of 1997 To 2020

Table 1: Rule against column.

In addition, another requirement is to keep the garbage data into another table for reporting purpose so that client can view the garbage data and fix them. To accomplish the job we have dedicated to created stored procedures for each rule. As a result we had to created about 35 distinct stored procedures to validate the customer table data. Client usually sends us customer data in text files and we bulk load them into staging tables. But our main challenge was to validate the data of all 150 columns by running on average 4 rules (4 stored procedures) against each columns. Our customer table contains more than 3 million of records and each validation rule stored procedure took considerable time to select the garbage value and save them to a Error Log Table. In this situation running these stored procedures in parallel mode and 10-12 procedures at a time (a chunk) was an splendid solution for us to get the job done. Alright, no more talk, let us jump to the actual implementation.

Metadata Management

As I spoke earlier that we will use SQL Server Agent Job to run our stored procedure Asynchronously .So in run time you will have very minimal control over the running stored procedures or statements. In this situation we have used a metadata table that stores the stored procedures execution status like when they started and finished or any error message during execution period. The table creation scripts would be:

CREATE TABLE AsyncProcessStatusLog
(
	Id                      INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	StoredProcedureName     VARCHAR(100) NOT NULL,
	StartTime               DATETIME NOT NULL,
	EndTime                 DATETIME NULL,
	ProcessStatus           CHAR(1) NOT NULL
	CHECK  (([ProcessStatus]='F' OR [ProcessStatus]='S' OR [ProcessStatus]='R')),
	 /*------- R=Running S=Success F=Fail */
	ErrorMessage			VARCHAR(2000) NULL 
)

Process Status:

"R" indicates the procedures is running state.
"F" indicates the procedure failed and an exception occurred.
"S" means the procedures successfully executed.

After that we need to create a stored procedure named "InsertUpdate_AsyncProcessStatusLog_usp " that will insert/update status into the "AsyncProcessStatusLog " table.

CREATE PROCEDURE InsertUpdate_AsyncProcessStatusLog_usp
	@StoredProcedureName VARCHAR(100),
	@ProcessStatus CHAR(1),
	@ErrorMessage VARCHAR(2000)
AS
BEGIN
	
	DECLARE @now DATETIME = GETDATE()
		
	IF NOT EXISTS (
	       SELECT 1
	       FROM   AsyncProcessStatusLog
	       WHERE  StoredProcedureName = @StoredProcedureName
	   )
	    INSERT INTO AsyncProcessStatusLog
	      (
	        StoredProcedureName,
	        StartTime,
	        EndTime,
	        ProcessStatus
	      )
	    VALUES
	      (
	        @StoredProcedureName,
	        @now,	--Procedure start executing
	        NULL,
	        'R'		--We know this status is "Running" at this stage.
	      )
	ELSE
	    UPDATE AsyncProcessStatusLog
	    SET    EndTime              = @now,	--Execution fnishing time.
	           ProcessStatus        = @ProcessStatus,--Process Status (F or S will come here)
	           ErrorMessage         = @ErrorMessage
	    WHERE  StoredProcedureName  = @StoredProcedureName
END

How to write The Stored Procedures/Statements for parallel execution

Now let me prepare some sample stored procedures that will be run in parallel. Some convention need to be followed when creating them. Have a look below:

CREATE PROCEDURE CheckValidDate_usp
--Parameter goes here
AS
BEGIN
 BEGIN TRY
	--Saving this SP status to metadata table that it is in Running state.
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'R',
		    @ErrorMessage = NULL
		
	------------------------------------------
	--Details Implementation (Business Logic)
	--Details Implementation (Business Logic)
	--Details Implementation (Business Logic)
	WAITFOR DELAY '00:00:10'
	-------------------------------------------
	--Saving this SP status to metadata table that it has successfully finished the task.
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'S',
		    @ErrorMessage = NULL
 END TRY
 BEGIN CATCH
	--Oh!! some error occured and keeping this information here.
	DECLARE @ErrorMsg VARCHAR(2000) = ERROR_MESSAGE()
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'F',
		    @ErrorMessage = @ErrorMsg
 END CATCH
END

Maintaining Stored Procedures execution status by executing "InsertUpdate_AsyncProcessStatusLog_usp" stored procedure.

Setting Initial Running Status (@ProcessStatus ='R') by the top most statement.

Updating with Success Status (@ProcessStatus ='S') by the statement placed just above the END TRY block.

Updating with Failed Status (@ProcessStatus = 'F') by the statement inside CATCH block with additional parameter @ErrorMessage.

· Business Logic Block:

This block usually contains main TSQL code to satisfy the business logic. I have used WAITFOR DELAY '00:00:10' here to have an impression that the stored procedure takes 10 seconds to finish for demo purpose. (In real life situation this will be replaced by actual business TSQL logic.)

Sample Stored Procedures:

I have written 7 stored procedures against each rule by following the convention mentioned above. Please have a look on the attached TestStoredProcedures.sql file (After extracting AsyncSQLScripts.zip).

SL#	Rule	Associated Stored Procedure
1	DOB Must be a valid date	CheckValidDate_usp
2	DOB must be greater than the purchase date.	CheckDateGreaterThanAnotherDate_usp
3	The date format should be MM/DD/YYYY	CheckValidDateFormat_usp
4	Card Number should be an Empty value	CheckIsRequired_usp
5	Should be a valid Card Number	CheckValidCreditCardNumber_usp
6	Should be a valid Card Type	CheckCardTypeValid_usp
7	Expiry Date must be within a range of 1997 To 2020	CheckIsDateInValidRange_usp

Table 2: Rule wise stored procedure.

The main attraction!! The script that executes Stored Procedures Asynchronously via Agent Job:

We have our Stored Procedures and Metadata table ready. Now let us have a look on the main attraction, the scripts which will create SQL Agent Job steps on the fly and run our Stored Procedures In parallel mode.

CREATE PROCEDURE ExecuteSQL_ByAgentJob_usp(
    @SqlStatemet            VARCHAR(4000),
    @SPNameOrStmntTitle     VARCHAR(100),
    @JobRunningUser         VARCHAR(100) = NULL,
    @JobIdOut               UNIQUEIDENTIFIER OUTPUT
)
AS
BEGIN
	
	SET NOCOUNT ON;  
	
	DECLARE @JobId          UNIQUEIDENTIFIER,
			@JobName        VARCHAR(250) = NULL,
			@DBName         VARCHAR(100) = DB_NAME(),
			@ServerName     VARCHAR(100) = @@SERVERNAME
	
	
	--Creating Unique Job Name by combining @SPNameOrStmntTitle and a GUID.		
	SET @JobName = @SPNameOrStmntTitle + '_' + CONVERT(VARCHAR(64), NEWID()) 
	
	--Currently logged user name will be used to execute the job if not provided one.
	IF @JobRunningUser IS NULL
		SET @JobRunningUser = SUSER_NAME()
	
	--Adds a new job executed by the SQLServerAgent service
	EXECUTE msdb..sp_add_job @job_name = @JobName, @owner_login_name = @JobRunningUser, 
	@job_id = @JobId OUTPUT 
	
	--Targets the specified job at the specified server 
	EXECUTE msdb..sp_add_jobserver @job_id = @JobId, @server_name = @ServerName 
	
	--Tell job for its about its first step.
	EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step1', @command 
	= @SqlStatemet,@database_name = @DBName, @on_success_action = 3 
	
	--Preparing the command to delete the job immediately after executing the statements 
	DECLARE @sql VARCHAR(250) = 'execute msdb..sp_delete_job @job_name=''' + @JobName + ''''
	
	EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step2', @command = @sql 
	
	--Run the job
	EXECUTE msdb..sp_start_job @job_id = @JobId
	
	--Return the Job via output param.
	SET @JobIdOut = @JobId
END

^{Main logic taken from}^here

The stored procedure works as follows:

Creates a job with an unique name, register it to the sever. The has two steps.
In Step-1: the job executes the provided SQL Statement.
In Step-2 : the job executes command to delete itself.
The procedures also return the JobId for further reference.

Breaking the queries into small chunks

Now we have everything to run the our stored procedures asynchronously. But what about to run them in a small chunk. Now let me say few words about the benefits of executing resource intensive talks in a smaller chunk.

if we run 100 parallel tasks there is a significant chance for the server to go out of resource with a error like " Insufficient System Memory To Run This Query".
If we need to forcefully stop query execution only current chunk will be stopped and it ensures no further processing.

We have total 7 stored procedures here, we will run 3 procedures at a time. In that case the chunk execution scenario will be as follows:

SL#	Stored Procedure	Chunk #
1	CheckValidDate_usp	1
2	CheckDateGreaterThanAnotherDate_usp
3	CheckValidDateFormat_usp
4	CheckIsRequired_usp	2
5	CheckValidCreditCardNumber_usp
6	CheckCardTypeValid_usp
7	CheckIsDateInValidRange_usp	3

Table 3: Chunk wise script distribution

This chunk size actually depends on case to case so I prefer in real life, better to keep this value will be in a configuration table so that any time this value can be changed.

Arrangements for running the SPs asynchronously:

To the run the queries asynchronously we need to keep in mind the following arrangements.

Prepare and keep all queries/stored procedures(with required parameters) in a temp table or table variable.
Execute the first chunk of using "ExecuteSQL_ByAgentJob_usp" SP with the dynamic statement prepared at previous step and wait until first chunk to be finished and run the subsequent chunks.
Monitor the scripts by querying the metadata table "AsyncProcessStatusLog".
Notify user for any untoward situation during run time.

Now let us discuss each steps in details.

CREATE PROCEDURE Customer_Validator_usp
--Any parameter goes here
AS
BEGIN
	SET NOCOUNT ON;
	
	---Cleanup previous metadata
	---Just for testing purpose. Think about your won implementation style.	
	DELETE FROM   AsyncProcessStatusLog
	
-----------------PART1------------------------------------
	DECLARE @Scripts TABLE (
	            Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
	            Script NVARCHAR(4000)
	        )
	
	INSERT INTO @Scripts
	SELECT 'EXEC CheckValidDate_usp'--Additional parameters 
	UNION ALL
	SELECT 'EXEC CheckDateGreaterThanAnotherDate_usp'
	UNION ALL
	SELECT 'EXEC CheckValidDateFormat_usp'
	UNION ALL
	SELECT 'EXEC CheckIsRequired_usp'
	UNION ALL
	SELECT 'EXEC CheckValidCreditCardNumber_usp'
	UNION ALL
	SELECT 'EXEC CheckCardTypeValid_usp'
	UNION ALL
	SELECT 'EXEC CheckIsDateInValidRange_usp'
 
-----------------PART2------------------------------------	
	
	DECLARE @sql NVARCHAR(4000),
	        @Itr INT,
	        @RecCount INT,
	        @ScriptTitle VARCHAR(200),
	        @JobId UNIQUEIDENTIFIER
	
	DECLARE @TotalScriptSentToJob     INT = 0,
			--Hard code here.Should be configurable.
	        @MaxValidatonProcedureToRunAtaTime INT = 3,
	        @IsChunkProcessing        BIT
	
	SET @Itr = 1 --Seeting the initial value.
	SET @RecCount = (
	        SELECT COUNT(*)
	        FROM   @Scripts
	    )
	
-----------------PART3------------------------------------
	WHILE (@Itr <= @RecCount)
	BEGIN
	    SELECT @sql = t.Script
	    FROM   @Scripts t
	    WHERE  id = @Itr
	    --Just o identify the script name getting first 10 char of the SP
	    SET @ScriptTitle = LEFT(REPLACE(@sql, 'EXEC ', ''), 10) 
	    
	    EXEC ExecuteSQL_ByAgentJob_usp
	         @SqlStatemet = @sql,
	         @SPNameOrStmntTitle = @ScriptTitle,
	         @JobRunningUser = 'sa',
	         @JobIdOut = @JobId OUTPUT
	  
-----------------PART4------------------------------------
	    SET @TotalScriptSentToJob = @TotalScriptSentToJob + 1
	    
	    --Wait for some seconds until send the next procedure to job.
	    --It may take some time to initialize the job and write to metadata table.
	    IF (@TotalScriptSentToJob = @MaxValidatonProcedureToRunAtaTime)
	    BEGIN
	        SET @TotalScriptSentToJob = 0
	        SET @IsChunkProcessing = 1
	    END
	    
-----------------PART5------------------------------------	    
	    IF (@IsChunkProcessing = 1)
	    BEGIN
	        DECLARE @Result INT
	        
	        EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp
	        PRINT 'I am waiting the chunk to be finished.'
	        IF (@Result = -1)
	        BEGIN
	            RAISERROR ('Exception occured in some srored procedure.', 17, 2) 
	            RETURN -1
	        END
	        ELSE
	        BEGIN
	            SET @IsChunkProcessing = 0
	        END
	    END
	    
	    ---Cleanup section------	
	    SET @sql = ''	    
	    SET @Itr = @Itr + 1
	END
	
-----------------PART6------------------------------------
	--Wait for the last chunk to be finished.
	
	EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp
	PRINT 'I am waiting the last chunk to be finished.'
	
	IF (@Result = -1)
	BEGIN
	    RAISERROR ('Exception occured in some srored procedure.', 17, 2) 
	    RETURN -1
	END
	
	--Allow other scripts to run.
	PRINT 'I am finished!'
END

PART-1 Preparing and Storing Scripts in a table variable

The scripts are kept into a table variable in a straight forward way just to reduce complexity. But in real life situation please find out your won suitable way to prepare the scripts with associated parameters dynamically and store them to the table variable.

PART-2 Variable declaration Section

Here required variables have been declared. Not all are needed to be described here but I would like to tell few words about "@TotalScriptSentToJob" and "@MaxValidatonProcedureToRunAtaTime" variables. The "@MaxValidatonProcedureToRunAtaTime" variable stores the value of how many Stored Procedures will run at a time(Chunk Size) and "@TotalScriptSentToJob" keeps the incremented value of how many Stored Procedures already sent to SQL Agent for asynchronous processing. The purpose of "@IsChunkProcessing" is to decide whether the system will go to wait state to stop processing any other queries until the current chunk to be finished.

PART-3 Beginning of the WHILE LOOP

Prepare Job name, and sending the command to the SQL Agent job for parallel processing.

PART-4 Decides to go for WAIT state

After sending scripts to the Agent Job the value of "@TotalScriptSentToJob" get incremented and get compared with "@MaxValidatonProcedureToRunAtaTime" to determine whether max number of Stored Procedures already been sent against a chunk or not. If max number of Stored Procedures already sent then it reinitialize "@TotalScriptSentToJob" with "0" and "@IsChunkProcessing " with "1" .

PART-5 Wait State:

If get "@IsChunkProcessing =1". The system goes to wait state until the current asynchronously running stored procedures to be finished. How system goes to WAIT state after finishing a chunk, has been described in details later in this article. This PART also terminate the whole process for any error occurs during run time.

PART-6 WAIT State When Last chunk value is always less than the actual chunk

At the end, once again the system check for any running/active statements and waits for them to be completed. This situation takes place when the last chunk value is always less than the actual chunk value.

To get a clear idea about this, just have a look on the Table 3 (Chunk wise script distribution) where we have total 7 stored procedures and we have divided them into 3 chunks like 3+3+1. In the last chunk only one procedures will run. In this situation the following code block never comply with this situation.

IF (@TotalScriptSentToJob >= @MaxValidatonProcedureToRunAtaTime)

Because the value of "@TotalScriptSentToJob" will be "1" but our initial chunk size is set to 3 (@MaxValidatonProcedureToRunAtaTime=3) . So the value of "@IsChunkProcessing" will never a get a value "1". Thus no WAIT state will happen for this chunk inside the WHILE LOOP. To get rid from this , the "EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp" statement has been used once again after END block of WHILE loop.

How system maintains WAIT state after finishing a chunk

The system goes in wait state by executing "EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp". This procedures run a infinite loop and checking the status of each procedures running under this chunk by querying the "AsyncProcessStatusLog " metadata table.

Let us see the implementation:

CREATE PROCEDURE Wait_Unitl_Chunk_ToBe_Finished_usp
AS
BEGIN
	WHILE (1 = 1)
	BEGIN
	    WAITFOR DELAY '00:00:3'
	    
	    IF EXISTS(
	           SELECT 1
	           FROM   AsyncProcessStatusLog aps
	           WHERE  aps.ProcessStatus = 'F'
	       )
	        RETURN -1
	    
	    
	    IF NOT EXISTS(
	           SELECT 1
	           FROM   AsyncProcessStatusLog aps
	           WHERE  aps.ProcessStatus = 'R'
	       )
	        RETURN 1
	END
END

You may remember from "How to write The Stored Procedures/Statements" section that when the each procedures start running it writes a row in the metadata table with an initial status (ProcessStatus) "R" indicates "Running". For any exception the status is updated with a value of "F" indicates "Failed". If the procedures runs successfully the status becomes "S" means "Success". The infinite loop breaks when there is no active (running) queries in the job. The procedure return 1 in normal situation and -1 if any exception occurs.

Using the code

So far we have enough idea about the architecture. Now let's run the scripts and see the how queries are running in parallel and chunk by chunk.

Please make sure SQL Agent Service is running.
Extract AsyncSQLScripts.zip and execute DB_Metadata_Table.sql. This will create a test database with metadata table. Afterwards, execute TestStoredProcedures.sql, ExecuteSQL_ByAgentJob_usp.sql , Wait_Unitl_Chunk_ToBe_Finished_usp.sql sequentially.
Finally execute Customer_Validator_usp.sql. Now we are ready to test our code. Close all open window of SSMS.
Open two new blank query tab. Write following script in the respective tabs.

Query Tab 1

USE TestAyncDB
GO
 
EXEC Customer_Validator_usp

Query Tab 2

 SELECT apsl.StoredProcedureName,
       apsl.StartTime,
       apsl.EndTime,
       DATEDIFF(second, apsl.StartTime, apsl.EndTime) AS ElapsedSeconds
FROM   AsyncProcessStatusLog apsl
ORDER BY
       apsl.StartTime

Go to Window Menu and Press "New Vertical Tab Group" just to see the windows side by side.
Now execute the scripts of Query Tab 1 ( "EXEC Customer_Validator_usp") afterwards, quickly switch to Query Tab 2 and press F5 continuously after couple of seconds.
You will see 3 stored procedures (First Chunk) start running at the same time(In Parallel) like below:

Figure 1: Query Running in parallel mode for first chunk.

in the image below, the second chunk has stared and one stored procedure already finished and other two procedures is running in parallel.

Figure 2: Second chunk is running.

The next image shows all Stored procedures finished running asynchronously in 3 separate chunks.

Figure 3: All stored procedures has finished running in 3 chunks.

Now go back to Query Tab 1 and see the "Messages" tab in the result pane. You will find something similar like this.

Figure 4: Message generated by SSMS.

System goes to wait state after executing each chunk (3+3+1).

The Real Benefit

If we would run the query synchronously(one after another) the first stored procedure will wait for the second procedures to be finished. In that case, it will take 1 minute 10 seconds to finish (as we have put several WAITFOR DELAY inside the stored procedures).

Stores Procedure	WAITFOR DELAY (Seconds)
CheckValidDate_usp	10
CheckDateGreaterThanAnotherDate_usp	10
CheckValidDateFormat_usp	15
CheckIsRequired_usp	5
CheckValidCreditCardNumber_usp	10
CheckCardTypeValid_usp	10
CheckIsDateInValidRange_usp	10
Total	70 Seconds

Table 4: WAITFOR DELAY setting inside each stored procedures.

Now let us run another query to determine how much time it took to complete all 7 stored procedures in asynchronous mode.

SELECT MIN(apsl.StartTime)     MinStartTime,
       MAX(apsl.EndTime)       MaxEndTime,
       DATEDIFF(second, MIN(apsl.StartTime), MAX(apsl.EndTime)) AS 
       ElapsedSeconds
FROM   AsyncProcessStatusLog apsl

The output will be:

MinStartTime	MaxEndTIme	ElapsedSeconds
2013-02-05 12:00:58.287	2103-02-05 12:01:38.283	40

It took only 40 seconds to finish.Thus we have saved 30 seconds and got almost 42% performance improvement.

Lesson Learned During from Real Life Implementation

Please be 100% careful about the following 2 exception inside the Stored Procedures code that you should consider for parallel execution. Because SQL Server TRY CATCH block does not honor this type of exception.
- Compile errors, such as syntax errors that prevent a batch from executing.
- Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.
In this situation, the stored procedure's running ("R") state for ever and will never changes thus the whole process will hang on by WAIT State. You must have to manually kill the process to break situation then.
Please add an extra code block inside " Wait_Unitl_Chunk_ToBe_Finished_usp" stored procedure to break to loop after a defined time. Because if the system went into a long wait state for a long running query or any non recognized system exception mentioned above so that other query can have slot or resource to execute.
Have a look on the SQL Agent Job history and clean up any non deleted job(for exception) periodically. The ExecuteSQL_ByAgentJob_usp stored procedures gives JobId as an output parameter you can save this to the metadata table for future reference to identify which job got exception and delete them automatically.

Conclusion

Executing long running queries asynchronously not only save time but also ensure maximum usages of CPU and memory. On top of this, not running all queries at a time, running them in a smaller chunks also prevent the queries from occupying all system resources so that that the queries do not suffer from lack of resources. Thus the overall system runs in a managed and optimized fashion.