请教邹建或其他大侠们，SQL Server 的缓存机制

[shuaixf]

在SQL Server 2000 中可以将表标记为缓存，运用如下就可以
1) DBCC PINTABLE (@db_id, @tbl_id);
2) exec sp_tableoption ....

SQL Server 2000的注释：
DBCC PINTABLE 不会导致将表读入到内存中。当表中的页由普通的 Transact-SQL 语句读入到高速缓存中时，这些页将标记为内存驻留页。当 SQL Server 需要空间以读入新页时，不会清空内存驻留页。

请教：
1:怎样将表读入内存？DBCC PINTABLE 只能标记的作用，查看了一些文档说用select * from tb就可以，但经过测试发现该表并没有缓存，即便是查询一段记录也没有被缓存【SQL Server 2000 环境下测试的】。
2：SQL Server 2005 中取消了DBCC PINTABLE 功能，问在2005中怎样继续做到让表标记为缓存。
3：SQL Server 的缓存机制是怎样的？以前都是弄ORACLE 的，那里面缓存机制很清晰，SQL Server中找不到相关文档，请各位大侠指点，感激不尽！！！

[shuaixf]

大侠们：出来帮帮忙吧！

小弟先谢过了

[ashzs]

1、你说经过测试发现没有被缓存，你是怎么测试的？只要你执行了这个SQL，就会被缓存，除非你的服务器在疯狂的作换页操作。怀疑你的测试手段。

2、在SQL2005中你不用作任何事，你也不需要做任何事。因为SQL Server是只能在Windows平台上运行的，这两个产品都是微软的，所以它们磨合的非常好，依靠它们的算法基本够用。Oracle支持的平台众多，因此内存的设置也更细粒度。但是Oracle10g也倾向于动态调整内存，而不需要用户过多的介入SGA的设置了！
SQL2005对内存只是总量的设置，基本没有细粒度用户接口。其机制基本对内存是懒释放方式。只要没有内存限制首先会疯狂吃内存，当有其他操作系统进程向其发出内存竞争时，它才会将引用次数和使用次数较少的释放（当然有其算法）。

3、SQL Server主要有两个缓存对象，和Oracle类似：执行计划缓冲池（plan cache）和数据缓冲池（buffer pool）。你可以将Oracle的share pool理解成sql server的plan cache,而Oracle的buffer pool和SQL Server的buffer pool类似。和Oracle一样，你可以通过查询系统表得知缓存的内容、引用次数、命中次数、容量大小等信息。
其他例如大型池和PGA等机制应该也有，但是我没有深入研究过，可以看看其他人的意见。

[shuaixf]

请问ashzs：

3、SQL Server主要有两个缓存对象，和Oracle类似：执行计划缓冲池（plan cache）和数据缓冲池（buffer pool）。你可以将Oracle的share pool理解成sql server的plan cache,而Oracle的buffer pool和SQL Server的buffer pool类似。和Oracle一样，你可以通过查询系统表得知缓存的内容、引用次数、命中次数、容量大小等信息。

SQL Server有什么视图可以查询缓存的相关信息？

我用select * from tb 后执行DBCC MEMUSAGE(NAMES)发现缓存中的表的页数增大。
这一点足以证明你说的是对的，我开始说的是错误的

测试方式：有一个10万人的用户表
我用一程序【这跟我这边的业务相关】：

1：用户表与一明细表关联，并且用户ID为1到1000，当然我这个是用游标一个个读出来的
declare studCur cursor is
select * from user
where id between 0 and 1000

open studCur
.....
取出用户id =>@id
select count(*)
from mxb --明细表
where studId=@id

假设存在
【由于要用游标做相关业务，这里我就简略了，用查询替代】

2：将这1000条用户添加到某一张表tp中，
insert into tp values(@id.....)

3: 在表tp中删除@id的记录，然后在修改user表

就这样的一个测试过程，第一次测需要400s，第二次需要100s
但期间看缓存似乎没有改变

然后select * from user 或select * from user where id between 1000 and 2000
用同样的方法去测试用户名为1000至2000的用户
时间还是400s

这是为什么的？
为什么第二次时间就会大大缩减

说的似乎比较麻烦，真是不好意思。。。。。。
请指教！

[ashzs]

1、如何操作观察系统池：
除了你用的DBCC MEMUSAGE(NAMES)你还可以使用下面的命令来操作系统池：

--察看执行计划缓冲区
DBCC PROCCACHE

--清除执行计划缓冲区
DBCC FREEPROCCACHE

