Showing posts with label clarifications. Show all posts
Showing posts with label clarifications. Show all posts

Thursday, March 22, 2012

Clarifications on Queue Service and Queue Readers

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

LubomirOnly reading one message per invocation will together slow down you application enough so that the other things you're worried about will probably be minor by comparison. As long as there are enough seperate dialogs so that there's at least one dialog per queue read you should see parallelism because a receive only locks one conversation group. If I understand what you're doing you will have one one dialog per conversation group so you will lock on one dialog per transaction.|||

Hello,

A. Service Broker functionality is entirely driven by the normal transactional operations rules of SQL Server. All effects of any Service Broker verbs (BEGIN DIALOG, SEND, RECEIVE, END CONVERSATION etc) will be undone if the transaction is rolled back. So if a queue reder rollsback a RECEIVE, the messages will be back in the queue, available again for activation. Be careful dough, if you activated stored procedure rollsback frequently, you are in danger of triggering poison message detection mechanism. If the activated procedure rollsback the RECEIVE verb result 5 times in a row, the whole queue will be disabled. For details, see http://msdn2.microsoft.com/en-us/library/ms166137.aspx

And yes, queue readers will continue to be activated as long as there are messages in the queue available for receive. Also, queue readers will be activated each time a database comes online, like at server start up.

B. Queue readers can progress in paralel as long as there are multiple conversations available for processing. RECEIVE verb will lock the conversations on which it returned messages, so that no other queue reader processes messages from the same conversation(s). for more details, see the 'Conversation groups' topic in BOL, at http://msdn2.microsoft.com/en-us/library/ms166131(en-US,SQL.90).aspx

HTH,
~ Remus

|||Thank you very much for the prompt replies guys.
This makes perfect sense.

Lubomir

Clarifications on Queue Service and Queue Readers

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

LubomirOnly reading one message per invocation will together slow down you application enough so that the other things you're worried about will probably be minor by comparison. As long as there are enough seperate dialogs so that there's at least one dialog per queue read you should see parallelism because a receive only locks one conversation group. If I understand what you're doing you will have one one dialog per conversation group so you will lock on one dialog per transaction.|||

Hello,

A. Service Broker functionality is entirely driven by the normal transactional operations rules of SQL Server. All effects of any Service Broker verbs (BEGIN DIALOG, SEND, RECEIVE, END CONVERSATION etc) will be undone if the transaction is rolled back. So if a queue reder rollsback a RECEIVE, the messages will be back in the queue, available again for activation. Be careful dough, if you activated stored procedure rollsback frequently, you are in danger of triggering poison message detection mechanism. If the activated procedure rollsback the RECEIVE verb result 5 times in a row, the whole queue will be disabled. For details, see http://msdn2.microsoft.com/en-us/library/ms166137.aspx

And yes, queue readers will continue to be activated as long as there are messages in the queue available for receive. Also, queue readers will be activated each time a database comes online, like at server start up.

B. Queue readers can progress in paralel as long as there are multiple conversations available for processing. RECEIVE verb will lock the conversations on which it returned messages, so that no other queue reader processes messages from the same conversation(s). for more details, see the 'Conversation groups' topic in BOL, at http://msdn2.microsoft.com/en-us/library/ms166131(en-US,SQL.90).aspx

HTH,
~ Remus

|||Thank you very much for the prompt replies guys.
This makes perfect sense.

Lubomirsqlsql

Clarifications on DW

Hi,
hope someone could help me out with these issues I
have.
(1)when u do an extraction from the source database table
to the destination database table using DTS, does dts copy
only the data that is not available in the destination
table or does it Reload the entire sets of rows in the
source table irrespective of whether its there are not? is
it possible to do incremental loads?
(2)how can changes be managed in DW Database? example:
take a situation where I extract inventory data from my
OLTP and put it on to a DW Database on day 1. suppose i go
an change the Quantity on Hand column for a particlur
record in the OLTP, how can i reflect this change on my DW
wihout doing a load. is it possbile to monitor for changes
in the OLTP and update same in the DW?
(3)where can i get some tutorials n writing front end
applications to query a Cube created in Analysis Server?
Thanks in Advance for your help
Regards
Imran(1) Yes it is possible to do incremental loads. See this page for details -
http://www.sqldts.com/?277,1
(2) This is the beauty of SCD's - slowly changing dimensions - above link
will help along with this page - http://www.dbmsmag.com/9604d05.html
(3) Before writing your own, you might want to look at reporting services -
http://www.microsoft.com/sql/reporting/default.asp
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <anonymous@.discussions.microsoft.com> wrote in message
news:50d001c40001$bd78bbb0$a401280a@.phx.gbl...
> Hi,
> hope someone could help me out with these issues I
> have.
> (1)when u do an extraction from the source database table
> to the destination database table using DTS, does dts copy
> only the data that is not available in the destination
> table or does it Reload the entire sets of rows in the
> source table irrespective of whether its there are not? is
> it possible to do incremental loads?
> (2)how can changes be managed in DW Database? example:
> take a situation where I extract inventory data from my
> OLTP and put it on to a DW Database on day 1. suppose i go
> an change the Quantity on Hand column for a particlur
> record in the OLTP, how can i reflect this change on my DW
> wihout doing a load. is it possbile to monitor for changes
> in the OLTP and update same in the DW?
> (3)where can i get some tutorials n writing front end
> applications to query a Cube created in Analysis Server?
> Thanks in Advance for your help
> Regards
> Imran
>