The Mysterious Notification

June 17th, 2006

Update:: for a way to leverage SqlDependency and Query Notifications from LINQ, see SqlDependency based caching of LINQ Queries.

One of the most mysterious features shipped with SQL Server 2005 seems to be the various flavors of notifications on data change: SqlNotificationRequest, SqlDependency and SqlCacheDependency. I see confusion on how this features work, how to use them and how to troubleshoot problems. Why are there three flavors of apparently the same functionality? And how is Service Broker involved into all of these?

Query Notifications

In reality, there is only one feature in the SQL Server 2005 engine that delivers notifications on subscriptions for data changes: Query Notifications. Clients can submit a query requesting to be notified when data was modified in a manner that would change the query result and the server sends a notification when this change occurs. These requests are called ‘query notification subscriptions’. The list of notification subscriptions can be seen in the server level view sys.dm_qn_subscriptions:

 

select * from sys.dm_qn_subscriptions

 

Along with the query submitted for the notification, the client submits a service name and a broker instance. Each notification subscription begins a Service Broker dialog with this provided service and broker instance. When data is changed and the change would affect the result of the submitted query result, a message is sent on this dialog. By sending this message, the client is considered notified and the notification subscription is removed. If client desires to be notified on further changes, is supposed to subscribe again.

So we’ve seen that the notification is not delivered back to the client, but a Service Broker message is instead sent to the service the client provided in the subscription request. All normal rules for delivery, routing and dialog security apply to the dialog used to send this message. This means that the notification message can be sent to service hosted in the same database, in a different database or even on a remote machine. Also, there is no need for the client to be connected to receive the notification. It is perfectly acceptable for a client to submit a query for a notification subscription then disconnect and shutdown.

