Server 2005性能排错

SQL 跟踪

如果PerfMon计数器显出了很高的重编译数量，编译将在SQL Server中占用很多的CPU资源。我们将需要查看Profiler 跟踪并从中找到找到被重编译的存储过程。SQL Server Profiler跟踪给出我们重编译原因的信息。你可以使用下列事件。

SP:Recompile和SQL:StmtRecompile事件类指出了哪个存储过程和语句被重编译。当你编译一个存储过程，一个事件为这个存储过程生成，其中每条语句将被编译。然而，当存储过程重编译时，只有导致重编译的语句被重编译（在SQL Server 2000中将是整个存储过程）。下面列出了SP:Recompile事件类更多重要的数据列。特别是EventSubClass数据列决定重编译的原因。SP:Recompile当存储过程或触发器被重编译被触发一次，但不会被独立查询引发。在SQL Server 2005中，监视SQL:StmtRecompiles也非常有用，该事件类在所有类型的重编译中都会被触发，包括批，独立查询，存储过程和触发器。如下是我们关系的事件中关键的数据列：

◆EventClass

◆EventSubClass

◆ObjectID (represents stored procedure that contains this statement)

◆SPID

◆StartTime

◆SqlHandle

◆TextData

更多信息，请见SQL Server 联机丛书中“SQL:StmtRecompile Event Class”。

如果你有保存的跟踪文件，你可以使用下列查询查看所有捕捉的重编译事件。

select 

spid,

StartTime,

Textdata,

EventSubclass,

ObjectID,

DatabaseID,

SQLHandle 

from 

fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)

where 

EventClass in(37,75,166)

事件类37是 Sp:Recompile， 75 是 CursorRecompile， 166是SQL:StmtRecompile

你可以通过SqlHandle和ObjectID列或其他列将这个查询的结果分组，也可以查看是否最多的重编译类型是存储过程或其他原因（例如SET选项改变等）。

Showplan XML For Query Compile.

Showplan XML For Query Compile事件发生于Microsoft SQL Server编译或重编译一段SQL语句时。该事件有关于语句编译或重编译的信息。信息包括查询计划和过程的对象ID。捕获这些事件是有性能开销的，因为它捕获了每次编译或重编译。如果你在系统监视其中看到很高的SQL Compilations/sec计数器值，你应该监视这个事件。通过这些信息，你可以看到那条语句被频繁的重编译。你可以使用这些信息改变这些语句的参数。这将影响重编译的数量。

DMVs.

当你使用sys.dm_exec_query_optimizer_info DMV，你可以得到SQL Server花费在优化的时间。如果获取了这个DMV的2个快照，你可以得到在给定的时间段内花费在查询优化的时间。

select * 

from sys.dm_exec_query_optimizer_info
counter          occurrence           value                

---------------- -------------------- --------------------- 

optimizations    81                   1.0

elapsed time     81                   6.4547820702944486E-2

特别是查看elapsed time，该时间由于优化而产生。因为优化过程的时间基本上就是用户优化操作的CPU时间（因为优化处理是CPU时间的主要部分），你可以得到一个好的度量，找到那段编译时间占用了大量的CPU时间。

其他包含有用信息的DMV有：

sys.dm_exec_query_stats.

你希望查看的数据列有：

◆Sql_handle

◆Total worker time

◆Plan generation number

◆Statement Start Offset

更多信息请查看SQL Server 联机丛书相关主题

sys.dm_exec_query_stats.

特别是plan_generation_num预示了查询编译时的次数。下面是示例给你展示了被重编译次数最多的25个存储过程。

select * 

from sys.dm_exec_query_optimizer_info
select top 25

sql_text.text,

sql_handle,

plan_generation_num,

execution_count,

dbid,

objectid 

from 

sys.dm_exec_query_stats a

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where 

plan_generation_num >1

order by plan_generation_num desc

更多信息请见Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

[1] [2] [3] [4] [5] [6] [7]