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 Saturday, August 29, 2009 3:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:29 PM
Points: 16, Visits: 280
Comments posted to this topic are about the item Execute T-SQL Scripts in Parallel


Post #779498
Posted Monday, August 31, 2009 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:29 PM
Points: 16, Visits: 280
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
Posted Monday, August 31, 2009 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, August 31, 2009 5:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:29 PM
Points: 16, Visits: 280
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
Posted Monday, August 31, 2009 5:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 9,294, Visits: 9,492
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
Posted Tuesday, September 1, 2009 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 1, 2009 2:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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




Post #781056
Posted Tuesday, September 1, 2009 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse