Execute T-SQL Scripts in Parallel

Question

Execute T-SQL Scripts in Parallel

jmsma2002

Say Hey Kid

Points: 692
More actions
August 29, 2009 at 3:46 pm

#209289

Comments posted to this topic are about the item Execute T-SQL Scripts in Parallel

Viewing 15 posts - 1 through 15 (of 35 total)

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

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

Another improvement that I can think out is to implement the 2PC transaction protocol. So this tool can be even used to improve performance of distributed trancations.

jbuttery Ten Centuries Points: 1226 More actions · Answer 2

I tried what you suggested and also tried running entire script. Couldn't get either to run. Could you post another script that will?

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

jbuttery (8/31/2009)
I tried what you suggested and also tried running entire script. Couldn't get either to run. Could you post another script that will?

Sorry for the inconvenience. I find the web page’s default encoding (Unicode) actually adds some hidden characters to the script.

Now I’ve found the trick: First change the page encoding to be "Western European (Windows)", secondly copy and paste the code to a SSMS window, then change the strange character (it’s â€ˆ on my screen) to white space. It works on my side now.

This is the first time for me to contribute script in this site, and I’ve not figured out how to change the encoding behavior here.

Good luck!

James

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

FYI --

I have re-submitted the same script "Execute T-SQL Scripts in Parallel" since I find I cannot copy and paste the code due to the Page's Unicode encoding adds strange chars to the script. I re-submitted it by changing to "Western European (Windows)" on the editor page first, and I'll test once I got it approved.

Thanks,

James

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Where is sp_exec_init documented?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

I still have the same issue after re-submitting. Please use the trick mentioned above to copy and paste the script.

Good luck!

James

brownp Valued Member Points: 70 More actions · Answer 7

I was able to clean the text and implement the solution. It runs great on our 4cpu dev server. I did notice that when the SQL you pass to the ‘sp_exec’ procedure produces an error you get no error message returned to you in the SSMS environment. Any work around for this other than copious logging?

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

brownp (9/1/2009)
I was able to clean the text and implement the solution. It runs great on our 4cpu dev server. I did notice that when the SQL you pass to the ‘sp_exec’ procedure produces an error you get no error message returned to you in the SSMS environment. Any work around for this other than copious logging?

Great to hear that you like it. You can use the following query to monitor all the sqls' running status. The error should appear in the [return_msg] column if there is any.

select * from pmaster.dbo.exec_queue

Basically the tool can be imagined as to "create many hidden SSMS windows", so I simply save all status in that table. As for workaround, you might change the pmaster.dbo.p_exec_wait stored procedure to print errors or raiserror, etc.

The sp_exec_wait is provided for the programming convenience. Sometimes you don't need it. For example, weeks ago I used the tool to rebuild all index of our huge database in 4-way parallel (not the meaning of execution plan parallel but the scripts parallel). I disabled the whole SQL Agent service in case any job got started accidentally, then I simply sp_exec all the rebuild sqls and used the following query to monitor their running.

select *

,wait_or_work=convert(varchar,dateadd(ss,datediff(ss,isnull(worker_start_time,send_time),isnull(worker_end_time,getdate())),'00:00:00'),114)

,sql=(select top 1 log_msg from pmaster.dbo.exec_log l(nolock) where l.exec_queue_id=q.exec_queue_id order by log_id)

from pmaster.dbo.exec_queue q(nolock)

jbuttery Ten Centuries Points: 1226 More actions · Answer 9

Cleaning the text format wasn't obvious. I brought it into a hex editor, cleared strange chars and posted it back into SSMS and it ran without errors.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

RBarryYoung (8/31/2009)
Where is sp_exec_init documented?

Still waiting for an answer ... ?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

RBarryYoung (9/1/2009)
RBarryYoung (8/31/2009)
Where is sp_exec_init documented?
Still waiting for an answer ... ?

Sorry, I haven't written a lot document yet. Do you only want to know how to call it? It's like:

EXEC dbo.sp_exec_init [@worker_num=4]

The @worker_num means how many worker processes you want to run your sql's. It's optional argument.

Indeed that's the wrapper of this stored procedure in the pmaster database. The code is not long or fancy, I think you guys can figure it out. Forgive me this time since my work load is heavy at present.

CREATE procedure [dbo].[p_exec_init]

@worker_num smallint = 4

as

......

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

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:

Wilfred
The best things in life are the simple things

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

Nested tasks not possible?

Situation: from a main routine, I execute a subroutine with sp_exec. inside this subroutine, some calculations are done (a generated SQL statment). If I execute this SQL statement with sp_exec it seems it's not executed at all. no error message either. Can't find the reason. (SQLStatement is ok, checked)

exec mainroutine

-- in loop: sp_exec subroutine

-- in loop: sp_exec sql statement

Question: can you add another process from within a parallel process?

Wilfred
The best things in life are the simple things

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

If you simply sp_exec in loops, notice still they (many worker spids) are all controlled by current master SPID and in theory they will all run in the same parallel scope. However, you can use sp_exec_wait to control the running sequence between sql groups. Like the following pseudo code:

sp_exec_init

loop begin

sp_exec '...'

loop begin

sp_exec '...'

loop end

sp_exec_wait

sp_exec '...'

loop end

sp_exec_end

If you really want to start other parallel scopes within current parallel process, the following pseudo code shows the trick. You can launch a worker spid to run the sp_exec script.

--This is in current master process

sp_exec_init

--This will launch a worker process who will master a bunch of worker spids

sp_exec '

sp_exec_init

sp_exec ''...''

sp_exec_wait

sp_exec_end

'

--This will launch another worker process who will master a bunch of worker spids

sp_exec '

sp_exec_init

sp_exec ''...''

sp_exec_wait

sp_exec_end

'

sp_exec_wait

sp_exec_end

Anytime you can look at pmaster.dbo.exec_queue table to debug the running of your script.