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.
TIP
The change detection mechanism is based on that used for indexed views. In the same way that only views that obey certain criteria can be indexed, so there are certain restrictions applying to the SELECT statements that can be used with query notifications. See http://msdn2.microsoft.com/en-US/library/ms181122.aspx
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.
NOTE
Query notification is solely dependent on Service Broker and doesn’t rely on Notification Services.
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:
1 2 3 4 5 6 7 8 |
<connectionStrings> <add id=”conAW”” connectionString = “server = <servername>; integrated security = true; database=AdventureWorks; persist security info=true” providerName=”system.data.sqlclient”/> </connectionStrings> |
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:
1 2 |
System.Web.Caching.SqlCacheDependency new_dependency = new System.Web.Caching.SqlCacheDependency(command); |
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
:
1 |
Cache.Insert(“CustomerStores”, Customer_Stores, new_dependency); |
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:
1 2 3 4 5 6 |
void Application_Start(object sender, EventArgs e) { string connectionString = System.Configuration.ConfigurationManager. ConnectionStrings[“ConAW”].ConnectionString; System.Data.SqlClient.SqlDependency.Start(connectionString); |
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:
1 2 |
use master alter database <dbname> SET ENABLE_BROKER |
TIP
To verify that the broker is enabled, run the query, select is_broker_enabled from sys.databases where name = '<dbname>'
and make sure it is set to 1 for 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
, andSERVICE
permissionsSUBSCRIBE QUERY NOTIFICATIONS
SELECT
on underlying tablesRECEIVE
onQueryNotificationErrorsQueue
The database_principal
can be given permission to create the queue, the service and the procedure using the following T-SQL commands:
1 2 3 4 |
use <dbname> GRANT CREATE PROCEDURE TO <database_principal> GRANT CREATE QUEUE TO <database_principal> GRANT CREATE SERVICE TO <database_principal> |
The next step is to grant the requisite privileges to the login to subscribe to query notifications in your database:
1 2 |
use GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal |
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
:
1 2 |
use <dbname> GRANT SELECT ON OBJECT::schema.tablename TO 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:
1 2 |
Use <dbname> GRANT RECEIVE ON QueryNotificationErrorsQueue TO |
TIP
You can run exec
sp_helprotect
NULL,
'database_principal'
in the context of your database to see if any of the above permissions are missing. Generally, if these requisite permissions are not granted, query notification will not work.
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
:
1 2 |
ALTER DATABASE db1 SET TRUSTWORTHY ON ALTER DATABASE db2 SET TRUSTWORTHY 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.
TIP
You will always see an entry in this DMV immediately after the front-end app runs the query with the subscription for the first time. You can also run SQL Profiler and monitor QN:Subscription (1 – Subscription registered). This event is raised every time a subscription is registered successfully by the database engine.
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.
TIP
If a duplicate subscription is submitted before the timeout value of the existing subscription is reached, then SQL Server will update the timeout for the existing subscription and will not create a new subscription.
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
andStarting
;SP:Starting
andCompleted
;SP:StmtStarting
andStmtCompleted
- T-SQL events –>
SQL:BatchStarting
andBatchCompleted
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):
1 2 3 4 5 |
<qnev:QNEvent xmlns:qnev=”http://schemas.microsoft.com/SQL/Notifications/ QueryNotificationProfiler”> <qnev:EventText>subscription registered </qnev:EventText><qnev:SubscriptionID>id_num </qnev:SubscriptionID></qnev:QNEvent> |
Also, a subscription will be registered in sys.dm_qn_subscriptions DMV at this stage.
TIP
Whenever SQL Server fires a notification, you will observe the event QN:Subscription (3 – subscription fired) in your profiler trace. If the notification cannot be fired due to issues with Service Broker, then you will see QN:Subscription (4 – Firing failed with broker error, 5 – Firing failed without broker error, 6 – Broker error intercepted).
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<qnev:QNEvent xmlns:qnev=”http://schemas.microsoft.com/SQL/Notifications/ QueryNotificationProfiler”> <qnev:EventText> subscription fired </qnev:EventText> <qnev:SubscriptionID>0</qnev:SubscriptionID> <qnev:NotificationMsg> qn:QueryNotification xmlns:qn=”http://schemas.microsoft.com/SQL/Notifications/ QueryNotification” id=”SPAN”> type=”subscribe” source=”statement” info=”invalid” database_id=”<id>” —————————more </qnev:NotificationMsg> —————————more |
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
andInfo
=query
indicates that SQL Server was not able to register a subscription because theSELECT
statement didn’t meet the requirements for query notification.Source
=system
andInfo
=invalid
indicates that SQL Server was not able to register a subscription because an invalid statement was issued (such asINSERT
orUPDATE
) 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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<qnev:QNEvent xmlns:qnev=”http://schemas.microsoft.com/SQL/Notifications/ QueryNotificationProfiler”> <qnev:EventText>subscription fired</qnev:EventText> <qnev:SubscriptionID>subscription_id</qnev:SubscriptionID> <qnev:NotificationMsg>qn:QueryNotification xmlns:qn=”http://schemas.microsoft.com/SQL/Notifications/ QueryNotification” id=”subscription_id”” type=”change” source=”data” info=”update” database_id=”<db_id>” —————————–more </qnev:NotificationMsg> —————————–more |
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.
Load comments