Unfinished dialog conversations in Service Broker

Hello! Could someone help me with the subject?

Our company is developing an application with quite a high load on
database. Our DBMS is SQL Server 2005. We're using QuieryNotification
(via SQLDependency object) for caching data to speed up Web-interface.

After utilizing that feature we've faced a problem. Our SQL Server
started to consume more and more virtual memory (up to 1.5 Gb) and
(which is the worst) CPU time. The problem is that we've got LOTS (up
to 4M) of unfinished dialog conversations in Service Broker. Ok, I
finished that conversation by using

END CONVERSATION @ConvHandle WITH CLEANUP;

for each conversation in sys. conversation_endpoin
ts with sate 'DI' (all
of them had that state actually).
It helped for a while - memory consumption dropped to 100 - 150 Mb
(after SQL Server restart). But those conversation endpoints keep
appearing.

The next thing I did was:
(conversation endpoints to non-existing services)

select
SVC.name as this_service,
CEP.state_desc,
CEP.far_service,
SVCC.name as contract,
CEP.is_initiator,
CEP.is_system,
CEP. far_broker_instance,

CEP.dialog_timer
from
sys. conversation_endpoin
ts CEP,
sys.services as SVC,
sys.service_contracts as SVCC
where
SVC.service_id = CEP.service_id and
SVCC.service_contract_id = CEP.service_contract_id and
CEP.far_service not in (select name FROM sys.services)

The result was as following (only first 2 rows):

1	http://schemas.microsoft.com/SQL/No...r />
-4ECE-923
0-8231B08C3A37	1900-01-01
00:00:00.000
2	http://schemas.microsoft.com/SQL/No...r />
-4ECE-923
0-8231B08C3A37	1900-01-01
00:00:00.000

....

and day after day the number of such conversation endpoints keeps
growing.

The questions arise:
1) Should we worry about (that high) memory consumption? Now we're
developing our application so no actual load is currently generated. I
wonder what should we expect in production environment. (Our
development server is P4 HT 3.0 GHz, 2 Gb of RAM under Windows 2003
Server sp1)
2) Those "dead" conversation endpoints are "system". Who should close
them? And why are they still in DB even after far_service is dropped?
3) Can anybody tell me how much memory (and CPU time) does Service
Broker use? And how it depends on number of services/queues/dialog
conversations/messages?

Thank you.

When the server has this high memory load, can you run the DBCC MEMORYSTATUS
command and either post back here with the result or send it to me at
 remus(dot)rusanu(at)
microsoft(dot)com
I want to see if the memory consumption is indeed related to the large
number of stranded dialogs.

I'll try to get a hold of somebody from QN/SqlDependency to look into why
the dialogs are stranded in the first place.

To answer your questions:

1) When the system memory consumption goes high, issue a DBCC
 FREESYSTEMCACHE('ALL
') statement. If the memory consumption goes down
significantly, you shouldn't be worried for the productions system. It means
all memory is consumed by caches and they would free the memory if the
system needs it.
2) 'system' conversations should be closed by the Query Notification
infrastructure (or Event Notifications for EN dialogs). The two endpoints of
a conversation (initiator and target) are distinct and they communicate
trough messages (even when within the same database). When you drop the
'far_service', the target endpoint is closed and an error message is sent to
the initiator. Hence the  DISCONNECTED_INBOUND
 endpoints you see, those are
initiators that received the error message from the target.
3) dialogs should consume memory only if they have pending messages to be
delivered (in sys.transmission_queue). The amount of memory consumed depends
on a variety of factors and the memory consumed should be freed if memory is
needed by other components in the server. The amount of memory consumed is
not dependent on number of services/queues/contracts/messages.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx


"AlexS" <slipchenko@gmail.com> wrote in message
news:1144307715.354092.54590@e56g2000cwe.googlegroups.com...
> Hello! Could someone help me with the subject?
>
> Our company is developing an application with quite a high load on
> database. Our DBMS is SQL Server 2005. We're using QuieryNotification
> (via SQLDependency object) for caching data to speed up Web-interface.
>
> After utilizing that feature we've faced a problem. Our SQL Server
> started to consume more and more virtual memory (up to 1.5 Gb) and
> (which is the worst) CPU time. The problem is that we've got LOTS (up
> to 4M) of unfinished dialog conversations in Service Broker. Ok, I
> finished that conversation by using
>
> END CONVERSATION @ConvHandle WITH CLEANUP;
>
> for each conversation in sys. conversation_endpoin
ts with sate 'DI' (all
> of them had that state actually).
> It helped for a while - memory consumption dropped to 100 - 150 Mb
> (after SQL Server restart). But those conversation endpoints keep
> appearing.
>
> The next thing I did was:
> (conversation endpoints to non-existing services)
>
> select
> SVC.name as this_service,
> CEP.state_desc,
> CEP.far_service,
> SVCC.name as contract,
> CEP.is_initiator,
> CEP.is_system,
> CEP. far_broker_instance,

