On the Journey to be a SQL Server Professional: SQL Server 2012实施与管理实战指南摘要

第19章 sql server常用日志信息与推荐搜集方法 840

· 常用SQL系统信息与搜集的八大方法：Windows事件日志，SQL ErrorLog，性能监视器，SQL Trace, DMVs, sqldiag, pssdiag, performance dashboard。

· 其中最常用的是：Windows事件日志，SQL ErrorLog，和性能监视器

· SQL Trace是SQL提供的一个非常强大的，但也是很容易造成负面影响的信息收集工具。怎麽合理地收集SQL Trace日志，需要一些技巧。SQL Trace产生的日志文件经常是非常庞大的，可以借助一些工具把日志导入到SQL表格里更有效地分析。微软还提供了一个工具，可以自动从Trace文件里找出最复杂的语句。

· SQL的系统管理视图能显示出SQL的运行状态。有些信息对定位和解决问题非常重要。

· 除了上述以手工收集步骤为主的方法外， SQL里还有一个自动信息收集工具：SQLDiag。它可以自动收集大部分有用的信息。微软还有个内部工具，叫PSSDIAG。它比SQLDiag更强大。仅需要简单的配置和操作步骤就可以收集多种信息。最后SQL还有个自动化监视工具：Performance Dashboard，可以自动产生一些报表，帮助DBA做一些基本的分析工作。

19.1. Windows事件日志 841

· Windows事件日志是一个很好的界定问题性质的工具。当DBA要对SQL做健康检查时，首先要检查的不是SQL自己的日志，而是Windows事件日志。当确定Windows日志里没有明显的错误和警告以后，再去看后面SQL日志信息。

· 在Windows里运行eventvwr.msc /s或者eventvwr /s。其中/s参数的意思是远程机器.

· Windows主要有三种日志：Application、Security、System。对于SQL会主要关心系统日志和应用程序日志。Windows会在自己的系统日志system log里记录SQL这个服务的启动、正常关闭、异常关闭等信息。SQL也会把自己的一些概要信息同时记录在Windows的应用程序日志里Application Log。当处理一些连接认证问题时，可能会偶尔用上安全日志。日志里的每一条记录，都属于Information、Warning、Error中的一类。记录会标明日期、时间、来源。如果在应用日志里，从SQL产生的记录其来源名称都会是MSSQLSERVER。

· 在事件查看器里，还能把日志另存为*.evt文件或*.txt文件，以供DBA带到其它机器上打开分析。打开一个*.evt文件的方法，是右键点击“事件查看器（本地）”树型结构，选择”open log file”。用这种方法，DBA就能像看本机上的日志记录一样，分析从其它机器保存下来的日志文件了。

· 要注意的是，用事件日志查看器打开的日志，其时间会随着机器的时区有所变化。不同时区的机器打开同一个*.evt文件，其显示的时间会不一样。

19.2. sql server errorlog文件 843

· 检查完Windows的基本状况后，就可以开始检查SQL的健康状况了。不管你是遇到什么问题，建议第一个要检查的是SQL的ErrorLog文件。

· 当SQL启动的时候，会在某个固定的路径下生成一个“errorlog”的文件。SQL默认会保留7份errorlog文件。

· 日志文件的默认路径是安装路径下的XXX\MSSQL\LOG的目录下。如果你要分析的是一台陌生的服务器，可以用很多种方法找到errorlog路径。一种比较简单的方法。是在SQL 配置管理器里的属性字符串里，找到那个“-e”的参数。

· errorlog本身非常重要，它记录了SQL的整个开启、运行、终止过程。如果SQL遇到了比较严重的问题，在errorlog里也会有所显示。

· errorlog文件里会记录的内容有：

（1）SQL的版本，以及Windows和Processor基本信息

（2）SQL的启动参数，以及认证模式，内存分配模式

（3）每个数据库是否能够被正常打开。如果不能，原因是什么

（4）数据库损坏相关的错误

（5）数据库备份与恢复动作记录

（6）DBCC CHECKDB记录

（7）内存相关的错误和警告

（8）SQL调度出现异常时的警告。一般SERVER HANG 服务器死机会伴随着这些警告

（9）SQL I/O操作遇到长时间延迟的警告

（10）SQL在运行过程中遇到的其它级别比较高的错误

（11）SQL内部的访问越界错误（Access Violation）

（12）SQL服务关闭时间

· 总之，如果SQL在运行过程中发生异常，一般来讲errorlog里都会有所提示。所以在检查SQL相关问题的时候，总是从errorlog着手，先确认errorlog里是干净的。如果errorlog里有一些错误或警告，就要确认这些错误和警告发生的时间，是不是前端感觉到问题的时间。如果时间能对得上，那就要着重分析一下了。

· 如果开启一些设置，在errorlog里还能看到的有用信息有：

（1）所有用户成功或失败的登入

（2）死锁以及其参与者的信息需要打开跟踪标志1222 或1204

DBCC TRACEON (1222)

DBCC TRACEON (1204)

· 但是errorlog里也不是什么问题都能记录。它不能反映的问题有：

o （1）阻塞问题。只要阻塞还没有严重影响SQL的线程调度，errorlog里是不会有体现的。

o （2）普通性能问题，超时问题。如果性能问题不是由于内存使用异常、线程调度异常，或者是I/O子系统反应非常缓慢，而是由于表格或语句设计导致，errorlog里也不会有所反映。

o （3）Windows层面异常。如果Windows层面出现工作不正常，或者服务器不响应，SQL是很难自我判断的。errorlog里一般不会有所体现。这也是我们为什麽要第一步就要检查Event Log的原因。所以errorlog只是分析SQL问题的一个很好的出发点。对于SQL，还要结合其它丰富的信息记录功能，综合分析问题。

