性能调优5:执行计划

查询优化器基于当前的统计信息和参数,衡量开销之后,选择“最优”的执行计划,需要注意的是,“最优”是相对的,优化器不可能穷举所有的执行计划来评估其开销,这个“最优”的标准是对当前参数和当前的统计信息来说的,优化器从生成的备选执行计划中选择开销最小的。由于执行计划的编译和生成是很耗费资源和时间的,因此,SQL Server会把生成的任一执行计划缓存起来,以便重用。

由于关系表的数据和结构可能发生改变,数据更新会导致统计信息过时,而之前的参数可能不具有代表性,使得已生成的执行计划不能代表其他参数值,导致查询性能低下。因此,应当监控执行计划的性能,当发现参数嗅探问题时,应该及时修改代码以重编译;当发现统计信息过期时,应及时更新统计信息等。

一,缓存机制

SQL Server使用特定的缓存机制,以重用第一次执行查询时生成的执行计划,总的来说,SQL Server内部有以下四种执行计划缓存机制:

  • Ad hoc 查询缓存
  • 参数化Ad Hoc查询缓存
  • sys.sp_executesql 执行的查询,是一种参数化的查询语句
  • 存储过程

对于Adhoc查询的缓存,是SQL Server自动进行的,用户不能干预,而后两种是用户可以干预的,用户可以通过优化代码来复用“模板化”的查询。所谓模板化语句,是指除了个别的常量发生变化之外,语句主体不变,可以把变化的常量作为一个参数,不变的语句主体作为一个模板来处理,SQL Server优化器把这个模板编译成执行计划,传入不同的参数会使用相同的执行计划。

1,Ad Hoc查询缓存

对于任意一个Ad Hoc查询,SQL Server都会缓存它的执行计划,但是,只有当批处理语句的文本完全匹配时,才会复用已缓存的执行计划,完全匹配的处理过程是:

  • SQL Server根据批处理语句的文本计算出一个Hash值,对后续的Ad Hoc查询的文本同样计算Hash值,当两个Hash值相同时,说明两个批处理的文本完全相同,相当于同一个查询的重复执行,SQL Server优化器会复用已缓存的执行计划。
  • 如果Ad Hoc查询的文本有任意一个字符发生变化(比如,大写字符变小写字符,不同的换行,多了一个空格等),都会导致计算出的Hash值不同,进而不能复用执行计划。也就是说,Ad Hoc查询的文本必须完全匹配才能复用执行计划。

大量的Ad Hoc查询缓存会占用计划缓存的空间,这些缓存可能只会被使用一次,以后再也不会被使用。如果数据库系统中存在大量的一次性查询语句,应设置Server 级别的性能优化选项:Optimize for Ad hoc Wrokloads。

“针对即席工作负载进行优化”是一个Server级别的性能优化选项,用于提高包含许多临时批处理的工作负载的计划缓存的效率,如果把该选项设置为True,则数据库引擎在首次编译批处理时只保留计划缓存中的一个存根,而不是存储整个执行计划。当再次调用该批处理时,数据库引擎识别出该批处理在之前被执行过,进而从计划缓存中删除该执行计划的存根,并把完全编译的执行计划添加到计划缓存中。当非参数化的Ad-Hoc查询较多时,可以避免计划缓存存储过多的不会被复用的执行计划。

2,参数化Ad-Hoc

SQL Server 自主决定是否把查询中的常量作为参数来对待,除了常量不同之外,其他语句主体都相同,这就是这个查询语句的模板,不同的参数使用相同的执行计划。

例如,对于以下两个查询语句,除了常量1和2不同之外,其他语句都相同,

select ID, Name from dbo.Users where ID=1
select ID, Name from dbo.Users where ID=2

SQL Server对该语句做参数化处理,得到模板,只要语句符合该模板,优化器就复用已缓存的执行计划。

select ID, Name from dbo.Users where ID=@id

3,Prepared 查询缓存

用户使用sys.sp_executesql 控制参数和模板,只要模板相同,而参数不同,都可以复用已缓存的执行计划。

4,存储过程

用户创建的存储过程,在第一次执行时,编译和生成执行计划,并缓存到计划缓存中,当下次调用相同的存储过程,即使使传递的参数不同,SQL Server都会复用执行计划。

二,参数嗅探

