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
