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

Parallel Processing Expand / Collapse
Author
Message
Posted Tuesday, October 13, 2009 5:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:50 AM
Points: 1,570, Visits: 678
I did originally design the system using Service Broker Queues, but that just added complexity without functionality, I finally realised that KISS applied, and stopped trying to using SB Queues just because I thought they were cool, and used a number of Agent jobs instead.
You can get back as many rows as are returned in the OUTPUT clause, the processing I was designing only handles a single row /value at a time (RBAR by design), I suggest you try it, it would be easier than trying to explain!!



Post #802066
Posted Tuesday, October 13, 2009 7:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:31 PM
Points: 535, Visits: 877
I get everything in your post... but what does the colon do?

-- Clear the temp table ...
ProcessNextServer:
DELETE #DRT_Update



Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #802140
Posted Tuesday, October 13, 2009 7:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:50 AM
Points: 1,570, Visits: 678
ProcessNextServer:

the : is a label identifier, in this case the label is a target of a GOTo statement -

ProcessNextServer:

--
--Code goes here
--

GOTO ProcessNextServer




Post #802146
Posted Tuesday, October 13, 2009 8:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:31 PM
Points: 535, Visits: 877
Well I have learned something new today, I think I can go home now. :D

Thanks for the information.



Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #802161
Posted Wednesday, October 14, 2009 12:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Simon Facer (10/13/2009)
I did originally design the system using Service Broker Queues, but that just added complexity without functionality, I finally realised that KISS applied, and stopped trying to using SB Queues just because I thought they were cool, and used a number of Agent jobs instead.
You can get back as many rows as are returned in the OUTPUT clause, the processing I was designing only handles a single row /value at a time (RBAR by design), I suggest you try it, it would be easier than trying to explain!!

I have tried it Simon, and when I implemented it, SB eliminated about half of the code in your article and was considerably simpler. What SB gets rid of is all of the Event Queuing, Dequeuing and Locking worries, and code to handle all of that, it's all built in with SB.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #802523
Posted Wednesday, October 14, 2009 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:11 AM
Points: 4, Visits: 89
Can you explain why there will be a deadlocks when SQL Server doesn't perform a lock escalation even when the batch size is high?

Each process will lock different sets of key(rows) , I don't understand how deadlock could happen.

Thanks
Post #803097
Posted Wednesday, October 14, 2009 11:18 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 11:09 PM
Points: 162, Visits: 263
qiyuef (10/14/2009)
Can you explain why there will be a deadlocks when SQL Server doesn't perform a lock escalation even when the batch size is high?

Each process will lock different sets of key(rows) , I don't understand how deadlock could happen.

Thanks


Hi

I would request you to try out the example given in the article. It can be directly executed.
If you see the keys locked during the process, you'd find that there is some key which is locked in common by both the processes. Ideally that should not happen and it doesn't happen when the batch size is as small as 5 in the example. But for a relatively bigger batch size there is a conflict.

Thanks
Satish More
Post #803230
Posted Friday, October 16, 2009 9:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
RBarryYoung (10/14/2009)
I have tried it Simon, and when I implemented it, SB eliminated about half of the code in your article and was considerably simpler. What SB gets rid of is all of the Event Queuing, Dequeuing and Locking worries, and code to handle all of that, it's all built in with SB.

Absolutely. While the article is a good one (full marks for effort and thoroughness!) and Simon's snippet of code is a valid use of the OUTPUT clause, both are looking to re-invent things you get for free with Service Broker.

Many years ago I remember settling on READPAST and UPDLOCK for queueing - but I was never 100% happy with it as a solution. Service Broker provides a robust and scalable framework for free, so it seems daft not to take advantage.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #804333
Posted Monday, October 19, 2009 8:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 19, 2009 11:17 PM
Points: 2, Visits: 6
I am new to T-SQL.

Is there a way to spawn a pre-determined number of the processing threads from one script or stored procedure? So that, there is no need to use mutiple database connections (such as multiple SQL panes in SQL Server Management Studio).

Many thanks.
Post #805395
Posted Monday, October 19, 2009 10:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Polon Tang (10/19/2009)
I am new to T-SQL.

Is there a way to spawn a pre-determined number of the processing threads from one script or stored procedure? So that, there is no need to use mutiple database connections (such as multiple SQL panes in SQL Server Management Studio).

Many thanks.

Service Broker is the way to do that.

You could also make multiple duplicate SQL Agent jobs with different names and then submit them simultaneously from the SSMS interface. Kind of kludgey though.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #805411
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse