优化 Ad

图片 8

执行计划生成后会存储在 plan cache
中,以便重用,如果计划缓存从来都没有被重用过,将会造成内存资源的浪费,这有可能是由于非参数化的
Ad-hoc引起的。

执行计划与参数化设置
 
当TSQL 语句发送到SQL Server引擎时,SQL
引擎需要对先其进行语法分析检查,然后生成执行计划,再按照执行计划运行并按照指定格式封装结果集返回,TSQL
的运行时间包括生成执行计划的时间和与运行执行计划的时间,SQL
Server引擎依据各种索引+约束+统计等数据库对象尝试找出一条够好(执行成本够低的)执行计划。
 
对应复杂的TSQL语句,涉及到众多的表和索引,需要评估多种执行方案,消耗大量CPU资源,并且增加整个语句的执行时间,因此SQL
SERVER 使用计划缓存区来缓存执行计划使生成的执行计划可重用。
 
SQLSERVER查询大致分成两类:Ad Hoc 和 Prepared
AdHoc查询指将查询参数直接放入SQL语句中,过滤条件没有明确参数化。
Prepared查询指将查询参数与查询语句独立开来,如使用sp_executesql或存储过程来执行。
 
简单参数化
如果执行不带参数的 SQL 语句,SQL Server
将在内部对该语句进行参数化以增加将其与现有执行计划相匹配的可能性。此过程称为简单参数化。
但在处理复杂的 SQL 语句时,关系引擎可能很难确定哪些表达式可以参数化。
数据库默认使用简单参数化。
如对于语句:

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

当执行代码时,会产生一个 hash值,用于匹配计划缓存中的 hash值,相同的
hash值代表语句是相同的。如果执行一个存储过程,会按照存储过程名来创建
hash值,如果在存储过程之外执行代码,那么
hash值会根据整个语句产生。你的代码有一点点字面上的改变,都会产生不同的
hash值,导致计划无法重用。当有大量 Ad-hoc 执行时,会导致计划缓存的膨胀。

–=====================================================
–清理计划缓存
DBCC FREEPROCCACHE

针对这类问题,可以考虑使用存储过程、函数或者参数化
Ad-hoc,但是有时候的确没有办法,必须使用非参数化的 Ad-hoc。从 SQL Server
2008 开始,引入了一个“针对即席工作负荷进行优化”的选项,如图 3-9所示。

GO

–Adhoc 查询

《Microsoft Sql server 2008 Internals》索引目录:

找到该选项的具体步骤是:右键实例,然后选择“属性”,再选择“高级”,之后把图
3-9箭头处的 False 改成 True。下面是针对该选项的官方解释:

SELECT * FROM dbo.TB3 WHERE object_id=4

–查看缓存
GO
select cp.usecounts as ‘使用次数’,cp.cacheobjtype as ‘缓存类型’,
cp.objtype as [对象类型],st.text as ‘TSQL’,qp.query_plan as
‘执行计划’,
cp.size_in_bytes as ‘执行计划占用空间(Byte)’          
from sys.dm_exec_cached_planscp
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
ORDER BY[对象类型]

 

图片 1

由上图可以发现,查询不仅生成了一个 Adhoc 类型的执行计划,生成一个
Prepared 类型的执行计划,而Prepared 类型的执行计划便是SQL SERVER
内部生成的。
再次执行查询:

–=====================================================
–Adhoc 查询
SELECT *FROM dbo.TB3 WHERE object_id=3

 图片 2

查询生成了一个 Adhoc 类型的执行计划,并重用了之前生成的 Prepared
类型的执行计划。
 
强制参数化
通过指定将数据库中的所有 SELECT、INSERT、UPDATE和 DELETE
语句参数化,可以覆盖 SQL Server 的默认简单参数化行为。
当数据库启动强制参数化后,DML语句中出现的任何文本值都将在查询编译期间转换成参数(部分情况下例外)。
强制参数化可以解决那些简单参数化选项下无法参数化的复杂语句。

–===========================================================
–将数据库设置为强制参数化
USE [master]
GO
ALTER DATABASE [DB0003] SET PARAMETERIZATION FORCEDWITH NO_WAIT
GO
optimizefor ad hoc workloads

“针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计划缓存的效率。如果该选项设置为
1,则数据库引擎将在首次编译批处理时在计划缓存中存储一个编译的小计划存根,而不是存储完全编译的计划,这样避免缓存那些不会再重复使用的执行计划,缓解内存压力。

–=====================================================
–启用optimize for ad hoc workloads
SELECT * FROM sys.configurations
WHERE name=’optimize for ad hocworkloads’
GO
SP_CONFIGURE ‘optimize for ad hoc workloads’,1
GO
RECONFIGURE

《Microsoft Sql server 2008
Internal》读书笔记–目录索引

