SQL缓存相关性的性能问题 [英] Performance questions for SQL Cache Dependency

问题描述

我正在一个项目中，我们正在考虑将SQLCacheDependency与SQL Server 2005/2008一起使用，我们想知道这将如何影响系统性能.

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

SQLCacheDependency对象(查询通知)的数量是否会对SQL Server性能(即对受影响的表的插入，更新和删除操作)产生负面影响?

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 ?

例如，在单个表上的50000个不同的查询通知在SQL Server 2005/2008中对插入和删除该表有什么影响(在性能方面).

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.

是否有关于如何使用SQLCacheDependencies的建议?有官员做与不做吗?我们已经在互联网上找到了一些信息，但尚未找到有关性能影响的信息.

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.

推荐答案

使用轮询机制的SQL Cache依赖项不应成为sql服务器或应用程序服务器上的负载.

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

让我们了解sqlcachedependency的所有工作步骤并进行分析:

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

1. 数据库已启用sqlcachedependency.
2. 一个表说'Employee'已启用sqlcachedependency. (可以是任意数量的表)
3. Web.config已更新为启用sqlcachedependency.
4. 配置了使用sql缓存依赖项的页面. 就是这样.

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.

内部:

• 步骤1.在数据库中创建一个表'ASPnet_sqlcachetablesforchangenotification'，该表将存储启用了sqlcachedependency的'Employee'表名.并添加一些存储过程.
• 步骤2.在"ASPnet_sqlcachetablesforchangenotification"表中插入"Employee"表项.还在此雇员"表上创建一个插入更新删除触发器.
• 第3步.通过提供连接字符串和轮询时间来启用sqlcachedependency的应用程序.

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

只要"Employee"表中发生更改，就会触发触发器，从而更新"ASPnet_sqlcachetablesforchangenotification"表. 现在，应用程序每隔5000毫秒轮询一次数据库，并检查对"ASPnet_sqlcachetablesforchangenotification"表的任何更改.如果有任何更改，将从内存中删除相应的缓存.

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.

缓存与数据新鲜度相结合的巨大好处(最多可以使数据陈旧5秒).轮询是由后台进程执行的，因此不应成为性能障碍.因为从上面的列表中可以看到，该任务对CPU的要求最低.

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.

这篇关于SQL缓存相关性的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！