The system only uses one database called pmaster.

Each sp_exec_init call will create a queue per that connection, and all sp_exec's after that and within the same connection will queue the query to that queue, the sp_exec_end call will drop that queue and clean up. Internally each queued query will use a new spid to run.

It's allowed to have multiple connections, and within each of those connections you may run a pair of sp_exec_i nit and sp_exec_end. They won’t interrupt each other.

Within one connection (or one sql script), I only expect one level of sp_exec_init and sp_exec_end pair, so do not make nested calls directly. However, you may still sp_exec a string of statements including sp_exec_init and sp_exec_end pair, and it will be run in another spid and start a whole isolated world. Personally I have not run scenario like that.

This is a view that I use to monitor the queues --

USE [pmaster]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE view [dbo].[vw_exec_state] as

select m.master_spid,m.worker_num,s.*

from dbo.exec_master m

cross apply(

select COUNT(1) [queued]

,SUM(CASE when worker_start_time is not null then 1 else 0 END) [started]

,SUM(CASE when worker_end_time is not null then 1 else 0 END) [finished]

,SUM(CASE when (worker_start_time is not null) and (worker_end_time is null) then 1 else 0 END) [running]

,SUM(CASE return_code when 0 then 1 else 0 END) [success]

,lower(dbo.convert_seconds(DATEDIFF(SECOND,MIN(q.create_time),GETDATE()))) [duration]

,MAX(q.exec_queue_id) max_queue_id

from dbo.exec_queue q where q.master_spid = m.master_spid

) s

GO

Sometimes you may lose the connection before calling the sp_exec_end, or you chose not to call sp_exec_end so that you may check status later. This proc allow you to drop and cleanup a queue that was created in another connection --

USE [pmaster]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[p_exec_end2]

@master_spid smallint = null

,@wait bit = 1

as

set nocount on

if (@master_spid is null) set @master_spid=@@SPID

if @wait=1

while exists(select * from dbo.exec_queue where master_spid=@master_spid and worker_spid is null)

waitfor delay '00:00:03';

begin try

begin transaction;

exec dbo._p_exec_clean @master_spid,'';

commit transaction;

end try

begin catch

exec dbo.p_printerror;

IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

return Error_number();

end catch

exec dbo.p_exec_cleanqueue;

return @@error

GO