《MSSQL技巧之二重编译详解》由会员分享，可在线阅读，更多相关《MSSQL技巧之二重编译详解（15页珍藏版）》请在金锄头文库上搜索。
1、MSSQL技巧之二：重编译详解中讯汉扬 向翔议题存储过程编译过程计划缓存参数化查询引起重编译的原因阀值选项批处理的编译存储过程编译过程命令解析器外部经过SQL native client连接MSSQL关系引擎TDS解析查询优化器解析是否存在语法错误，并编译为查询树将SP分成多个查询，并对每个进行优化（根据IO，CPU，内存，统计，索引等权衡使用比较好的执行计划，但可能不会是最佳）计划缓存系统视图：sys.syscacheobjects SELECT cacheobjtype, usecounts as Count, cast(C.sql as varChar(max) as StoredPro
2、cedure 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各种计划缓存批处理按照字符串解析存储过程按照存储过程名解析参数化查询使用set statistics xml选项查看参数化查询使用OPTIMIZE FOR指定参数优化alter proc proctestx intasbeginselect * from Production.P
3、roduct where productid = x option (OPTIMIZE FOR (x = 0)end影响重编译原因set选项的改变对象的架构被改变自最后一次编译以来表内数据发生大规模改变sp_recompile 对象的架构对于表或视图添加或删除列对于表添加或删除约束，缺省约束，规则对于表或索引视图添加索引对于表或索引视图删除索引 删除一个定义在表上的统计 语句级重编译MSSQL2005支持对语句级的重编译，而2000下只能全部重编译使用with recompile选项对单个语句实行重编译。有利于数据选择范围变动非常大的情况。阀值的计算对于一个表的重编译阀值部分地决定于引用该表的
4、查询重编译的频度。RT依赖于表类型（永久或临时），和查询计划被编译时该表的行数(即势)。批中所引用的所有表的重编译阀值和批的查询计划存储在一起。RT被计算如下。（n指当查询计划被编译时引用的一个表的势）永久表：如果n500，RT=500+0.20*n临时表：如果n6，RT=6.如果n=6500，RT=500+0.20*n表变量：RT不存在。阀值的最佳实践由于临时表的阀值较小，当第一次编译的时候行数很小，那么临时表将及其容易被重新编译，可以使用keep plan提示将临时表的阀值计算与永久表的阀值计算方式置为相同。由于表变量没有阀值限制，那么就不会引起重编译，所以在表数据量非常小的时候会提高性能
5、。影响重编译的选项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
6、 MB 的表，则始终进行完整扫描来收集统计信息。采样数据（而不是分析所有数据）可以将统计信息自动更新的开销降至最低。在某些情况下，统计采样无法获得表中数据的精确特征。可以使用 UPDATE STATISTICS 语句的 SAMPLE 子句和 FULLSCAN 子句，控制按逐个表的方式手动更新统计信息时采样的数据量。 不需要统计信息的列可以禁用统计值Factors Affecting PerformanceApplication Design - 2-tier, 3-tier, connection poolingQuery Design - poor queries, inappropriat
7、e use of cursorsTransaction Management - waiting on client, deadlocksDatabase Design over-normalization, no supporting indexesData Distribution missing statistics, low selectivitySQL Server - insufficient resources or improperly configuredOperating System - improperly configuredHardware - insufficient resources (CPU, Memory, etc), hardware errorsThe Typical Performance PyramidApplication / Query / Database DesignOperating EnvironmentHardware
