How CommandNotification works with OutputCaching

There is some magic to the way SQL Server 2005 cache dependencies work with the OutputCache directive, and I finally had the chance to dig a little deeper and find out exactly what that magic was, so I thought I'd post about it here in case anyone else is interested.

 

The rationale behind SQL cache dependencies with output cached pages is that you may want a page to be flushed from the cache if the underlying data that was used to generate the page has changed. With SQL 7/2000 cache dependencies, you must install a script in your database, set up a configuration file entry, and then specify exactly which tables should be monitored in the SqlDependency attribute of the OutputCache directive to enable the dependency checking. For example, if we knew that a page depended on the authors and publishers tables in the pubs database, we might have an output cache directive that looked like:

 

<%@ OutputCache Duration="3600" SqlDependency="pubs:authors,pubs:publishers" VaryByParam="none" %>

 

This technique relies on the ASP.NET worker process to poll the database at a periodic interval checking a 'change' table that keeps track of when tables change.

 

When you are using SQL Server 2005, however, you can use the notification service along with Service Broker to asynchronously call back into the ASP.NET worker process and signal that the an item in the cache is no longer valid. What's interesting about using this with output caching is that you need do nothing more than specify the string "CommandNotification" in the SqlDependency attribute, and it "just works" (well, as long as the queries are somewhat constrained and everything is properly set up in SQL 2005...).

 

<%@ OutputCache Duration="3600" SqlDependency="CommandNotification" VaryByParam="none" %>

 

This was the part I've always wondered about - how did they instrument all of the SQL queries on a page to enable notifications and tie them to the cache entry for that output cached page? It turns out that when you mark a page as output cached and set SqlDependency to CommandNotification the page sets a flag in the call context (yes, the same call context you may know from remoting - System.Runtime.Remoting.Messaging.CallContext) called "MS.SqlDependencyCookie". The name actually threw me for a bit, because I began looking for an HTTP cookie with that name and couldn't find one (which was good news - that would have been a major hack :). Anyway, if this cookie is set, the SqlCommand class will automatically create a SqlDependency object and attach it to any queries made, tied to the cache key for the output cached page (the key is stored as a value in the cookie). The end result is that every single SqlCommand used on a page that is output cached and has the SqlDependency set to "CommandNotification" will automatically be issued with a SqlDependency requesting a notification on change.

 

The advantage of this technique is that it is so easy to take advantage of - just write your page like you always would, with either declarative SqlDataSource controls or dynamic queries coded by hand, and all of your commands will automatically be set up with notifications. The drawback is that you don't have any finer-grained control over which data access should trigger a notification and which should not (at least that I could find). Also, if any of the queries in your page do not meet the requirements for SQL 2005 cache dependencies (like using SELECT * or simple table names (authors instead of dbo.authors) ), the page will never be cached at all.

 

 

---

Limited training time? Need to learn ASP.NET 2.0 and SQL Server 2005? Come join Fritz Onion and Dan Sullivan the week of July 17, 2006 in Waltham, MA for a Pluralsight Double Feature: ASP.NET 2.0 and SQL Server 2005 Register today!

---