> CEP.dialog_timer
> from
> sys. conversation_endpoin
ts CEP,
> sys.services as SVC,
> sys.service_contracts as SVCC
> where
> SVC.service_id = CEP.service_id and
> SVCC.service_contract_id = CEP.service_contract_id and
> CEP.far_service not in (select name FROM sys.services)
>
> The result was as following (only first 2 rows):
>
> 1 http://schemas.microsoft.com/SQL/No...nSer
vice
>  DISCONNECTED_INBOUND

>  SqlQueryNotification
Service-3c4235fa-007b-4c7a-8b39-ecc411a78380
> http://schemas.microsoft.com/SQL/No...ficatio
n 1 1
> FF6329EC-FFC7-4ECE-9230-8231B08C3A37 1900-01-01
> 00:00:00.000
> 2 http://schemas.microsoft.com/SQL/No...nSer
vice
>  DISCONNECTED_INBOUND

>  SqlQueryNotification
Service-e9ca1a35-e376-44a8-a8e8-4d5fe291705c
> http://schemas.microsoft.com/SQL/No...ficatio
n 1 1
> FF6329EC-FFC7-4ECE-9230-8231B08C3A37 1900-01-01
> 00:00:00.000
>
> ....
>
> and day after day the number of such conversation endpoints keeps
> growing.
>
> The questions arise:
> 1) Should we worry about (that high) memory consumption? Now we're
> developing our application so no actual load is currently generated. I
> wonder what should we expect in production environment. (Our
> development server is P4 HT 3.0 GHz, 2 Gb of RAM under Windows 2003
> Server sp1)
> 2) Those "dead" conversation endpoints are "system". Who should close
> them? And why are they still in DB even after far_service is dropped?
> 3) Can anybody tell me how much memory (and CPU time) does Service
> Broker use? And how it depends on number of services/queues/dialog
> conversations/messages?
>
> Thank you.
>

Thank you for the post. It really helped though in an indirect manner.
1) Concerning DBCC MEMORYSTATUS  and  FREESYSTEMCACHE('ALL
') -- I sent a
message to your e-mail with all the details.
2) I'm really displeased by a necessity of dealing with 'system'
conversations. I've got a feeling that I'm on a way of reinventing the
wheel. I looked through the code, where SqlDependency is used (I was
not writing it by myself). And I verified that everything is in place,
just like in all the samples and references I found (ranging from MSND
to developer sites on the Internet) - each SqlDependensy.Start() is
coupled with SqlDependensy.Stop(). I went further and looked through
the code of SqlDependensy and  SqlQueryNotification
 together with
tracing all Service Broker related activity in DB. As i supposed, each
SqlDependensy.Start() leads to creation of service, appropriate queue
and stored procedure for dispatching messages for that service. Each
SqlDependensy.Stop() in turn leads to dropping that stored procedure
together with service and queue. So everything seems ok at this point.

But while monitoring Service Broker activity I discovered the
following:
When we create SqlDependency object, at least two dialog conversations
are actually established:

1) From  SqlQueryNotification
Service-{guid} (created by
 CreateQueueAndServic
e method in
 SqlDependencyProcess
 Dispatcher+SqlConnec
tionContainer) to self

2) From
http://schemas.microsoft.com/SQL/No...nSer
vice
to  SqlQueryNotification
Service-{guid}.
With the latter being 'system'
I can guess that the second one is needed for QN infrastructure. The
problem is that after SqlDependensy object is disposed (by calling
Stop()), first dialog is closed (queue, service and stored procedure
are dropped) and second becomes  DISCONNECTED_INBOUD what means that
far_service ended dialog explicitly. But this conversation point
remains in DB. From my personal experience such things happen when
EndDialog message is not processed properly (according to protocol,
both parts of conversation should either explicitly end conversation by
issuing END CONVERSATION or do it in respond to EndDialog message which
is posted to the queue when the other part explicitly does so).

