We run an ASP.Net web application on a web server and this application
communicates to a separate database server to get its data.

Many customers access their information via this web server and each
customer has its own database on the database server.

The database server is running SQL Server 2005 and each database has the SQL
Service Broker enabled to allow SQL Notifications to be processed.

The web application uses the web cache to store its information and we use
SQL Cache Dependencies to monitor the cache, so it will invalidate the web
cache when a change is made to the database on the monitored Dependency.

Also the application does invoke the static method ‘SqlDependency.Start’ at
startup to create the Queue and Service.

All our dependencies work fine and the application experiences good
performance due to this.

But we do have a very intermittent problem which is unique to one customer
database, where for a certain area of the application the SQL Cache
dependencies seem to invalidate instantly after they are created. This is
causing a massive slow down in the loading of this specific area, because the
data is not being cached and is constantly being requested from the database
as the cache is always null.

It is very specific to this area of the application as other areas that use
Cache Dependencies will still register and work at the same time.

We are also very sure that there is no add, update or delete being executed
on the problem dependencies to cause them to invalidate, so we are baffled as
to why this happens.

As Mentioned this is intermittent and does not always happen, but when it
does this customer experiences very poor performance and the CPU on the
database server runs at a high load.

We have at least 20 customer databases on this database server all using the
Broker Service and Cache Dependencies and none of these experience this
intermittent Caching problem.

I have also noticed in the SQL error log that the following error occurs at
around the same time I am trying to register the dependencies and the
SQLQueryNotificationService GUID matches to the GUID for the Notification
Service and Queue of the specific database.

The query notification dialog on conversation handle
'{F138CB34-B175-DD11-A3DA-0019D1AD2856}.' closed due to the following error:
'<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Descrip
tion>Cannot
find the remote service
&apos;SqlQueryNotificationService-e629d29e-cf56-42fe-990a-fb8814b44261&apos;
because it does not exist.</Description></Error>'.

I know that this error usually happens for subscriptions notified after the
application has exited meaning the service and Queue GUID it is trying to
communicate to no longer exists, but in our case the Service and Queue still
do exist, so I don’t know if this has any relevance.

Hopefully I have explained the above appropriately and I will just mention
another thing that we are looking into that you could verify as having any
relevance, and that is the amount of conversation handles the queue can
contain as maybe this needs to be increased based on a higher load of cache
dependencies being created?