SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parallel Processing


Parallel Processing

Author
Message
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 724
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!!



Adam Seniuk
Adam Seniuk
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 1040
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
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 724
ProcessNextServer:

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

ProcessNextServer:

--
--Code goes here
--

GOTO ProcessNextServer





Adam Seniuk
Adam Seniuk
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 1040
Well I have learned something new today, I think I can go home now. BigGrin

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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14834 Visits: 9518
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."
qiyuef
qiyuef
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 103
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
Sunny-138471
Sunny-138471
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 266
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
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15792 Visits: 11355
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Polon Tang
Polon Tang
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14834 Visits: 9518
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search