Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Execute T-SQL Scripts in Parallel Expand / Collapse
Author
Message
Posted Thursday, November 11, 2010 6:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:26 AM
Points: 78, Visits: 422
Thanks James, You did a great job! It's working perfect!

Best regards
Helmut
Post #1019270
Posted Thursday, November 18, 2010 5:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:26 AM
Points: 78, Visits: 422
Great job! It's very useful!

Helmut
Post #1022800
Posted Wednesday, December 8, 2010 3:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:07 AM
Points: 938, Visits: 1,142
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
Post #1031767
Posted Tuesday, April 16, 2013 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 9:10 AM
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
Post #1442808
Posted Tuesday, August 13, 2013 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 2:15 AM
Points: 5, Visits: 17
Do you have any information about the load that this script have on database servers?
Post #1483662
Posted Wednesday, August 14, 2013 6:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:36 AM
Points: 97, Visits: 132
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
Post #1484229
Posted Thursday, February 27, 2014 8:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 1:51 PM
Points: 138, Visits: 370
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
Post #1546182
Posted Monday, March 3, 2014 3:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:29 PM
Points: 16, Visits: 280
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




Post #1547105
Posted Monday, March 3, 2014 3:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:29 PM
Points: 16, Visits: 280
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.



Post #1547115
Posted Sunday, May 18, 2014 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 13, 2014 9:48 AM
Points: 8, Visits: 243
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?
Post #1572083
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse