执行计划–参数化设置

澳门新葡亰手机版 2

执行计划生成后会存储在 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 语句时,关系引擎可能很难确定哪些表达式可以参数化。
数据库默认使用简单参数化。
如对于语句:

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

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

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

GO

–Adhoc 查询

找到该选项的具体步骤是:右键实例,然后选择“属性”,再选择“高级”,之后把图
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