Execute T-SQL Scripts in Parallel

Question

Execute T-SQL Scripts in Parallel

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Robb Melancon Ten Centuries Points: 1129 More actions · Answer 1

I like the tool, however is there anyway to have it return rows? I have a need to execute multiple sprocs in parallel but these procs return result sets for a report.

Thanks for your time,

Robb

jmsma2002 Say Hey Kid Points: 692 More actions · Answer 2

Robb Melancon (5/7/2010)
I like the tool, however is there anyway to have it return rows? I have a need to execute multiple sprocs in parallel but these procs return result sets for a report.
Thanks for your time,
Robb

The method I've been using is to create a table in tempdb (don't use those like #tmp but use a real table like tempdb.dbo.tmp_result), and let all the parallel runing scripts insert into the table. Then you can collect combined results after all sprocs return.

Robb Melancon Ten Centuries Points: 1129 More actions · Answer 3

Thanks for the reply. The only problem with using temps is that they would need to be global or declared directly in temp database like you said. This is fine for single user but when you have multiple users running reports I'd need to create a guid per each run of the report and filter on the guid. There will be other problem potentially with this like locking for inserts while some other user is running a report etc. It may be the only solution though. Another thing I thought would be passing the table as a parameter but this may have some performance issues. Anyway, thanks again for the reply.

jmsma2002 Say Hey Kid Points: 692 More actions · Answer 4

Robb Melancon (5/10/2010)
Thanks for the reply. The only problem with using temps is that they would need to be global or declared directly in temp database like you said. This is fine for single user but when you have multiple users running reports I'd need to create a guid per each run of the report and filter on the guid. There will be other problem potentially with this like locking for inserts while some other user is running a report etc. It may be the only solution though. Another thing I thought would be passing the table as a parameter but this may have some performance issues. Anyway, thanks again for the reply.

In that case, perhaps you can create seperate temp tables for each users instead of filtering on a guid column, like:

tmp_5B12952D614E497A93F7EA670B279A75

tmp_5C0EDDEFE61740EDAB5A0DDBC67088F0

I think that won't require filtering on guid and won't have locking issues. Only need to optimize tempdb a bit or even creating a user database as the temp db.

gsc_dba SSCertifiable Points: 5407 More actions · Answer 5

Wilfred van Dijk (3/19/2010)
Excellent piece of code! I implemented it to speed-up some Datawarehouse processing routines. Processing time dropped from 500 minutes to 70 minutes! :w00t: :w00t: :w00t:

I am intrigued how you achieved this, can you please give a brief example...

After some subtle changes in our ETL processes I can now see how!

gsc_dba

HelmutR Ten Centuries Points: 1184 More actions · Answer 6

Thanks James, You did a great job! It's working perfect!

Best regards

Helmut

HelmutR Ten Centuries Points: 1184 More actions · Answer 7

HelmutR

Ten Centuries

Points: 1184

November 18, 2010 at 5:46 am

#1252425

Great job! It's very useful!

Helmut

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 8

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 Grasshopper Points: 13 More actions · Answer 9

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 SSC Veteran Points: 219 More actions · Answer 10

Do you have any information about the load that this script have on database servers?

mrpaulandrew Mr or Mrs. 500 Points: 552 More actions · Answer 11

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 SSCrazy Points: 2529 More actions · Answer 12

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 Say Hey Kid Points: 692 More actions · Answer 13

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 Say Hey Kid Points: 692 More actions · Answer 14

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 Mr or Mrs. 500 Points: 599 More actions · Answer 15

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?