SQL Server 2005性能测试之CPU篇

EventSubClass数据列对于确定重编译原因来说非常重要。一旦过程或者触发器被重新编译，SP:Recompile就会被触发，但是有可能被重编译的即席批处理不会引发这个事件。 在SQL Server 2005中，监视SQL:StmtRecompiles时非常有用的，任何类型的批处理，即席查询，存储过程或者触发器被重编译时，这个事件类都会被触发。

保存trace文件，使用下面的查询来查看所有的重编译事件。

Select spid,starttime,textdata,eventsubclass,objected,databaseid,
sqlhandle from fn_trace_gettable (‘filepath.trc’,1) where EventClass in(37,75,166) 

EventClass 37是SP:Recompile, 75是CursorRecompile, 166是SQL:StmtRecompile.

也可以进一步对这些查询结果根据Sqlhandle和ObjectID列进行分组来查看是否有某个存储过程存在大量的重编译或者由于其他原因导致的重编译（如Set选项变化）。

Showplan XML For Query Compile. 这个事件类在Microsoft SQL Server编译或者重新编译SQL语句时发生。这个事件中有关于被编译或者重编译的语句的信息。这些信息包括查询计划和存在问题的过程的Object ID。如果发现SQL Compilations/sec计数器数值很高，应该监视这个事件类。通过这些信息可以发现哪些语句被频繁的重编译。可以使用这些信息改变那些语句的参数。这应该会降低重新编译的次数。

DMVs

当使用sys.dn_exec_query_optimizer_info DMV时，可以得到SQL Server花费在优化上的时间。

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” 操作。

◆避免由于统计信息发生变化而导致的重编译（例如，当查询计划因为改变统计信息而不能被达到最优时），指定KEEPFIXED PLAN查询提示。通过这个选项的作用，重编译仅当出现正确性相关的变化时才会发生（例如，当底层表结构发生变化时才会重新编译查询）而不是由于统计数据。如果一个表的架构发生变化，或者表被sp_recompile存储过程标记，重编译将会发生。

◆关闭被定义在一个表上的或者被索引的视图上的index & statistics的statistics自动更新防止由于在对象上的statistics的改变引起的重编译。注意，无论如何，关闭”auto-stats” 功能不是很好的选择。这是因为查询优化器不在对数据变化产生作，可能会导致非最优查询计划被执行。

◆批处理中应该使用具属对象名（如：dbo.table1）来避免重编译和对象之间的二义性。

◆避免由于延迟编译导致的重编译，不要使用条件结构（如IF）来插入DML和DDL或者建立DDL。

◆运行DTA查看是否有可以改善编译时间和查询执行时间。

◆检查是否存储过程使用WITH RECOMPILE选项建立或者查询是否使用了RECOMPILE。如果存储过程使用WITH RECOMPILE选项建立，在SQL Server 2005中，考虑利用语句级别的RECOMPILE如果存储过程中的某个语句需要被重新编译。这可以避免每次执行存储过程时的强制编译，同时允许单独的语句重编译。

性能测试应用



从性能测试的角度出发，可以在负载测试过程中收集有关的性能计数器，同时利用SQL Profiler收集负载测试期间有关重编译的事件类。一般情况下负载测试都会产生较高的CPU利用率，特别是压力测试。在测试结束后收集性能计数器确定是否存在过多的编译和重编译情况。

在确定系统出现过多的编译和重编译后，对trace和DMV结果进行分析找出产生大量编译和重编译的存储过程或者语句。根据不同的原因提出相应的解决方案。

[1] [2]