The client consumes the notification message just like any other Service Broker message: by RECEIVE-ing it from the service’s queue. The notification message will be of type [http://schemas.microsoft.com/SQL/Notifications/QueryNotification], an XML message type. This message is part of the [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]contract, which means that the service that receives the notification messages must be bound to this contract. After the client receives the message, is supposed to end the conversation on which the message was received, using END CONVERSATION (make sure you don’t use the CLEANUP clause!).

 

The restrictions that apply to queries submitted for notifications are explained in BOL in the ‘Creating a Query for Notification’ topic:http://msdn2.microsoft.com/en-us/library/ms181122(SQL.90).aspx. Although the query can be a stored procedure, it must not contain any flow control statements (IF, WHILE, BEGIN TRY  etc).

Clients can submit notification subscription request by programming directly against the SQL Native Client, using the HTTP SOAP access to SQL Server or, most commonly, using the ADO.Net client components. AFAIK clients cannot subscribe using the OLEDB, ODBC or JDBC components. Updated: It is possible from OleDB and ODBC as well, see Working with Query Notifications.

The cost of a subscription

Query notification subscriptions have little cost in the SQL Server 2005 engine. Notification subscriptions are only metadata, and the effect of a subscription is to modify the query plans in a manner that allows the relevant data changes to be detected. The picture below shows how a plan for a INSERT INTO TEST … statement is modified when there is a query notification subscription active with the query SELECT * FROM TEST:

 

This plan shows that, while cheap, query notification subscriptions are not free. The cost associated with them is similar to the cost of having a secondary index on the data, or the cost of having an indexed view.

Server Restart

When the SQL Server 2005 is restarted, all query notification subscriptions are notified and ended.

KILL QUERY NOTIFICATION SUBSCRIPTION

This Transact-SQL statement can be used to administratively end query notification subscriptions. The BOL describe its usage: http://msdn2.microsoft.com/en-us/library/ms186764.aspx

SqlNotificationRequest

This is simplest ADO.Net component for subscribing to Query Notifications. This class is used directly to create a query notifications subscription. The usage is straightforward:

-         Create a new SqlNotificationRequest object, passing in the appropriate Service Broker service name and broker instance

-         Assign the newly created SqlNotificationRequest to the Notification property of a SqlCommand

-         Execute the SqlCommand.

MSDN has a sample of this technique here: http://msdn2.microsoft.com/en-us/3ht3391b.aspx

Using the SqlNotificationRequest leaves the task of handling the notification message entirely to the client application. While this is the most flexible way of leveraging the Query Notification functionality, it requires knowledge and understanding of the way Service Broker delivers the messages and how to write a Service Broker application to process the notification messages.

SqlDependency

This component tries to make the task of handling the Query Notification subscription messages as straightforward as possible. Using the SqlDependency the application developer gets a CLR callback whenever data has changed. How this is achieved is very simple: the SqlDependency uses SqlNotification to subscribe to query notifications and then the SqlDependency infrastructure uses a just-in-time-created service and queue to receive the notification messages. It starts a background client thread that posts a WAITFOR(RECEIVE…) on this queue and whenever a notification message is delivered by Service Broker into this queue, the background thread will receive this message, inspects it and invoke the appropriate callback. The static methods Start and Stop on the SqlDependency class are starting and stopping this background thread, as well as create and drop the service and queue used by the SqlDependency infrastructure. This background thread is shared by all requests in one appdomain. This is important, because if each SqlDependency request would start its own listener, the back end server would quickly get swamped by all those requests issuing WAITFOR(RECEIVE…) statements, each blocking a server thread.

The MSDN contains a sample of how to use the SqlDependency here: http://msdn2.microsoft.com/en-us/a52dhwx7.aspx. Note how, similarly to the SqlNotification usage, the client is expected to subscribe again if it whishes to be further notified.

Abrupt client disconnects

If a client disconnects abruptly without stopping the SqlDependency infrastructure, the queue and service created by the SqlDependency.Start are left stranded. This is not a problem, because the SqlDependency infrastructure also deploys a stored proc associated with activation on this queue and sets up a dialog timer. When this timer fires, the procedure is activated and this procedure cleans up the queue, the service and the activated procedure itself. Due to the pending WAITFOR(RECEIVE…) permanently posted by the SqlDependency background thread, the activated procedure will launch only if the client has disconnected w/o cleaning up.

SqlCacheDependency

This is a component designed specifically for ASP.Net to cache data, using the SqlDependency whenever possible. I’m not going to dwell into how it works, simply because I don’t really know. But once one understands how the Query Notifications and SqlDependency class work, the SqlCacheDependency is just another use of this infrastructure, with nothing special to it.

Troubleshooting Query Notifications

While the SqlDependency infrastructure is a great help to developers, is it often used w/o properly understanding its functionality and I often see people totally lost when it comes to troubleshooting a problem. In fact, BOL has a dedicated chapter for this topic here: http://msdn2.microsoft.com/en-us/library/ms177469.aspx. The Profiler can show the Query Notification events that are reported when a new subscription is registered. Once a notification subscription is notified, the notification message is delivered using Service Broker and all of my comments related to troubleshooting dialogs apply to this message delivery as well. If the notification message is no delivered, the first place to look is the transmission_status column in the sys.trasnmission_queue view in the sender’s database.

6 Responses to “The Mysterious Notification”

  1. [...] First, lets review in brief how the SqlDependency works. The application is supposed to invoke the static method SqlDependency.Start at startup to deploy the necessary infrastructure, then use instances of SqlDependency object associated with a SqlCommand to receive callbacks when the query executed is notified (data has changed), and finally call SqlDependency.Stop when the application shuts down to tear down the infrastructure deployed at startup. I have explained before how the server side Query Notifications feature works to detect the changes and to notify the subscriptions, see http://rusanu.com/2006/06/17/the-mysterious-notification/ [...]

  2. sheir says:

    I am new to Query Notification and am trying to get it working with a Windows Application using WCF.

    How come calling SqlDependency.Stop(_myConnectionString) does not clear out the subscription(s) (ie rows) in sys.dm_qn_subscriptions???

    Is there a way to force it to clear out?
    Besides manually running the
    KILL QUERY NOTIFICATION SUBSCRIPTION qnIDvalue?

  3. remus says:

    @sheir: SqlDependency.Stop does not clear subscriptions indeed, and there is no way to clear them out. The subscription would clean themselves out over time either by firing (and failing to deliver to the now defunct target) or by server/database startup.

  4. sheir says:

    Hi,
    I seem to not be receiving any Timeout notifications and am not sure why.

    I do get the Data Change notifications and if I wait say 10mins (abouts), there is a timeout and my WCF service gets disposed (see that in my Tracing messages using DebugView from sysinternals).

    If I could get the timeout notification then I can inform the client to resubscribe, but as it stands now; the client still thinks it is connected to my WCF service and then gets an error message about my service cannot be used for communication because its in a faulted state. Of course that happens because my service was disposed of in the back-end (server-side) code (this is a WinApp).

    My code is based on the sample (WCFQN) found in your article
    Remove pooling for data changes from a WCF front end.

    My client-side is a winApp using the M-V-P design pattern and then there is the server-side with the QN bits based on your sample.

    So any thoughts on why my WCF Service gets Disposed when I do not (on client-side) tell it to Dispose and thus never gets the Timeout Notification?

  5. sheir says:

    Hello Remus,
    I manage to figure out why the timeout was happening and also a work-around solution for it.

    Turns out that WCF Services have an inactivity timeout value and its default is 10 minutes.
    This explains why in the DebugView (from systeminternals) i was only seeing the termination of my WCF Service (via Trace.Writeline messages) and nothing from the SQLDependency portion of my code.

    What happens is the service did not get called from the client for 10mins and so was cleaned up by the Framework.

    Now the workaround (for my case) was to use a timer (system.timers namespace) have it call the “subscribe” method of my duplex WCF service (client only gets subscribed once) every few minutes. This is sort of its “heart-beat”.

    This then keeps the WCF Service from being terminated by the Framework.
    Now when my application terminates, it does the cleanup (calls Dispose) of my service which calls the Stop() of the SQLDependency and everything gets nicely cleaned up.

    Well…almost everything…there is still a lot of stuff left lying around in SQL Server.
    select * from SYS.CONVERSATION_ENDPOINTS; — records there
    select * from sys.transmission_queue; — NO records

    – records here with count matching those in SYS.CONVERSATION_ENDPOINTS
    SELECT qn.*
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
    FROM sys.internal_tables AS it
    JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id
    WHERE it.internal_type_desc = ‘QUERY_NOTIFICATION’;

    How can I clean up those tables when the client instance of the application ends???

    If I create a “usp_cleanupQN” stored proc and just have “KILL QUERY NOTIFICATION SUBSCRIPTION ALL”, I am worried that it will terminate ALL QN for every client not just this instance.

    I know that SQLDependency instance has an ID value, would I be able to use that id value to somehow figure out which records in those tables to delete???

    Any ideas??

    Thanks
    Sheir

  6. [...] of how that change occurs. I have talked before about how Query Notifications works in the article The Mysterious Notification. This feature was designed specifically for client side cache invalidation: the applications runs a [...]