How does a SqlCacheDependency know when to communicate back to any listeners when data in a table changes?

Question

I have been doing some research and I can see most of the plumbing for this system however I'm not sure how sql server knows when to fire a message back to any listeners (applications) when data in a table changes, for instance. I'll start by explaining what I understand up to the point that I'm getting lost.

1) Service Broker needs to be enabled on the database and some permissions need to be set.

2) The database schema should be deployed.

3) Using aspnet_regsql.exe, enable sql cache dependency for the database and tables you need to cache data for (this step creates a table to track changes and triggers on the tables to capture changes and increment a value in that table).

4) Set up the sql cache dependency in the .net application. For instance, in a web app, you'll need to add config values for the poll time, connection string, etc; start/stop the dependency in your global.asax.cs, and then add sql cache dependencies when you add items to your cache.

4a) Part of what happens when starting the dependency is that the infrastructure is set up for a queue, a service, and a sproc for communication and cleanup afterwards. Using Sql Query Profiler, you can see connection being made and a communication channel being set up on the service for the application to receive messages from sql server.

5) Here is where I get confused. At this point, I've cached an item in my application cache with a reference to the sql cache dependency on the underlying table so that my app can receive changes in case that row changes. If I manually run an update on that row, I can see the trigger being hit and the value in the tracking table being incremented by 1. However, I do not see any communication going back to the application, nothing on sql query profiler, nor is the item removed from the cache. I also don't see anything in the queues on the database (neither the dynamic application queue nor the standard error/transmission queues)

My question is, what is watching that tracking table in the database so that messages can be sent back up to the sql dependencies that are concerned about changes to this data?

Any help is greatly appreciated... I've been scouring through many online references and can't find any concrete explanation of this.

Answer 1

A new table is created in your database that holds a name of the table you want to check for updates on, and a change number. Each table you have setup for sqldependency has a trigger set up for updates/inserts that increments the changeid in the new table I just described.

Your mental model of how this works is backwards. Your application checks the log to determine if a table has changed.

So if the changelog table (That's what I call it) is tracking two tables in your database (Product, User) It will look like this.

+Table Name + ChangeNumber +
| Product   | 1            |
+-----------+--------------+
| User      | 1            |
+-----------+--------------+

Now if you modify anything in either of these tables, the trigger will increment ChangeNumber and we now know they changed.

Obviously there is more to this, but this is the general idea.

Note: It should be noted that you can invalidate a page if one or more tables change, so if your page has dependency set up for both of these tables, if one of them changes it will invalidate the cached page and re-cache an updated version.