With the advent of SQL Server 2005, your application can request SQL Server to notify it when critical data in a database has changed. Up to that point, an application can safely continue to retrieve data from the cache. This provides a much more granular level of control and takes any guesswork out of the question of how often the cache should be refreshed. This is possible via a new feature called query notification, used in conjunction with .NET 2.0.

The aim of this article is to show you how to make full use of SQL 2005 query notification with ASP.NET 2.0. It tackles the implementation details largely from SQL Server’s perspective and includes many DBA tips and tricks for troubleshooting query notification. However, the article should also interest developers as it will enable them to implement QN in their ASP.NET applications, without causing performance degradation of their SQL Server machines.

An overview of query notification

A multi-user web application should be able to provide the latest critical data to its users. To be sure of meeting this need, your application may have to retrieve the results from a database every time the user issues the query. However, this unnecessarily increases network round-trips, and is an inefficient use of resources. To reduce the number of round-trips, developers have traditionally relied on caching mechanisms. Once a query is run, the data is subsequently retrieved from the application’s local cache. However, the lifetime value of this cache is generally defined so that the application can periodically update the cache with new values from the database, irrespective of whether the data in the back end has changed or not. This is much better, but it is still not precise.

With query notification enabled then when you run a query against the back-end database, you not only retrieve the data into the cache, but also tell SQL Server to register a subscription for notification if the underlying data changes in a way that will affect the result of the query.

In turn, SQL Server cooperates with the application. Whenever it runs a statement that causes the underlying data to change, SQL Server looks for any active subscriptions in the sys.dm_qn_subscriptions Dynamic Management View (DMV) and sends a notification message to a message queue on Service Broker called QueryNotificationErrorsQueue.

When the notification is received, the event handler in your application invalidates the cache and the next time the application runs the query, it will fetch the data from the back-end server. All this is done without the need to write any complex application code.

Enabling query notification in ASP.NET using SQLCacheDependency

To demonstrate the ease of use of this feature, I’m going to showcase a simple web application written in ASP.NET 2.0 using C#, and running against the AdventureWorks database.

Let’s start by creating a new ASP.NET 2.0 web application. The first step is to define, in your connection string, the identity of the Windows login that will be executing the queries against SQL Server. This login (or the login used for impersonation) should be granted all the permissions that I list in the Granting permissions section of this article.

You can define your connection string in your web.config file, as follows:

The default.aspx.cs file (supplied in the code download for this article) is very simple, but describes fully how to take advantage of query notification using the SQLCacheDependency class. A SQLCacheDependency object can be used to establish a relationship between an item stored in the Cache object and the results of a SQL Server 2005 query.

First, in the GetCustomerStores() function, I instantiate an object of type SQLCacheDependency, passing in the ADO.NET command object that is used to submit the query:

Second, I populate the cache with the results returned by executing the query, using the Cache.Insert method. We specify the cache key (CustomerStores), the object to be inserted (a DataSet, Customer_Stores, containing the results of our query on the Store table in AdventureWorks), and finally establish the link between Customer_Stores and SQLCacheDpendency:

The first time this application loads, there will be nothing in the application cache and Customer_Stores will be null. As a result, the GetCustomerStores function will be called. When this happens, the function will register a notification subscription with SQL Server. It will also load up the cache with the output of our query. When the user runs this query again the data will be retrieved from the cache. You can confirm this by running the app in debug mode.

Let’s also take a look at the Global.asax file (also included in the code download). Here, I am calling the System.Data.SqlClient.SqlDependency.Start() method:

This creates the necessary queue, service and procedure and starts a listener to monitor the queue. You don’t have to write a single line of code to implement this – it is all done automatically. When SQL Server raises a notification it merely sends it to QueryNotificationErrorsQueue. Once it arrives there, the above listener gets activated and retrieves the message from the queue. SQL Server doesn’t send the notification to the machine running the application.

 Enabling query notification in SQL Server

