Planning for Notifications
To use query notifications effectively, you should consider whether your application can benefit from query notifications, whether the queries that your application uses support notifications, and the strategy your application will use for subscribing to and receiving notifications.
Query notifications provide a convenient way to reduce roundtrips to the database if the data in the query changes relatively infrequently, if the application does not require an instantaneous update when the data changes, and if the query meets the requirements and restrictions outlined in Creating a Query for Notification. Many Web-based applications meet these criteria, and these applications can take advantage of query notifications.
Not every scenario benefits from query notifications. Query notifications are useful in situations where an application reads data from the database frequently, but where updates to the data are relatively infrequent. For example, an online catalog application will be viewed more frequently than the catalog is updated. For an online shopping cart, however, the content of a particular could be updated quite frequently, so query notifications provide less benefit.
Query notifications are more efficient when an application issues queries that share a common structure and vary only in the values of the parameters. For example:
SELECT ProductNumber, Name FROM Production.Product WHERE ListPrice < 300
SELECT ProductNumber, Name FROM Production.Product WHERE ListPrice < 500
In this case, query notification subscriptions for both notifications share the same internal template, requiring less overhead in SQL Server than two notifications with a different query structure. Notice, however, that the parameters in the queries are preserved. Even though the queries share a template, adding an item with a ListPrice of 350 causes notification on the second query, but not the first.
When query notifications are active on a table, updates to the table are more expensive. The Database Engine performs extra work to check subscriptions and, if necessary, generate notifications. Reusing internal templates helps to minimize the overhead per subscription. Therefore, you should use query notifications only for applications that submit queries with a similar structure. An application that submits queries with different structures should not use query notifications.
For example, an application that shows catalog items in a given price range submits queries with the same structure. In this case, the Database Engine can reuse the internal template for each query, and query notifications may improve performance. However, an application that allows ad hoc reporting submits queries with varying structure. In this case, the application should not use query notifications.
The Database Engine maintains an internal template as long as it is used by at least one registered subscription. The Database Engine limits the number of different internal templates on a specific table. Once this limit is reached, the Database Engine does not register subscriptions that would cause a new template to be created. Instead, the Database Engine immediately generates a subscription message indicating that the subscription could not be registered.
Planning an Efficient Query Notifications Strategy
Query notifications generally work very well when the total number of notifications is light to moderate, and the application does not require instantaneous notification response time when the data changes. The typical Web-tier cache invalidation scenario fits this model, and tends to be a good application for using query notifications. Query notifications might not be the best choice for applications when notifications must be received with a sub-second response time, when the network infrastructure is not both fast and reliable, or when the volume of notifications is very high.
When using query notifications, test and tune your application at the scale and in the environment where it will operate when it is deployed. Consider the real world use-case scenario with the heaviest expected load, and plan for bursts of high activity if the possibility can occur.
If you are using query notifications in a situation where you need reliable sub-second query notifications, the same techniques apply to your query notifications application that apply to building any high-performance OLTP application.
Make sure your application does not hold locks for longer than a fraction of a second. For example, do not run multi-statement transactions from a client over a network that has unreliable performance.
Identify and eliminate hot-spots in your user data tables.
The query notifications internal tables are often scanned sequentially for each update to a related user table on which you have set query notifications. If the table-level lock held on the query notifications internal table could become a bottleneck, then consider partitioning the user table that has the query notification into several separate tables to reduce the number of possible notifications that must be evaluated for each data change.
If your notification requests have a short useful life span, consider using a time-out with the SqlDependency Constructor that is significantly less than the default of 5 days (for example one minute). This can greatly reduce the number of rows in internal query notifications tables. That in turn can reduce time that is required to process those tables, and reduce lock contention on them.
Alternatives to Query Notifications
If you need a fast, highly predictable response time for notifications about data changes in an environment with high data update rates and many outstanding notification requests, consider alternate solutions, such as one of the following.
Create an AFTER UPDATE trigger on the table being monitored, whose action uses SQL Server Service Broker to send a message to the entity needing the notification. (This could be designed in several ways, such as adding a column to the table of interest with an indication of the entity that must be notified of changes, or joining the primary table to second table that contains information about the entity that requires notification.)
Use a custom application-layer solution that does not rely on query notifications. For example, configure a notification to occur entirely from a middleware application that maintains the data being monitored in a collection of main-memory objects. Your application can generate a notification when an object is modified in a way that meets your criteria for a notification.
Use Windows Server App Fabric Cache, which supports a change notifications mechanism, based on an in-memory object cache and callback functions you register with the objects.