19.3. 性能监视器 845

· 性能监视器是Windows的一个工具，在系统管理工具组里。默认里面就有很多Windows层面的性能计数器，可以监视系统的运行。

· SQL自己开发了一些扩展的性能计数器。在安装SQL的时候，会注册到Windows里。这样，Windows的性能监视器就能看到一些以“SQL”打头的计数器了。SQL在运行时，会统计这些计数器的值。

· 默认性能监视器是用来实时检测系统的，在窗口里，用不同颜色的线条表示不同的计数器值。当窗口画满以后，会从头覆盖前面的内容。所以默认只能看到最近一小段时间的值。但是在现实的问题分析中，实时监测还是比较少的。更常见的场景是需要在问题发生之前，就要开启性能计数器的收集，收集一段时间之后，或者问题重现之后，再离线地分析问题的现象和原因。那么这样的日志怎样收集呢？

· 收集性能计数器做离线分析的步骤：

o （1）在性能监视器左边的窗口，展开性能日志和警告子树，点击“计数器日志”。在右边的窗口里，右键点击，选择“新日志设置”，它会弹出一个对话框，让你为新的日志记录配置命名。这里我们取名为Test。（注意：这里是以WindowsXP/2003/2008的性能监视器为例。Windows2008R2和Windows7的性能监视器界有了比较大的变化）。

o （2）在接着弹出的对话框里，就可以配置DBA要搜集的信息要求了。首先要选择搜集哪些计数器，以及它们的取样时间间隔sample data every，默认是15秒取一次这个间隔能够满足大部分需求。有说法讲在搜集和磁盘相关的性能日志时，间隔要设置短一点，最好是3到5秒。如果设置30秒以上，可能信息就不完整了。所以15秒是大部分情况下比较好的选择。

o （3）选择添加对象，就可以选择要收集的性能监视器对象。对于非在线分析，问题可能还不清楚，很难确定哪些性能计数器有用，哪些没有用。所以在这里，一定要多选一些。一般的SQL问题，可以选择下面这些对象：

ü 在memory,process,physicaldisk,processor,system对象下的所有计数器，以及它们的所有instance

ü 所有以SQLSERVER：开头的性能监视对象

ü 如果要监视CPU类问题，最好还包含thread下面的所有计数器，以及它所有的instance

有些DBA会担心，抓这麽多计数器会不会影响性能。应该说根据经验，性能监视器对系统整体性能的影响几乎感觉不到。所以可以比较放心大胆地多收一些计数器。

o （4）设置文件的位置和最大大小

另一个重要配置，是日志文件存放在哪里，保存格式，以及最大大小。如果性能日志文件大小超过1GB，可能有些机器打开会很慢。所以一定要注意其最大值可以设为200MB。如果一个200MB的文件写满，性能监视器会自动创建一个新的。文件格式可以选二进制文件。日志搜集当然可以手动开始和终止。但是如果问题会发生在半夜，最好能让系统自动开启，自动关闭。性能监视器也可以帮DBA做到这一点。

当得到一个性能日志后，可以通过以下步骤查看日志中的数据

ü 打开性能监视器里，选择查看日志数据

ü 在数据源里添加日志文件

ü 然后点击数据选项卡，就能看到在原来那台服务器上收集的性能计数器了添加你所关心的计数器到右侧的窗口。

ü 然后如果窗口中间的滚动条，你可以把时间段缩短到你最关心的那段时间。滚动条被拉到想要的位置后，你需要在窗口单击右键，然后Zoom to,这样才能完成对时间段的选择。

19.4. sql trace文件 851

19.4.1. sql trace文件的收集方法 851

· 事件组

o Database事件组

当DBA要监视数据文件和日志文件的自动增长与自动收缩的时候，可以选择收集Database事件组下面的这些事件，不过如果只是关心文件大小是什么时候变化的，可以定期运行TSQL脚本，或者使用性能监视器。如果要分析是什么操作触发了文件大小变化，可以使用SQL Trace。

o Errors and Warnings事件组

这些事件会搜集在SQL里发生的所有错误和警告信息。如果SQL运行不正常，很可能这些事件会有反映。所以建议每次收集时，都把还这个事件组的事件全都选上。

o Locks事件组

ü dead lock graph，lock：deadlock，lock：deadlock chain- 这三个事件是跟踪死锁的。因为死锁在SQL里发生的频率不会太高，所以在做死锁问题的时候，可以把它们三个都选上。但是要注意，要先选上“显示所有列”，再选事件，因为有些重要的字段默认的模板里没有选上。

ü Lock: Timeout和Lock：Timeout（timeout>0） - 在发生阻塞的时候，会有Lock Timeout事件发生。可是，阻塞是SQL里为了实现事务隔离所需发生的事件，所以阻塞在SQL里发生得非常普遍。收集这两个事件对问题分析的帮助不会太大。

ü Lock：Acquired 、Lock: Cancel、Lock: Escalation、Lock: Released: - 这些事件能够跟踪一句语句在运行过程中对锁资源的申请和释放过程。但是在繁忙的生产环境里，SQL会申请大量的锁资源。所以这些事件会产生大量记录。通常情况下，只会在测试环境里，测试单条语句时，才敢把它们加上。在生产环境上，要尽可能避免使用它们。

o “Performance”事件组 -事件主要分两类：

ü Auto Stats能够记录SQL里发生的自动创建或更新统计信息的事件。

ü 其它有showplan字样的，是关于各种形式的执行计划以及运行信息。它们的相同点和不同点要有目的地选择，不要重复收集。需要注意的是，执行计划一般都比较大，而每一条语句执行，都会有它的执行计划。所以如果要收集执行计划，结果日志肯定会很大。所以一定要在必要的时候，才加入执行计划事件。

