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

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

Answer 2

maybe we can make some performance test project to find out those questions's answer.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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. step2) inserts a 'Employee' table entry in the 'ASPnet_sqlcachetablesforchangenotification' table. Also creates an insert update delete trigger on this 'Employee' table. step3) 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.