三、结合跟踪和性能监视器输出

marty001

转载　　Profiler GUI简化了Profiler跟踪的收集。不幸的是，这种简易性有其代价。Profiler工具捕捉的事件进入内存中的缓冲以便通过网络反馈给GUI。GUI依赖网络，网络流量可能降低系统的速度并导致缓冲被填满。这将在较小的程度上影响服务器的性能。进一步地，当缓冲被填满，服务器将开始丢弃事件以避免严重地影响服务器性能。
　　1、使用GUI捕捉跟踪
　　可以以两种方法两创建一个脚本化跟踪-手工或者使用GUI。在轻松地满足脚本的所有要求之间，最简易的方法就是使用Profiler工具的GUI,需要如下步骤：

• 　　定义一个跟踪；
  
• 　　单击文件=》导出=》脚本跟踪定义；
  
• 　　必须选择目标服务器类型， SQL Server2005/2008；
  
• 　　未文件命名，并保存它；
  

　　这些不走将生成所有步骤跟踪并将其输出到一个文件所需的所有脚本命令。
　　使用Management Studio手工启动新的跟踪:

• 　　打开文件；
  
• 　　使用系统的相关名称和路径替换InsertFileNameHere；
  
• 　　执行脚本，它将返回带有TraceId的单列结果集；
  

　　可以通过SQL Agent自动化这个脚本的执行，甚至可以使用sqlcmd.exe使用程序从命令行运行这个脚本。不管使用哪种方法，这个脚本将启动跟踪。如果没有定义跟踪停止时间，就必须使用TraceId手工停止跟踪。
　　2、使用存储过程捕捉跟踪
　　查看上一节中定义的脚本，会看到以特定顺序条用的一系列命令：

• 　　sp_trace_create:创建一个跟踪定义；
  
• 　　sp_trace_setevent:添加事件和事件列到跟踪中；
  
• 　　sp_trace_setfilter:将过滤器应用到跟踪；
  

　　一旦定义了SQL跟踪持续到跟踪被停止。因为SQL跟踪作为一个后端进程持续运行，Managerment Studio会话不需要保持打开。可以使用SQL Server内建函数fn_trace_getinfo确定正在运行的跟踪，查询如下：
SELECT * FROM ::fn_trace_getinfo(default);　　输出图：

http://images.cnitblog.com/blog/347600/201311/01134838-fb5f7115caf049588b82e847d1039956.jpg

　　fn_trace_getinfo函数的输出中，不同的traceid的数量表示SQL Server上活动跟踪的数量。
　　第三列(value)表示跟踪是否正在运行(value=1)或者停止(value=0)。可以通过执行存储过程sp_trace_setstatus停止特定的跟踪，如traceid=1，如下所示：
EXEC sp_trace_setstatus 1,0;　　在跟踪停止之后，它的定义必须执行sp_trace_setstatus关闭并且从服务器中删除，如下所示:
EXEC sp_trace_setstatus 1,2;　　为了验证跟踪成功地停止，重新执行fn_trace_getinfo函数，并确定该函数的输出不包含该traceid。
　　这种技术所创建的跟踪文件的格式与Profiler创建的跟踪文件相同。因此，这种跟踪文件可以与Profiler创建的文件以相同的方式进行分析。
　　使用前一小节所概述的存储过程捕捉SQL跟踪，避免了与Profiler GUI相关的开销。而且还比Profiler工具提供了管理SQL跟踪计划的更大灵活性。
三、结合跟踪和性能监视器输出
　　如果自动化了性能监视器捕捉到文件，又自动化了Profiler数据捕捉到一个文件。它们覆盖相同的时间段，那么就可以在SQL Profiler GUI中一起使用它们。确定跟踪有StartTime和EndTime数据字段，按照以下步骤进行：

• 　　打开跟踪文件(当然前提是你曾经 另存为=》跟踪文件)；
  
• 　　单击 文件=》 导入性能数据；
  
• 　　选择导入的性能监视器文件；
  

　　执行上面的操作将打开如下所示对话框，这里允许选择包含性能监视器计数器。
　　选择了想要包含的计数器之后，单击OK按钮将一起打开Profiler和性能监视器数据。现在，可以开始一起使用跟踪数据和性能监视器数据。如果在顶部窗口选择一个时间，它将在性能 监视器中放置一条红线，显示数据中事件发生的时间。相反，可以单击性能监视器数据，表示那段 时间的事件将被选中。这些性能工作得很好，将可以在调整过程中定时使用它们以确认瓶颈和压力 点，并确定导致这些压力的特定查询。
四、SQL Profiler使用要点
　　SQL Profiler使用建议如下：

• 　　限制事件和数据列的数量；
  
• 　　抛弃用于性能分析的启动事件；
  
• 　　限制跟踪的输出大小；
  
• 　　避免联机数据列排序；
  
• 　　远程运行Proflier；
  

