SQL2005缓存计划小结

SQL2005缓存计划小结

by：怀疑一切，但不否定一切 

from: http://www.cnblogs.com/tom-fu/archive/2008/11/20/1337824.html     

  缓存计划可以被分成两类：编译的计划和执行上下文。前者是对所有用户共享的，后者是针对某个特定用户的，它包含了某个用户执行此计划时的具体参数等相关的信息。编译的计划有四种类型，可以通过下面的语句进行查看：

Code
SELECT * 
FROM sys.dm_os_memory_cache_counters 
WHERE name IN ('Object Plans', 'SQL Plans', 
'Bound Trees', 'Extended Stored Procedures');

 

这四类分别对应于Object Plans（存储过程、函数、触发器），SQL Plans（即席查询、自动参数化、SP_EXECUTESQL执行的语句），Bound Trees（生成计划时代数化阶段所生成的结构），Extended Stored Procedures（扩展存储过程）。

执行上下文被存储于称为SQL Manager Cache (SQLMGR)的地方。如果缓冲区到达上限时，SQLSERVER会先释放掉SQLMGR这部分的内存，因为它们的编译成本是0。而编译的计划成本会被减半，如果成本到达0后也会被释放。

SQL2005的过程缓存是动态分配的，它的最大大小由下面的公式计算得到：

这里所指的visible target memory是当使用DBCC MEMORYSTATUS命令时在Buffer Counts的结果集中的Visible行返回的大小。如下图所示：

如果是64位的操作系统Target和Visible两者是相等的。Target是系统的可见内存和SQLSERVER最大可用内存两者中较小的那个。如果是32位系统，当设置了/3GB启动参数此值最大为3G，否则最大为2G。

如果32位系统中使用了多于4G的内存时，假设你为SQLSERVER配置了AWE。则Target和Visible就不会再相等了。过程缓存是不能存放于AWE内存中的。

假设你在32位操作系统中运行 SQLSERVER2005SP2，系统中有16G内存。你为SQLSERVER配置了AWE，并且考虑到有SSRS、SSIS、FULLTEXT等SQL服务，你设置SQLSERVER引擎的最大可用内存为12G。此时使用DBCC MEMORYSTATUS命令应该看到Target的大小应该是1572864。因为Buffers列的值是代表缓冲区页面的数量，每页的大小为8K。因此12G的话应该是12*1024*1024/8=1572864个缓冲页面。而此时的Visible是不等于Target的，假设当前Visible的值是2G。则过程缓存的最大大小就是2*0.75=1.5G。

    如果是在64位系统中，则上面的计算公式为4*0.75+8*0.1=3.8G。我们看到SQL2005的SP2比SP1对过程缓冲区的大小做了更多的限制，这是为了防止因为有大量的缓存计划而导致与数据缓存争抢内存。

    那么一个批命令会不会有多个执行计划呢？只有在改变了执行时的环境时才会生成另一个执行计划。视图sys.dm_exec_plan_attributes包含了某计划的一些属性信息，下面我们试验一下改变执行环境后为同一批命令生成多个执行计划,执行下面的语句：我得到的结果如下：

Code
USE AdventureWorks; 
GO 
DECLARE @dbid int; 
SET @dbid=DB_ID(); 
DBCC FLUSHPROCINDB(@dbid); 
GO 
SET DATEFORMAT mdy; 
GO 
SELECT * FROM HumanResources.Employee WHERE EmployeeID=1 
GO 

SET DATEFORMAT ymd; 
GO 
SELECT * FROM HumanResources.Employee WHERE EmployeeID=1 
GO 
SELECT st.text, qs.sql_handle, qs.plan_handle 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
WHERE st.text LIKE '%Employee%'; 

GO 

 

从第三个结果集看到因为两个语句执行时，因为改了日期格式的设置，分别为其生成了一个执行计划。你可以看到两个计划都对应了相同的sql_handle，但是却有不同的plan_handle。

如果这时使用两个不同的plan_handle进行计划属性的查询，会得到如下的结果：

Code
--使用前面查询所得到的plan_handle
SELECT * FROM sys.dm_exec_plan_attributes(0x06000800DBFC6801B841C60F000000000000000000000000) 
WHERE is_cache_key=1 

SELECT * FROM sys.dm_exec_plan_attributes(0x06000800DBFC6801B8617805000000000000000000000000) 
WHERE is_cache_key=1 

 

