Not able to find details for some Query Notification Info and Sources

Question

Hello,

We want to use SqlNotificationRequest for Cache Synchronization. We found it very useful and very good feature. But We could not find help/documentation for extreme conditions - Like, one possible Query Notification Info "query template limit" - What does it mean? and how to recover from this error? In which situations this error will occur? How Sql Server will behave in this error? Available documentation shows how to use query notification feature but they do not give enough insight for extreme error conditions. If we want to implement this feature in production environment then we should have knowledge and plan of action for such possible extreme error conditions. Other two less docemented Notification Sources are "kill" and "check".

Can anyone please give information for these types and Info for Query Notification? Or Are we missing documents on these things - If so please give source for those documents.

Regards.

Answer 1

Hi,

I'm trying to find more information for you regarding your question. I will update the thread once I have it.

Thanks,

Cathy Miller

Answer 2

Thank you Cathy for your reply.

Regards.

Answer 3

Hi Cathy,

Is there any update?

Regards.

Vishal

Answer 4

Posting on behalf of Susan Price:

 

---

 

Hello,

 

The query template limit is described in the topic Planning for Notifications here: http://msdn.microsoft.com/en-us/library/ms187528.aspx.  Basically, when queries share a common structure and vary only in the parameter values, SQL Server can use an internal template to more efficiently store multiple similar queries that have query notification subscriptions.  An internal template is maintained for each query structure that has at least one registered subscription.  There is a limit on how many internal templates can be maintained.  Once the limit is reached, you will no longer be able to register a subscription that will require a new template to be created (until another template is no longer needed and is destroyed).

 

I am not sure from your question under what circumstances you encountered "kill" and "check."  They are not documented because they correspond to internal sources.  "Kill" is used when a subscription is explicitly killed using KILL QUERY NOTIFICATION SUBSCRIPTION statement. “Check” corresponds to an internal database consistency check.

 

Some additional information about query notification messages is here: http://msdn.microsoft.com/en-us/library/ms189308.aspx and additional information about status codes is here: http://msdn.microsoft.com/en-us/library/ms187793.aspx.

 

An important thing to remember about query notifications is that they were designed for use only when data changes are relatively infrequent (e.g. on the order of minutes, not seconds).  Updating tables in the presence of query notification subscriptions and creating new query subscriptions both have costs.  Frequent updates or creation of many new subscriptions can affect performance.  Also, creating a large number of subscriptions that never fire and have a long timeout can result in accumulation of a large number of subscriptions, which can also affect performance.

 

I hope this information is helpful.

 

Regards,

Susan Price, SQL Server

---

Boris Baryshnikov, SQL Server

Answer 5

Hello Boris, Susan, Cathy

Thank you for your help.

I never encountered "kill" and "check."   But from query notification documents I got these two possible sources so I asked for them. But now from answer, it is clear that they are for internal SQL server purpose and users, like me, are not going to encounter it.

We may have around 80 to 100 configuration tables in our new project's database and we are thinking to observe them for changes using Query Notification so we can synchronize our cache. We are thinking of having one subscription per table so there will be 80 to 100 subscriptions. Are these many subscriptions OK? These are configuration tables and going to be updated less frequently.

"An important thing to remember about query notifications is that they were designed for use only when data changes are relatively infrequent (e.g. on the order of minutes, not seconds).  Updating tables in the presence of query notification subscriptions and creating new query subscriptions both have costs.  Frequent updates or creation of many new subscriptions can affect performance.  Also, creating a large number of subscriptions that never fire and have a long timeout can result in accumulation of a large number of subscriptions, which can also affect performance."

Here, overall performance of SQL server will be affected or performance of Insert/Update/Delete on tables which have query notifications will be affected?

Thanks and Regards.

Vishal

Answer 6

Hi Vishal,

We may have around 80 to 100 configuration tables in our new project's database and we are thinking to observe them for changes using Query Notification so we can synchronize our cache. We are thinking of having one subscription per table so there will be 80 to 100 subscriptions. Are these many subscriptions OK? These are configuration tables and going to be updated less frequently.

This number of subscriptions is fine, as soon as there are no relevant data changes or new subscriptions coming at a rate of few per second or so.
Please, note however that if there’s a scenario when new query notification subscriptions can be created at a high rate (for example, if the system uses different subscription per the application user, and has high rate of actions requiring creating a subscription for each user), this may also imply performance issues, even if data is changed infrequently (though in such case, obviously, you would end up with large number of active subscriptions, 80-100 multiplied by number of users created them).

Here, overall performance of SQL server will be affected or performance of Insert/Update/Delete on tables which have query notifications will be affected?

In the worst case, overall performance of SQL Server can be affected, since performance of queries related to query notification may significantly slow down and occupy important server resources like worker threads and others for much longer time.

Thanks,

Cathy Miller

Microsoft Online Community Support

 

Answer 7

Hello Cathy,

First of all thank you for the response.

We will have 80 to 100 subscriptions only. They will not be multiplied by number of users. Reason is that we will have our separate single component which will subscribe query notification and when that component will find data change through query notification that component will give changed records to our Notification Service which has end points for all clients/users and that notification service will inform all clients/user about data change.

Regards,

Vishal