《Microsoft Sql server 2008 Internals》读书笔记--第九章Plan Caching and Recompilation(7)

mofdan

　　《Microsoft Sql server 2008 Internals》读书笔记订阅地址：
　　http://www.iyunv.com/downmoon/category/230397.html/rss
　　《Microsoft Sql server 2008 Internals》索引目录：
　　《Microsoft Sql server 2008 Internal》读书笔记--目录索引
　　上文我们了解计划缓存内部操作的第一部分－缓存存储。今天我们继续关注已编译计划、执行上下文和计划缓存元数据相关的几个非常有用的系统函数和视图。
　　■已编译计划（Compiled Plans)
　　在Object和SQL计划缓存存储中有两种主要的计划类型：已编译的计划和执行计划。已编译计划是我们检查sys.dm_exec_cached_plans视图时看到的类型之一，前面我们已经提到过与编译计划有关的三个主要的objType值:Adhoc,Prepsred,Proc。已编译的计划可以被存储在对象存储或SQL存储中，这取决于他们的objType值。已编译计划被看作可量化的内存对象。他们re-create和成本太高了，因此，SQL Server试图在缓存中保留它们。当SQL Server面临较大的内存压力时，移除缓存对象的策略使我们的编译计划不是被移除的第一个对象。
　　一个编译计划被认为是一个完全的批处理，而不仅仅是单个的语句。对于一个多语句的批处理，你可以把已编译计划看作一个计划数组，已编译的计划能被在多个会话与用户间共享。定义给某个已编译计划的特定执行的（被存储在另外一个结构中的）信息，被称为可执行计划。
　　■执行上下文(Execution Contexts)

　　可执行计划或执行上下文，被看作附属于已编译计划，而不显示在sys.dm_exec_cached_plans视图中。可执行计划是在一个已编译计划执行时被运行时创建。可执行计划可能是存储在对象存储中的对象计划，也可能是存储在SQL存储中的SQL计划。每个可执行计划针对一个已编译计划的一次执行包含特定的运行时信息，包括实际的运行时参数、任何局部变量信息、在运行时为对象创建的Object IDs,User ID,批处理中关于当前执行语句的信息等。
　　当SQL Server开始执行一个已编译计划时，它从已编译计划中生成一个可执行计划。每一个编译计划中的独立语句，都得到自己的可执行计划，你可以看作是一个运行时查询计划。与已编译计划不同的是，可执行计划只能针对单个的会话。例如，如果100个用户模拟执行相同的批处理，将会有100个针对相同已编译计划的可执行计划。可执行计划能被从相关的已编译计划再生成。比起Create相对成本要低一些。稍后我们会关注与此相关的sys.dm_exec_cached_plan_dependent_objects视图。
　　■计划缓存元数据(Plan Cache Metadata)
　　
　　■句柄(handle)
　　sys.dm_exec_cached_plans视图为每个已编译计划包含一个值plan_handle。Plan_Handle是SQL Server从完整的编译计划中提取出的一个哈希值，它对当前的每一个已存在的编译计划是惟一的，可以被多次重用，可以被看作已编译计划的标识。如果批处理中的某个独立的语句被重编译，但Plan_Handle仍然保留，原因就是我们前面讨论过的基于更正的优化策略。
　　批处理中的实际SQL 文本或对象被存储在另外一个缓存（SQL Manager,简称SQLMGR)中。与批处理相关的T-SQL文本，包括所有注释，被存储在它的项(entries)。缓存在SQLMGR的T-SQL文本可以通过sql_Hanlde从数据值中提取出来。SQL_Handle包含一个完整批处理文本的哈希值，因为它对某个批处理是惟一的，因而，SQL_Handle可以看作SQLMGR批处理文本的一个标识。
　　任何定义的T－SQL批处理，有相同的SQL_Handle值，但未必有相同的Plan_Handle。如果缓存键是的任何值改变，我们在计划缓存中得到一个新的plan_Handle。
　　我们可以在sys.dm_exec_cached_plans视图中，很容易得到sql_Handle的值，从sys.dm_exec_cached_plan_atrributes函数获取一个特定的plan_Handle，如下语句：


SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
   FROM sys.dm_exec_cached_plans
     OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
   WHERE cacheobjtype = 'Compiled Plan'
   ) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
  IN ("set_options", "object_id", "sql_handle")) AS pvt;　　sys.dm_exec_query_stats视图包含plan_Handle和sql_Handle，由于sql_Handle的值是隐含的，有时，难以决定我们的查询计划究竟跟哪个sql_Handle相关。此时，还需要借助于其他函数。
　　下面我们看几个函数：
　　■sys.dm_exec_sql_text
　　相关参数可以参看：http://technet.microsoft.com/zh-cn/library/ms181929.aspx
　　主要功能：返回由指定的 sql_handle 标识的 SQL 批处理的文本。
　　示例：


USE Northwind2;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country  'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country  'USA';
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;
GO　　结果类似下表：

Text	sql_handle	plan_handle
-- this is an example of the relationship between  -- sql_handle and plan_handle  SELECT LastName, FirstName, Country  FROM Employees  WHERE Country  'USA';	0x02000000F42D6709ED82BBD1 AA82185699D108D4A5895AA9	0x06001A00F42D670918891B05 000000000000000000000000
-- this is an example of the relationship between  -- sql_handle and plan_handle  SELECT LastName, FirstName, Country  FROM Employees  WHERE Country  'USA';	0x02000000F42D6709ED82BBD1 AA82185699D108D4A5895AA9	0x06001A00F42D670938841B05 000000000000000000000000

