What’s the SqlDependency doing now?

I'm headed out on the road. For quite a while. Wanted to settle this before I went.

Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and a Service Broker Dialog Timer. Every 120 seconds, the timer fires, which activates the procedure, which sets the timer again. The first time I saw this, I thought the procedure was polling. Actually its more like its trauling. But its not polling (whew).

The way SqlDependency works now is that requests for query notifications set up their own broker services to catch the notification message. The activation proc (also set up for the notifications) relays the notification message back to the client. Which calls your event handler, or in the case of ASP.NET's SqlNotificationRequest, invalidates the cache.

So what's the timer for? The timer is trauling to find services and procedures (they have GUIDs in their names) set up by SqlDependency instances and will clean them up if it finds any. This is NOT on an individual SqlDependency instance basis, but on a *per-listner* basis. That is, once for every client that calls Start.

Take home message is, now more than ever, you need to limit the number of clients that call Start. Maybe only ASP.NET (or other) caches???

I'm glad that's settled. See you on the road I'm teaching/talking about this more in person…c'mon over and say hi.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.