12理解执行计划与缓存

1、执行计划王辉内容执行计划执行计划缓存编译与重编译和执行有关的DMV为什么需要执行计划查询执行阶段什么是执行计划?常见的执行计划元素实际的 vs. 评估的执行计划案例：分析执行计划执行计划常见问题为什么我的查询要花这么长时间执行?为什么这个查询执行的时间要长于类似查询执行的时间?为什么SQL Server忽略了我刚创建的很适合的索引?执行计划可以帮助你验证查询是否按照预期执行，分析执行情况为什么需要执行计划SQL Server 关系数据库服务器存储引擎 (访问方法, 数据库页缓存, 锁定, 事务, )SQL-OS (任务计划, 缓存池, 内存管理, 同步单元, )查询优化(生成执行计划, 选择索

2、引, 联接次序, 统计信息, 开销)查询执行(查询运算符, 内存授予, 并行处理)语言处理(分析/绑定/视图解析)工具 (DBCC, Backup / Restore, BCP, )Metadata, Type system, Expression Services语句/批执行计划缓存管理SQL Server 关系数据库服务器查询优化器查询生命周期概述查找计划缓存重用或生成执行计划内存授予, 并行处理执行找到执行计划找到已编译的计划未找到自动参数化绑定， 视图解析分析查询优化将计划放入缓存新语句查询优化(生成执行计划, 选择索引, 联接次序, 统计信息, 开销)查询执行(查询运算符, 内存授予

3、, 并行处理,显示执行计划)语言处理(分析/绑定,语句/批执行,计划缓存管理)Query Optimization deep dive in earlier Session查询优化SQL Server 查询优化器是基于开销的优化器而不是基于规则的 例如: Oracle 7 之前版本和部分并行数据仓库输入: 已做如下操作的查询树:对SQL语句进行语法解析语义分析及检查 (也称为Algebrizing 包含类型推导，类型检查，绑定变量，表名及字段名等）输出: 一个（希望是）好的查询计划SQL Server的执行计划中包含：操作的类型操作的顺序索引的选择基于可用统计值评估的行计数SQL Server

4、 执行基于开销的优化成本是指派给每一个元素的什么是执行计划?一个执行计划会显示查询时如何执行的或者它将如何被执行显示查询计划3种形式: 文本, XML , 图形化按内容分: 仅运算符, 包含开销和基数估计，包含开销和基数估计和运行时信息SQL Trace/Profiler 在 Performance 事件类中有 8 个 Showplan 事件文本XML图形化运算符SET SHOWPLAN_TEXT ONN/AN/A包含开销和基数估计SET SHOWPLAN_ALL ONSETSHOWPLAN_XML ON单击查询编辑器工具栏上的“显示估计的执行计划”包含运行时信息SET STATISTICS

5、PROFILE ONSET STATISTICS XML ON 单击查询编辑器工具栏上的“包括实际的执行计划”数据访问操作数据组织堆(Heap)B树访问数据扫描（Scan）查找（Seek）表和聚集索引扫描和查找嵌套循环和查找合并和哈希连接聚合筛选和排序数据修改常见的执行计划元素表扫描读取整个堆聚集索引扫描读取表的整个聚集索引聚集索引查找从表中聚集索引的的特定位置读取表和聚集索引查找和扫描嵌套循环常用于内连接操作对数据路径中顶部每一行，在数据路径的底部执行一次查找RID 查找使用行编号查找堆键值查找查找一个聚集索引嵌套循环和查找合并连接常用于内连接需要两个输入使用相同的排序顺序哈希匹配效率较低的

6、连接，通过对一个输入的每一行计算哈希值， 然后在内存中创建哈希表保存哈希值其他输入则对哈希表进行查找合并和哈希连接流聚合高效数据已经排好序用于处理聚合哈希匹配聚合通过哈希表来对不必须排序的数据进行聚合聚合过滤低成本操作当用 WHERE 或者用 HAVING 谓词时用到仅传输满足过滤条件的行排序当必须要排序操作的时候经常使用Order By可以被用于其他操作，例如合并连接操作的排序输入或者执行 DISTINCT 操作开销非常大过滤和排序INSERT用于 INSERT 操作UPDATE用于 UPDATE 操作DELETE用于 DELETE 操作T-SQL MERGE 语句可以合并插入、删除、更新。

7、数据修改案例：分析执行计划SELECT TOP 9 PlateForum.Title,Tag,idx as fidx,groupidx FROM PlateForum with(nolock) WHERE CONTAINS(Tag,朋友) and state2 order by NEWID()案例：分析执行计划(续）改写：SELECT FT_TBL.Title,Tag,idx as fidx,groupidx FROM PlateForum AS FT_TBL INNER JOINCONTAINSTABLE (PlateForum, Tag, 朋友, 9) AS KEY_TBL ON FT_T

8、BL.IDX = KEY_TBL.KEY WHERE state2 and RANK100 ORDER BY NEWID()什么会影响查询优化器的选择?查询优化统计信息索引和约束内存CPU核数询问SQL Server如何执行这个查询称为评估的执行计划SQL Server 不会执行这个查询，只是估算该如何执行并不总是能获得评估的执行计划实际的计划可能和评估的计划不一样过期的统计值丢失统计值实际的并行度评估的计划基于统计值返回评估的计数根据步长也有可能根据数据密度算一个统计的平均值实际 vs. 评估的执行计划查询优化统计信息用于估计开销直方图包含列中值的分布情况最大梯级数200用来估计查询谓词的基

9、数直方图只有第一列的值密度每个唯一值在列中出现的重复次数用来估计查询谓词、联接、聚合的基数字符串索引中单个词的统计信息 SQL Server 2005新功能查看统计信息DBCC SHOW_STATISTICS 统计信息如何取得读取字段的记录值建立一个排序过的数据分布信息利用完整扫描或是抽样来取记录由数据表内的记录数目动态决定抽样记录所占的百分率选择抽样从数据表或是拥有读取统计所需字段的最小非簇索引随机选择数据页，选到的数据页取其内部所有的记录来更新统计信息创建和更新统计信息CREATE / UPDATE STATISTICS可以执行扫描所有行或者抽样行数百分比或行数缺省抽样行数与总行数呈对数关

10、系CREATE STATISTICS, sp_updatestats默认情况下，SQL Server自动创建和更新统计信息一般地，使用默认设置即可自动创建/更新统计信息数据库级别数据库选项AUTO_CREATE_STATISTICS 自动创建谓词所使用的列的统计信息AUTO_UPDATE_STATISTICS 何时? 普通表: 500; 500 + 20% 行数改变后临时表: 6; 500; 500 + 20% 行数改变后当心:自动更新总是使用缺省抽样行数,即使创建统计信息时使用FULLSCAN指定全表扫描。创建索引时创建的统计信息会使用全表扫描，但以后该统计信息的自动更新也是使用缺省抽样行数

11、。自动更新统计信息表级别可以对表/索引禁用自动更新统计信息使用sp_autostats禁用后必须手动维护统计信息例如：ETL加载后或批量更新后不会对表变量自动创建和更新统计信息你也不能手动对表变量创建统计信息查询优化器总是假定表变量只有一行记录统计信息存在的问题统计信息用于给每个查询操作提供基数估计 (CE)过滤, 联接, 分组聚合, 以图形方式显示查询的执行计划时，缺少的统计信息将予以警告显示（表名称以红色文本显示）。局限在多次联接/分组聚合等操作后，基数估计的准确性可能会降低隐性的相关性很难处理数据的相关性: 两列被关联谓词的相关性: 多个相关的筛选条件数据的不均衡性会影响基数估计, 尤其

12、是采用抽样处理时有时很难从抽样中正确猜断数据密度更新统计信息会导致查询重新编译。统计信息最佳实践关闭自动更新的理由 减少不必要的重编译 如果你确定数据更新不会影响数据的分布密度不希望影响忙时的数据库性能 在闲时定期更新有时可以使用全表扫描来更新统计信息不均衡的数据分布 在闲时定期更新（有足够的时间和I/O）查询的编译/重编译会等待统计信息的自动更新考虑 AUTO_UPDATE_STATISTICS_ASYNC 数据库选项对于大型表考虑采用筛选统计信息（SQL 2008)对大型分区表考虑采用分区统计信息(SQL 2014)统计信息 升级要考虑的事从 SQL Server 2000 升级所有SQL

13、 Server 2000 的统计信息都被认为是过期的，自动更新程序（如果未禁用）会处理这些统计信息最佳实践：在升级后手动更新全部统计信息如果统计信息不是自动更新的，那么升级后需要手动更新sp_updatestats resample = resample更细节的描述请参见 从 SQL Server 2005 升级不需要升级统计信息演示：基数估计的不准确性执行计划优化最佳实践收集“实际的执行计划”比较“估计的”和“实际的” 行数较大的差异意味着这里可能出现问题留意执行警告 联机丛书描述了以下几种情况的补救方法Sort Warning 排序操作的内存不足Exchange Spill 并行查询计划中

14、的通信缓冲区已暂时写入 tempdb 数据库Hash Warning 哈希操作过程中发生哈希递归或哈希援助Bitmap Warning已在查询中禁用位图筛选内容执行计划编译与重编译执行计划缓存和执行有关的DMV查询编译语法检查规范化编译优化参数化这是什么?在 SQL Server 2000 “自动参数化”只能用于非常简单的查询在SQL 2005 中被称为简单参数化研究查询计划时，会发现“参数”突然出现了。如下：查询: select * from T where c=5计划: |-Table Scan(OBJECT:(tst1.dbo.T), WHERE:(T.C=Convert(1)好处: 当

15、同样的查询，参数不再是“5”时，不需要重编译查询执行计划缓存依靠查询语句本身来判别缓存，所以只有使得执行计划缓存中的查询语句一模一样，才能重用执行计划方法：参数化强制参数化SP_EXECUTESQL存储过程强制参数化 (示例)查询: select * from T where c=1 or c=2计划: |-Table Scan(OBJECT:(tt.dbo.T), WHERE:(tt.dbo.T.c=(1) OR tt.dbo.T.c=(2)ALTER DATABASE SET PARAMETERIZATION FORCED计划: |-Table Scan(OBJECT:(tt.dbo.T)

16、, WHERE:(tt.dbo.T.c=0 OR tt.dbo.T.c=1)ALTER DATABASE SET PARAMETERIZATION SIMPLE放入存储过程后带来的矛盾当把查询放到存储过程中后，参数无法被嗅探到由于执行计划缓存重用的特性，而导致不同选择性的查询使用了同种执行计划高选择性-非簇索引查找低选择性-簇索引查找这导致了低选择性的查询执行性能较差平衡参数化和非参数化对语句可以使用查询提示：OPTION( PILE)OPTION(Optimize For参数)对存储过程可以使用：SP_ PILEWITH PILE 创建时使用WITH PILE 执行是使用使用局部变量代替存储

17、过程参数使用计划指南使用决策树存储过程优化器查询提示分类SELECT ProductName, Products.ProductIDFROM dbo.Order Details WITH (INDEX(0) INNER LOOP JOIN dbo.ProductsON Order Details.ProductID = Products.ProductIDWHERE Products.UnitPrice 100 OPTION ( PILE, LOOP JOIN)表提示联接提示查询提示优化器提示示例表提示: INDEX (n) | (name)SQL 2008开始可以强制使用索引查找操作联接顺序

18、，方法FORCE ORDER 提示但是不能跨子查询指定顺序，或把聚合放到联接之下 FORCE ORDER 总是查询级别的LOOP | MERGE | HASH JOIN 可以指定整个查询级别，也可指定单个联接注意:如果在为单个联接指定了联接提示，则尽管仍须遵守查询提示，但该联接提示将优先联接这两个表。聚合HASH GROUP = 使用哈希聚合ORDER GROUP = 使用流聚合更多信息请参阅联机丛书SQL 2005及以上版本新增的提示 PILE 提示，可对单个查询，而不是存储过程下面的提示可以更多的来控制查询计划 OPTIMIZE FOR 可在优化查询时指示查询优化器对局部变量使用特定值US

19、E PLAN 强制使用特定查询计划PARAMETERIZATION 提示 可在数据库级别设置也可在计划指南中指定计划指南FORCESEEK SQL 2008 新增MAXDOP SQL 2008 新增 PILE 提示SQL Server 2000: CREATE PROCEDURE WITH PILESQL Server 2005 及更高版本, 单个语句可以使用OPTION ( PILE)SQL 2000 如果需要为了变量的当前值重编译执行计划，只能使用动态 SQL 语句或重编译整个存储过程更简单的语法No need to grant access to all referenced objec

20、ts; execute on the sp sufficesOPTIMIZE FOR 提示OPTION ( OPTIMIZE FOR ( variable_name = literal_constant ,n ) )仅在查询优化期间使用该变量值，在查询执行期间不使用该值可以抵消优化器的默认参数检测行为 可以更好的实现行为可预测性使用时应对数据的情况有足够的了解USE PLAN 提示可以使执行计划固定下来用法: 在USE PLAN后面使用捕获的 XML 执行计划，使优化器总是使用相同的计划示例:SELECT * FROM t OPTION (USE PLAN N )43USE PLAN 提示指定

21、了什么计划的结构和计算的顺序执行算法索引方案查询中用到的对象限制SQL 2005: 不能用于 INSERT/UPDATE/DELETESQL 2008: 可以!不能用于分布式查询不能用于全文查询只支持支持静态游标和快速只进游标计划指南 概念查询之外的提示查询文本无法或不希望被直接更改时，可以使用计划指南来优化查询性能查询被编码在应用程序中当执行查询时，SQL Server 将 Transact-SQL 语句与计划指南进行匹配，然后在运行时将此 OPTION 子句附加到查询，或使用指定的查询计划。计划指南功能概述创建: sp_create_plan_guide删除、启用或禁用: sp_contr

22、ol_plan_guide匹配: 在模块编译时(存储过程, DML 触发器,表值用户定义函数, 标量用户自定义函数)在批处理编译时目标原始用户查询简单或强制参数化查询计划指南使用说明 应用程序很难修改在用户提交的一个批处理中,强制一个查询使用MERGE JOIN强制优化器总是使用“最坏情况”的参数值进行优化 想避免所有语法上与前一个查询相同而只是常量文字值不同的查询的编译开销在计划指南中指定FORCED PARAMETERIZATION为手动参数化的查询指定特定的计划SQL 2008 中的计划指南固定查询计划 可以直接从计划缓存中的查询计划创建计划指南sp_create_plan_guide_

23、from_handlesys.fn_validate_plan_guide 函数可以验证查询计划是否仍然有效 (比如在表结构变化后)SQL Profiler 事件：Plan_guide_successful 和 Plan_guide_unsuccessful在 Performance 分类中性能计数器： Guided Plan Executions/sec 和 Misguided Plan Executions/sec 提供与SQL Profiler 事件类似的功能存储过程自动重编译的原因架构变化统计变化延期编译Set属性改变临时表改变存储过程创建时使用了 PLIE查询提示或使用了OPTION （ PILE）监视重编译性能计数器SQLServer: SQL Statistics: Batch Requests/secSQLServer: SQL Statistics: SQL Compilations/secSQLServer: SQL Statistics: SQL pilations/secSQL Profiler：SP: pile，Cursor pile SQL:Stmt pile 内容执行计划编译