SQL Server provides an event notification mechanism where the distributed cache like NCache can register itself for change notification through SqlCacheDependency and then receive notifications from SQL Server when underlying data changes in the database. This allows NCache to immediately invalidate or reload the corresponding cached item and this keeps the cache always synchronized with the database. However, SqlCacheDependency can become a very resource intensive way of synchronizing the cache with the database.
A better alternative involves writing a CLR stored procedure that connects with NCache from within SQL Server and directly updates or invalidates the corresponding cached item. It is more resource efficient because it is not creating data structures related to SqlCacheDependency. And, it also does not fire .NET events to NCache. Instead, it opens up an NCache client connection and directly tells NCache whether to invalidate a cached item or reload it. And, this connection with NCache is highly optimized and much faster and lighter than .NET events.
use Northwind
alter database Northwind
set trustworthy on;
go
drop assembly SMdiagnostics
drop assembly [System.Web]
drop assembly [System.Messaging]
drop assembly [System.ServiceModel]
drop assembly [System.Management]
CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication
Foundation\SMdiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Management] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.management.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM N'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY NCache from N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll' WITH permission_set = unsafe
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestSProc(string cacheName)
{
//--- Put your code here
SqlPipe sp = SqlContext.Pipe;
try
{
sp.Send("Starting .....");
if (string.IsNullOrEmpty(cacheName))
cacheName = "mycache";
Cache _cache = NCache.InitializeCache(cacheName);
_cache.Insert("key", DateTime.Now.ToString());
sp.Send("Test is completed ...");
}
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
CLR based stored procedures or triggers can greatly improve the application performance as compared to the SqlCacheDependency that is relatively slower and can be overwhelming for large datasets.