Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
  • Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
  • Effective monitoring: People want to see which session is running what.
  • Adjustable maximum threads in the course of execution.


I implemented those and posted on CodePlex, http://psql.codeplex.com. It’s very easy to use.

set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete

The main session is responsible for starting an asynchronous batch, setting execution configurations, queuing tasks, waiting for batch completion, and stopping a batch.

set xact_abort on
begin transaction
--Code here...
commit

An asynchronous batch must be started within a transaction. The transaction will not be propagated in to any asynchronous commands. When transaction is completed (commit or rollback), the batch will stop automatically, currently executing asynchronous commands will be cancelled and waiting commands will be aborted.

It’s recommended to set xact_abort on. This guarantees that when the main session is cancelled/killed, transaction will be rolled back automatically, hence batch will be cancelled. Procedures used in the first example

  • AsyncName: Optionally you can give current batch a name.
  • AsyncMaxThreads: Set max number of concurrent worker threads
  • AsyncExecute: enqueue a T-SQL command. As long as a command is enqueued, it will be started immediately when there is an available worker threads
  • AsyncWait: this procedure will wait all worker threads to complete.

One more example

set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test-1'
exec dbo.AsyncMaxThreads @MaxThreads = 3
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10'''
exec dbo.AsyncWait -- wait commands above to complete
--commit
--begin transaction
exec dbo.AsyncName @Name = 'Test-2'
exec dbo.AsyncMaxThreads @MaxThreads = 2
----Start: those queries will be executed by 2 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:35'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:25'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:15'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete

Batch name and max threads can be changed any time before a batch is finished. In example above, First 4 asynchronous commands will be run concurrently by 3 threads. After they are done, last 3 commands will be executed by 2 threads.

To splitting this batch into two, you can simply add commit after the first AsyncWait invocation and add begin transaction right after the commit statement you just added. This will not make difference in terms of asynchronous command executions. But it will show differently in the monitor.

While the first demo is running, run following query in a separate session

select * from dbo.AsyncTaskList()


PSQL-Result100

Pass the main session id into AsyncTaskStatus function to get more details.

select * from dbo.AsyncTaskStatus(53)

PSQL-Result200
Function AsyncTaskStatus will give you the statuses of each command in a asynchronous batch. This structure will stay in memory and attached to the main session id (53 in this case) until the next batch gets started(in session 53).

Run the first demo again, click on “Stop” in SSMS while the main session is waiting/running, and then check the results from function dbo.AsyncTaskStatus to see how it behaves.

select * from dbo.AsyncTaskStatus(53)

PSQL-Result300

What if one of the workers generates an error? By default, executing commands will be cancelled, waiting commands will be aborted, and errors generated by the worker will be thrown by dbo.AsyncWait procedure

set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''; raiserror(''Error'', 16, 1)'
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete

PSQL-Result400

Check both monitoring functions

select * from dbo.AsyncTaskList()
select * from dbo.AsyncTaskStatus(53)

PSQL-Result500

This behaviour can be adjusted by following procedure.

  • Procedure AsyncCloseWhenException @CloseWhenException, @ForceClose
  • This procedure should be run within main session. It defines how the batch behave when there is an exception in one of the worker thread.

    When @CloseWhenException is 0, exceptions in worker threads will not terminate the main session.
    When @CloseWhenException is 1, exceptions in any worker threads will terminate the main session.

    @ForceClose is applicable when @CloseWhenException=1. It tells the framework how to terminate the running workers. The commands in waiting list will be aborted regardless.
    When @ForceClose is 0, main session will exit until all running workers complete
    When @ForceClose is 1, running workers will be cancelled. Main session will return once the command cancellation is done

This procedure tells the framework what to do when there is an exception in a running worker. For instance, I want the process to continue when exception.

set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
exec dbo.AsyncCloseWhenException @CloseWhenException=0, @ForceClose=0
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10''; raiserror(''Error'', 16, 1)'
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete

In this particular case, the main session will not return any errors. But the monitor will tell which session has error.

select * from dbo.AsyncTaskList()
select * from dbo.AsyncTaskStatus(53)

PSQL-Result600

An running asynchronous batch can be terminated by AsyncClose procedure.

  • Procedure AsyncClose @spid, @ForceClose
  • This procedure allows user to terminate a running asynchronous batch. The commands enlisted in the batch but not started yet will be aborted.
    @spid: main session’s session id.
    When @ForceClose is 0, main session will exit until all running workers complete
    When @ForceClose is 1, running workers will be cancelled. Main session will return once the command cancellation is done.

For an instance, re-run the first demo again

set xact_abort on
begin transaction
exec dbo.AsyncName @Name = 'Test'
exec dbo.AsyncMaxThreads @MaxThreads = 3
----Start: those queries will be executed by 3 threads
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:40'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:30'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:20'''
exec dbo.AsyncExecute @Command = 'waitfor delay ''00:00:10'''
exec dbo.AsyncWait -- wait commands above to complete
commit -- async batch complete

From another session, run following command

exec dbo.AsyncClose 53, 0 
select * from dbo.AsyncTaskList()
select * from dbo.AsyncTaskStatus(53)

PSQL-Result700

The waiting session is aborted. the running session will keep going until it complete. Main thread/session will wait as well. What if @ForceClose is set

exec dbo.AsyncClose 53, 1
select * from dbo.AsyncTaskList()
select * from dbo.AsyncTaskStatus(53)

PSQL-Result800

The executing workers are cancelled and the waiting workers are aborted.

More implementations are coming

  • Plan to support enlisting parameterized commands
  • Plan to support call back events. For instance, a procedure can be configured and called before a command is started.
  • Unload an executing or waiting task.

This project is posted on CodePlex, http://psql.codeplex.com. I am still improving it. Any ideas, suggestions, and questions you may have or you’d like to join the project, please contact me.

Brought you by http://sqlnotes.info

Run T-SQL in Parallel

You May Also Like

6 thoughts on “Run T-SQL in Parallel

  1. You have System.Threading in the import list. I don’t see that on the approved list of assemblies. Is this assembly required to be marked UNSAFE?

  2. Hi John

    I know this was posted long time ago but I really need your source code for Async calling T_SQL batches.
    The site: http://psql.codeplex.com no longer exists.
    Can you give me another address please.

    Thanks

Leave a Reply to dan holmes Cancel reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.