The code above demonstrates how little you need to do in your front-end application to make good use of query notifications. However, there are certain steps that need to be carried out on the server-side in order for this to work, the first being to enable Service Broker.

Enabling Service Broker

As I mentioned earlier, query notification relies on the underlying Service Broker architecture. So the first step is to enable Service Broker on the database in question:

Granting permissions

Before query notification can work you need to grant the following permissions to the database_principal. This database_principal can be the same login as you used in your connection string:

• CREATE PROCEDURE, QUEUE, and SERVICE permissions
• SUBSCRIBE QUERY NOTIFICATIONS
• SELECT on underlying tables
• RECEIVE on QueryNotificationErrorsQueue

The database_principal can be given permission to create the queue, the service and the procedure using the following T-SQL commands:

The next step is to grant the requisite privileges to the login to subscribe to query notifications in your database:

Make sure that the database_principal listed above is either the login specified in the connection string of your application, or the login that is impersonated in your application to run queries against the database. In this article, I am assuming that there is no impersonation.

In order to register a subscription for QN, the database_principal must have SELECT privileges on the underlying tables that it is trying to query. This would be the case if the login is sysadmin or db_owner. If not, then run the following command to grant the SELECT privileges to the database_principal:

The database_principal must also have requisite permissions to receive the notification messages sent to the message queue by SQL Server. The login above should therefore also have RECEIVE permissions on the QueryNotificationErrorsQueue. You can grant this permission as follows:

Finally, in cases where the T-SQL SELECT that you are registering for query notification queries more than one database you should set the TRUSTWORTHY property of each database to ON:

Once you have granted the above permissions and set your front-end app to use SQLCacheDependency, you are ready to take advantage of query notification.

Query notification in action

When your front-end app runs the T-SQL query for the first time under the context of database_principal, SQL Server will register a notification request, create a subscription in the DMV sys.dm_qn_subscriptions and then execute the command.

If SQL Server is not able to register a subscription, it will send a <qn:QueryNotification> message to the queue. If the Type of this message is set to subscribe, it indicates that SQL Server was not able to successfully register a subscription. Refer to the Monitoring and troubleshooting query notification section below for more information on this.

Once this subscription is registered and the results returned to the app by the database engine, the front-end app will continue to query the cache, until a notification message is raised by SQL Server and sent to the QueryNotificationErrorsQueue, indicating that a change has occurred that may affect the result of the subscribed query.

Using SQLCacheDependency, your application has already established the relationship between an item in your application’s cache and the results of this query. When the message arrives in QueryNotificationErrorsQueue, the listener will raise the OnChange event in your application.

The associated event handler (OnChangeEventHandler) will handle this event. This delegate has a parameter called SQLNotificationEventArgs, which captures the details of the message coming from SQL Server. The cache will be invalidated and, next time the application runs, it will fetch the data directly from SQL Server (the GetCustomerStores() function will get called again after the notification is received).

The notification messages sent to the queue are in XML and are of the <qn:QueryNotification> variety. The Type element is set to the value change, indicating that this notification message was created because of a change that could affect the outcome of the query.

A notification will be raised by SQL Server whenever the database engine cannot guarantee that the data in the cache is up to date. Scenarios include:

• Changes to the data in the columns since the last SELECT statement was run
• SQL Server being restarted
• Changes to the underlying schema
• Expiration of a subscription (see later)

The Source and Info elements of the notification message provide the details about the kind of change that triggered this notification. Refer to the Monitoring and troubleshooting Query Notification section below, for more information on this.

This subscription has a default timeout value, which is reflected in the timeout column in the sys.dm_qn_subscriptions DMV. If the data doesn’t change during this time, SQL Server will raise a notification at the end of the subscription timeout, and will remove this subscription from the DMV.

Monitoring and troubleshooting query notification

SQL Server provides a means of monitoring the query notification setup and its impact on overall server performance. The best way to monitor and troubleshoot query notification issues is by running a profiler trace. Create a new profiler trace which includes at least the following events:

