Implementation of SQL cache dependency in the .NET framework

Today's world is moving very fast due to the development and evolution of computer and software. The software ensures that the business process runs better, smooth, and reliably to ensure the required information is available at any time and at the faster rate. Nowadays, the organizations are using more electricity than paper. Most of the organizations runs the software applications that executes on database/data stores. The database is very critical for any business intelligence application to work smarter. These applications processes the large chunk of data every second and produces the information to make decisions based on it. The application performance may reduce if the average number of database calls increases because the processing happens outside the application server machine boundary. It becomes very important to monitor the number of database calls that are made from the application for the better performance. This is achieved using caching.

Caching is the process by means of which the application maintains its own local copy of the data to work with. Ideally this copy of the data is exactly same as the backend store. The cache ensures that the application gets the data whenever the application needs it at the faster rate. The access to the local copy of the data is much faster than fetching the data from the backend store. The caching is very robust and excellent feature provided by .NET framework. In this post, I will discuss the important concept called SQL dependency caching that can improve the performance of any business intelligence application drastically. There are numerous articles available on the internet which guides on the implementation of the SQL cache dependency in the application. I will restrict my blog post to include only the concept of SQL cache dependency from the perspective of framework's internal implementation. There are several other types of caching that are available in .NET framework which a developer can use in conjunction with each other.

The need of having the separate set of data can be well understood by the analogy of ATM - Automatic Teller Machine scenarios. The bank offers ATM service to reduce the overhead of managing customer's routine requests inside the bank premises. The customers can do their routine transactions in ATM very quickly instead of visiting the bank. The bank can reduce the number of customers rushing inside the bank every day by increasing number of ATM machines around it. Therefore the number of ATM machines are more than number of branches of the bank. The customer can always visit bank in case of failure of ATM machine. The ATM machine can be visualize as the cached data of the application. There can be several copies of the cache within the application depending on the type of operations performed by the feature of the application. The application can request the new copy of the data from the store in case of cache refreshes (just like periodic clearance, refilling and maintenance of the ATM machine done by the bank).

Let's understand few caching related concepts from .NET perspective.

1. Cache: It is the in-memory store of data which reduces the database calls from the application
2. Cache Dependency: The dependency is the actual store of data which resides on same or different machine. Dependency can be understood as a link between cache and actual store. The runtime clears the cached data when there are any changes in the dependency object
3. Callbacks: These are the special types of functions that gets executed when there are changes in the actual store. They are known as callbacks because they are invoked by the operating system on the application when the dependency object changes. The callback happens real-time which means the callback function executes as soon as operating system detects the changes in the dependency object.

SQL Dependency Caching:

The SQL dependency caching works with the SQL server. It is the type of dependency for the application cache object. The SQL dependency enables MSSQL¹ to notify the application when the data in the underlying tables changes. SQL cache dependency can be enabled on MSSQL by few configuration steps as provided below:

Configuration on SQL server:

1. Enable caching infrastructure at the server level using aspnet_regsql command.

e.g.: aspnet_regsql -S dumbledore -U sa -P welcome@123 -d Northwind -ed

The above command creates following database objects for SQL server's internal use:

1. Table: AspNet_SqlCacheTablesForChangeNotification: This table maintains the list of the tablenames that are registered for cache dependency. This means the SQL server notifies the application when data changes in any of these tables.
2. SP²: AspNet_SqlCachePollingStoredProcedure: This stored procedure polls the database to know if the data has been changed from the last poll. This stored procedure is executed periodically within the time period set in config files. Sql profiler can trace the cal to this SP calls when configured for a database server.
3. SP: AspNet_SqlCacheQueryRegisteredTablesStoredProcedure: This SP gets the list of the tables registered for SQL cache dependency.
4. SP: AspNet_SqlCacheRegisterTableStoredProcedure: This SP registers the table for the sql cache dependency which means that the table is monitored and the application is notified if the underlying data changes

5. SP: AspNet_SqlCacheUnRegisterTableStoredProcedure: This SP unregisters the table for the sql cache dependency which means that the table is no longer monitored for the dependency notifications

6. SP: AspNet_SqlCacheUpdateChangeIdStoredProcedure: This SP updates the version number of the table in the AspNet_SqlCacheTablesForChangeNotification table after the data has been modified. This is used by the polling stored procedure to decide whether the data has been changed or not since the last poll.
   
   
   

   Figure: 1 - Enable sql dependency at database level

   
   
   
   Figure:2 - Tables created after enabling sql cache dependency on MSSQL

   
   

   

   Figure-3: Stored procedures created after enabling sql cache dependency on MSSQL
   
   

2. Enable table for sql cache dependency using aspnet_regsql command.
   
   

Figure-4: Enable SQL cache dependency for a table in MSSQL

e.g.: aspnet_regsql -S dumbledore -U sa -P welcome@123 -d Northwind -t Categories -et

The above command creates the trigger on the table specified with -t argument (in this case, Categories). The trigger updates the version number in AspNet_SqlCacheTablesForChangeNotification table to ensure that the next poll recognizes the changes in this table.

Figure-5: Triggers created after enabling SQL cache dependency on table in MSSQL

3. Enable the broker on the database using ALTER DATABASE NORTHWIND SET ENABLE_BROKER

This ensures that the broker service is set on the database which notifies all the applications that has registered for the SQL cache dependencies.

Application code:

1. The application should register itself for SQL cache dependencies. This can be done using SqlDependency.Start(connectionString). This needs to be done at the application level.
2. The application should create cache dependency objects using SqlCacheDependency. The constructor takes two arguments:
1. databaseEntryName: Name of the database used in the config file
2. tableName: Name of the table to set for sql cache dependency

This needs to be done at the feature level. The dependency should include all the database tables which needs to be registered for the change notifications.

3. The application should create the cache object using Cache.Add(...) and pass the SqlCacheDependency object (created in step 2) as the CacheDependency parameter
4. We're good to go.
   
   

Validating the code:

1. Execute the code without any changes in the database. The value should be picked up from the cache
2. Execute the code after modifying the data in the database. The changes can happen from the application or SQL Management Studio. As soon as the change happens on database the operating system raises the callback to execute the event of the type CacheItemRemovedCallback delegate. The cache should be invalidated and application should make the new database call and refresh the cache
   
   

Various types of dependency:

The application can set the multiple dependencies on the same cache object using AggregateCacheDependency. e.g.: The application can register 5 database tables and 1 text document as the dependency for the single object. In this case, the application receives notification whenever anyone of the above dependency changes.

Applicability:

The caching improves the performance of the application drastically, if used with care. It works on the prediction of how the normal user will operate on the website. The caching is best suited while working with set of master data which does not change often. The application code may not fetch the same data everytime. Instead the code should read the data from the cache that are less likely to be updated frequently. We need to get the proper mix!

---

¹MSSQL is acronym of Microsoft SQL Server
²SP is acronym of Stored Procedure