笔记56-徐 常用SQL系统信息与搜集方法 P455 第14章 Windows事件日志，SQL ErrorLog，性能监视器 ，PSSDiag，Nexus

笔记56-徐  常用SQL系统信息与搜集方法 P455  第14章 Windows事件日志，SQL ErrorLog，性能监视器 ，PSSDiag，Nexus

--常用SQL系统信息与搜集方法 P455  第14章 Windows事件日志，SQL ErrorLog，性能监视器

--作为一个可支持性比较高的应用软件，SQL在最近的几个版本里，
--不断地开放出系统状态记录新功能，供DBA实时地了解SQL当前的运行
--状态，或者分析过去某一段时间内的运行历史。所以关于SQL的系统
--运行信息，是非常丰富的。

--在这些信息里，有些概况性信息是SQL会自动记录的。例如SQL什么时候
--开启的，上次是怎麽被关闭的，是否出现过严重的系统异常，从SQL服务
--启动以来，数据库做过的读写总量等。


--大部分具体信息必须打开一些开关才能生成。例如，每个用户的登入登出
--记录、运行的每一条语句的编译、执行情况等。DBA有很大的灵活性，可以
--根据自己关心的问题，定制和搜集需要的系统信息。

--DBA要花一些功夫，学习处理各类问题所要收集的信息，以及他们的搜集
--和分析方法。开启有些信息搜集可能会影响系统性能，所以收集他们必须
--有目的性，也要选择合适的手段

--所以作为一个比较独立的章节，这里总结了DBA可能会用到的大部分信息
--收集方法，供读者参考。这些方法有手工收集的，也有自动收集的。


--常用的系统信息有：
--（1）Windows事件日志

--（2）SQL ErrorLog

--（3）性能监视器


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


--在谈论内存、CPU、I/O、阻塞等章节里，曾经介绍过SQL的系统管理视图。他们
--从另一个角度，反应SQL的运行状态。有些信息对定位和解决问题非常重要
--后面会介绍几个最有用的DMV，以及他们的查询脚本

--SQLSERVER的支持工程师在处理问题时，一般会收集以上所有的信息。但是手工
--收集步骤非常麻烦，重复性劳动很多。在SQL里有一个自动信息收集工具：

--SQLDiag
--可以自动收集大部分有用的信息。

--最后还要介绍SQL的另一个自动化监视工具：Performance Dashboard

--利用这个工具，可以自动产生一些报表，帮助DBA做一些基本的分析工作


--1、Windows事件日志 Event Log
--作为一个Windows开启和管理的服务程序，Windows会在自己的系统日志system log
--里记录SQL这个服务的启动、正常关闭、异常关闭等信息。SQL也会把自己的一些
--概要信息同时记录在Windows的应用程序日志里Application Log

--而Windows日志本身又能够反映操作系统的健康情况，是否有任何软件或硬件的异常
--如果Windows本身不能正常工作，SQL的运行一定会受到影响。例如在前面讨论SQL
--数据库损坏的章节里，我们就提到过一些可能会导致数据库损坏的硬件错误信息
--这些信息可以在系统日志里找到。如果Windows本身发生crash，也能在系统日志里
--找到相应记录。这时候连不上SQL就是正常的了。


--所以Windows事件日志是一个很好的界定问题性质的工具。当DBA要对SQL做健康检查
--时，首先要检查的不是SQL自己的日志，而是Windows事件日志。当确定Windows日志
--里没有明显的错误和警告以后，再去看后面SQL日志信息。如果在Windows日志里发现
--明显错误或者警告，就需要和系统工程师一起，确定他们产生的原因，以及避免的
--方法

--在Windows里运行eventvwr.msc /s或者eventvwr /s或者eventvwr
--/s 参数的意思是 远程机器
--就可以开启事件查看器Event Viewer。在Windows7和Windows2008R2里面，界面
--会有所不同，但是主要内容还是类似的


--Windows主要有三种日志：Application、Security、System、应用程序，安全，系统
--对于SQL会主要关心应用程序日志和系统日志。当处理一些连接认证问题时，可能会
--偶尔用上安全日志。日志里的每一条记录，都属于Information、Warning、Error
--中的一类。记录会标明日期、时间、来源。如果在应用日志里，从SQL产生的记录
--其来源名称都会是MSSQLSERVER。双击某一条记录，Windows会弹出一个对话框，
--显示记录的具体内容


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