　　1、限制事件和数据列
　　在跟踪SQL查询时，可以通过过滤事件和数据列来决定哪些SQL活动应该被捕捉。选择更多的事件造成了大量的跟踪开销。数据列不会增加太多的开销，因为它们只是一个事件类的特性。因此，知道每个所希望跟踪事件的原因，并根据必要性来选择事件是很重要的。
　　最小化捕捉的事件数量避免SQL Server浪费宝贵的资源带宽去生成所有的事件。捕捉像锁和执行计划这样的事件时应该小心进行，因为这些事件会使跟踪输出变得非常大并降低SQL Server的性能。
　　过滤分两个阶段：预过滤由SQL Server执行，后过滤由用户执行。预过滤是捕捉SQL Server活动的联机阶段，预过滤提供多种溢出：

• 　　降低了SQL Server的性能影响，因为生成有限数量的时间；
  
• 　　降低跟踪输出大小；
  
• 　　简化后过滤操作，首先因为要捕捉的事件更少了；
  

　　预过滤的唯一缺点是，可能丢失一些彻底分析中需要的重要信息。
　　2、丢弃性能分析所用的启动事件
　　所用于性能分析的信息围绕一个查询的资源开销。想SP:StmtStarting这样的启动事件不提供这种信息，因为只有在事件完成之后，才能计算I/O量、CPU负载和查询的持续时间。所以，在跟踪运行缓慢的查询以进行性能分析时，不需要捕捉启动事件。这种信息由对应的完成事件来提供。
　　什么情况下适合捕捉启动事件呢？应该在预期某些SQL查询因为错误而不能结束执行，或者频繁发现Attention事件的时候捕捉启动事件。Attention事件一般表示用户中途撤销了查询或者查询超时，可能因为查询运行了太长时间。
　　3、限制跟踪输出大小
　　除了预过滤事件和数据列，其他过滤条件也会限制跟踪输出的大小。同样，限制大小可能丢失所关注的总体系统状态中感兴趣的事件。但是，如果关注于开销较大的查询，过滤器是有帮助的。
　　通过过滤器，能够筛选执行事件》=2或逻辑读数量》=100的查询，因为消耗太低的查询基本上不需要优化。
　　4、避免在线数据列排序
　　在性能分析期间，一般在不同的数据列(如Duration、CPU、Reads)上排序以确定相应数字最大的查询。如果脱机排序，就能降低在与SQL Server交互时必须进行的Profiler活动。排序捕捉到的SQL跟踪输出的方法如下：

• 　　捕捉跟踪，不做任何排序或分组；
  
• 　　另存为跟踪输出到一个跟踪文件；
  
• 　　打开跟踪文件并按照需要在特定的数据列上排序或分组跟踪文件输出；
  

　　5、远程运行Profiler
　　直接在生产服务器上运行测试工具一般不是一个好办法。Profiler有一个大型的用户界面，因此，在其他机器上运行它更好。与系统监视器相似，Profiler不应该通过终端服务会话来运行，因为这样工具的主要部分仍然在服务器上运行。在直接将跟踪输出收集到一个文件时，保存在Profiler运行的本地文件上。这仍然是比通过系统存储过程将Profiler作为服务器端跟踪来运行更加资源密集的操作。使用系统存储过程仍然是最好的选择。
　　6、限制使用某些事件
　　某些事件的开销比其他的事件大。由于生成的查询的特性，语句完成事件的开销可能非常大。需要谨慎地使用，特别是在已经遇到压力的系统上，必须谨慎使用的事件有：Showplan XML事件，Performance:Showplan XML、Performance:Showplan XML for Query Compile和Performance:Showplan XML sTATISTICS Prifile。虽然这些事件可能有用，但是不要在生产机器上使用它们。
五、没有Profiler的情况下查询性能度量
　　建立一个跟踪能收集许多数据供以后使用，但是这种收集可能代价很大，必须等待结果。
　　如果要立即捕捉系统的性能度量，特别是关于查询性能的度量，那么动态管理视图sys.dm_exec_query_stats正式所需要的。如果还需要查询运行及其单独开销的历史记录，那么跟踪仍然是更好的工具。但是，如果只需要知道这时候运行时间最长的查询或者最多的物理读操作，则可以从sys.dm_exec_query_stats得到这些信息。
　　因为sys.dm_exec_query_stats只是一个视图，可以简单地对其进行查询并获得服务器上查询计划统计的信息。
列描述Plan_handle引用执行计划的指针Creation_time计划创建的时间Last_execution time查询最后一次使用的计划时间Execution_count计划已经使用的次数Total_worker_time从创建起计划使用的CPU时间Total_logical_reads从创建器计划使用的读操作数量Total_logical_writes从创建器计划使用的写操作数量Query_hash可用于识别有相似逻辑的查询的一个二进制hashQuery_plan_hash可用于识别有相似逻辑的计划的一个二jinzhihash　　为了过滤从sys.dm_exec_query_stats返回的信息，需要将其连接到其他动态管理函数上，如sys.dm_exec_sql_text可以显示与计划相关的查询文本，sys.dm_query_plan显示用于查询的执行计划。一旦连接到其他DMF，可以限制希望过滤得数据库或过程。