Performance questions for SQL Cache Dependency

Question

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 are wondering about the following questions

Can the number of SQLCacheDependency objects (query notifications) have negative effect on SQL Server performance i.e. on insert, update and delete operations on affected tables ?

What effect (performance wise) would for example 50000 different query notifications on a single table have in SQL Server 2005/2008 on insertion and deletion on that table.

Are there any recommendations of how to use SQLCacheDependencies? Any official do‘s and don‘ts? We have found some information on the internet but haven‘t found information on performance implications.

If there is anyone here that has some answers to these questions that would be great.

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

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