--需要啰嗦一句，用事件日志查看器打开的日志，其时间会和时区有关系。不同时区
--设置的机器打开一个*.evt文件，其显示的时间会不一样。例如，如果某个错误
--信息发生在美国的白天，那么用在中国的机器打开，其时间会显示在晚上。如果
--你按美国时间找，就会找不到了。但是保存成 *.txt格式 文本文件格式就不会
--有这种问题




--2、SQLSERVER ErrorLog文件
--检查完Windows的基本状况后，就可以开始检查SQL的健康状况了。不管你是遇到
--什么问题，建议第一个要检查的是SQL的ErrorLog文件。
--当SQL启动的时候，会在某个固定的路径下生成一个“errorlog”的文件。SQL默认
--会保留7份errorlog文件，按照时间顺序，依次用文件扩展名.1,.2,.3,...,.6
--表示。每重启一次服务，文件扩展名都会加一。最早的那份，会被删除


--日志文件的默认路径是安装路径下的C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
--\LOG子目录。当然DBA也能够修改其设置（在配置管理器里，双击sql服务-》高级-》转储目录）。
--如果你要分析的是一台陌生的服务器，可以用很多种方法找到errorlog路径。
--一种比较简单的方法。是在SQL 配置管理器里选择SQL服务，在其属性-》高级里找到一个
--“启动参数”的高级属性


--在属性字符串里，会有一个“-e”的参数。他的后面就是跟errorlog文件的位置

--errorlog文件以文本方式记录。用任何文件编辑器，包括记事本，SSMS都能打开
--一般来讲，errorlog文件的大小不会很大。用这些工具完全能够满足需求。但是
--errorlog本身非常重要，他记录了SQL的整个开启、运行、终止过程。如果SQL
--遇到了比较严重的问题，在errorlog里都会有所显示


--前面已经介绍过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里也不是什么问题都能记录。他不能反映的问题有：
--（1）阻塞问题。只要阻塞还没有严重影响SQL的线程调度，errorlog里是不会有体现的

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

--（3）Windows层面异常。如果Windows层面出现工作不正常，或者服务器不响应，SQL是
--很难自我判断的。errorlog里一般不会有所体现。这也是我们为什麽要第一步就要检查
--Event Log的原因

--所以errorlog只是分析SQL问题的一个很好的出发点。对于SQL，还要结合其他丰富的
--信息记录功能，综合分析问题


--3、性能监视器 Performance Monitor
--性能监视器是Windows的一个工具，在系统管理工具组里。默认里面就有很多Windows层面
--的性能计数器，可以监视系统的运行。直接运行"perfmon"，也可以打开他。这里以
--WindowsXP/2003/2008的性能监视器为例。Windows2008R2和Windows7的性能监视器界面
--有了比较大的变化，功能也有扩展，更加好用。同时也完全向前兼容。后面谈到的功能
--都有包括

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

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


--通常可以使用下面这些步骤：
--（1）在性能监视器左边的窗口，展开性能 日志和警告子树，点击“计数器日志”
--在右边的窗口里，右键点击，选择“新 日志设置”，他会弹出一个对话框，让你
--为新的日志记录配置命名。这里我们取名为Test


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


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

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

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

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


--有些DBA会担心，抓这麽多计数器会不会影响性能。应该说根据经验，性能监视器对
--系统整体性能的影响几乎感觉不到。所以可以比较放心大胆地多收一些计数器。
--基本工作原理是在.NET编译出的IL代码里放入钩子用来记录时间，然后通过直观的界面显示出哪部分代码耗能最大。
--只是间隔可能还是选15秒比较安全



--（4）设置文件的位置和最大大小
--另一个重要配置，是日志文件存放在哪里，保存格式，以及最大大小。如果
--性能日志文件大小超过1GB，可能有些机器打开会很慢。所以一定要注意其最大值
--可以设为200MB。如果一个200MB的文件写满，性能监视器会自动创建一个新的。
--文件格式可以选二进制文件

--日志搜集当然可以手动开始和终止。但是如果问题会发生在半夜，最好能让系统
--自动开启，自动关闭。性能监视器也可以帮DBA做到这一点


