SQL Server 2005 cache dependency - is there a limit to the no. of registered tables?

Question

I'm trying to implement sql server 2005 cache dependency in ASP.Net 3.5. I have a lot of lookup tables (around 50 in all) in my database that will hardly change after the initial setup. I plan to register all of them in the SQLCacheDependency. I'm wondering if there will be a performance hit when so many tables are registered, because SQL Server will need to keep track of when their data change. And will these overheads nullify the benefits of caching in the first place? Thank you very much.

Answer 1

If they really change seldom then the impact is minimal. You can read up my article at http://rusanu.com/2006/06/17/the-mysterious-notification that explains some of the underlying technology of Query Notifications and the associated cost.

Answer 2

The SqlDependency is intended to have a small number of dependencies actually registered at a time against SQL Server. This is mentioned in the BOL topic for it, but no actual numbers are provided. The SqlDependency utilizes Service Broker, creating a service and queue to handle the notification of change. You can create your own Service Broker implementation that would probably scale better for the number of tables that you are dealing with. Performance would definitely be better if you can reuse the service. See Denny's article on this:

http://itknowledgeexchange.techtarget.com/sql-server/improving-sql-service-broker-performance/