SQL Server 2005，缓存和所有爵士乐 [英] SQL Server 2005, Caches and all that jazz
问题描述
问题的背景：我正在为我的网站实施一个缓存系统。目前，我们正在探索内存缓存，以实现此目的。但是，我正在查看SQL Server是否存在类似的内容。我了解到MySQL具有查询缓存，尽管它不是分布式的，但可以作为一种停止差距措施。 MySQL查询缓存是否等同于SQL Server中的缓冲区缓存？
Background to question: I'm looking to implement a caching system for my website. Currently we're exploring memcache as a means of doing this. However, I am looking to see if something similar exists for SQL Server. I understand that MySQL has query cache which although is not distributed works as a sort of 'stop gap' measure. Is MySQL query cache equivalent to the buffer cache in SQL Server?
所以这是我的问题：
- 是否有办法知道当前存储在缓冲区高速缓存中？
- 紧接着，是否有一种方法可以将某些表或结果集强制进入缓存
- 我对所进行的操作有多少控制权在缓冲区和过程缓存中？我知道以前曾经有一个DBCC PINTABLE命令，但是此命令已被终止。
- 稍有不同的主题：缓存甚至应该存在于数据库层吗？还是使用Velocity / Memcache管理缓存更为谨慎？是这样，为什么呢？当处理具有重叠触发器的许多对象时，似乎缓存失效是一种痛苦。
- Is there a way to know is currently stored in the buffer cache?
- Follow up to this, is there a way to force certain tables or result sets into the cache
- How much control do I have over what goes on in the buffer and procedure cache? I understand there used to be a DBCC PINTABLE command but that has since been discontinued.
- Slightly off topic: Should the caching even exists on the database layer? Or it is more prudent to manage caches using Velocity/Memcache? Is so, why? It seems like cache invalidation is something of a pain when handling many objects with overlapping triggers.
谢谢！
推荐答案
奈，
对您的问题的回答如下：
Answers to your questions follow:
-
来自 Wiki-始终正确...？ :-) 。有关 Microsoft 的更多答案，请参见缓冲区缓存。
From Wiki - Always correct... ? :-). For a more Microsoft answer, here is their description on Buffer Cache.
< h3>缓冲区管理
SQL Server将RAM中的页面缓冲到
以最小化磁盘I / O。可以在内存中缓冲任何8 KB页面
，当前缓冲的所有页面的
集合称为缓冲区高速缓存
。 SQL Server可用的内存
的数量决定了
个页面如何在内存中缓存。
缓冲区高速缓存由
缓冲区管理器管理。从
读取或写入任何页面都会将其复制到
缓冲区高速缓存中。随后的读取或
写入将重定向到内存中的
副本，而不是光盘上的版本。
仅当在某些
时间内未引用内存中的
高速缓存时，此页面才由
缓冲区管理器在光盘上更新。在将页面写回到
光盘时，使用异步I / O，从而
的I / O操作在
后台线程中完成，因此其他
操作不必等待
I / O操作完成。每页
连同其校验和
一起被写入。当读回
页面时，将再次计算
的校验和并与存储的
版本匹配，以确保页面中的
未被损坏或被篡改。
SQL Server buffers pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksum when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime.
有关此答案，请参考上面的答案：
For this answer, please refer to the above answer:
读取或写入任何页面都会将其复制到缓冲区高速缓存中。随后的读取或写入将重定向到内存中的副本，而不是光盘上的版本。
Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version.
您可以查询 sys.dm_os_sys_info 目录中的 bpool_commit_target 和 bpool_committed 列视图分别返回保留为内存目标的页面数和当前在缓冲区高速缓存中提交的页面数。
You can query the bpool_commit_target and bpool_committed columns in the sys.dm_os_sys_info catalog view to return the number of pages reserved as the memory target and the number of pages currently committed in the buffer cache, respectively.
我觉得Microsoft已经拥有是时候弄清楚他们产品的缓存了，应该值得信赖。
I feel like Microsoft has had time to figure out caching for their product and should be trusted.
我希望此信息对您有所帮助，
I hope this information was helpful,
这篇关于SQL Server 2005，缓存和所有爵士乐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
