SQL Server 2005性能测试之CPU篇（编译与重编译）

核心提示： Select * from sys.dn_exec_query_optimizer_infoCounter occurrence valueOptimizations XX XXElaspsed time XX XXElaspsed time是消耗在优化上的时间，这个事件一般接近于消耗在优

Select * from sys.dn_exec_query_optimizer_info
Counter occurrence value
Optimizations XX XX
Elaspsed time XX XX

Elaspsed time是消耗在优化上的时间。这个事件一般接近于消耗在优化上的CPU时间。

另外一个用来捕获这些信息的DMV是 sys.dm_exec_query_stats

下列是需要查询的数据列：

◆Sql_handle

◆Total worker time

◆Plan generation number

◆Statement Start Offset

Plan_generation_num表示查询被编译的次数。下列语句给出前25个被编译的存储过程。

Select top 25 sql_text.test,sqlhandle,plan_geration_num,
execution_count,dbid,
objectid from sys.dm_exec_query_stats across apply sys.dm_exec_sql_text(sql_handle)
as sql_text where plan_generation_num>1 order by plan_generation_num desc

解决方法

如果检测到过多的编译/重编译，考虑以下解决方法：

◆如果重编译是因为SET选项引起，使用SQL Profiler确定是哪一个SET发生了变化。尽量避免在存储过程内部修改SET选项。可以选择在连接级别上设置，并确保SET选项在连接的生命周期中不会发生变化。

◆临时表的重编译极值比一般表要低。如果由于统计信息变化导致重新编译临时表时，可以考虑把临时表替换为一个table变量，同样的变化不会影响table变量。这种方法的缺点是查询优化器不能跟踪table变量的信息，因为系统不会为table变量建立和维护统计信息。这可能导致不能优化对于表变量的查询。

另外一个选择是使用KEEP PLAN查询提示。它设置临时表的极限值与永久表一致。EventSubClass列将显示临时表上发生了”Statistics Changed” 操作。