【学习散记8之--执行计划的缓存和重新使用 】

执行计划的缓存和重新使用

ps：MSDN摘

SQL Server 有一个用于存储执行计划和数据缓冲区的内存池。。内存池中用于存储执行计划的部分称为过程缓存。
执行计划包含:
 1.查询计划:执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用。这称为查询计划。
       这里我的理解就是这个直读数据结构其实就是一个语句的框架 或者说本体（比如 select * from kof where id=@id）。
 2.执行上下文：每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构。此数据结构称为执行上下文。
       这里我觉得应该是执行当前SQL中你专有的一些数据,可以放入查询计划中(比如 你的语句elect * from kof where id=3 3就是你的执行上下文)
       
执行SQL语句与执行计划中的联系：
  关系引擎将首先查看过程缓存中是否有用于同一 SQL 语句的现有执行计划。SQL Server 将重新使用找到的任何现有计划，从而节省重新编译 SQL 语句的开销。如果没有现有执行计划，SQL Server 将为查询生成新的执行计划。

执行计划的老化：
  什么叫老化？就是没用了,就会弃用它.那在SQL SERVER怎么样去判断老化的执行计划呢？
  每个查询计划和执行环境都有相关的成本因子，可表明编译结构所需的费用。这些数据结构还有一个年龄字段。对象每由连接引用一次，其年龄字段便按编译成本因子递增。
  比如：1)如果查询计划的成本因子为 8 并且被引用了两次，则其年龄变为 16。
     2)惰性写入器进程定期扫描过程缓存中的对象列表。然后，惰性写入器减少每个对象的年龄字段，每扫描一次减少 1。
     3)不断重复1）2）步骤,不断变化对象的年龄字段.
  再举个例子：我的一个SQL语句成本因子为6.我第一次引用后 他的年龄变成6.
        惰性写入器每2天扫描一次,6天后,它的年龄变成3(6-3).
        然后三天后我又连续调用2次 ,年龄又变成了15.说明它至少还30天可以不被调用.
   原因很简单 一旦对象的年龄字段为0 是惰性写入器进程将释放对象的条件之一,还有2个条件是：
           a.内存管理器需要内存而所有可用内存都正在使用。
           b.对象在当前没有被连接引用。
  因为每次引用对象时其年龄字段都会增加，所以经常被引用的对象的年龄字段不会减为 0，也不会从缓存老化掉。
  
重新编译执行计划：
  能够达到重新编译执行计划的因素有2种：一种是上面提到的执行计划的老化,另外一种就是数据库中的某些更改可能导致执行计划效率降低或无效.
  导致计划无效的情况包括：
   1.对查询所引用的表或视图进行更改（ALTER TABLE 和 ALTER VIEW）。
   2.对执行计划所使用的任何索引进行更改。
   3.对执行计划所使用的统计信息进行更新，该更新可能是从语句（如 UPDATE STATISTICS）中显示生成，也可能是自动生成的。
   4.删除执行计划所使用的索引。
   5.显式调用 sp_recompile。
   6.对键的大量更改（其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改）。
   7.对于带触发器的表，插入的或删除的表内的行数显著增长。
   8.使用 WITH RECOMPILE 选项执行存储过程。
  举个例子：删除执行计划所使用的索引 就会改变执行计划

   create table test(id int,value int)

   --建立索引

   create clustered index CL_SK_ID on test(id)

   insert test values(1,2)

   insert test values(2,3)

   select * from test where id=1

   --删除索引

   drop index  CL_SK_ID on test

    select * from test where id=1

   ---------------按CTRL+L----------------------

  PS:语句级编译的问题：
   在 SQL Server 2000 中，只要批处理中的语句导致重新编译，就会重新编译整个批处理，无论此批处理是通过存储过程、触发器、即席批查询，还是通过预定义的语句进行提交。
   在 SQL Server 2005 和更高版本中，只会重新编译批处理中导致重新编译的语句。
   语句级重新编译有助于提高性能，因为在大多数情况下，只有少数语句导致了重新编译并造成相关损失（指 CPU 时间和锁）。
   因此，避免了批处理中其他不必重新编译的语句的这些损失。
   