o “Security Audit”事件组

ü 这一组事件的目的，是监视SQL里各项和安全有关的事件，例如有人加入了一个DB User、一个Login，有人做了数据库备份、DBCC动作，有人修改了用户密码等。如果要对SQL做安全监控，这些事件都是要考虑的。

ü 如果是要一般地监视运行，可能要选择的只有Audit Login和Audit logout通过这两个事件，我们能够看到一个连接的生命周期。如果有用户抱怨连接失败，也可以跟踪Audit Login Failed。如果连接请求是被SQL拒绝的，可以看到拒绝的时间和理由。

o “Server”事件组

ü 它的下面只有三个事件，Mount Tape、 Server Memory Change 、Trace File Close。这三个事件在SQL里发生的频率都不会很高，所以加进来也不会有很大影响。

o “Sessions”事件组 - 只有一个事件：ExistingConnection，反映在日志开始收集的时候，SQL里已经有的连接。这个事件总是要被选上的。

o “Stored Procedures”事件组

ü 这是一个很重要的事件组，事件的选择也很有讲究。常用的事件分成两类。一类和编译、重编译有关：

Ø SP:CacheHit

Ø SP:CacheInsert

Ø SP:CacheMiss

Ø SP:CacheRemove

Ø SP:Recompile

这些事件的量也会很大。所以只有当怀疑问题和执行计划重用、或者编译、重编译相关的时候，才需要选择。其它问题不要选择收集这些事件。另一类与编译、重编译无关，而是与存储过程运行有关：

Ø RPC: Completed, RPC: Starting- 应用程序调用了一个存储过程。这两个事件记录了存储过程的开始和结束。一般的SQL应用程序，例如，使用ADO连接运行一个存储过程，在SQL里看到的都是RPC事件。在RPC:Completed事件里，不但有结束时间，也包含开始时间。所以如果连接正常，一个RPC:Completed事件就应该包含RPC:Starting里的信息。但是如果连接非正常地退出，或者遇到了SQL异常，可能存储过程的运行只能看到RPC:Starting事件，看不到RPC:Completed事件，但是这种几率是比较小的。

Ø SP: Completed, SP: Starting: 如果连接是以SQL Batch的方式调用存储过程，例如在SSMS里运行sp_who，看到的会是一组SP: Completed, SP: Starting事件。像RPC一样，SP:Completed事件也能包含SP:Starting的绝大部分信息

Ø SP: StmtCompleted, SP: StmtStarting: 前两组事件都是以整个存储过程为单位的一个复杂的存储过程，可能最后执行的指令数会达到几千行，甚至几万行，十几万行（如果里面有循环逻辑）。当知道了一个存储过程慢，就要知道是哪一部分，或者是哪一句话最慢。这时候就需要SP: StmtCompleted, SP: StmtStarting事件来帮忙。和SP:Completed、RPC:Completed不同的是，如果一个存储过程在运行过程中被cancel了（例如，遇到了运行超时），SP:Completed、RPC:Completed都能被抓到，但是正在运行的语句不会有SP:StmtCompleted，后面没有运行的语句都不会有SP:StmtCompleted,SP:StmtStarting事件。所以通过SP: StmtCompleted, SP: StmtStarting事件可以很好地看出存储过程在被终止时执行到了哪一步。但是SP:StmtCompleted,SP:StmtStarting事件会产生大量的日志记录，所以在问题定位阶段，一般不大会加入它们。而且，为了减少事件的数目，常常只收Completed事件，不收Starting事件。当问题有了方向之后，再加入更多的事件，有目的地收集和分析。

o “TSQL”事件组

ü 这个事件组也很重要。它的事件也分两类：

Ø 和编译、重编译相关的：

Exec PreparedSQL

Prepare SQL

SQL: StmtRecompile

Unprepare SQL

其中，SQL:StmtRecompile比较常用

Ø 关于批处理执行的：

v SQL: BatchCompleted和SQL: BatchStarting – 类似RPC: Completed、RPC: Starting

v SQL: StmtCompleted和SQL: StmtStarting - 类似SP: StmtCompleted 、SP: StmtStarting

相似地，在问题定位阶段，一般不会加入SQL:StmtCompleted SQL:StmtStarting。而且，为了减少事件的数目，常常只收Completed事件，不收Starting事件。当问题有了方向之后，再加入更多的事件，有目的地收集和分析

o “Transactions”事件组 - 常用的事件有：

ü DTCTransaction- 分布式事务的生命周期。正常来讲MSDTC事务在SQL里比较少，而且容易出问题。所以可以默认就收集它。

ü SQLTransaction - SQL事务的生命周期。SQL事务是SQL非常普通的操作。如果搜集，会产生大量记录。所以只会在遇到阻塞和死锁问题，又搞不清楚这个事务怎麽被打开时，才会借助这个事务分析问题。

ü TransactionLog- 记录SQL向事务日志文件里写入日志的动作。这个动作在SQL里非常普遍，建议不要收集。

· 所以这里来总结一下，对于一般性问题，作者建议收集的事件有哪些：

o 1、一个普通的Trace

ü Database：Data File Auto Grow、Data File Auto Shrink、Log File Auto Grow、Log File Auto Shrink

ü Errors and Warnings：除了Errorlog以外的所有事件

ü Locks：Deadlock Graph、Lock：Escalation

ü Performance：Auto Stats

ü Progress Report：Online Index Operation

ü Security Audit：、Audit Logi、Audit Login Failed、Audit Logout、Audit Server Starts and Stops、Audit Backup/Restore Event、Audit DBCC Event

