Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute T-SQL Scripts in Parallel


Execute T-SQL Scripts in Parallel

Author
Message
HelmutR
HelmutR
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 422
Thanks James, You did a great job! It's working perfect!

Best regards
Helmut
HelmutR
HelmutR
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 422
Great job! It's very useful!

Helmut
Wilfred van Dijk
Wilfred van Dijk
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 1352
Here's a handy function which shows the current status of the exec_queue:


if exists (select 1 from sys.objects where name = 'udf_queue_status')
drop function dbo.udf_queue_status
go

create function dbo.udf_queue_status()
returns table
as
return (
with cte(total, waiting, running, finished, succeeded, failed)
as
(
select (select count(*) from pmaster..exec_queue) as total
, (select count(*) from pmaster..exec_queue where worker_start_time is null and worker_end_time is null) as "waiting"
, (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is null) as "running"
, (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is not null) as "finished"
, (select count(*) from pmaster..exec_queue where return_code = 0) as "succeeded"
, (select count(*) from pmaster..exec_queue where return_code > 0) as "failed"
)
select total, waiting, running, finished, (cast(finished as real) / cast(coalesce(nullif(total,0),1) as real)) * 100 as percent_complete, succeeded, failed
from cte
)
go

EXECUTE sp_ms_marksystemobject 'udf_queue_status'
go



Wilfred
The best things in life are the simple things
Squong Diddley
Squong Diddley
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 39
Greetings James,

Very late to the party here, but wanted to add our thanks: excellent work, that is deeply appreciated.

Also to Wilfrid: thanks for your status function, nicely done and most useful.

All the best
--Squong
rpfelgueiras
rpfelgueiras
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
Do you have any information about the load that this script have on database servers?
Paul.Andrew
Paul.Andrew
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 150
Hi
Nice framework, we use something similar to load are data warehouses each night, but create the parallel processes dynamically as SQL agent jobs. Gives you a bit more visibility on whats happening and the system procedures sp_add_jobserver, sp_add_jobstep, start, delete etc are really easy to work with.
Thanks
mike 57299
mike 57299
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
Hi.
This is a great function. Can you tell me if the system uses one database file for this function? I have created separate databases for each client. If I run the sp_exec from each database, will they go to separate queues or one queue?

Thanks,
Mike
jmsma2002
jmsma2002
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 289
mike 57299 (2/27/2014)
Hi.
This is a great function. Can you tell me if the system uses one database file for this function? I have created separate databases for each client. If I run the sp_exec from each database, will they go to separate queues or one queue?

Thanks,
Mike


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



jmsma2002
jmsma2002
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 289
rpfelgueiras (8/13/2013)
Do you have any information about the load that this script have on database servers?


This system itself put tiny little load to database server. The real loads come from the queries that you use this system to launch. I only provide an efficient weapon to fire more bullets at the same time.

For example, we all know it bad idea to use a cursor to process a list of customer's data in sequence. By using my system, it's very easy to convert that code to make it process multiple customer's data in parallel.



dbatech99
dbatech99
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 299
This is great, useful code. Thanks for sharing. Are there any limitations with calling stored procedures? I have an index management proc which I want to run in parallel calls. When I fire them, they finish without executing and I don't get any error. They are not actually executing. Any thoughts?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search