“针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计划缓存的效率。如果该选项设置为
True,则数据库引擎将在首次编译批处理时在计划缓存中存储一个编译的小计划存根,而不是存储完全编译的计划。在这种情况下,不会让未重复使用的编译计划填充计划缓存,从而有助于缓解内存压力。

在运行以下语句:

–Adhoc 查询
SELECT *FROM dbo.TB3 WHERE
object_id=3图片 3

第一次执行后只会存储执行计划存根,只占用232 Byte的内存
再次执行一遍后才存储执行计划,使用24576 Byte的内存。

 图片 4
 
总结:
1>虽然可以使用“简单参数化”或“强制参数化”来优化 adhoc
查询,重用执行计划,但是仍会造成一定的性能损耗,对于重复执行的语句,还是应该将之参数化。
2>当大量只执行一次的adhoc 查询语句出现时,可以使用 optimize for ad
hoc workloads 来减少计划缓存使用的内存。
参考链接:

 
 
 

上文我们了解计划缓存内部操作的第一部分-缓存存储。今天我们继续关注已编译计划、执行上下文和计划缓存元数据相关的几个非常有用的系统函数和视图。

编译的计划存根使数据库引擎能够识别此临时批处理以前已经编译过,但只存储了编译计划存根,因此当再次调用此批处理时,数据库引擎会对此批处理进行编译,从计划缓存中删除编译计划存根并将完全编译的计划添加到计划缓存中。

已编译计划(Compiled Plans)

将“针对即席工作负荷进行优化”设置为 1
只会影响新计划,而已在计划缓存中的计划不受影响。

在Object和SQL计划缓存存储中有两种主要的计划类型:已编译的计划和执行计划。已编译计划是我们检查sys.dm_exec_cached_plans视图时看到的类型之一,前面我们已经提到过与编译计划有关的三个主要的objType值:Adhoc,Prepsred,Proc。已编译的计划可以被存储在对象存储或SQL存储中,这取决于他们的objType值。已编译计划被看作可量化的内存对象。他们re-create和成本太高了,因此,SQL
Server试图在缓存中保留它们。当SQL
Server面临较大的内存压力时,移除缓存对象的策略使我们的编译计划不是被移除的第一个对象。

编译计划存根是 sys.dm_exec_cached_plans 目录视图显示的 cacheobjtype
之一。它具有唯一的 SQL
句柄和计划句柄。编译计划存根没有与其关联的执行计划,并且查询计划句柄不会返回
XML 显示计划。

一个编译计划被认为是一个完全的批处理,而不仅仅是单个的语句。对于一个多语句的批处理,你可以把已编译计划看作一个计划数组,已编译的计划能被在多个会话与用户间共享。定义给某个已编译计划的特定执行的(被存储在另外一个结构中的)信息,被称为可执行计划。

执行上下文(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

相关参数可以参看:

主要功能:返回由指定的 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

详细说明:
这是一个标量函数,以sql_handle为参数,以XML
格式返回计划句柄指定的批查询的显示计划。计划句柄指定的计划可以处于缓存或正在执行状态。

■sys.dm_exec_text_query_Plan

详细说明:
Transact-SQL
批查询或批查询中的特定语句返回文本格式的显示计划。执行计划句柄指定的查询计划可处于缓存状态或正在执行状态。此表值函数与
sys.dm_exec_query_plan  类似,但存在以下差异:
1、查询计划的输出以文本格式返回。
2、查询计划的输出无大小限制。
3、可以指定批查询内的单个语句。
■sys.dm_exec_cached_plans

详细说明:

针对 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

结果:
图片 5
■sys.dm_exec_cached_plan_dependent_objects

 详细说明:

 针对每个 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;

图片 6

 ■sys.dm_exec_reauests

 详细说明:

 返回有关在 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 DESC

 图片 7

 ■sys.dm_exec_query_stats

详细说明:

返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。在从缓存删除计划时,也将从该视图中删除对应行。

示例,下列查询返回使用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;

图片 8

 注意sys.dm_exec_cached_plans和sys.dm_exec_query_stats的主要区别:(这个在MSDN也没有的啊)
1、前者为每个批处理仅有一行被编译、缓存。而后者为每条语句对应一行。
2、后者包含汇总信息汇集了某个特定语句的所有执行信息,返回为每个查询提供的数量巨大的性能信息,包括执行的次数和累积的I/O、CPU和持续时间。记住,这个视图仅仅在查询完成时被更新,因此,如果服务器当前处在一个大的工作负载下,你需要多试几次,以提取更加公正的信息。

本文主要介绍了已编译计划、执行上下文和计划缓存元数据和几个常用的系统函数,并介绍了几个葵花宝典级的调优语句。下文将继续关注缓存大小管理、缓存项的成本(Costing
of Cache entries)

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图