Tim
This subject is quite intricate and has many facets. It also appears often when discussing Service Broker, so I'll try to address it in a series of blog articles. I've started this today, see http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
HTH,
~ Remus
|||Thanks Remus. I am starting to notice that I am getting messages such as "There is insufficient system memory to run this query." and "There is insufficient memory available in the buffer pool." when I have a lot of conversations occurring (about 300K records in conversation_endpoints view). I am thinking that this is directly attributable to me creating a new conversation for every audit record(s) created. What is the best way for me to test that this is the case...that Service Broker is really the culprit in tying up all of my system memory?|||look in sys.dm_os_memory_clerks to see how memory is allocated|||Ok, sounds good. I am almost 100% sure it relates to me creating a new dialog for each message I pass.Do you plan to post another blog anytime soon regarding reusing conversations? The situation I am currently trying to figure out is how to handle closing (or handling) the conversations so that I can reuse them....more specifically:
1. I check a table to see if there are any dialog handles free to use. If they are not, I create a new one and send a message to a queue.
2. The activation proc on the queue gets the message from the queue, but the handle it receives is not the same as the one that was created when I sent the message. It seems that this handle represents the target (from sys.conversation_endpoints). At this point, I can't close that end of the conversation when I have processed the message because if I do, it puts the other end, the initiator, in a disconnected_inbound state, which means I can't reuse it later and send another conversation on it. So, what is the best way to handle that? I want to be able to reuse the handle that I originally created, but not really sure the best way to do it. Thanks in advance.
Tim|||
Yes, I plan a post soon. Here is how I recommend doing it: have a criteria when a dialog should be 'recycled' (ended and a new one started). Good candidate criterias would be 'after N messages sent' or 'X minutes/hours/days after was created'. When this criteria is met, the initiator should sent a special message, something like 'EndOfStream' and removes the handle from the association table (So subsequent usp)Send calls will start a new one). When the target receives this EndOfStream message, it responds with and END CONVERSATION. When the initiator receives the EndDialog message, it ends it side (initiator also must have activation on it's queue). I have arguments why I prefer this pattern, I'll detail in blog.
HTH,
~ Remus
|||Thanks Remus, I eagerly look forward to it. Also, here is a small dump of my dm_os_memory_clerks view when I was receiving the errors: type single_pages_kb multi_pages_kb OBJECTSTORE_SERVICE_BROKER 884584 0 CACHESTORE_BROKERTO 176936 0 MEMORYCLERK_BHF 146696 0 OBJECTSTORE_LOCK_MANAGER 126064 0 OBJECTSTORE_SERVICE_BROKER 101168 0 MEMORYCLERK_SQLSERVICEBROKER 19256 192 MEMORYCLERK_SQLSTORENG 10624 7088 CACHESTORE_OBJCP 6304 32 MEMORYCLERK_SOSNODE 6224 6048 MEMORYCLERK_SQLGENERAL 1832 2016 I also started getting a fun new error in one of my activation procedures: Internal Error: Text manager cannot continue with current statement. Run DBCC CHECKTABLE., which I think is directly related to me creating a new dialog for every message created.|||This is a procedure I wrote to manage a dialog pool. Basically it creates a number of conversations and then uses them until the number available drops below a certain threshold value. It then selects one at random (so you aren't reusing the same one every time). It works great, but I'd like to hear any comments from the experts.
CREATE PROCEDURE [usp_DialogFactoryCreate]
(
@.minDialogs AS INT,
@.maxDialogs AS INT,
@.fromServiceName AS NVARCHAR(256),
@.toServiceName AS NVARCHAR(256),
@.contractName AS NVARCHAR(256),
@.selectedDialog UNIQUEIDENTIFIER OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.dialogCount INT;
DECLARE @.conversationHandle AS UNIQUEIDENTIFIER;
-- State should be either STARTED_OUTBOUND or CONVERSING
SET @.dialogCount = (SELECT COUNT(*) FROM sys.conversation_endpoints WITH (NOLOCK)
WHERE far_service = @.toServiceName
AND state IN ('SO', 'CO'));
-- Create dialogs until we hit the maximum
-- This will also dictate how many activated procedures will be created for the queue
IF ( @.dialogCount < @.minDialogs)
BEGIN
WHILE (@.dialogCount <= @.maxDialogs)
BEGIN
-- Create dialogs with infinite lifetime for our pool
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE @.fromServiceName
TO SERVICE @.toServiceName
ON CONTRACT @.contractName
WITH ENCRYPTION = OFF;
SET @.dialogCount = @.dialogCount + 1;
END
END
-- Randomly select a dialog conversation
SET @.selectedDialog = (SELECT TOP(1) conversation_handle
FROM sys.conversation_endpoints
WHERE far_service = @.toServiceName AND state IN ('SO', 'CO')
ORDER BY NEWID());
RETURN (0);
END
GO
|||Variuos threads/transaction calling this procedure will conflict for the same conversation and cause contention.|||Hi Remus,
Ive solved my memory problem by reusing dialogs based upon how long they have been in use. However, now I am running into another tricky problem. What I am noticing when many messages are being passed around is that internal service broker tables are causing a huge number of locks in the database, sometimes over 100,000 of them, which will really lock up other processes on the server. How are these internal tables (QUEUE_MESSAGES_) constructed? Is it a matter of one per message received and processed? I have a feeling that it is being caused by me receiving (RECEIVE TOP(1)) one message at a time and processing that way. I know it isn't a great way to do it, and it is slower, but is it what is causing all of these internal locking in the database? BTW...reusing a dialog based upon how long it has been open was a great idea...thank you very much for it.
Tim
