肯请释疑：syscacheobjects 和执行计划的问题

Question

-- 执行下面语句
select cacheobjtype,objtype,sql,count(1) as counts from master..syscacheobjects 
where dbid= db_id('book')
group by cacheobjtype,sql,objtype
having count(1) >= 3
order by count(1) desc--- 得到结果
cacheobjtype objtype sql counts
---------------------------------------------------------------
Executable Plan Proc pt_BookContentDetail_new 46
Executable Plan Proc pt_saveBookMark 5
Executable Plan Proc p_chat_ismanager 3
Executable Plan Proc p_bookList_ByPages_v2 3
Executable Plan Proc P_SplitPageOneSql_v2 3
Executable Plan Proc p_chat_list 3
Executable Plan Proc ptitlesDetail 3
---------------------------------------------------------------我个人认为是 syscacheobjects 缓存了执行计划和存储过程，那上面的数据代表了什么意思？
特别是 pt_BookContentDetail_new 这个存储过程，是调用最频繁的存储过程，syscacheobjects竟然存了46次，这代表了什么？
是不是说明缓存没有得到很好的利用，还是执行计划没有缓存成功，如果 counts = 1 是不是才说明很好的用到了sqlserver的缓存功能？
敬请高人指教！

Answer 1

帖出select * from master..syscacheobjects where sql='pt_BookContentDetail_new'
的结果来看下.
影响执行计计划是否可以重用的原因比较多，比如说SET的设定等。

Answer 2

和这个问题类似:
http://www.sqlservercentral.com/Forums/Topic527079-149-1.aspx重用参数和执行计划
http://msdn.microsoft.com/zh-cn/library/ms175580.aspx

Answer 3

联机丛书上说,SET选项也会影响的,
sys.syscacheobjects (Transact-SQL)
http://technet.microsoft.com/zh-cn/library/ms187815.aspx

Answer 4

看看setopts这个列，影响编译计划的 SET 选项设置

Answer 5

LZ考虑的对: "如果 counts = 1, 才说明很好的用到了sqlserver的缓存功能".其实你的查询不是太好. 对于syscacheobjects中的PROC, 你应该看"Executable Plan"和"usecounts". 如果一个存储过程的执行计划很好的被cached了, 那它的Executable Plan里的refcounts = 1, 并且usecounts 会很大 (表示用得很频繁, cache了执行计划). 反之, usecounts会很小, 并且出现你那样的查询结果. --- 你的查询用来找执行计划cache得不好的proc到是很有用的.

Answer 6

refcounts = 1 ，sql 唯一 这样的记录实在不多，甚至有些存储过程只有一条insert语句，对参数不做任何处理，竟然也会有超过10个记录，越来越迷糊了。

Answer 7

你看一下count(1)>3的那些记录，每一笔之间的差别是什么?
select * from master..syscacheobjects where sql='pt_BookContentDetail_new' 
出来的这４６笔中，它们之间的差异在哪里？不可能是完全一样的

Answer 8

-- 执行下列语句
select bucketid,cacheobjtype,objtype,objid,refcounts,usecounts,sql from master..syscacheobjects 
where sql = 'pt_BookContentDetail_new'
order by refcounts desc, usecounts desc-- 结果如下
bucketid,cacheobjtype,objtype,  objid,     refcounts, usecounts, sql 
-----------------------------------------------------------------------------------------
31371 Compiled Plan Proc 295672101 59 13673 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 326045 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 323104 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 309903 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 231998 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 212652 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 204614 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 202863 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 201981 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 190714 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 183481 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 168330 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 166422 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 162427 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 150456 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 148341 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 146562 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 146292 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 136620 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 134155 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 129698 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 127652 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 125267 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 114319 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 98800 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 97573 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 61314 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 55785 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 53243 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 52106 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 51710 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 51428 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 43772 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 43322 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 42124 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 38076 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 32273 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 30623 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 26861 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 26554 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 18116 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 15957 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 14215 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 11886 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 9845 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 7950 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 1774 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 1716 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 964 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 892 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 870 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 418 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 7 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 7 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 5 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 5 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 4 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 4 pt_BookContentDetail_new
31371 Executable Plan Proc 295672101 1 3 pt_BookContentDetail_new（所影响的行数为 59 行）

Answer 9

你的是2005吗？
pt_BookContentDetail_new里是不是有语句指定了WITH RECOMPILE?

Answer 10

