Performance Questions For SQL Cache Dependency

I'm working on a project where we are thinking of using SQLCacheDependency with SQL Server 2005/2008 and we are wondering how this will affect the performance of the system. So we

Solution 1:

The SQL Cache dependency using the polling mechanism should not be a load on the sql server or the application server.

Lets see what all steps are there for sqlcachedependency to work and analyze them:

1. Database is enabled for sqlcachedependency.
2. A table say 'Employee' is enabled for sqlcachedependency. (can be any number of tables)
3. Web.config is updated to enable sqlcachedependency.
4. The Page where u r using sql cache dependency is configured. thats it.

Internally:

• step 1. creates a table 'ASPnet_sqlcachetablesforchangenotification' in database which will store the 'Employee' table name for which sqlcachedependency is enabled. and add some stored procedures aswell.
• step 2. inserts a 'Employee' table entry in the 'ASPnet_sqlcachetablesforchangenotification' table. Also creates an insert update delete trigger on this 'Employee' table.
• step 3. enables application for sqlcachedependency by providing the connectionstring and polltime.

whenever there is a change in 'Employee' table, trigger is fired which inturn updates the 'ASPnet_sqlcachetablesforchangenotification' table. Now application polls the database say every 5000ms and checks for any changes to the 'ASPnet_sqlcachetablesforchangenotification' table. if there r any changes the respective caches is removed from memory.

The great benefit of caching combined with freshness of data ( atmost data can be 5 seconds stale). The polling is taken care by a background process with should not be a performance hurdle. because as u see from above list the task are least CPU demanding.

Solution 2:

SQLCacheDependency is implemented as an indexed view and every time the table is modified this views index gets changed. so many views (SQLCacheDependency objects) on the same table mean quite a perf hit for modifications. however if you have 1 view (SQLCacheDependency object) per table you should have no problems.

the cache changed notification is async and is triggered when the server has resources.

Solution 3:

You're right, not much information on this is provided but there's a phrase related to your question in this page http://msdn.microsoft.com/en-us/library/ms178604%28VS.80%29.aspx

"The database operations associated with SQL cache dependency are simple and therefore do not incur a heavy processing cost on the server."

Hope this helps you although your question is a little bit old already.

Solution 4:

This page appears to have some good info on setup which technique to use well (granted I did just skim it).

Solution 5:

All I can provide is anecdotal evidence for performance, but we use SqlCacheDependency as a sort of "messaging solution" for a large enterprise application that processes on the order of ten thousand messages per hour.

The basic architecture is that our company uses Perforce for source control and we have a "subscription service" that receives messages from a trigger webservice call than gets called on every p4 commit and inserts a record into a SQL database. Our application has the dependency setup to send subscription notifications for every changeliest that affects a branch or path that you are monitoring.

The performance is fine. Trigger runs on the order of 200ms and we have never had a complaint about the latency of relaying the messages to end users.

As always, your mileage may vary.