2

I am using SQL server tables to act as a queue in my applications. Im using Query Notification using SQL Dependency, notably Jeremiah Gowdy's awesome implementation used in this blog post. http://jgowdy.blogspot.com/2010/01/sqldependency-query-notification-with.html

I implement this in a Windows Service and use it to "listen" to changes on a SQL Table i.e. my queue and do processing on the returned contents of the queue if theres a change - see code below, returns a dataset of the contents of the stored procedure.

The problem is, under heavy load or just over a period of time, changes are no longer detected, even though records exist in the table. When I restart the service the changes are then detected! Ive tried updating the table manually, to trigger a notification but to no avail. It seems like the notification service breaks at some point and fails to re-subscribe, but i cant be sure.

Im desperately trying to find a solution to this problem, as i have to constantly watch for changes in the tables themselves and restart the services if the queue gets stuck - not ideal!

Has anyone else had any issues with SQL Dependency and Query notification? Particularly any guidance on or knowledge on know issues would help. I know better queuing systems exist, but im trying to resolve this problem if i can rather than redevelop the entire project!!!

Here is a code snippet.

//Initialisation
public void StartWatcher()
{
    SqlCommand cmd = new SqlCommand();
    cmd = new SqlCommand("TransferExportQueue");
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    log.Info("Setting up SQL Watcher");
    //Setup the SQLWatcher
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.ConnectionString = ConfigurationManager.ConnectionStrings["Connexion"].ConnectionString;

    log.Info("Attempting to Start");
    SqlQueueWatcher = new SqlWatcher(builder.ConnectionString, cmd, SqlWatcher.SqlWatcherNotificationType.Blocking);
    SqlQueueWatcher.OnChange += new SqlWatcher.SqlWatcherEventHandler(QueueSQLWatcher_OnChange);
    SqlQueueWatcher.Start();

}

//OnChangeEvent
private void QueueSQLWatcher_OnChange(DataSet Result)
{
    //perform logic in returned contents of stored procedure
}

Here is my stored procedure

Create PROCEDURE [dbo].[TransferExportQueue] 
AS
BEGIN

SELECT [Id]
      ,[TransactionLogId]
      ,[QueueDate]
      ,[UpdateTable]
FROM [dbo].[TransferExportQueue]



END

UPDATE: I have had a sql server logs and im getting the following errors.

The query notification dialog on conversation handle '{9586CB6A-62BA-E311-983B-A0369F0A65D3}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.

It seems that Remote service keeps dropping, after more digging, it seems that its got to do with how SQLDependency does its clean up of the subscriptions after every call. Does anyone know how to fix this issue?

gorillapower
  • 450
  • 6
  • 15
  • Do you have only one initialization? If not then how many? – Rodion Apr 01 '14 at 12:02
  • You mean the "SqlQueueWatcher" instance? I Have a Common class which contains the SqlQueueWatcher base code, several other projects in my solution instantiate from this (around 8 projects). – gorillapower Apr 01 '14 at 12:20
  • check if you don't initialize more instances than amount of pool connections in DB – Rodion Apr 01 '14 at 12:23
  • Thanks rudym, I will check that, shouldnt an error be thrown if it runs out of pool connections though? – gorillapower Apr 01 '14 at 12:37
  • no error can be thrown. try to check pool conection like this [http://support.microsoft.com/kb/815158/EN-US] – Rodion Apr 01 '14 at 12:56
  • Thanks rudym, ive had a look at the Peformance monitor and having a bit of trouble seein the pooled connections. One thing i did read here, http://www.codemag.com/Article/0605061, is that "Notifications are returned through a single SqlConnection that is established solely for this purpose. This connection is not engaged in connection pooling." Does this make sense? – gorillapower Apr 01 '14 at 15:39
  • Do you think I should call each instance statically to reduce number instances globally? – gorillapower Apr 01 '14 at 22:12
  • Ive checked the non-pooled and pooled connections and for all services implementing the sql-watcher they all count 6 non-pooled connections or below. (6 = active application, 3 = dormant - i.e. no processing). It doesnt look out of control. See image - [link](http://imgur.com/TtfA7Dr) – gorillapower Apr 01 '14 at 23:14

1 Answers1

3

Read this article: SqlDependency.OnChange callback timing. The gist of it is that your app has a finite time to process the OnChange callback or the activated procedure timer will kick in and kill the service, wrecking havoc on your app. Personally I'm not a fan of the design, but it is as it is.

Welcome to the club of people who had to abandon the handy SqlDependency usage, with it's just-in-time deployed infrastructure, and use the more basic SqlNotificationRequest class, which requires you to explicitly deploy the target service/queue. What you loose in ease-of-use you'll gain in control over behavior and if you end up dropping the target service from under you're running application at least you'll only have yourself to blame ;)

Seriously though, switching from SqlDependency to SqlNotificationRequest is fairly easy and I recommend it.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks Remus, i was just reading your posts dealing with my exact problem! So theres no way to fix this issue? The problem is the application is deployed live and this issue only really came about under load testing! If not ive got some coding to do... – gorillapower Apr 02 '14 at 13:09
  • All I can say is enjoy you're codding :( if is not yet live site incident, you'll have time to design it properly. – Remus Rusanu Apr 02 '14 at 13:14
  • Thanks Remus, yes it is live im afraid. Battlestations. – gorillapower Apr 02 '14 at 13:21
  • do you have any good examples of how to implement SqlNOtificationRequest, there seem to very very few! I have looked here, http://msdn.microsoft.com/en-us/library/3ht3391b(v=vs.80).aspx but its buggy and doesnt seem to work as expected. Are there any resources you can point me to? – gorillapower Apr 03 '14 at 08:43
  • @RemusRusanu thanks for the great answer. So are you saying that if you use SqlDependency then it will take care of managing all the service broker stuff in the background? Thanks – tom redfern May 01 '14 at 07:53
  • @TomRedfern yes, [`SqlDependency.Start`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.start(v=vs.110).aspx) deploys the necessary objects. – Remus Rusanu May 01 '14 at 08:13