• Broker –> All
• Query notifications –>All
• Stored procedure –> RPC:Completed and Starting; SP:Starting and Completed; SP:StmtStarting and StmtCompleted
• T-SQL events –> SQL:BatchStarting and BatchCompleted

Run the profiler and launch your front-end application. When the T-SQL query in your front-end application is run for the first time, you should see the QN:Subscription EventClass raised in profiler, indicating that the subscription was successfully registered by SQL Server (1 – Subscription registered). The TextData column in profiler should show text similar to the one below (id_num is the subscriptionId of this subscription):

Also, a subscription will be registered in sys.dm_qn_subscriptions DMV at this stage.

If, on the other hand, SQL Server was not able to register a subscription, it will immediately raise a notification and you will see QN:Subscription EventClass (3 – Subscription Fired), but the TextData field will be similar to that shown below (note that I have removed unnecessary text and added line breaks for readability):

The important things to notice here are the Type and the Source elements. If the Type = subscribe, then it means that the subscription was not registered by SQL Server. To find out why SQL Server didn’t register this subscription, you need to look at the Source and Info elements. For example:

• Source = statement and Info = query indicates that SQL Server was not able to register a subscription because the SELECT statement didn’t meet the requirements for query notification.
• Source = system and Info = invalid indicates that SQL Server was not able to register a subscription because an invalid statement was issued (such as INSERT or UPDATE) which doesn’t support notification.

Refer to the Query Notification Messages section in Books Online for more details on the description of each element. You can also refer to the SQL Server errorlogs to check for any error messages.

If the subscription registered successfully, then you should expect to receive a notification from SQL Server when the underlying data changes. To see this in action, update the Name of one of the stores in the Sales.Stores table in AdventureWorks. This will trigger a notification to the QueryNotificationErrorsQueue.

In Profiler, you will see QN:Subscription with an EventSubclass of 3 (Subscription fired). The TextData field will be similar to the following (again, I have removed unnecessary text and added line breaks for readability):

This time, you should see Type = change with the Source and Info elements indicating the nature of the change that caused the notification. Here, we have Source = data and Info = update, indicating that the notification was raised because an UPDATE statement was issued against one of the underlying tables in the SELECT statement.

If you see Source = system and Info = restart, then this  indicates that the notification was raised because SQL Server was restarted. Refer to Query Notification Messages topic in Books Online for full details on these events and their meanings.

If you don’t see this event, SQL Server was not able to fire a subscription. In this case, you should refer to the profiler for any exceptions, attentions or error messages. Also, refer to SQL Server error logs for any errors pointed out. The information there should be sufficient to troubleshoot the issue.

You should check the QN:Subscription event  in the profiler trace for LoginName (which indicates the login that actually ran the query) and SessionLoginName (which indicates the login that was used by the application to connect to SQL Server in connection string) columns. The important thing to check here is that the LoginName value is the database_principal that is running all the queries in SQL Server.

When to use query notification

Whenever it cannot guarantee that the data in the cache is valid, SQL Server will raise a notification. Although this sounds like a very easy implementation, do remember that SQL Server’s overall performance may suffer if you are not prudent about using query notification. After a subscription is registered, SQL Server has an additional responsibility to continuously monitor changes. As a result, any changes (updates, deletes or inserts) to the table are more expensive.

Whenever a DML query is run on the underlying table, SQL Server not only has to update the data in the table, it also has to check for any active subscriptions, and raise a notification accordingly. It is, therefore, important to maintain a balance between query notification and overall performance.

As you can see, not all scenarios benefit from query notifications. Ideally, a scenario in which the application primarily reads the data more often and updates are infrequent can benefit most from query notifications. For example, e-commerce websites using read-mostly lookup tables to showcase product catalogs will benefit from query notification, while online stock trading applications will not.

In conclusion

Query notification is just one of the great features that SQL Server 2005 provides. However, I can’t do justice to a description of all its details without sounding pedantic. Let me know if you would like to more on this topic and I will be glad to publish it.

Thanks for your time reading this article. Check back for updates at my blog.