I've heard the advice that a client application shouldn't use more than 10 concurrent SqlDependency objects. Does this suggestion apply to the ASP.NET layer Is there any reason that we couldn't have 100 SqlDependency objects running concurrently in the ASP.NET layer

Re: how scalable is service broker?

Remus Rusanu

Service Broker is designed for extremely high scalability, supporting millions of services and dialogs. However your question is not about Service Broker, but about SqlDependency. If there are recommendations for the number of SqlDependency objects running concurrently those derive from the SqlDependency itself, not from the Service Broker. There are two main factors that limit the SqlDependency scalability:

- in order to receive notifications, each appdomain posts one WAITFOR(RECEIVE...) statement on the back-end server. This causes one SQL thread to block and may lead to an unresponsive server if too many threads are 'hijacked' like this.

- SqlDependency when it receives a notification it calls the completion callaback. As far as I know this is done in one thread, thus all callbacks are serialized.

You can work around both limitations if you choose to go to the more basic facility of the SqlNotifications instead of SqlDependency. The later is focused on ease of use, while the former is focused on simply exposing the server side Query Notifications feature. With SqlNotifications you can scale much higher if you know what you're doing. Depending on what you have in mind/app needs I can advice on how to do this.

~ Remus