结合这里
http://www.microsoft.com/china/technet/prodtechnol/sql/2005/recomp.mspx#EYC
查询计划和执行上下文
当一个可缓存的批处理被提交给 SQL Server 2005 进行执行时，该批处理会被编译，而它的一个查询计划会被放到计划缓存中。查询计划是一种只读的可重入结构（由多个用户共享）。任何时候，查询计划在计划缓存中最多只能有两个实例：一个用于所有的串行执行，另一个用于所有的并行执行。并行执行的副本适用于所有的并行级别。（严格说来，如果相同的用户使用带有相同会话选项的两个不同会话设置的两个相同的查询同时达到 SQL Server 2005，在执行时将存在两个查询计划。但是，当执行结束时，仅有一个查询计划会保留在计划缓存中。）执行上下文是从查询计划中派生的。执行上下文是为生成查询结果而“执行”的。执行上下文也被缓存和重用。当前执行批处理的每位用户将拥有一个执行上下文，其中保存了特定于其执行的数据（比如：参数值）。虽然被重用，但是执行上下文并不是可重入的（例如，它们是单线程的）。也就是说，在任何时候，一个执行上下文只能执行一个由会话提交的批处理，而在执行时，相应的上下文不会提供给任何其他会话或用户。查询计划与从中派生的执行上下文之间的关系如下图所示。其中，有一个查询计划，从中派生了三个执行上下文。这些执行上下文包含参数值和特定于用户的信息。对于参数值和特定于用户的信息而言，查询计划都不是明确的。在计划缓存中，一个查询计划和多个相关联的执行上下文可以共存。然而，单个执行上下文（如果没有相关联的查询计划）无法存在于计划缓存中。只要从计划缓存中删除了查询计划，所有相关联的执行上下文也将随之被删除。当搜索计划缓存以寻找计划重用的机会时，将比较各个查询计划，而不是各个执行上下文。一旦找到了可重用的查询计划，就能找到（导致执行上下文重用）或新生成可用的执行上下文。所以，查询计划重用不一定会导致执行上下文重用。执行上下文是在“匆忙中 (on the fly)”派生的，其间一个主干执行上下文会在批处理执行开始之前生成。随着执行的进行，将生成必要的执行上下文片断并放入该主干中。这意味着，即便从中删除了特定于用户的信息和查询参数，两个执行上下文也不必完全相同。由于派生自相同查询计划的执行上下文的结构可以彼此不同，因此用于特定执行的执行上下文对性能有轻微的影响。随着计划缓存变“热”并达到稳定状态，这种性能差异的影响会越来越小。例如：假设批处理 B 包含一个“if”语句。当 B 开始执行时，就会为其生成一个执行上下文。假设在首次执行时，提取了“if”的“true”分支。此外，假设在首次执行时，B 再次由另一个连接提交。因为当时唯一存在的执行上下文正被使用，所以将生成第二个执行上下文，并提供给第二个连接。假设第二个执行上下文提取了“if”的“false”分支。当这两个执行都完成之后，将有第三个连接提交 B。假设 B 的第三个执行选择了“true”分支，如果 SQL Server 为该连接选择了 B 的第一个执行上下文而非第二个执行上下文，那么完成该执行的速度将稍快一些。 可重用批处理 S 的执行上下文，即使 S 的调用顺序有所不同。例如，调用顺序可以是“存储过程 1 --> 存储过程 2 --> S”，而第二个调用顺序可以是“存储过程 3 --> S”。可对 S 的第二次执行重用其第一次执行的执行上下文。如果批处理执行生成了严重级别高达 11 或更高的错误，那么其执行上下文会被破坏。如果批处理执行生成了一个警告（严重级别为 10），那么执行上下文就不会被破坏。因此，即便没有内存方面的压力——会导致计划缓存缩小，计划缓存中所缓存的（给定查询计划的）执行上下文的数量也会起伏不定。不缓存并行计划的执行上下文。SQL Server 编译并行查询计划的一个必备条件是：满足了处理器关联掩码和“最高程度的并行”服务器级选项的值（可能是用“sp_configure”存储过程设置）后所剩下的处理器的最低数量大于 1。即使编译了并行查询计划，SQL Server 的“查询执行”组件也可能会从中生成一个串行执行上下文。不缓存派生自并行计划的任何执行上下文——串行或并行。但是，会缓存并行查询计划。--------------------------
是不是 Compiled Plan 指的就是 执行计划 ，Executable Plan  指的就是 执行上下文
请和尚和其他知道的朋友明示，谢谢！

Answer 11

我的是sql2000，没有指定 with recompile 

Answer 12

晕.楼主你的SP是有重用的啦.usecounts都已经几十万了.造成出现这么多笔的原因，应该是setopts字段引起的啦，你查下看看.可能是不同的连线之间属性不一样，或是数据库有上下文切换造成的。

Answer 13

setopts 都是 0 ，上下文切换是sql2005里面的吧，我是sql2000，越来越迷糊了