　　注意：由于SET选项发生变化，而导致完全相同的批处理最终有两个计划。因此，在作查询优化时，请务必关注选项不同带来的计划差异。
■sys.dm_exec_query_Plan
　　详细说明：http://msdn.microsoft.com/zh-cn/library/ms189747.aspx
这是一个标量函数，以sql_handle为参数，以XML 格式返回计划句柄指定的批查询的显示计划。计划句柄指定的计划可以处于缓存或正在执行状态。
　　■sys.dm_exec_text_query_Plan
　　详细说明：http://technet.microsoft.com/zh-cn/library/bb326654.aspx
Transact-SQL 批查询或批查询中的特定语句返回文本格式的显示计划。执行计划句柄指定的查询计划可处于缓存状态或正在执行状态。此表值函数与 sys.dm_exec_query_plan  类似，但存在以下差异：
1、查询计划的输出以文本格式返回。
2、查询计划的输出无大小限制。
3、可以指定批查询内的单个语句。
■sys.dm_exec_cached_plans
　　详细说明：http://technet.microsoft.com/zh-cn/library/ms187404.aspx
　　针对 SQL Server 为了加快查询执行而缓存的每个查询计划返回一行。可以用此动态管理视图来查找缓存的查询计划、缓存的查询文本、缓存计划占用的内存量，以及重新使用缓存计划的计数。
　　示例，下列查询返回使用频度最高的语句：(这是不是优化的一个小技巧呢？呵呵。)


SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
    cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC　　结果：
http://rvaufg.blu.livefilestore.com/y1pQepYZtbOzOHiG8UYkDgVpOXNSth_cd8WXji7aeByAJCxhl7tJNQwFaqSaYlOqjDooyoTSiODRgNYMXt8SngAKGCV0WjNFpen/2010-07-10%2017-42-04.png?psid=1
■sys.dm_exec_cached_plan_dependent_objects
　　详细说明：http://technet.microsoft.com/zh-cn/library/ms403826.aspx
　　针对每个 Transact-SQL 执行计划、公共语言运行时 (CLR) 执行计划和与计划关联的游标返回一行。
　　示例：


SELECT text, plan_handle, d.usecounts, d.cacheobjtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY
  sys.dm_exec_cached_plan_dependent_objects(plan_handle) d;http://rvaufg.blu.livefilestore.com/y1pCRtZr9EHccCRSMG2NWsjcyTFDzPyxyH8aLBhPvOT2kotkzMshlJagD6zB0TwQU31GQ3Unel9CY3J-1dXLQOeobcAjQvnoA9q/2010-07-10%2017-48-20.png?psid=1
　　■sys.dm_exec_reauests
　　详细说明：http://technet.microsoft.com/zh-cn/library/ms177648.aspx
　　返回有关在 SQL Server 中执行的每个请求的信息。
示例,下列查询返回当前正在执行的前10个最耗时的语句：（邀月提示，这又是一个调优的好技巧，不是吗？呵呵）


SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
  ((CASE statement_end_offset
     WHEN -1
       THEN DATALENGTH(text)
     ELSE statement_end_offset
  END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESChttp://rvaufg.blu.livefilestore.com/y1pkbqyI7rO9R7ehhBXZ3aHq9onP2K4V4_IkR0kJSP0YcR9x4xuOxn8Of1vAG5sAR6R046VB3ivj4a1TgHmyMegBjf7917u68OE/2010-07-10%2017-57-43.png?psid=1
　　■sys.dm_exec_query_stats
　　详细说明：http://technet.microsoft.com/zh-cn/library/ms189741.aspx
　　返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。
　　示例,下列查询返回使用CPU最长时间的前10个语句：（邀月提示，这是第三个调优的好技巧。呵呵）



SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
  ((CASE statement_end_offset
     WHEN -1
       THEN DATALENGTH(text)
     ELSE statement_end_offset
  END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;http://rvaufg.blu.livefilestore.com/y1p9wafJfdYZfHRSRcObxKPBLgCL8bGPr78DRKg1flm0Vmmkox5zIBdM8ODloEpu1H3D-pB4l67J4M2oH44P1Wznfu3dQCNtV5G/2010-07-10%2018-05-33.png?psid=1
　　注意sys.dm_exec_cached_plans和sys.dm_exec_query_stats的主要区别：(这个在MSDN也没有的啊）
1、前者为每个批处理仅有一行被编译、缓存。而后者为每条语句对应一行。
2、后者包含汇总信息汇集了某个特定语句的所有执行信息，返回为每个查询提供的数量巨大的性能信息，包括执行的次数和累积的I/O、CPU和持续时间。记住，这个视图仅仅在查询完成时被更新，因此，如果服务器当前处在一个大的工作负载下，你需要多试几次，以提取更加公正的信息。
　　本文主要介绍了已编译计划、执行上下文和计划缓存元数据和几个常用的系统函数，并介绍了几个葵花宝典级的调优语句。下文将继续关注缓存大小管理、缓存项的成本(Costing of Cache entries)