--当得到一个性能日志后，可以在性能监视器里选择 查看 日志 数据

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

--然后点击数据选项卡，就能看到在原来那台服务器上收集的性能计数器了
--这时候再点击“源”选项卡，能看见性能日志文件所包含的那段时间。拉动滚动条，可以
--把时间段缩短到DBA最关心的那段时间

--对收集到的日志，DBA可以进行分析



--4、SQL Trace文件
--SQL的一大优点，是能够把SQL里发生的很多事件记录下来。而记录下的日志
--通常被称为SQL Trace文件。在SQL安装的管理工具里，有一个叫SQLSERVER Profiler
--的工具，可以用他来搜集和分析SQL Trace文件。这个工具比较直观，我们就
--开始介绍SQL Trace  开始菜单-》性能工具-》数据库引擎优化顾问的下面

--（1）SQL Trace文件的收集方法
--首先，SQL Trace里能够有哪些事件呢？在Profiler里新建一个Trace，在
--在事件选择里选择“显示所有事件”，就能看到一个清单
--里面的事件分类，在SQL2005这个版本的时候已经有21个之多，而每个分类下
--又有不同数目的事件。可以说，DBA想要看到的事件，基本上都能覆盖。可是
--事件太多，如果所有的事件都收集，产生的SQL Trace会非常庞大，SQL就受不了
--这里总结一下，在做不同的问题分析时，经常要用到的事件


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


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

--这个事件组也能从一个角度反映性能问题。例如，attention事件记录了每一个客户端
--取消的请求。运行超时command timeout就是其中一个类型。如果你发现
--一个语句运行了15秒或30秒，然后紧跟着一个attention事件，就说明这里发生了
--一个客户端的command timeout。而hash warning，missing column statistics，
--missing join predicate，sort warning很可能伴随着一个运行速度不理想的语句


--Locks事件组
--dead lock graph，lock：deadlock，lock：deadlock chain
--这三个事件是跟踪死锁的。因为死锁在SQL里发生的频率不会太高，所以在做
---死锁问题的时候，可以把他们三个都选上。但是要注意，要先选上
--“显示所有列”，再选事件，因为有些重要的字段默认的模板里没有
--选上。
--Lock:Timeout和Lock：Timeout（timeout>0）
--在发生阻塞的时候，会有Lock Timeout事件发生。可是，阻塞是SQL里为了实现
--事务隔离所需发生的事件，所以阻塞在SQL里发生得非常普遍。收集这两个事件
--对问题分析的帮助不会太大。还不如用性能监视器里SQLSERVER：Locks-Lock Timeouts/sec
--这个计数器看一个总的趋势。所以在实际使用中，很少选他们
--Lock：Acquired 、Lock:Cancel、Lock:Escalation、Lock:Released:
--这些事件能够跟踪一句语句在运行过程中对锁资源的申请和释放过程。
--但是在繁忙的生产环境里，SQL会申请大量的锁资源。所以这些事件会产生大量
--记录。通常情况下，只会在测试环境里，测试单条语句时，才敢把他们加上。
--在生产环境上，要尽可能避免使用他们


--“Performance”事件组
--“Performance”事件组里的事件主要分两类：Auto Stats能够记录SQL里发生的自动
--创建或更新统计信息的事件。其他有showplan字样的，是关于各种形式的执行计划
--以及运行信息。他们的相同点和不同点要有目的地选择，不要重复收集
--需要注意的是，执行计划一般都比较大，而每一条语句执行，都会有他的执行计划
--所以如果要收集执行计划，结果日志肯定会很大。所以一定要在必要的时候，才
--加入执行计划事件


--“Security Audit”事件组
--这一组事件的目的，是监视SQL里各项和安全有关的事件，例如有人加入了一个
--DB User、一个Login，有人做了数据库备份、DBCC动作，有人修改了用户密码等
--如果要对SQL做安全监控，这些事件都是要考虑的
--如果是要一般地监视运行，可能要选择的只有Audit Login和Audit logout
--通过这两个事件，我们能够看到一个连接的生命周期。如果有用户抱怨连接
--失败，也可以跟踪Audit Login Failed。如果连接请求是被SQL拒绝的，可以
--看到拒绝的时间和理由