参数嗅探是指在创建存储过程,或者参数化查询的执行计划时,根据传入的参数进行预估并生成执行计划。SQL Server生成的执行计划对当前参数来说是最优的,而对其他大多数参数来说,是非常低效的。有些时候,针对一个查询的第一次传参,已经产生了一个执行计划,当后续传参时,由于存在对应参数的数据分布等问题,导致原有的执行计划无法高效地响应查询请求,这就出现参数嗅探问题。

参数嗅探的本质是优化器根据参数来生成的执行计划不是最优的,导致优化器在复用执行计划时,语句的查询性能变得十分低下。对于参数嗅探问题,必须重新生成执行计划,可以使用语句重编译,编译提示(optimize for)等功能来避免。

三,影响执行计划复用的因素

SQL Server不会永久保存计划的缓存,并且存在缓存中的执行计划也不会永久不变,每个计划都会有一个Age值,当SQL Server探测到内存压力时,会触发Lazy Writer进程,用于清空所有的脏页,释放数据缓存。当扫面到计划缓存时,会降低Age值,当复用一次计划时,会增加Age值。当系统遇到内存压力,或Age值降到0时,执行计划会被移除内存。

除了这两个条件之外,当遇到下面的条件时,执行计划一会被移除内存,被重新编译:

  • 查询引用的基础表的结构被更改
  • 查询引用的索引被更改或被删除
  • 查询引用的统计信息被更新
  • 执行计划被强制重新编译(详见本问第四小节)
  • 单一查询中混合了DDL和DML操作,也称为延迟编译
  • 在查询中修改set选项
  • 查询所用到的临时表的结构被修改
  • 等等

在执行计划执行过程中,执行计划被重新编译,是优化器根据表结构,索引结构和统计信息做出优化的结构,目的是为了避免继续使用不合适的执行计划。

四,强制重新编译执行计划

修改存储过程,触发器等模块(Module)能够使其执行计划重新编译,除此之外,还有其他方法,能够强制重新编译执行计划

1,标记,下次重新编译

使用该存储过程,标记一个执行模块(SP,Trigger,User-Defined Function)在下次执行时,重新编译执行计划

sys.sp_recompile [ @objname = ] 'object'

2,不复用执行计划

在创建存储过程时,使用WITH RECOMPILE 选项,在每次执行SP时,都重新编译,使用新的执行计划。

CREATE PROCEDURE dbo.usp_procname 
    @Parameter_Name varchar(30) = 'Parameter_default_value'
WITH RECOMPILE

3,执行时重新编译

在执行存储过程时,重新编译存储过程的执行计划

exec dbo.usp_procname @Parameter_name='Parameter_value' 
WITH RECOMPILE

4,语句级别的重新编译

在SP中,使用查询选项 option(recompile),只重新编译该语句级别的执行计划

select column_name_list
from dbo.tablename
option(recompile)

SQL Server在执行查询之后,查询提示(RECOMPILE)指示存储引擎将计划缓存抛弃,在下次执行存储过程时,强制查询优化器重新编译,生成新的执行计划。在重新编译时,SQL Server 优化器使用当前的变量值生成新的计划缓存。

五,控制执行计划

优化器会根据查询选择执行计划,选择索引,表关联算法等,但是,当发现优化器选择了低效的执行计划时,可以使用hint来控制执行计划,SQL Server提供了三种类型的hint:

  • 查询提示(query hint):告知优化器在整个查询过程中都应用某个提示,
  • 关联提示(join hint):告知优化器在关联时使用特定的关联算法
  • 表提示(table hint):告知优化器使用表扫描,还是表上特定的索引

1,查询提示

使用option来设置查询提示,

  • 用于group by 聚合,可以控制分组的算法:hash group 和order group
  • 用于控制关联的算法, option(hash join)
  • 通常情况下,优化器决定表关联的顺序,可以使用force order选项,使优化器按照join的顺序来关联, option(force order)
  • 使用maxdop来确定语句执行的最大并发度,option(maxdop 1),取消并发执行。
  • 按照指定的参数来优化 option(optimize for (@para_name= constant_value))

2,关联提示

在 join关键字前面使用Loop,Merge和Hash来控制关联的算法

3,表提示

在引用的表名后面,通过with()来设置表提示 table_name with(hints),

当使用索引时,使用 with(index(index_name))来设置,

 

参考文档:

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2019-01-10 08:29  悦光阴  阅读(1114)  评论(0编辑  收藏  举报