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.
我正在研究一个项目，我们正在考虑使用SQLCacheDependency与SQL Server 2005/2008，我们想知道这将如何影响系统的性能。
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 ?
SQLCacheDependency对象(查询通知)的数量是否会对SQL服务器性能产生负面影响，例如对受影响表的插入、更新和删除操作?
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.
例如，SQL Server 2005/2008中单个表上的50000个不同查询通知对该表的插入和删除有什么影响(性能方面)?
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.
关于如何使用SQLCacheDependencies，有什么建议吗?有什么官方行为吗?我们在因特网上找到了一些信息，但是没有找到关于性能影响的信息。
If there is anyone here that has some answers to these questions that would be great.
如果在座有谁能回答这些问题，那就太好了。
5 个解决方案
#1
6
The SQL Cache dependency using the polling mechanism should not be a load on the sql server or the application server.
使用轮询机制的SQL缓存依赖项不应该是SQL服务器或应用服务器上的负载。
Lets see what all steps are there for sqlcachedependency to work and analyze them:
让我们看看sqlcachedependency如何工作的所有步骤，并分析它们:
- Database is enabled for sqlcachedependency.
- 为sqlcachedependency启用数据库。
- A table say 'Employee' is enabled for sqlcachedependency. (can be any number of tables)
- sqlcachedependency启用一个表，其中显示“Employee”。(可以是任意数量的表格)
- Web.config is updated to enable sqlcachedependency.
- 网络。配置被更新为启用sqlcachedependency。
- The Page where u r using sql cache dependency is configured. thats it.
- 使用sql缓存依赖项配置ur的页面。这是它。
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.
- 步骤1。在数据库中创建一个“ASPnet_sqlcachetablesforchangenotification”表，它将存储启用sqlcachedependency的“员工”表名。并添加一些存储过程。
- step 2. inserts a 'Employee' table entry in the 'ASPnet_sqlcachetablesforchangenotification' table. Also creates an insert update delete trigger on this 'Employee' table.
- 步骤2。在“ASPnet_sqlcachetablesforchangenotification”表中插入一个“Employee”表条目。还在这个“Employee”表上创建了一个insert update delete触发器。
- step 3. enables application for sqlcachedependency by providing the connectionstring and polltime.
- 步骤3。通过提供connectionstring和polltime，使应用程序能够应用于sqlcachedependency。
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.
每当在“Employee”表中发生更改时，都会触发触发器，触发器将依次更新“ASPnet_sqlcachetablesforchangenotification”表。现在，应用程序在数据库中显示每个5000ms，并检查对“ASPnet_sqlcachetablesforchangenotification”表的任何更改。如果有任何更改，则从内存中删除相应的缓存。
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.
缓存与数据的新鲜度相结合的巨大好处(大多数数据可能会过期5秒)。轮询由后台进程负责，不应该成为性能障碍。因为正如你从上面看到的，任务对CPU的要求最少。
#2
4
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.
SQLCacheDependency是作为一个索引视图来实现的，并且每当表被修改时，这个视图索引就会被修改。在同一个表上有如此多的视图(SQLCacheDependency对象)，这意味着修改的效果非常好。但是，如果每个表有一个视图(SQLCacheDependency对象)，那么应该没有问题。
the cache changed notification is async and is triggered when the server has resources.
缓存更改的通知是异步的，当服务器有资源时触发。
#3
2
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
你是对的，这里没有提供太多信息，但是在这个页面上有一个与你的问题相关的短语: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."
“与SQL缓存依赖关系相关的数据库操作很简单，因此不需要在服务器上花费大量的处理成本。”
Hope this helps you although your question is a little bit old already.
希望这对你有所帮助，虽然你的问题已经有点过时了。
#4
1
This page appears to have some good info on setup which technique to use well (granted I did just skim it).
这个页面似乎有一些关于如何使用好的设置的好信息(假定我只是略读了一下)。
#5
1
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.
我所能提供的只是性能方面的轶事证据，但是我们将SqlCacheDependency用作一种大型企业应用程序的“消息传递解决方案”，该应用程序每小时处理一万条消息。
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.
基本的体系结构是，我们的公司使用Perforce进行源代码控制，我们有一个“订阅服务”，从触发器webservice调用接收消息，而不是在每次p4提交时被调用，并将记录插入到SQL数据库中。我们的应用程序具有依赖性设置，可以为每一个影响到正在监视的分支或路径的变更发送订阅通知。
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.
性能很好。触发器运行在200ms的订单上，我们从来没有抱怨过将消息转发给最终用户的延迟。
As always, your mileage may vary.
一如既往，你的里程可能会有所不同。
#1
6
The SQL Cache dependency using the polling mechanism should not be a load on the sql server or the application server.
使用轮询机制的SQL缓存依赖项不应该是SQL服务器或应用服务器上的负载。
Lets see what all steps are there for sqlcachedependency to work and analyze them:
让我们看看sqlcachedependency如何工作的所有步骤，并分析它们:
- Database is enabled for sqlcachedependency.
- 为sqlcachedependency启用数据库。
- A table say 'Employee' is enabled for sqlcachedependency. (can be any number of tables)
- sqlcachedependency启用一个表，其中显示“Employee”。(可以是任意数量的表格)
- Web.config is updated to enable sqlcachedependency.
- 网络。配置被更新为启用sqlcachedependency。
- The Page where u r using sql cache dependency is configured. thats it.
- 使用sql缓存依赖项配置ur的页面。这是它。
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.
- 步骤1。在数据库中创建一个“ASPnet_sqlcachetablesforchangenotification”表，它将存储启用sqlcachedependency的“员工”表名。并添加一些存储过程。
- step 2. inserts a 'Employee' table entry in the 'ASPnet_sqlcachetablesforchangenotification' table. Also creates an insert update delete trigger on this 'Employee' table.
- 步骤2。在“ASPnet_sqlcachetablesforchangenotification”表中插入一个“Employee”表条目。还在这个“Employee”表上创建了一个insert update delete触发器。
- step 3. enables application for sqlcachedependency by providing the connectionstring and polltime.
- 步骤3。通过提供connectionstring和polltime，使应用程序能够应用于sqlcachedependency。
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.
每当在“Employee”表中发生更改时，都会触发触发器，触发器将依次更新“ASPnet_sqlcachetablesforchangenotification”表。现在，应用程序在数据库中显示每个5000ms，并检查对“ASPnet_sqlcachetablesforchangenotification”表的任何更改。如果有任何更改，则从内存中删除相应的缓存。
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.
缓存与数据的新鲜度相结合的巨大好处(大多数数据可能会过期5秒)。轮询由后台进程负责，不应该成为性能障碍。因为正如你从上面看到的，任务对CPU的要求最少。
#2
4
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.
SQLCacheDependency是作为一个索引视图来实现的，并且每当表被修改时，这个视图索引就会被修改。在同一个表上有如此多的视图(SQLCacheDependency对象)，这意味着修改的效果非常好。但是，如果每个表有一个视图(SQLCacheDependency对象)，那么应该没有问题。
the cache changed notification is async and is triggered when the server has resources.
缓存更改的通知是异步的，当服务器有资源时触发。
#3
2
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
你是对的，这里没有提供太多信息，但是在这个页面上有一个与你的问题相关的短语: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."
“与SQL缓存依赖关系相关的数据库操作很简单，因此不需要在服务器上花费大量的处理成本。”
Hope this helps you although your question is a little bit old already.
希望这对你有所帮助，虽然你的问题已经有点过时了。
#4
1
This page appears to have some good info on setup which technique to use well (granted I did just skim it).
这个页面似乎有一些关于如何使用好的设置的好信息(假定我只是略读了一下)。
#5
1
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.
我所能提供的只是性能方面的轶事证据，但是我们将SqlCacheDependency用作一种大型企业应用程序的“消息传递解决方案”，该应用程序每小时处理一万条消息。
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.
基本的体系结构是，我们的公司使用Perforce进行源代码控制，我们有一个“订阅服务”，从触发器webservice调用接收消息，而不是在每次p4提交时被调用，并将记录插入到SQL数据库中。我们的应用程序具有依赖性设置，可以为每一个影响到正在监视的分支或路径的变更发送订阅通知。
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.
性能很好。触发器运行在200ms的订单上，我们从来没有抱怨过将消息转发给最终用户的延迟。
As always, your mileage may vary.
一如既往，你的里程可能会有所不同。