ü Server：所有事件

ü Sessions：ExistingConnection

ü Stored Procedures：RPC:Completed, RPC:Starting

ü TSQL：SQL:BatchCompleted、SQL:BatchStarting、PrepareSQL、UnprepareSQL、SQL:StmtRecompile

ü Transactions：DTCTransaction

如果还要缩小日志生成量，可以去掉RPC:Starting和 SQL:BatchStarting

o 2、一个很详细的关于性能问题的Trace – 除上面的外，还要收集：

ü Performance：Showplan Statistics Profile

ü Stored Procedures：RPC:Output Parameter、SP:CacheMiss、SP:CacheRemove、SP:Recompile、

ü SP:Completed、SP:Starting、SP:StmtCompleted、SP:StmtStarting

ü TSQL：SQL:StmtStarting、SQL:StmtCompleted

ü Transactions：SQLTransaction

如果要缩小日志生成量，可以去掉SP:Starting 、SP:StmtStarting、 SQL:StmtStarting

当然，每个人分析问题的方法都可能不一样，对这些事件的喜好也不一样。上面只是两种建议的组合。在使用时可以根据实际问题作调整。

另外，按照默认的模板，有些事件比较重要的数据字段可能没有被包含。例如Performance下的“Showplan Statistics Profile”事件，如果不选Binary字段，可能整个执行计划就看不到。Trace就白收了。所以如果要收Trace，建议把所有字段都选上。

· 收集下这些事件保存在哪里呢？比较安全的做法是在Trace属性里做下列必须的设置：

o （1）设置Trace将要以文件形式保存，指定文件的名字和路径

o （2）设置文件的最大上限（一般设置为300MB到500MB），并且设置file rollover

o （3）设置由SQL来收集Trace，而不是Profiler

ü 能够收集SQL Trace的，不光是Profiler这个工具，SQL自己也能做。

ü 两者的不同在于如果让Profiler这个工具收集Trace（术语叫客户端Trace，Client Side Trace），SQL必须要把自己的一举一动告诉Profiler。这是两个不同的Windows进程，要进行进程间通信，是要消耗系统资源的，并且比较慢。如果用Profiler收集一个很繁忙的SQL的Trace，很可能Profiler来不及处理SQL发过来的海量事件，反过来要让SQL等它。结果是，Profiler作为一个性能监视工具，反而成为影响SQL性能的一个瓶颈。在SQL2000的生产环境里，作者碰到过很多次一开始运行Profiler，整个SQL就没有响应的情况。SQL2005以后会好一点，但是为了安全起见，建议禁止开启客户端Trace。

ü 让SQL自己收集Trace（服务器端Trace，Server Side Trace），因为没有进程间通信，对SQL性能的影响要小很多。所以在繁忙的服务器上，通常都使用这种方法。但是在Profiler里打开“Server processes trace data”，感觉上还是不很保险，因为Profiler为了能收集到的日志输出到屏幕，还是要和SQL进行通信，它们之间还是会有联系。

ü 其实，服务器端Trace是调用一些系统存储过程开启和关闭的。所以任何人只要有管理员权限，都可以使用SSMS这样的客户端定义和开启Trace。只不过写脚本太过复杂，很少有人愿意这麽做。在这里介绍一种既省力，又安全的方法开启服务器端Trace。

Ø 首先，请管理员在任何一台测试机上定义一个Trace（通过Profiler），主要是选择好要跟踪的事件和它们的字段。如果有过滤条件，也可以设置。

Ø 接着，选择 run 开启这个Trace

Ø 紧接着就可以停止它。

Ø 在Profiler界面里，选择导出脚本定义，Profiler会帮助你生成一段能够开启相同定义的服务器端Trace的脚本（如果在生产服务器上开启Profiler Trace没问题，当然也可以在服务器上直接用上面的步骤，生成脚本）。

19.4.2. sql trace文件的分析方法 851

· 收集到Trace日志后,可以把它拷贝到任何一个安装有SQL Profiler客户端工具的机器上打开。

o 比较重要的数据字段有:

ü EventClass:这个事件的名字

ü TextData：事件的内容。如果事件代表的是一个指令，这里能够看到指令的所有内容

ü SPID: 运行这个事件的连接SPID编号。在SQL里，根据这个编号可以跟踪一个连接

ü CPU：完成语句或某个动作所消耗的CPU时间。从这里可以找到消耗CPU比较多的连接和语句

ü Reads和Writes：完成语句或某个动作所做的读/写次数。注意的是，这里的单位并不是Page，也不是K或KB。SQL里做读和写的时候，会运行到某一段特定的代码。每调用一次这个代码，Reads/Write就会加1。所以这个值比较大，那语句一定做了比较多的I/O。但是不能通过这个值计算出I/O的绝对数量。另外，这个值反映的是逻辑读写量，不是物理读写量。

ü Duration: 完成语句或某个动作所消耗的时间，也就是从开始到结束的时间差。和CPU不同的是，如果语句在运行过程中遇到等待，等待的时间不会记录在CPU里，但是会记录在Duration里。通过这个值可以很容易找到运行缓慢的语句

o 另外，在分析一个Trace文件的时候，可以对里面的记录根据字段的值进行过滤，把感兴趣的记录挑出来。经常设置的过滤条件有，按照应用程序名application name把一个应用发过来的事件都列出来，或者设置duration大于多少毫秒，把时间比较长的语句都列出来等

o 这样的方法在分析一些比较小的Trace文件的时候，是足够的，界面很直观。但是在繁忙的SQL上收到的Trace文件，常常大小是以GB为单位的。有些SQL如果开Trace，每一分钟就能生成100MB甚至更多的日志记录。如果要分析一段时间里SQL的行为，DBA将要面临的是浩如烟海的日志记录，可能有几十个，甚至上百个Trace文件。使用Profiler去分析真的有无从下手的感觉。而且在操作上，打开几十个每个几百兆的日志文件，也是一件很费力的事情