--“Server”事件组
--他的下面只有三个事件，Mount Tape、 Server Memory Change 、Trace File Close
--这三个事件在SQL里发生的频率都不会很高，所以加进来也不会有很大影响
--尤其是Server Memory Change，如果发生，对SQL性能的影响不会很大。所以
--这个事件是可以经常收集的。当然，如果你同时收集了性能监视器日志，那个
--日志里也会有包含

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

--“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里的信息。理论上讲，只收
--RPC:Completed就可以了。但是如果连接非正常地退出，或者遇到了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事件。当问题有了方向之后，再加入
--更多的事件，有目的地收集和分析

--“TSQL”事件组
--这个事件组也很重要。他的事件也分两类
--和编译、重编译相关的：
--Exec PreparedSQL
--Prepare SQL
--SQL:StmtRecompile
--Unprepare SQL
--其中，SQL:StmtRecompile比较常用


--关于批处理执行的：
--SQL:BatchCompleted  SQL:BatchStarting  RPC:Completed
--RPC:Starting类似
--SQL:StmtCompleted SQL:StmtStarting
--SP:StmtCompleted SP:StmtStarting类似
--相似地，在问题定位阶段，一般不会加入SQL:StmtCompleted SQL:StmtStarting
--而且，为了减少事件的数目，常常只收Completed事件，不收Starting事件
--当问题有了方向之后，再加入更多的事件，有目的地收集和分析


--“Transactions”事件组
--常用的事件有：
--DTCTransaction：分布式事务的生命周期。正常来讲MSDTC事务在SQL里比较少，而且
--容易出问题。所以可以默认就收集他


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


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

--所以这里来总结一下，对于一般性问题，作者建议收集的事件有哪些
--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

--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，建议把
--所有字段都选上




--收集下这些事件保存在哪里呢？这也是一个非常重要的设置。在一个繁忙
--的生产环境里如果直接用SQL Profiler开一个Trace但不保存，可能会严重
--影响性能。搞不好整个SQL服务器都会没有响应，所以这种行为要被严格禁止

--比较安全的做法是在Trace属性里做下列必须的设置：
--（1）设置Trace将要以文件形式保存，指定文件的名字和路径
--（2）设置文件的最大上限，并且设置file rollover
--日志文件太大，分析起来会很吃力。所以要设置一个上限，一般设置为300MB到500MB
--当文件写满的时候，SQL会自动生成一个新的日志文件
--（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可以连接局域网里的任何一台SQL服务器，所以属于客户端


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


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

--首先，请管理员在任何一台测试机上定义一个Trace，主要是选择好要跟踪的事件
--和他们的字段。如果有过滤条件，也可以设置。接着，选择 run 开启这个Trace
--紧接着就可以停止他。在Profiler界面里，选择选择导出脚本定义，Profiler
--会帮助你生成一段能够开启相同定义的服务器端Trace的脚本（如果在生产服务器上
--开启Profiler Trace没问题，当然也可以在服务器上直接用上面的步骤，生成脚本）


---------------------------------------------------------------------------
--SQL Trace文件的分析方法

--注意:trace文件名字不能是中文!!!!!!!!!


--收集到Trace日志后,可以把他拷贝到任何一个安装有SQL Profiler客户端工具的机器上
--打开
--比较重要的数据字段有:
--EventClass:这个事件的名字

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

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

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

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

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


--其他字段可以在联机丛书里找到解释

--另外，在分析一个Trace文件的时候，可以对里面的记录根据字段的值进行过滤，把感兴趣的
--记录挑出来。这个功能在跟踪属性-》事件选择选项卡里选择列过滤按钮，就可以进行选择

--经常设置的过滤条件有，按照应用程序名application name把一个应用发过来的事件都列出来
--或者设置duration大于多少毫秒，把时间比较长的语句都列出来等

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


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

--函数语法：
fn_trace_gettable(FILENAME,number_files)

--其中filename是要读取的trace日志系列的第一个文件的名字和路径。number_files
--指的是要读几个日志文件。例如fn_trace_gettable('D:\跟踪死锁.trc',1)
--就是指在D:\路径下的跟踪死锁.trc 这一个文件。而

fn_trace_gettable('D:\跟踪死锁.trc',default)  --跟踪死锁.trc 文件名不能是中文
--指的是读取D:\路径下的跟踪死锁.trc以及其后续文件

USE [tempdb] --一定要指定tempdb数据库
GO
IF  OBJECT_ID('#temp_trc') IS NOT  NULL
DROP TABLE #temp_trc
--PRINT '#temp_trc 存在'
GO


--不需要事先存在#temp_trc表
SELECT * INTO #temp_trc
FROM fn_trace_gettable('D:\1.trc',1)  --trace文件名字不能是中文!!!!!!!!!
GO

SELECT * FROM [#temp_trc]


--函数返回的数据字段，则是trace日志里包含的所有字段。在指令里，可以在这些字段上
--设置过滤条件。例如最常见的是，是一个日志里收集了很多事件，现在我们只关心其中
--几个。那可以在eventclass这个字段上设置过滤条件。在联机丛书里找sp_trace_setevent
--这个系统存储过程的定义，可以找到所有事件的编号。例如下面指令，可以将
--D:\路径下的1.trc以及后续文件中的RPC:Completed和SQL:BatchCompleted事件记录
--保存在当前数据库的Sample表里

USE [tempdb] --一定要指定tempdb数据库
GO
IF  OBJECT_ID('#temp_trc') IS NOT  NULL
DROP TABLE #temp_trc
--PRINT '#temp_trc 存在'
GO


--不需要事先存在#temp_trc表
SELECT * INTO #temp_trc
FROM fn_trace_gettable('D:\1.trc',1)  --trace文件名字不能是中文!!!!!!!!!
WHERE [EventClass] IN(10,12)
GO

SELECT * FROM [#temp_trc]



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


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

--http://support.microsoft.com/kb/944837

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

--例如要分析在D:\TraceAnalysis里的trace文件，输出也产生在D:\TraceAnalysis下，命令可以是：
--注意参数 是 -f  不能是/f
--参数区分大小写
--参数后面要紧挨着参数值，路径不能有双引号： -oD:\TraceAnalysis

>"C:\Program Files\Microsoft Corporation\RMLUtils\ReadTrace.exe" -ID:\TraceAnalysis\1.trc  -oD:\TraceAnalysis -f


--如果ReadTrace正常执行，会将Trace文件的记录自动导入SQL里的一个叫PerfAnalysis的数据库里
--然后再作出计算。在生成目录下会产生ReadTrace.log文件

--生成的报表，可以选择 RML Utilities for SQLSERVER ->Reporter来访问

--注意，要确保当前你的数据库有PerfAnalysis 数据库！！！！！！！！！！！！！！！！！





---------------------------------------------------------------------------------------
--系统管理视图跟踪
--在介绍阻塞的时候，已经介绍过系统管理视图sysprocesses的作用。
--在第11章进一步介绍了他在初步判断系统瓶颈时所起到的独特作用。所以，
--如果要监视SQL的运行状态，每隔一段时间就查询一下这张视图，能够收集到
--很多有用的信息。尤其当问题不知道什么时候出现，DBA不能随时盯着服务器时，要
--事先定义一些脚本，让他们在SQL上运行，把结果输出到文本文件里，以供事后分析


--有些读者会问，既然有了SQL Trace，为什麽还要跟踪这些视图呢？做视图的跟踪
--有其不可替代的作用。首先，系统管理视图可以提供很多SQL Trace捕获不了的信息，
--例如，当前所有连接的运行状态，所有打开的事务，每个连接正在运行或上次运行
--的最后一句指令等。其次，查询这些视图，一般不会对性能造成太大影响，结果输出
--也比较小。其开销会比开SQL Trace要小很多。所以当问题原因还不很明确，问题又
--不经常发生的时候，可以先跟踪一段时间的系统视图。对问题有了一点方向以后，再
--决定是不是真的要捕获一个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]


DBCC SQLPERF(waitstats)
--SQL 各种等待状态出现的次数的统计

DBCC SQLPERF(umsstats)
--每个调度进程里的队列以及他们的状态信息 Scheduler ID
--参见第6章节
--（2）：如果线程的数目远小于设置的最大数，那就要考虑是不是memtoleave有压力了
--由于线程使用的是memtoleave的内存，确认SQL还有足够的memtoleave
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

--内存分配信息
-------------------------------------------------------------------------------------------


--跟踪系统性能脚本:
SET NOCOUNT ON
GO
USE master
GO

DECLARE @i INT
DECLARE @loops INT
DECLARE @delayStr VARCHAR(20)

--根据需求，可以调整@loops和@delayStr的值
SET @loops = 60000
SET @delayStr = '00:0:10'  --每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


--DBA可以将这段脚本保存成一个叫queryprocess.sql的批处理文件，然后用osql
--或者sqlcmd来调用。比如用osql，执行的命令是：
--osql /E /S /i queryprocess.sql  /o queryprocess.out /w 3000

--在下面这个链接上，有一个叫PerfStatsScript.sql的监视脚本可供下载。这个脚本
--里涵盖了大部分有用的SQL运行状态信息，使用广泛。DBA可以用osql或sqlcmd工具调用他
--来监视SQL运行。做阻塞或性能问题的时候，他的输出很有价值
--http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005performance-statistics-script.aspx
--除了反映一些SQL当前运行状态的管理视图,SQL还提供了很多管理视图,反映SQL自启动以来
--的历史信息,包括哪些语句比较慢,哪些语句最占资源,哪些数据库文件的读写最频繁,最容易
--出现I/O问题,哪些表格修改最多,最容易出现阻塞问题等等。通过这些视图，可以从
--一个独特角度，了解SQL的运行状况。对解决问题也有帮助



--其中几张核心的DMV是：
SELECT * FROM sys.[dm_exec_query_stats]
--关于现在SQL缓存有执行计划的指令，他们的执行历史记录，包括CPU,READS,WRITES,编译重编译等
--从这个视图可以了解语句执行历史

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，阻塞等统计信息。从这个视图
--可以了解数据库到底哪些表最繁忙

SELECT * FROM sys.[dm_io_virtual_file_stats](DB_ID('AdventureWorks'),NULL)
--以文件为单位，统计各个文件上发生过的I/O及其I/O等待。可以知道在SQL层面，哪个数据文件最繁忙


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')
--可以计算出tempdb的使用情况。


SELECT * FROM sys.[dm_os_buffer_descriptors] WHERE [database_id]=DB_ID('AdventureWorks')
--内存里数据页面的缓存情况

--对这些管理视图写查询，也是一件比较复杂的事情。在下面这个链接里
--有很多脚本的范例。理解别人写的查询，是一个学习的好切入点
--http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f%5B0%5D.Type=Tag&f%5B0%5D.Value=SQL%20Server%202005



--笔者自己也总结过一套脚本DMV.sql 。他可以把对DMV的查询结果存储在一个数据库里，再通过语句查询
--QueryDMVDB.sql，方便做离线的问题分析。读者可以在本书的范例里找到。通过这套脚本，可以收集
--到不少SQL的运行信息。比较有趣的是：
--（1）一个SQL里修改最多的表格和索引是哪些

--（2）发生最多阻塞的表格和索引

--（3）每个数据库文件上发生的I/O等待统计

--（4）SQL认为表格应该加的索引

--（5）当前内存里缓存的数据页面有哪些


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

--在笔者做技术支持的开始几年里，就要使用一段模版，一步步地教客户怎麽
--把需要的信息收集全。模板的长度有好几页。很多客户看了如此复杂的步骤
--就被吓回去了。下定决心要收集的客户，也往往折腾几次，才能把所有步骤
--都做对。那时候信息收集很头痛

--这个问题随着一个工具的出现而得到了解决。和微软SQL技术支持打过交道
--的读者可能知道，有个微软内部工具，叫PSSDiag，可以自动收集很多信息。
--因为这个工具非常好用，所以在SQL2005里，扩展了原来的SQLDiag应用，
--增强了其信息收集的能力



--SQLDiag可以收集的信息有：
--（1）Windows事件日志
--（2）SQLSERVER ErrorLog，以及SQL配置信息，一些重要运行信息
--（3）SQL曾经产生的DUMP文件
--（4）服务器系统配置信息
--（5）同时包含有系统和SQL性能计数器的性能日志
--（6）服务器端Trace


--所以本章前面讲的内容，这个工具都可以自动收集。而且他可以帮助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）只会收到：
--(1)SQL errorlog文件内容，以及SQL配置信息和一些重要运行信息（XXXX_sp_sqldiag_Shutdown.out）
--(2)SQL曾经产生的dump记录（XXXX_SQLDUMPER_ERRORLOG.log）
--(3)服务器系统配置信息（XXXX_MSINFO32.txt）
--(4)SQL默认开启的Default Trace文件（log_XXX.trc）

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

--使用的指令是：
SQLdiag.exe /I <configure_file> /O <output_directory>
--例如：
--SQLdiag.exe /I SD_General.XML /O D:\SQLDIAOUT


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


--而在指定的D:\SQLDIAOUT目录，可以看到以下更多信息
--（1）SQL Trace文件（XXXX_sp_trace.trc）
--（2）Windows事件日志（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



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

--SQLdiag的唯一缺点，是不会定期查询系统管理视图，所以建议的自动化信息收集方式
--是SQLdiag（使用SD_General.xml 和 SD_Detailed.xml做配置文件），在加上
--PerfStatsScript.sql的监视脚本的输出。用这两个手段，就能够比较全面地收集
--系统信息了



-----------------------------------------------------------------------------------------
--系统自动监视工具------Performance Dashboard
--使用系统管理视图的缺点是：个数很多，定义复杂，视图和视图
--之间的关系也要研究一番才能弄懂。如果有什么工具，能够自动
--从这些视图里获取信息，然后以最终用户看得懂的形式展现，那就好了

--幸运的是，在SQL2005上微软开发了一个这样的工具，叫Performance Dashboard
--没有用过的DBA，建议使用一下，他的功能不是很花哨，但是还是挺实用的

--在微软网站上搜寻Performance Dashboard，可以找到下面链接
--http://www.microsoft.com/en-us/download/details.aspx?id=22602

--可以免费下载这个工具。他支持SP2以后的SQL2005版本。安装后，在
--C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard
--子目录下会看到很多报表定义文件（*.rdl）。注意他们是32位的。如果
--你的SQL服务器是64位的，报表文件会在
--C:\Program Files(x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard
--子目录下会看到很多报表定义文件（*.rdl）下面

--第一次使用，需要在SSMS里注册一个报表。具体的步骤是，右键点击服务器名字
--然后选择报表-》自定义报表

--选择路径C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard
--下的performance_dashboard_main.rdl

--做完以后，再选择报表，就能看到右键菜单多了performance_dashboard_main的入口报表
--再执行C:\Program Files(x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard
--路径下的setup.sql

--报表的内容主要分三个部分：
--Current Activity：当前SQL里的运行状态，包括现在的CPU使用，连接的等待状态，阻塞等


--Historical Information:SQL从上次启动以来记录下的一些历史信息，包括等待状态，比较复杂的语句等

--Miscellaneous Information:有三个报表，分别反映当前SQL开启的Trace，每个数据库状态，以及
--SQL认为的数据库上缺少的索引

--很多之前讲到的查询，在这里都有现成的报表可以直接使用。读者可以在自己的SQL上
--试试是不是喜欢。这里只举一个例子。



--Historical Information里选择By Duration，就能够看到SQL所收集的，历史上运行时间
--总和最长的语句。这些报表和ReadTrace的结果很像



--Performance Dashboard是SQL2005版本上的工具。那在SQL2008上又是用什么呢？
--在SQL2008里，默认把这个功能实现在了 Management Data Warehouse组件里
--并且把这个功能有了进一步的提高。在SSMS里，展开管理-》数据收集
---下面默认有三个收集信息的task。把这些task打开以后，过一段时间，
--右键点击数据收集，选择报表，就能看到根据收集到的历史信息所生成
--的几张报表。这些报表和Performance Dashboard是比较类似的


--DBA可以利用这些报表，实时观察自己的SQL运行状态和历史信息，确认服务器
--正在正常运行。如果遇到问题，这也是分析和定位问题的一个很好的出发点


--最后注意，按左上角的刷新按钮，就能不断报表数据，获取服务器最新的状态信息

--SQLSERVER默认会开启Trace，Trace文件路径在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
--最多5个文件，每个文件最大大小：20MB
--自动开启trace以方便自己跟踪自己
--当大小超过了指定大小就会创建新的trace文件，文件名编号加1，最早那个会删除