Sometimes it happens that stranded dialogs are ended with error by
Service Broker. (In SQL Server's log) It looks like this:

Date		06.04.2006 18:25:49
Log		SQL Server (Archive #1 - 07.04.2006 8:50:00)

Source		spid21s

Message
The query notification dialog on conversation handle
'{44409924-80C5-DA11-90ED-001676027084}.' closed due to the following
error: '<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Co
de><Description>Cannot
find the remote service
& apos;SqlQueryNotific
ationService-ffb0db45-747e-4e16-b1cc-6480e8a866c2'
because it does not exist.</Description></Error>'.

I suppose that this happens when query notification event occurs and QN
service tries to send a notification message using the dialog (ignoring
that fact that other part has ended it). What discourages me is that no
subscriptions are active
(select * from R60Master.sys.dm_qn_subscriptions returns empty set -- I
have never seen any records there actually).

Well, maybe I'm searching in the wring place.

After all my investigations I "invented" DLL trigger firing on
DROP_SERVICE (it looks like this):

CREATE Trigger  trOnQueryNotificatio
nServiceDrop
ON DATABASE
FOR DROP_SERVICE
AS
DECLARE @ObjName nvarchar(max)
DECLARE @Msg nvarchar(max)
SELECT @ObjName =
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)& #91;1]','nvarchar(ma
x)') ;
IF (@ObjName like  'SqlQueryNotificatio
nService-%') BEGIN
-- Service was dropped - select all conversation endpoints to this
service and close them
DECLARE @ConvHandle uniqueidentifier
DECLARE Conv CURSOR FOR
SELECT conversation_handle
FROM sys. conversation_endpoin
ts
WITH (nolock)
WHERE state = 'DI' and far_service = @ObjName;

OPEN Conv;
FETCH NEXT FROM Conv INTO @ConvHandle;
WHILE (@@FETCH_STATUS = 0) BEGIN
END CONVERSATION @ConvHandle WITH CLEANUP;
FETCH NEXT FROM Conv INTO @ConvHandle;
END
CLOSE Conv;
DEALLOCATE Conv;
END

I have two versions:
1) Our developers are doing wrong. But they did everything according to
samples, manuals, references, etc. So this should be some kind of very
stupid mistake (like misprinting). Unlikely.
2) The bug in QN infrastructure - not processing EndDialog correctly
(or something like this). Well, it happens, but also unlikely.

After that I don't know what to think and will appreciate any kind of
information that will help in resolving the problem.

Thank you.


Remus Rusanu [MSFT]  wrote:[color=darkred
]
> When the server has this high memory load, can you run the DBCC MEMORYSTAT
US
> command and either post back here with the result or send it to me at
>  remus(dot)rusanu(at)
microsoft(dot)com
> I want to see if the memory consumption is indeed related to the large
> number of stranded dialogs.
>
> I'll try to get a hold of somebody from QN/SqlDependency to look into why
> the dialogs are stranded in the first place.
>
> To answer your questions:
>
> 1) When the system memory consumption goes high, issue a DBCC
>  FREESYSTEMCACHE('ALL
') statement. If the memory consumption goes down
> significantly, you shouldn't be worried for the productions system. It mea
ns
> all memory is consumed by caches and they would free the memory if the
> system needs it.
> 2) 'system' conversations should be closed by the Query Notification
> infrastructure (or Event Notifications for EN dialogs). The two endpoints 
of
> a conversation (initiator and target) are distinct and they communicate
> trough messages (even when within the same database). When you drop the
> 'far_service', the target endpoint is closed and an error message is sent 
to
> the initiator. Hence the  DISCONNECTED_INBOUND
 endpoints you see, those are
> initiators that received the error message from the target.
> 3) dialogs should consume memory only if they have pending messages to be
> delivered (in sys.transmission_queue). The amount of memory consumed depen
ds
> on a variety of factors and the memory consumed should be freed if memory 
is
> needed by other components in the server. The amount of memory consumed is
> not dependent on number of services/queues/contracts/messages.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> HTH,
> ~ Remus Rusanu
>
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
>
> "AlexS" <slipchenko@gmail.com> wrote in message
> news:1144307715.354092.54590@e56g2000cwe.googlegroups.com...