Hello,
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><Description>Cannot find the remote service 'SqlQueryNotificationService-e629d29e-cf56-42fe-990a-fb8814b44261' 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. Any help would be greatly received.