这些天看了一篇微软官方发布的MS SQL Server2008性能问题处理及优化的英文文档，里面知识点介绍地很详细，在现实工作中也很实用，遂产生了想把它翻译一下的念头。翻译的过程，既可以帮助自己复习一下这些技术，也可以向其他还不熟悉这一块的朋友介绍一些新的知识，何乐而不为呢。只是这篇文章有点长，我会分成几篇随笔去介绍，所以，不光是对我耐性的考验，也是对你的考验哦！
--------------------------------------------
当一个批处理或者远程过程访问（RPC）被提交到SQL Server， SQL Server会在执行语句之前检查查询计划的有效性及正确性。如果其中一项检查失败，这个批处理可能会被重新编译然后生成一个不同的查询计划。这种编译被称作是重复编译。一般而言，这种重复编译需要保证正确性，并且通常在服务器发现了由于基础数据发生变化而可能会存在一个更优化的查询计划时，这种重复编译就会发生。编译实际上是一种CPU密集型操作，并且这种密集操作会导致系统CPU-bound性能问题。
在SQL Server 2000中，当SQL Server编译一个存储过程，整个的存储过程都会被编译，而不仅仅是存储过程中触发了编译的某一条语句。在SQL Server 2008 及SQL Server 2005中，一个好的改变是这种编译发生在存储过程的语句级别。当SQL Server 2008 或者SQL Server 2005编译存储过程时，仅仅引起重复编译的那个语句会被编译--而不是整个的存储过程。这种特性会使用较少的CPU带宽并且会导致较少使用较少的编译锁去锁定资源。重复编译可以再多种情况下发生，例如：
--架构改变（schema）
--统计信息改变（Statistics）
--延迟编译
--SET option changes
--临时表改变（temporary table）
--存储过程中使用了RECOMPILE 或OPTION（RECOMPILE）查询限制
诊断
你可以使用Performance Monitor和SQL Server Profiler去诊断过度的编译及重复编译。
Performace Monitor
SQL Statistics对象提供计数器去监视编译，并且这种监视类型可以加在SQL Server的某个instance上。你必须监视查询编译和重复编译组合的数目，从得到的多个批处理中找出是否是这些编译导致了CPU的过多使用。理想状态，SQL Recompilations/sec 比率及 Batch Requests/sec 比率应该是很低的。除非用户正在提交很多及时查询语句。
下面这些是重要的几个数据计数器：
--SQL Server: SQL Statistics：Batch Requests/sec
--SQL Server: SQL Statistics: SQL Compiulation/sec
--SQL Server: SQL Statistics: SQL Recompilations/sec
更多信息，请参阅：SQL Statistics Objects
SQL Server Profiler Trace
如果Performance Monitor 计数器显示出来存在着很大数量的重复编译，这些SQL Server引出的重复编译有可能会造成较高的CPU使用。查看profiler trance去查找正在被编译的存储过程。SQL Server Profiler trace提供的信息也包含着导致重复编译的因素。你可以使用下面的这些events去获取这些信息。
SP:Recompile / SQL:StmtRecompile
SP:Recompile以及SQL:StmtRecompile event类别显示出哪些存储过程及语句已经被编译过了。当你编译一个存储过程，会为这个存储过程生成一个event，并且会有一个针对每条语句的event被编译。然而，当一个存储过程被编译，只有其中的引起重复编译的语句被编译。SP：Recompile中的其它更重要的数据列也被列举到了这里。EventSubClass数据列很特殊，它可以用来确定重复编译的原因。SP:Recompile被触发的情况：存储过程或者触发器被编译并且即时查询似乎要被编译但编译实际上没有被激活。在SQL Server 2008及SQL Server 2005中，监视SQL:StmtRecompile也很必要，因为这个event class会在任意类型的批处理、即时查询、存储过程或者触发器被重复编译时激活。
这个event中需要重点关注的数据列有：
--EventClass
--EventSubClass
--ObjectID（会呈现出是哪个存储过程包含这个查询语句）
--SPID
--StartTime
--SqlHandle
--TextData
如果你已经保存了一个trace文件，你可以使用下面的查询语句去查看已经追踪到这个trace中的重复编译events。
select spid, startTime, Textdata, EventSubclass, ObjectID, DatabaseID, SQLHandle from fn_trace_gettable('e:\recompiletrace.trc',1) where EventClass in (37,75,166)
EventClass 37=Sp:Recompile, 75=CursorRecompile, 166 = SQL:StmtRecompile
你可以对这个查询得到的结果按照SqlHandle和ObjectID列进行更进一步的分组，或者按照其它更多的列，以帮助查看是否大部分的重复编译是由一个存储过程或者其它更多方面原因造成的（例如一个set option被改变）。
Showplan XML For Query Compile
Showplan XML For Query Compile event class 会在SQL Server编译或者重新编译一个T-SQL 语句时发生。这个event包含正在被执行编译或者重复编译的语句的信息。这些信息包含了有问题存储过程的查询计划和object ID。 这个event 对于日常性能捕捉有比较大的意义，因为它被用来捕捉每个编译及重复编译。如果你在Performance Monitor中看到了一个SQL Compilations/sec计数器有很高的值，你应该监视这个event。从这个信息里面，你可以看得出哪个语句正在被重复编译。你可以使用这些信息区修改这些语句的参数化设置。这样也可以减少重复编译的次数。
DMVs
当你使用sys.dm_exec_query_optimizer_info这个动态管理试图时，你可以很方便地看到SQL Server 用于优化所花费的时间。如果你为这个试图做两个快照，你就可以更好的感受在两个试图的间隔段内用于优化的时间。
select * from sys.dm_exec_query_optimizer_info
值得一提的是，结果中的elapsed time，这个值是由于优化所消逝的时间。因为这个消逝时间几乎接近CPU用于优化语句所使用的时间，所以你可以使用很好得知道到底是哪些编译和重编译造成了CPU的高使用。
另外一个被用来检测这些信息的DMV是sys.dm_exec_query_stats。其中包含的重要数据列有：
--sql_handle
--Total worker time
--Plan generation number
--Statement Start Offset
尤其是，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