上面图中红框表示的date_format的值分别为3和1。因此，如果你要使用不同的客户端进行数据库的连接时一定不要忘记指定它们使用相同的SET选项等等被缓存的信息，也不要在执行时改变这些被缓存的属性。从上面的结果你也可以看到sql_handle和plan_handle是1：N的关系。

     缓存中保存的是整个批命令的计划，它由每个单独的语句的计划组成。为了能高效的执行每条语句，在执行之前都会判断此语句所引用的对象架构有无变化、统计信息是否过期。如果符合任一条件，即便批已经开始执行，这条语句也会被重新编译。在SQL2000中会导致整个批中的语句都被重新编译，而2005因为有了语句级编译的功能可以减少对CPU和内存的占用。

    随着缓存数量的增加，最终会达到上述公式计算所得的最大边界值。这种内存压力可以被分成两类：本地(Local Memory Pressure)和全局内存压力（Global Memory Pressure）。

     当某个存储区域，比如SQL Plans存储区域达到了总可用内存的一定比例或是因为缓存计划的数量过多时，SQLSERVER就会对此区域的缓存进行相应的清理。如果你执行一条SQL语句时正好触发了清理过程，因为清理过程是在此线程中执行的，所以你就必须要等待清理结束后才能得到结果。

前面使用DBCC MEMORYSTATUS看到过Target的大小，还有一个Commited。它是当前SQLSERVER已经占用的内存。如果你发现Target和Commited的值相等，但是这时机器并没有任何的数据库操作。原因是因为SQLSERVER一旦占用了这些内存后，只有在外部程序运行时没有可用的物理内存时。SQLSERVER才会释放掉已占用但是当前并不使用的内存，这就是所谓的外部全局内存压力。如果由于对数据进行排序或是聚合等大量占用内存的操作导致可用物理内存用尽，并且虚拟内存也要被消耗完时，这时SQLSERVER也会选择释放掉过程缓冲区中的一些内存，这就是所谓的内部全局内存压力。

每个缓存计划的大小是和批处理中的字符数相关的，在sys.dm_exec_cached_plans中的size_in_bytes字段表示的是缓存占用的字节数。那么为了能让缓存计划少占用些内存，你应该尽量减少批命令中使用过多的参数。比如你执行像SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID IN(43697,43698,43699,43700,……)这样的语句。如果上面的语句封装在存储过程中，你把IN里的值当作一个字符串传进来，同样也会使该存储过程占用大量的缓存资源，但是你的注释内容是不被计算在内的。因此你可以随意添加对代码的注释。你可以自己试一下在不同的参数值时，当前缓存的大小是多少。

更多内容还在学习中，如有不正确的地方请更正。另外，有一个地方我也一直没有弄明白，原文地址：http://www.mydatabasesupport.com/forums/sqlserver-server/243005-compilations-vs-recompilations-sec.html 此文中说如果语句中包含了大于8K的常量时，不会缓存该语句。但是我通过下面的实验发现，不管指定多长的查询条件，照样会缓存此计划。(我在SQL Server2005 SP3 WinXP Professional的机器上试了，超过8192不能缓存此计划)

Code
CREATE TABLE [dbo].[Student]( 
    [No] [bigint] NOT NULL, 
    [Name] [nchar](10) NOT NULL, 
    [Address] [nchar](4000) NOT NULL, 
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
( 
    [No] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
INSERT INTO dbo.Student VALUES(1,'tom1',Replicate('A',4000)) 
INSERT INTO dbo.Student VALUES(2,'tom2',Replicate('B',4000)) 
INSERT INTO dbo.Student VALUES(3,'tom3',Replicate('C',4000)) 
INSERT INTO dbo.Student VALUES(4,'tom4',Replicate('D',4000)) 
INSERT INTO dbo.Student VALUES(5,'tom5',Replicate('E',4000)) 
GO 

DECLARE @dbid int; 
SET @dbid=DB_ID(); 
DBCC FLUSHPROCINDB(@dbid); 
GO 
SELECT Replicate('A',4200) 
GO 
SELECT * FROM dbo.Student WHERE [Address]=N'输入多于8192个A进行查询' 
GO 
SELECT cp.size_in_bytes,st.text, qs.sql_handle, qs.plan_handle 
FROM sys.dm_exec_query_stats qs 
    JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle=cp.plan_handle 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
WHERE st.text LIKE '%Student%'; 
GO