SQL SERVER 高级技巧系列之二：重编译详解.ppt

MSSQL技巧之二：重编译详解 中讯汉扬 向翔 议题 存储过程编译过程 计划缓存 参数化查询 引起重编译的原因 阀值 选项 批处理的编译 存储过程编译过程 计划缓存 系统视图：sys.syscacheobjects 各种计划缓存 批处理 按照字符串解析 存储过程 按照存储过程名解析 参数化查询 使用set statistics xml选项查看参数化查询 使用OPTIMIZE FOR指定参数优化 影响重编译原因 set选项的改变 对象的架构被改变 自最后一次编译以来表内数据发生大规模改变 sp_recompile 对象的架构 对于表或视图添加或删除列 对于表添加或删除约束，缺省约束，规则 对于表或索引视图添加索引 对于表或索引视图删除索引 删除一个定义在表上的统计 语句级重编译 MSSQL2005支持对语句级的重编译，而2000下只能全部重编译 使用with recompile选项对单个语句实行重编译。 有利于数据选择范围变动非常大的情况。 阀值的计算 对于一个表的重编译阀值部分地决定于引用该表的查询重编译的频度。RT依赖于表类型（永久或临时），和查询计划被编译时该表的行数(即势)。批中所引用的所有表的重编译阀值和批的查询计划存储在一起。 RT被计算如下。（n指当查询计划被编译时引用的一个表的势） 永久表： 如果n=500，RT=500 如果n500，RT=500+0.20*n 临时表： 如果n6，RT=6. 如果n=6=500，RT=500. 如果n500，RT=500+0.20*n 表变量：RT不存在。 阀值的最佳实践 由于临时表的阀值较小，当第一次编译的时候行数很小，那么临时表将及其容易被重新编译，可以使用keep plan提示将临时表的阀值计算与永久表的阀值计算方式置为相同。 由于表变量没有阀值限制，那么就不会引起重编译，所以在表数据量非常小的时候会提高性能。 影响重编译的选项 ANSI_NULL_DFLT_OFF ANSI_NULL_DFLT_ON ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL DATEFIRST DATEFORMAT FORCEPLAN LANGUAGE NO_BROWSETABLE NUMERIC_ROUNDABORT QUOTED_IDENTIFIER 统计值 统计直方图 使用DBCC SHOW_STATISTICS 查看统计 统计列最多200个 20% 的数据行发生变化时更新统计信息 查询优化器始终确保采样的行数尽量少。对于小于 8 MB 的表，则始终进行完整扫描来收集统计信息。 采样数据（而不是分析所有数据）可以将统计信息自动更新的开销降至最低。在某些情况下，统计采样无法获得表中数据的精确特征。可以使用 UPDATE STATISTICS 语句的 SAMPLE 子句和 FULLSCAN 子句，控制按逐个表的方式手动更新统计信息时采样的数据量。 不需要统计信息的列可以禁用统计值 * * 命令解析器 外部经过 SQL native client 连接 MSSQL关系引擎 TDS解析 查询优化器 解析是否存在语法错误，并编译为查询树 将SP分成多个查询，并对每个进行优化（根据IO，CPU，内存，统计，索引等权衡使用比较好的执行计划，但可能不会是最佳） SELECT cacheobjtype, usecounts as Count, cast(C.sql as varChar(max)) as StoredProcedure FROM Master.dbo.syscacheobjects C JOIN Master.dbo.sysdatabases D ON C.dbid = C.dbid WHERE D.Name = DB_Name() AND ObjType = Adhoc ORDER BY StoredProcedure alter proc proctest @x int as begin select * from Production.Product where productid = @x option (OPTIMIZE FOR (@x = 0)) end Factors Affecting Performance Application Design - 2-tier, 3-tier, connection pooling Query Design - poor queries, inappropriate use of cursors Transaction Management - wa