Execute T-SQL Scripts in Parallel

  • jmsma2002

    Say Hey Kid

    Points: 692

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

  • jmsma2002

    Say Hey Kid

    Points: 692

    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: 1196

    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

    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

    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: 143327

    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

    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

    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

    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: 1196

    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: 143327

    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

    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: 8969

    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: 8969

    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

    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 '...'

    sp_exec '...'

    loop begin

    sp_exec '...'

    loop end

    sp_exec_wait

    sp_exec '...'

    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 ''...''

    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 ''...''

    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.

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

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