May 7, 2010 at 12:20 pm
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
May 7, 2010 at 3:09 pm
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.
May 10, 2010 at 8:26 am
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.
May 10, 2010 at 1:04 pm
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.
June 17, 2010 at 4:06 am
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
November 11, 2010 at 6:09 am
Thanks James, You did a great job! It's working perfect!
Best regards
Helmut
November 18, 2010 at 5:46 am
Great job! It's very useful!
Helmut
December 8, 2010 at 3:59 am
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
April 16, 2013 at 9:28 am
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
August 13, 2013 at 5:30 am
Do you have any information about the load that this script have on database servers?
August 14, 2013 at 6:09 am
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
February 27, 2014 at 8:37 pm
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
March 3, 2014 at 3:12 pm
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
March 3, 2014 at 3:39 pm
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.
May 18, 2014 at 8:36 am
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?
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply