Friday, January 11, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 2 SqlCacheDependency

In my previous post, I reviewed a basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to go over the SqlCacheDependency class and how to use with SQL Server 2005 notifications services.


For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):


Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

The next thing you'll have to do is update the Global.asax to setup the SqlDependency in the Application_Start method.  You do this with the following code:

protected void Application_Start(object sender, EventArgs e)

When you call this method, the runtime sets up the relationship between the application domain and the connection string that will be used later during the notification registration process.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)


SqlCacheDependency is an implementation of the System.Web.Caching.CacheDependency class, and can be used as an invalidation mechanism.  It registers a SqlCommand or a table for change notification and clears the associated cache items when it is notified. 

If you don't need a granular query to notify you of when a change occurs, and you are already using the System.Web cache, then this option will probably work best for you.

Below is how to use the class, and you can see how simple it is.

public static List<Person> GetPeople()
    string cacheKey = "personsCacheKey";
    List<Person> people = (List<Person>) HttpContext.Current.Cache.Get(cacheKey);
    if (people == null)
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons select p;
        people = query.ToList();
        SqlCacheDependency dependency = new SqlCacheDependency(ctx.GetCommand(query) as SqlCommand);
        HttpContext.Current.Cache.Add(cacheKey, people, dependency, DateTime.MaxValue,
                                              TimeSpan.Zero, CacheItemPriority.Normal, null);
    return people;

Any changes that occur within the database will trigger a notification to this dependency class, and will automatically clear the cache for you.

Note how you can get the SqlCommand from the Linq query to wire to the SqlCacheDependency.  The Linq team really did a good job at covering their bases.  I have yet to hit an issue with this language that has prevented me from accomplishing something.  It's very smart.


Anonymous said...

Friend'm trying to use his example so that when they do, the change in the Cache Bank and not back!
I need to do some other setting in SQL Server for the SQL Server Broker Work?

Jim Fiorato said...


I put together some troubleshooting service broker tips. See the bottom of that post.

Anonymous said...

Jim I read the Post, and my application is correct!
Fighting the script at the base so that the cache not inspired, I make changes in the base, and the cache does not change anything.
I do not know where most moving. I need to do something with IIS? machine from the ISS and the Bank may be different machines?
Here I am using the code if you can give me a Help

Anonymous said...

private List KeyWord "GetKeyWordFromCache()
string cacheKey = "KeyWordCacheKey";

List KeyWord ListKeyWord = (List KeyWord)System.Web.HttpContext.Current.Cache.Get(cacheKey);
if (ListKeyWord == null)
NewsDataContext DbNews = new NewsDataContext(
var query = from p in DbNews.KeyWords select p;
ListKeyWord = query.ToList();

SqlCacheDependency dependency = new SqlCacheDependency(DbNews.GetCommand(query) as SqlCommand);

System.Web.HttpContext.Current.Cache.Add(cacheKey, ListKeyWord, dependency, DateTime.MaxValue,
TimeSpan.Zero, CacheItemPriority.Normal, null);
return ListKeyWord;

Anonymous said...

I picked out the signal of Greater and Lesser Do List to make the Post.
Sorry I bothered!
Thank you for your help.