o SQL针对这类情况，提供了一个叫fn_trace_gettable的函数，可以用来把trace文件里的记录像一张表格一样查询出来。我们可以使用它将记录转入到SQL里，然后再用查询语句进行统计分析。

fn_trace_gettable(FILENAME,number_files)，其中filename是要读取的trace日志系列的第一个文件的名字和路径。number_files指的是要读几个日志文件。例如fn_trace_gettable('D:\test.trc',1) 就是指在D:\路径下的test.trc 这一个文件。而fn_trace_gettable('D:\test.trc',default) ，指的是读取D:\路径下的test.trc以及其后续文件。

o 例子

USE [tempdb] --一定要指定tempdb数据库

IF OBJECT_ID('#temp_trc') IS NOT NULL

DROP TABLE #temp_trc

--PRINT '#temp_trc 存在'

--不需要事先存在#temp_trc表

SELECT * INTO #temp_trc

FROM fn_trace_gettable('D:\test.trc',1) --trace文件名字不能是中文!!!!!!!!!

SELECT * FROM [#temp_trc]

o 上述函数返回的数据字段，则是trace日志里包含的所有字段。在指令里，可以在这些字段上设置过滤条件。例如在上面的query中加上WHERE [EventClass] IN(10,12)

o 将trace文件使用fn_trace_gettable函数导入数据库表格的最大好处，是可以将多个日志文件里的记录一次存入数据库，然后就可以利用数据库强大的查询功能，对里面的数据进行分析。如果记录很多，甚至可以在表格上加索引，提高查询速度。这个比一遍又一遍地打开Profiler，设置过滤条件要快多了。

19.4.3. 自动化分析sql trace 862 – ReadTrace

· 每次都要手写查询语句，来分析trace文件里的记录，有很多重复劳动，比较麻烦。微软技术支持部门为了节省大家的时间，开发了一个叫ReadTrace的工具可以自动帮你分析抓来的语句。这个工具可以在下面这个文章里的的连接下载http://support.microsoft.com/kb/944837

· 安装完毕后，在C:\Program Files\Microsoft Corporation\RMLUtils下会有一个小工具叫ReadTrace。这个是cmd命令行工具，要在cmd下运行。

例如要分析在D:\TraceAnalysis里的trace文件，输出也产生在D:\TraceAnalysis下，命令可以是：ReadTrace –iD:\TraceAnalysis\1.trc -oD:\TraceAnalysis -f

--注意参数是 –f,不能是/f

--参数区分大小写

--参数后面要紧挨着参数值，路径不能有双引号。

如果ReadTrace正常执行，会将Trace文件的记录自动导入SQL里的一个叫PerfAnalysis的数据库里，然后再作出计算。在生成目录下会产生ReadTrace.log报表，可以选择 RML Utilities for SQLSERVER – “Reporter”来访问。

o 总体报告（Performance Overview）

o 按Application Name, Database ID, Login Name排序，找出造成最大SQL工作量的Applicaton/Database/Login

o 按照同一类型的语句，统计最昂贵的语句。

ü 单击“Unique Batches”,可以得到一个关于Batch或者存储过程级别的报表。另外，如果你单击任何其中的一条语句，不但能看到该语句的具体事例，还能看到它是在哪些时间被调用的，这个对验证管理员的判断很有帮助。

ü 单击“Unique Statements”,可以得到一个关于Statement级别的报表。

19.5. 系统管理视图跟踪 866

· 既然有了SQL Trace，为什麽还要跟踪这些视图呢？做视图的跟踪有其不可替代的作用。

o 首先，系统管理视图可以提供很多SQL Trace捕获不了的信息，例如，当前所有连接的运行状态，所有打开的事务，每个连接正在运行或上次运行的最后一句指令等。

o 其次，查询这些视图，一般不会对性能造成太大影响，结果输出也比较小。其开销会比开SQL Trace要小很多。

o 所以当问题原因还不很明确，问题又不经常发生的时候，可以先跟踪一段时间的系统视图。对问题有了一点方向以后，再决定是不是真的要捕获一个SQL Trace，以及要捕获什么事件。

· 对DBA比较有帮助的跟踪指令有下面这些：

SELECT * FROM sys.[sysprocesses]

--更精确地，是下面几张视图的联合结果

SELECT * FROM sys.[dm_exec_sessions]

SELECT * FROM sys.[dm_exec_requests]

SELECT * FROM sys.[dm_os_tasks]

SELECT * FROM sys.[dm_exec_connections]

--每个调度进程里的队列以及它们的状态信息Scheduler ID

--如果线程的数目远小于设置的最大数，那就要考虑是不是memtoleave有压力了

--由于线程使用的是memtoleave的内存，确认SQL还有足够的memtoleave

DBCC SQLPERF(umsstats)--User-Mode Scheduling: internal thread scheduler in SQL

--内存分配信息

SELECT type ,

SUM(virtual_memory_reserved_kb) AS [vm reserved] ,

SUM(virtual_memory_committed_kb) AS [vm commited] ,

SUM(awe_allocated_kb) AS [awe allocated] ,

SUM(shared_memory_reserved_kb) AS [sm reserved] ,

SUM(shared_memory_committed_kb) AS [sm committed] ,

SUM(single_pages_kb) AS [singlepage allocator],

SUM(multi_pages_kb) AS [multi page allocated]

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY type

--An example of 跟踪系统性能脚本:

--Save the script to c:\users\xxx\desktop\queryprocess.sql

SET NOCOUNT ON

USE master

DECLARE @i INT

DECLARE @loops INT

DECLARE @delayStr VARCHAR(20)

--根据需求，可以调整@loops和@delayStr的值

SET @loops = 1

SET @delayStr = '00:0:10' --每秒执行一次脚本

DBCC SQLPERF(waitstats,CLEAR)

DBCC SQLPERF(umsstats,CLEAR)

SET @i = 0

WHILE @i < @loops

BEGIN

WAITFOR DELAY @delayStr

PRINT 'START TIME'

SELECT CURRENT_TIMESTAMP

PRINT 'END TIME'

PRINT ''

PRINT 'START SYSPROC'

SELECT *

FROM [sys].[sysprocesses]

PRINT 'END SYSPROC'

PRINT ''

PRINT 'START WAITSTATS'

DBCC SQLPERF(waitstats)

PRINT 'END WAITSTATS'

PRINT ''

PRINT 'START SYSSCHED'

DBCC SQLPERF(umsstats)

PRINT 'END SYSSCHED'

PRINT ''

-- ...

--根据需求加入其它监视语句

SELECT @i = @i + 1

END

Run the following osql or sqlcmd:

osql /E /S /i c:\users\charlie\desktop\queryprocess.sql /o c:\users\charlie\desktop\queryprocess.out /w 3000

· 下面的链接是微软的技术支持工程师为SQL Server不同版本编写的Perf_stats脚本。这些脚本涵盖了大部分有用的SQL运行状态信息，使用广泛。DBA可以用osql或sqlcmd工具调用它来监视SQL运行。做阻塞或性能问题的时候，它的输出很有价值（http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&version=36）。

PerfStatsScript2005 - Mainly Focusing on Current Data

PerfStatsScript2008

PerfStatsScript2008R2 (the same as 2008)

PerfStatsScript2012

PerfStatsScript2014

PerfStatsScript说明：它主要是建立了两个存储过程：

o 一个叫sp_perf_stats09 –列出最耗时的请求及找到阻塞源头，其中#tmp_request存储当前活动的请求，任务，等基本信息，而#tmp_requests2是据#tmp_requests查询当前的活动事务，阻塞的进程。

o 另一个叫sp_perf_stats_infrequent09- 收集sqlserver的性能计数器，如内存管理，CLR等sys.dm_os_performance_counters，CPU运行情况，I/O情况

o 每10分钟执行一次sp_perf_stats09，每小时执行一次sp_perf_stats_infrequent09

· 除了反映一些SQL当前运行状态的管理视图,SQL还提供了很多管理视图,反映SQL自启动以来的历史信息,包括哪些语句比较慢,哪些语句最占资源,哪些数据库文件的读写最频繁,最容易出现I/O问题,哪些表格修改最多,最容易出现阻塞问题等等。通过这些视图，可以从一个独特角度，了解SQL的运行状况。对解决问题也有帮助。其中几张核心的DMV是：

--关于现在SQL缓存有执行计划的指令，它们的执行历史记录，包括CPU, READS, WRITES, 编译重编译等

--从这个视图可以了解语句执行历史

SELECT * FROM sys.[dm_exec_query_stats]

--表格以及其索引的查询，修改情况，以及上面发生过的I/O，阻塞等统计信息。从这个视图可以了解数据库到底哪些表最繁忙

SELECT * FROM sys.[dm_db_index_operational_stats](DB_ID('AdventureWorks'),NULL,NULL,NULL)

SELECT * FROM sys.[dm_db_index_usage_stats] WHERE [database_id]=DB_ID('AdventureWorks')

--以文件为单位，统计各个文件上发生过的I/O及其I/O等待。可以知道在SQL层面，哪个数据文件最繁忙

SELECT * FROM sys.[dm_io_virtual_file_stats](DB_ID('AdventureWorks'),NULL)

--可以计算出tempdb的使用情况。注意：这几个DMVs在2005 – 2008 R2中只适用于tempdb。

SELECT * FROM sys.[dm_db_file_space_usage]

--WHERE [database_id]=DB_ID('AdventureWorks')

SELECT * FROM sys.[dm_db_session_space_usage]

--WHERE [database_id]=DB_ID('AdventureWorks')

SELECT * FROM sys.[dm_db_task_space_usage]

--WHERE [database_id]=DB_ID('AdventureWorks')

--内存里数据页面的缓存情况

SELECT * FROM sys.[dm_os_buffer_descriptors] WHERE [database_id]=DB_ID('AdventureWorks')

· More scripts from others

http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f%5B0%5D.Type=Tag&f%5B0%5D.Value=SQL%20Server%202005

· Scripts from the author: Sample Scripts - Mainly on Cumulative Statistics

o DMV.sql 。它可以把对DMV的查询结果存储在一个数据库里。

o QueryDMVDB.sql，是查询上面存储在表中的结果，方便做离线的问题分析。通过这套脚本，可以收集到不少SQL的运行信息。比较有趣的是：

§ 一个SQL里修改最多的表格和索引是哪些

§ 发生最多阻塞的表格和索引

§ 每个数据库文件上发生的I/O等待统计

§ SQL认为表格应该加的索引

§ 当前内存里缓存的数据页面有哪些

19.6. sqldiag工具 868 、

· 前面介绍了很多种对分析SQL问题有帮助的日志信息，以及它们的收集方式每种信息都有用，可是收集的方式又各有不同。有些是要在问题发生前开启，有些是要在问题发生以后收集的。有些是图形工具，有些又是运行指令。

· 在笔者做技术支持的开始几年里，就要使用一段模版，一步步地教客户怎麽把需要的信息收集全。模板的长度有好几页。那时候信息收集很头痛。这个问题随着一个工具的出现而得到了解决。有个微软内部工具，叫SQLDiag /PSSDiag，可以自动收集很多信息。因为这个工具非常好用，所以在SQL2005里，PSSDiag扩展了原来的SQLDiag应用，增强了其信息收集的能力。

· SQLDiag可以收集的信息有：

（1）SQL Server的default trace

（2）Windows事件日志

（3）SQL曾经产生的DUMP文件

（4）服务器系统配置信息

（5）同时包含有系统和SQL性能计数器的性能日志

（6）服务器端Trace

（7）SQLDiag文本文件

其中，SQLDiag文本文件中包含SQL Server所有错误日志的日志，SQL Server的配置，以及一些重要的动态视图(四、五十个)。

所以本章前面讲的内容，这个工具都可以自动收集。而且它可以帮助DBA自动开服务器端Trace，比用Profiler要安全多了。

· SQLDiag工具默认安装在：C:\Program Files\Microsoft SQL Server\90\Tools\Binn。需要在命令行窗口运行。如果一台服务器从来没有运行过这个工具，可以在命令行直接运行\SQLdiag.exe，工具会使用默认的配置，收集系统信息。当出现SQLDIAG Collection started. Press Ctrl+C to stop. 信息以后就按Crtl+C，终止这个工具的执行。SQLDIAG 工具会在Binn目录下产生三个XML文件，并且产生一个SQLDIAG子目录，存放刚才收集的信息。

· 使用默认配置（即Binn目录下的SQLDiag.xml）只会收到：

o (1)SQLDiag文本文件（<server>_sp_sqldiag_Shutdown.out）

o (2)SQL曾经产生的dump记录（XXXX_SQLDUMPER_ERRORLOG.log）

o (3)服务器系统配置信息（XXXX_MSINFO32.txt）

o (4)SQL默认开启的Default Trace文件（log_XXX.trc）

· 但是使用另外两个XML配置文件SD_General.xml 和 SD_Detailed.xml，就能够收集到SQL Trace和性能日志。但是这样，产生的文件就会比较大，放在SQL安装目录下就不再合适。

o 需要在SQLDiag里指定输出文件路径。使用的指令是

SQLdiag.exe /I <configure_file> /O <output_directory>

例如：SQLdiag.exe /I SD_General.XML /O D:\SQLDIAOUT

o 运行这句话之后，就能看到SQLdiag开启Perfmon（AddingPerfmon counters）和Trace（Starting Profiler Trace）。等SQLdiag成功开启以后，DBA就可以开始尝试重现自己的问题。当问题跟踪下来以后，再按Ctrl+C，工具会停止日志收集。有些信息是在工具关闭前收集的，所以可能要等一会才能完全停止。

o 而在指定的D:\SQLDIAOUT目录，可以看到以下更多信息

ü （1）SQL Trace文件（XXXX_sp_trace.trc）

ü （2）Windows事件日志 – 需要修改SD_General.xml中的设置成True.

XXXX_applog_Shutdown.txt,

XXXX_seclog_Shutdown.txt,

XXXX_syslog_Shutdown.txt

ü （3）Windows性能日志（SQLdiag.blg）用性能监视器也打不开这个文件，要使用系统自带的小工具relog /? 在cmd下运行的，还要将名为SQLDIAG.BLG 的 sqldiag性能日志文件转换为名为 sqldiag.csv的 .csv 日志文件，使用命令：relog D:\SQLDIAOUT\SQLDIAG.BLG -f CSV -o D:\sqldiag.csv

o 两个XML配置文件SD_General.xml 和 SD_Detailed.xml的主要区别是SD_Detailed.xml会在SQL Trace里收集更多的事件，所以输出会要大很多。DBA可以根据需要选择其中一个。

19.7. 强烈推荐：pssdiag工具 873

· SQLdiag虽然能自动化的收集很多信息，但它不会定期查询系统管理视图。另外，sqldiag的配置还是比较麻烦。SQL Server只提供三种模板。如果你只需要做一些个性化的调整，需要手工编辑XML文件。

· PSSDiag仅需要简单的配置和操作步骤就可以收集你所需要的多种信息。

· 下载：http://diagmanager.codeplex.com/

· 安装默认路径：C:\Program Files\Microsoft\Pssdiag\

· 不需要安装在ＳＱＬ　Ｓｅｒｖｅｒ服务器上。推荐ＤＢＡ安装在自己的工作机上。需要使用时，先先配置好要收集的信息，生成自动收集包，再复制到服务器上运行。

19.7.1. 使用pssdiag收集信息 874

· 在安装目录下运行diagconfig.exe，打开PSSDiag的配置界面。

· 配置要收集的信息。

o 选择版本 – 目前只支持2005和2008/R2

o Server Type: X86, X64, or IA64

o Instance Name

o 收集Windows事件日志

o 收集性能监视器日志

o 收集SQLDiag信息

o 收集SQL Trace – 缺点是没法filtering

o 其它诊断信息

· Save，生成pssd.cab文件。

· DBA要将这个压缩文件复制到SQL Server服务器上。

· 在服务器上收集信息

o 解压到一个文件夹中

o 双击、执行pssdiag.cmd文件。

o 成功开启后，可以开始尝试重现自己的问题。

o 当问题被跟踪下来后，Ctrl+C停止日志收集。

o Pssdiag工具会在pssdiag.cmd所在目录生成一个output文件夹，并把所有收集到的数据放在该文件夹里。从文件夹的名称很容易了解到每个文件里包含的是什么信息。

19.7.2. 自动化分析pssdiag收集的信息 876

· 可以使用SQLNexus来自动化地分析PSSDIAG的部分输出内容。

· 要用SQLNexus，需先安装readtrace工具，因为SQLNexus需要调用ReadTrace来自动化分析SQL Trace文件。

· 使用SQLNexus的步骤：

o 下载：http://sqlnexus.codeplex.com/

o 解压，双击sqlnexus.exe运行这个工具。

o 连接SQL Server。因为SQLNexus会把PSSDiag收集到的一些信息导入到数据库中做进一步的分析，因此，这里需要指定一个SQL Server实例。SQLNexus会创建一个叫SQLNexus的数据库来存放这些信息。

o 将PSSDiag的数据导入到SQLServer中。

o 导入完成后，使用左侧Reports列表中的各个连接来查看各项分析结果。例如：

ü 对SQLServer中发生的阻塞和资源等待的分析。

ü 对SQLServer瓶颈的分析

ü 对PerfStats脚本输出内容的分析

ü 对SQL Trace的分析，这里其实是直接使用的readtrace的报表。

· SQLNexus最大的作用在于，它可以使你对SQL Server的总体性能有个大致了解。如果你收集了PSSDiag，强烈建议你使用SQLNexus先做一个自动化的分析。根据分析结果，再从PSSDiag的输出文件中找到对应的日志，然后进行详细的分析和检查。

· 其它有关背景资料

o What is SQL Nexus?

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data. Visit Getting Started page.

o SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag

o What is SQLDiag?

In SQL Server 2005, the SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. For more information about sqldiag, please refer to SQL Server books online documentation:http://msdn2.microsoft.com/en-us/library/ms162833.aspx.

Though default templates are available, shipped products don't have a configuration user interface that would allow you to customize what profiler trace events or perfmon counters to collect. If you want to customize data collection, please use pssdiag/sqldiag manager from codeplex.

o What is PSSDIAG?(早先，SQLDiag只是PSSDiag的一部分，从2008开始，PSSDiag的功能扩展了，并取代了SQLDiag)，

PSSDIAG is a diagnostics utility used to collect profiler trace, perfmon data for SQL Server. PSSDAIG was created to troubleshoot SQL Server 7.0 and 2000 issues. It was evolved and shipped with SQL Server 2005 and beyond. But the shipped product only has a data collector engine called sqldiag.exe. Microsoft Product support continues to maintain a configuration tool that allows engineers to dynamically configure on what to collect. Due to customers' demand, this configuration tool has released under codeplex. please go to Pssdiag/SqlDiag Manager project

If you troubleshoot SQL Server 2000 performance problems, you can continue to use an older version of pssdiag (http://support.microsoft.com/kb/830232)

o 另外，RML取代了ReadTrace。What is RML?

The Microsoft SQL Server support team uses several internally written utilities to ease the work that is related to a typical customer support case. This article describes one utility suite that is called the Replay Markup Language (RML) Utilities for Microsoft SQL Server. Database developers and system administrators can use the RML Utilities for SQL Server to work with SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 CTP2.

o 安装和使用Steps 1-2-3

ü Install SQL Nexus and RML Utilities

ü Collect perf data from your SQL Server 2005/2008/2008R2/2012 instance using SQLDiag

ü Import and analyze the data using SQL Nexus

19.8. 系统自动监视工具——performance dashboard

· DMV的缺点是多且复杂。Performance dashboard这个工具可以产生形象、直观化、user-friendly的Customer Reports 。具体安装和使用步骤在各版本的说明见下。

· SQL Server 2005

http://www.mssqltips.com/sqlservertip/1553/installing-sql-server-2005-performance-dashboard-reports/

· SQL Server 2008 and R2

http://www.mssqltips.com/sqlservertip/2896/where-do-i-find-the-performance-dashboard-for-sql-server-2008/

· SQL Server 2012

http://www.mssqltips.com/sqlservertip/2670/install-sql-server-2012-performance-dashboard-reports/

19.8. 小结

· 在任何情况下，都需要收集SQL Server的errorlog。对于除性能问题以外的SQL Server问题，errorlog都能提供直接或间接的信息帮你定位。对于性能问题，errorlog也常常能显示出当前系统额性能瓶颈所在。对于排查那些整体性能不佳的问题，性能瓶颈是非常重要的突破口。

· Windows事件日志主要用于排查SQL Server异常宕机、无法启动一类的问题。

· PSSDiag是排查性能问题的神兵利器，如果在发生问题的时候能成功收集到一份PSSDiag的日志，对解决问题大有帮助。但PSSDiag有局限性：（1）它的日志较大，不适合长时间运行。如果性能问题发生的时间非常随机，并且问题出现后会很快消失，PSSDiag就不合适。（2）它会带来一定的负载。对于本身性能问题已经很严重的情况下，尤其是排查100% CPU问题时，要慎用。

· 造成PSSDiag日志大、负载高的主要原因其实是它包含的SQL Trace。在PSSDiag不能使用的情况下，可以单独运行性能监视器和Perf_stats脚本来收集性能数据。这两个工具带来的额外负载小，日志量相对较小，可以长时间运行。对那些那些问题随机，又不适合用PSSDiag的情况，性能监视器+Perf_stats脚本可以有效地定位问题。

· 对于CPU使用率高、个别语句执行缓慢的问题，如果不适合使用PSSDiag，可以依赖9.5节中的DMVs，Perf_stats及Perf_stats_snapshot脚本来收集信息（可以去http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&version=36下载，解压后就会看到Perf_stats及Perf_stats_snapshot脚本）。这些脚本和视图的输出结果包含系统中最消耗I/O资源、CPU资源和执行时间最长的语句、阻塞信息、等待状态、缺失索引等。

SQL Server 2012实施与管理实战指南摘要 - 第 19 章