参数化的问题：
 create table test(id int,value int)
 go
 insert test values(1,2)
 insert test values(2,3)
 select * from test where id=1
 select * from test where id=2
 这个例子的2句select语句目的是要让 SQL Server 总是认为语句实际生成了相同的计划并重复使用这些计划，但是 SQL Server 有时不能在复杂的 SQL 语句中检测到上述情况。
 使用参数将常量与 SQL 语句分隔开有助于关系引擎识别重复计划。如使用sp_executesql:
 DECLARE @MyIntParm INT
 SET @MyIntParm = 1
 EXEC sp_executesql
   N'SELECT * FROM test  WHERE ID = @Parm',
   N'@Parm INT',
   @MyIntParm
  ps:ADO、OLE DB 和 ODBC 使用参数标记。(详见MSDN)
 如果不使用参数的话,SQL会提供2种参数机制：
 
简单参数化(自动参数化) ：
 如果执行不带参数的 SQL 语句，SQL Server 将在内部对该语句进行参数化以增加将其与现有执行计划相匹配的可能性。
 上述SQL语句中的值1,关系引擎将自动用指定参数代替1,并在此基础上为此批处理生成执行计划.
 正是这种机制使得 select * from test where id=1 select * from test where id=2 生成了相同的执行计划，并对第二个语句重用第一个计划
 ps：那是在在简单参数化的默认行为下，SQL Server 只对相对较少的一些查询进行参数化。
   如果你想在大量并发查询的数据库,指定对数据库中的所有查询进行参数化（但受到某些限制）,则使用强制参数化

 

强制参数化 ：
 1启用方式：在 ALTER DATABASE 语句中将 PARAMETERIZATION 选项设置为 FORCED 可启用强制参数化。

 2压制性：通过指定将数据库中的所有 SELECT、INSERT、UPDATE 和 DELETE 语句参数化，可以覆盖 SQL Server 的默认简单参数化行为

 3好处：强制参数化通过降低查询编译和重新编译的频率，可以提高某些数据库的性能。

 4适用对象：能够通过强制参数化受益的数据库通常是需要处理来自源（例如，销售点应用程序）的大量并发查询的数据库。

 5强制形式：SELECT、INSERT、UPDATE 或 DELETE 语句中出现的任何文本值（无论以什么形式提交）都将在查询编译期间转换为参数。

 6强制参数化的例外：太多 详见MSDN

 7强制参数化文本转化类型：
   a.其大小适合 int 数据类型的整数文本将参数化为 int。
     对于较大的整数文本，如果它是包含任意比较运算符谓词的组成部分，则将参数化为 numeric(38,0)。如果它不是包含比较运算符的谓词的组成部分，则此类文本将参数化为 numeric，其精度仅够表示其大小，并且没有小数位。
   b.浮点数值将参数化为 float(53)。
   c.如果非 Unicode 字符串文本在 8000 个字符以内，将参数化为 varchar(8000)，如果多于 8000 个字符，则参数化为 varchar(max)。
   d.如果 Unicode 字符串文本在 4000 个 Unicode 字符以内，将参数化为 nvarchar(4000)，如果多于 4000 个字符，则参数化为 nvarchar(max)。
   e.如果二进制文本在 8000 字节以内，将参数化为 varbinary(8000)。如果多于 8000 字节，则转换为 varbinary(max)。
   f.Money 类型的文本，将参数化为 money

 8强制参数化注意事项：
   a.强制参数化实际上是在对查询进行编译时将查询中的文本常量更改为参数。因此，查询优化器可能会选择不太理想的查询计划。
   b.TERIZATION 选项设置为 FORCED 将刷新数据库的计划缓存中的所有查询计划，当前正在编译、重新编译或执行的查询除外。
   c.当将 SQL Server 数据库兼容性设置为 80，或将早期实例上的数据库附加到 SQL Server 2005 或更高版本的实例时，将禁用强制参数化（设置为 SIMPLE）。
   d.在重新附加或还原数据库时，PARAMETERIZATION 选项的当前设置将保留。