--从缓冲池中删除所有清除缓冲区。
DBCC DROPCLEANBUFFERS

系统视图就可以做到观察池内信息的目的。但是这些视图和Oracle的v$视图一样都是瞬时值。而且单独视图无法做到。我当时写了一些存储过程来专门作观察。你如果需要可以把你的邮箱通过消息发给我，我找到发给你。

至于hit count，SQL Server没有类似Oracle的statspack，你可以利用“计数器”来观察。例如：plan cache hitcount等等帮助文档多的是。通过计数器观察你可以了解各种池对象的命中率和换页情况，以便决定是否增加内存或作相应的优化。

2、你的测试情况第一次测需要400s，第二次需要100s。这正说明了缓冲池发挥了作用。因为你的SQL不需要再次解析了。查询的数据不需要物理I/O了，所以快了很多如果执行第一次后，执行：

--清除执行计划缓冲区
DBCC FREEPROCCACHE

--从缓冲池中删除所有清除缓冲区。
DBCC DROPCLEANBUFFERS

估计第二次也得400S。

还有你说的这个存储过程，我没看明白你想说明什么。：）
你是想说1-1000的操作和1001-2000用时一样还是说哪个更快了？

[ashzs]

你可以查询sysobjectcache，这里面存放的是plan cache的信息。你可以从里面知道下列信息：
1、现在在plan cache中有哪些SQL（长SQL可能会被截断）。
2、他被引用或使用了多少次。
3、执行计划占用了多大的内存空间。
5、SQL的类别。
6、所属数据库
...
对于你的疑问，这里面可能最有用的是SQL的类别和使用次数。如果你察看你的SQL类别是adhoc类别，那么可能会对你的这种现象有所解释。因为这种即时SQL，一般被重用的限制比较多，其和DBMS的版本和设置有很大的关系。你的SQL:
select * from user where id between 0 and 1000
select * from user where id between 1001 and 2000
可以说都是典型的Adhoc语句，但是以你的经历来看好像第一条被重用了，而第二条没有重用（时间没减少）。你可以首先执行两次between 0 and 1000看看sysobjectcache中这条SQL的使用次数。然后再执行between 1001 and 2000。

如果他们都是使用了两次，可能问题就不在plan cache,而是buffer pool。对buffer pool的观察方法，比较复杂，我原来的存储过程现在找不见了。我再找找。

还要考虑的一个因素是你机器内存和这两千条记录的大小（内存不够可能换页）。你可以将两千条纪录尽量减小（只保留主键ID最好），然后重新做这个实验。重新试验可以这样做：

1、只执行
select * from user where id between 0 and 1000
两次，观察时间和sysobjectcache。
2、只执行
select * from user where id between 1001 and 2000
两次，观察时间和sysobjectcache。

看看时间是否不同和sysobjectcache内容差异。

排除这两个纯语句问题后，在应用你的存储过程做实验。

[shuaixf]

谢谢ashzs你的真知灼见！！！

我所说的存储过程中测得1-1000的用户第一次400s 第二次100s 正如你所说的“这正说明了缓冲池发挥了作用”
在做1001-2000的时候，我先“select * from user where id between 1001 and 2000”，作用是想加载这些数据到缓存中
然后测试还是400s

“select 语句可以加载到缓存”从DBCC MEMUSAGE(NAMES)来看是这样的
但从测试上看又没有提升效率，这个就让我想不通了。。。。

我的邮箱地址是[e-mail]

按照你的提示再测试看看，^_^

[shuaixf]

如果执行相同的存储过程那么在plan cache 中就有了
"select ..."语句能使数据加载到缓存 buffer cache

那么1-1000 100s【第二次执行存储过程】
1001-2000 400s【第一次执行存储过程】
矛盾了啊？

其中是不是我有什么不对呢？

[shuaixf]

ashzs：你好！
我没有找到你所说的sysobjectcache，是不是syscacheobjets这个虚拟表呢？
按照你的说明，我测试了下
执行语句'select * from user where id between 0 and 1000'两次后usecounts=2
再执行'select * from user where id between 1001 and 2000'两次后usecounts=4
说明了解析的时候把上面两条语句当作一条语句来处理，plan cache也就没问题了。

按照你的说法很有可能是buffer cache 的问题，请帮忙找找看看你的存储过程，希望能让我借鉴下。谢谢！

[ashzs]

没错！是syscacheobjets。我打错了。一直爱这么叫 ：）

我整理一下，发给你。

[shuaixf]

找到了吗？大哥！
找到的话，请发到[e-mail]
谢谢！