Execute T-SQL Scripts in Parallel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by James Ma » Execute T-SQL Scripts in Parallel

24 posts, Page 1 of 3

1 2 3 »»»

Execute T-SQL Scripts in Parallel

Rate Topic

Display Mode

Topic Options

Author

Message

jmsma2002

Posted Saturday, August 29, 2009 3:46 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

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

Post #779498

jmsma2002

Posted Monday, August 31, 2009 11:14 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

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.

Post #780071

jbuttery

Posted Monday, August 31, 2009 3:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:40 AM
Points: 46, Visits: 133

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

Post #780284

jmsma2002

Posted Monday, August 31, 2009 5:07 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

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

Post #780345

jmsma2002

Posted Monday, August 31, 2009 5:29 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

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

Post #780351

RBarryYoung

Posted Monday, August 31, 2009 7:55 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

Where is sp_exec_init documented?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #780401

jmsma2002

Posted Tuesday, September 01, 2009 12:25 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

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

Good luck!
James

Post #780960

brownp

Posted Tuesday, September 01, 2009 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 2:54 PM
Points: 6, Visits: 29

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?

Post #781024

jmsma2002

Posted Tuesday, September 01, 2009 2:20 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 11:06 AM
Points: 13, Visits: 270

brownp (9/1/2009)

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)

Post #781056

jbuttery

Posted Tuesday, September 01, 2009 3:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:40 AM
Points: 46, Visits: 133

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.

Post #781096

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions