October 12, 2009 at 8:59 am
[p]Charles, I think you missed the point ... [/p]
SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.
October 12, 2009 at 9:11 am
Simon,
I see that your discussion post mentions multiple servers...
But in the article in question, http://qa.sqlservercentral.com/articles/Locking/67952/ only seems to be talking about one server.
So what does the article's technique have that MS SQL Server doesn't out of the box?
Thanks!
Charles Wilt
Simon Facer (10/12/2009)
[p]Charles, I think you missed the point ... [/p]SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.
October 12, 2009 at 9:23 am
The article was about how to process a single data set using multiple processes. The concept is how to start multiple independant non-overlapping processes against a single table. My example shows a use of the given technique (or my alternative) that cannot be accomplished with SMP.
October 12, 2009 at 7:41 pm
Very nice article. One suggestion would be to look into using Service Broker, as I believe that you will find that it can simplify or even enhance several aspects of your approach.
[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]
October 12, 2009 at 11:41 pm
Very good article.
Couple of things i would like to know about the update statement with output clause. What type of locks would be involved with update statement and how to get more than one record with the update statement since you have used MIN function.
"Keep Trying"
October 13, 2009 at 5:13 am
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!!
October 13, 2009 at 7:39 am
I get everything in your post... but what does the colon do?
-- Clear the temp table ...
ProcessNextServer:
DELETE #DRT_Update
October 13, 2009 at 7:47 am
ProcessNextServer: the : is a label identifier, in this case the label is a target of a GOTo statement -
ProcessNextServer:
--
--Code goes here
--
GOTO ProcessNextServer
October 13, 2009 at 8:13 am
Well I have learned something new today, I think I can go home now. 😀
Thanks for the information.
October 14, 2009 at 12:00 am
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.
[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]
October 14, 2009 at 2:34 pm
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
October 14, 2009 at 11:18 pm
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
October 16, 2009 at 9:58 am
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
SQLkiwi blog
@SQL_Kiwi
October 19, 2009 at 8:59 pm
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.
October 19, 2009 at 10:07 pm
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.
[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]
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply