Friday, January 25, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 5 Roll Your Own


This is the last in my series of posts about different options you have with SQL Server 2005 database cache invalidation.

Up until now, we've seen 4 different options for receiving notifications in our web or Windows application that data has changed.  Each of the options there, had one limitation, in that you can only receive notifications if a set of data has changed. 

So, what if you've got a more granular caching system.  One that caches individual entities, as opposed to collections of entities?  Up until this post, none of the options would provide you the ability to receive a notification on a single row changing.

I'm going to cover how to do this in this post, by showing you how to roll your own notification messages using SQL Server Service Broker.


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.

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.)

UPDATE #2: Updated post to include the issues that I've discovered after using this code for some time.  See this post about CPU and tempdb issues with SQL Service Broker.

Roll Your Own Cache Notification

Here is the list of tasks I'm going to be doing to get this to work:

  1. Create a SQL Server Service Broker Service and Queue
  2. Create a trigger on the table I'm interested in, that loops through the changed rows, and creates custom XML message with the identifying attributes of the row that has changed, and inserts that XML message into the Service Broker Queue.
  3. Write a background service in my ASP.NET application that listens for new entries in that Queue.

The first thing we have to do is create our SQL Service Broker Queue and Service.  These will be the structures for the queuing of notifications.  It's the same as in the SqlNotificationRequest example.

CREATE QUEUE PersonChangeMessages;
CREATE SERVICE PersonChangeNotifications
  ON QUEUE PersonChangeMessages

The result of this will be the following objects in SQL Server:

screen shot of Service Broker objects

The next thing we need to do is create our trigger on the table that we need to be notified of changes for, which will look like so:

CREATE TRIGGER [dbo].[tr_Person_NotifyCacheQueue]
   ON [dbo].[Person]
    -- Insert statements for trigger here
    DECLARE @Message XML
    DECLARE @Action VARCHAR(10)
    DECLARE @InsTblCnt INT
    DECLARE @DelTblCnt INT;
    SET @Action = '';
    SET @InsTblCnt = 0;
    SET @DelTblCnt = 0;
    SELECT @InsTblCnt = Count(*) FROM Inserted;
    SELECT @DelTblCnt = Count(*) FROM Deleted;
    IF @InsTblCnt > 0 AND @DelTblCnt > 0 SET @Action = 'UPDATE';
    ELSE IF @DelTblCnt > 0 SET @Action = 'DELETE';
    ELSE IF @InsTblCnt > 0 SET @Action = 'INSERT';
    ELSE SET @Action = 'NONE';
    IF @Action <> 'NONE'
            DECLARE @tempTable TABLE ( [ID] INT )
            IF @Action = 'DELETE'
                SET @Message = (SELECT * FROM Deleted FOR XML AUTO, ROOT ('ChangedPersonRows'))
                SET @Message = (SELECT * FROM Inserted FOR XML AUTO, ROOT ('ChangedPersonRows'))
            DECLARE @DialogHandle UNIQUEIDENTIFIER
            BEGIN DIALOG @DialogHandle
            FROM SERVICE [PersonChangeNotifications]
            TO SERVICE 'PersonChangeNotifications'
            ON CONTRACT []
            SEND ON CONVERSATION @DialogHandle
            MESSAGE TYPE [] (@Message);

The trigger loops through all the changes, and builds an Xml document out of it, then sends that Xml document to the SQL Server Service Broker Queue.

Just as before, we need to create a service in our application that listens to the queue for changes.  It's really similar to before, but in this case, we're getting the information from the queue message to construct our cache key.

public class Listener
    private static readonly string cacheKey = "Person:{0}";
    private static bool isAlreadyListening = false;
    public void Start()
        if (isAlreadyListening) return;
        //create and call the delegate for the asynchronous call
        AsyncListenerDelegate asyncListener = new AsyncListenerDelegate(Listen);
        asyncListener.BeginInvoke(HttpContext.Current, new AsyncCallback(StopListening), null);
        isAlreadyListening = true;
    private void Listen(HttpContext currentContext)
        while (true)
            //create the command that will listen to the queue
            using (SqlConnection connection = new SqlConnection(
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"DECLARE @conversationHandle uniqueidentifier;
                        DECLARE @message AS XML;
                        WAITFOR (RECEIVE @conversationHandle = [conversation_handle],
                            @message = CAST(message_body AS XML) FROM PersonChangeMessages);
                            END CONVERSATION @conversationHandle;
                            SELECT @message;";
                command.CommandTimeout = 60 * 5; //listen in 5 minute increments
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                        SqlXml sqlXml = reader.GetSqlXml(0);
                        XmlReader xmlReader = sqlXml.CreateReader();
                        while (xmlReader.Read())
                            while (xmlReader.MoveToAttribute("LastName"))
                                currentContext.Cache.Remove(string.Format(cacheKey, xmlReader.Value));
                catch (SqlException)
                    //if the query times out, that means that no messages have
                    //been sent to the queue yet, so we should keep listening.
    private void StopListening(IAsyncResult result)
        //no information needed here.

And finally, here's my service that retrieves the person:

public static Person GetPerson(string lastName)
    string cacheKey = string.Format("Person:{0}", lastName);
    Person person = (Person)HttpContext.Current.Cache.Get(cacheKey);
    if (person == null)
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons
                    where p.LastName == lastName
                    select p;
        person = query.Single();
        Listener listener = new Listener();
        HttpContext.Current.Cache.Insert(cacheKey, person);
    return person;
You can see here that I'm interested in more granular caching information.  I'm caching by individual person id's, and I don't want to dump the cache of all individual people when the person table changes.  I want to clear the cache for individual items, and this method allows me to do it.


LeeTheGreek said...

Great post. Works a treat. The only problem i can think of in using this within my applications is that the trigger does not cater for the ntext data type.

Jim Fiorato said...

Thanks Mr. TheGreek.

Yes, that trigger doesn't really do the text data types. But, those are going away anyway (use NVARCHAR(MAX) now), and I'm trying to think of a reasonable use case to have such a large text field as a cache key.

Keep an eye on you Service Broker Conversations.

select * from sys.conversation.endpoints

I've found it really easy to leak those, and put a bunch of additional overhead on the SQL Server.

Thanks for your feedback.

miksu said...


...interesting series, but I don't quite understand how this works with multiple clients. Is notification going to be registered for each of them? Or only the first one? - Vận tải Con thoi said...

Thanks for sharing. Nếu cần gửi hàng đi quảng ninh thì a/c liên hệ bên mình nhé. Hotline: 0903751981