SQL Server 性能诊断（7） - 信息收集根据正常的排查逻辑，在完成对内存、I/O、CPU、锁和等待事件的分析后

根据正常的排查逻辑，在完成对内存、I/O、CPU、锁和等待事件的分析后，通常可以定位到问题所在。在大多数情况下，问题的根源往往出现在 SQL 层面。因此，接下来应当重点分析导致性能问题的 SQL 语句。

然而，SQL 的性能问题涉及多个方面，包括查询语句的设计、索引的选择、执行计划的优化等多个因素。因此，这一部分的详细分析将作为后续案例进行补充。

在性能优化过程中，缩短排障时间是提升效率的关键。因此，首先需要明确“排障时间”的定义。通常，排障时间可以分为两个主要部分：

定位性能瓶颈的时间
这一阶段是通过各种分析手段，迅速识别系统中的性能瓶颈所在。
解决性能瓶颈的时间
一旦瓶颈被定位，接下来是采取相应的措施，进行优化或调整，从而解决问题。

因此，第一步的核心任务是快速定位问题。为了实现这一目标，首先需要着力于缩短信息收集的时间。通过高效的收集和分析系统性能数据，可以在最短的时间内识别出瓶颈所在，为后续的优化和解决方案奠定坚实的基础。

在《SQL Server 性能诊断（0） - 准备》这一篇中其实已经做了简单的信息收集，不过收集的信息有限，对于性能排障可能用处不大。所以这一篇我主要想做两件事情：

总结管理员可能会使用到的大部分信息收集方法
提供一键式收集信息脚本或者工具（对于不懂SQL Server的人也可以根据指引在问题发生或者正在发生阶段一键收集）

这篇文章还在不断完善中，非常期待各位大佬的宝贵建议！如果发现更好的优化思路或文章中的疏漏之处，还请在评论区不吝赐教，非常感谢您的指点和支持！

1. Windows事件日志

Windows会在自己的系统日志（System Log）里记录SQL Server这个服务的启动、正常关闭、异常关闭等信息。SQL Server也会把自己的一些概要信息同时记录在Windows的应用程序日志（Application Log）里。

而Windows日志本身又能够反映操作系统的健康情况，是否有任何软件或硬件的异常。如果Windows本身不能正常工作，SQL Server的运行一定会受到影响。

在Windows里运行“eventvwr”，就可以开启事件查看器（Event Viewer）。

Windows主要有三种日志：Application、Security和System。对于SQL Server，会主要关心Application Log和System Log。当处理一些连接认证的问题时，可能会偶尔用上Security Log。日志里的每一条记录都属于Information、Warning或Error中的一类。记录会标明日期、时间及来源。如果在应用日志里，从SQL Server产生的记录，其来源名称都会是“MSSQLSERVER”（默认实例）或是“MSSQL$实例名”（命名实例）。

在事件查看器里，还能把日志另存为*.evt文件（或.txt），以供管理员带到其他机器上打开分析。打开一个*.evt文件的方法，是右键单击“Event Viewer”树形结构，选择“Open Log File”。用这种方法，管理员就能像看本机上的日志记录一样，分析从其他机器保存下来的日志文件。也可以使用cmd命令导出：

cmd命令导出

rem 如果导出Security日志，需要管理员身份运行CMD
wevtutil epl Application C:\Users\admin\Downloads\Application_.evtx & wevtutil epl System C:\Users\admin\Downloads\System_.evtx & wevtutil epl Security C:\Users\admin\Downloads\Security_.evtx

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

2. SQL Server errorlog文件

SQL Server默认会保留7份errorlog文件，按照时间顺序，依次用文件扩展名.1,.2, ... .6表示。每重启一次服务，文件扩展名都会加一。最早的那份，会被删除。

cmd命令收集

sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_1.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 1, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_2.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 2, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_3.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 3, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_4.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 4, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_5.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 5, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_6.txt"
sqlcmd -S localhost -U sa -P yourpassword -Q "EXEC xp_readerrorlog 6, 1, NULL, NULL, NULL, NULL" -o "C:\Users\admin\Downloads\SQL_ErrorLog_7.txt"

注意，此方法与SSMS导出的格式有所不同，会存在很多空行以及空格等。所以条件允许，建议使用SSMS导出错误日志。

SSMS查看以及导出错误日志这里就不做叙述了。

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

SQL Server的版本，以及Windows和Processor基本信息。
SQL Server的启动参数，以及认证模式，内存分配模式。
每个数据库是否能够被正常打开。如果不能，原因是什么。
数据库损坏相关的错误。
数据库备份与恢复动作记录。
DBCC CHECKDB记录。
内存相关的错误和警告。
SQL Server调度出现异常时的警告。一般Server Hang会伴随有这些警告。
SQL Server I/O操作遇到长时间延迟的警告。
SQL Server在运行过程中遇到的其他级别比较高的错误。
SQL Server内部的访问越界错误（Access Violation）。
SQL Server服务关闭时间。

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

所有用户成功或失败的登入。
死锁及其参与者的信息。

它不能反映的问题有：

阻塞问题。只要阻塞还没有严重到影响SQL Server的线程调度，errorlog里是不会有体现的。
普通性能问题，超时问题。如果性能问题不是由于内存使用异常、线程调度异常，或者是IO子系统反应非常缓慢，而是由于表格或语句设计导致，errorlog里也不会有所反映。
Windows层面异常。

3. 性能监视器

默认的性能监视器用于实时监测系统，通过不同颜色的线条显示计数器值，但只显示最近一段时间的数据。实际问题分析中，通常需要在问题发生前就开始收集性能数据，并在问题发生后离线分析。

3.1. 手动收集

按win键，输入perfmon，回车打开性能监视器

3.1.1. 收集日志步骤：

创建数据收集器集：

- 在性能监视器中，右键“Performance Monitor”选择“New” → “Data Collector Set”。
- 输入名称（如“Test”）并点击“Next”。
- 选择保存路径，默认路径为 C:\Perflogs\Admin，点击“Finish”。

配置性能计数器：

- 找到创建的“Test”数据收集器，右键选择“New” → “Data Collector”。
- 输入新数据收集器名称并选择“Performance counter data collector”，点击“Next”。
- 设置收集间隔（默认15秒），点击“Add”添加计数器。
- 选择监控对象和计数器，例如Memory、Processor、SQLServer等对象下的所有计数器。

设置日志文件的大小与循环：

- 在Data Collector的“Properties”中，选择“Stop Condition”标签，勾选“Maximum Size”并设置日志文件大小（如300 MB）。
- 在“File”标签中勾选“Circular”，为日志文件命名格式添加日期后缀。

启动数据收集：

- 在左侧“User Defined”下选择创建的数据收集器，右键点击“Start”开始日志收集。

3.1.2. 查看日志步骤：

打开性能监视器，选择“View Log Data”。
在弹出框中，选择“Log files”并点击“Add”来添加日志文件。
添加相关的计数器，点击“OK”。
滚动调整窗口并选择关注的时间段，右键选择“Zoom to”查看详细数据。

3.1.3. 注意：

性能日志文件可以在300 MB时自动覆盖。
使用适当的计数器和合理的时间间隔可以更好地收集数据以帮助分析SQL Server性能问题。
对于非在线分析，问题可能还不清楚，很难确定哪些性能计数器有用，哪些没有用。所以在这里，一定要多选一些。一般的SQL Server问题，可以选择下面这些对象。

- “Memory”、“Process”、“PhysicalDisk”、“Processor”、“System”对象下的所有计数器，以及它们所有instance。
- 所有以“SQLServer:”开头的性能监视对象。
- 如果要监视CPU类问题，最好还包含“Thread”下面的所有计数器，以及它所有的instance。

注意，Total和<所有实例>是有区别的，Total是sum值，所以基本没有参考价值。

3.2. 命令行收集

管理员身份运行CMD

Logman.exe create counter MSSQL_Perf_Monitor_Total -f bin -max 5 -c "\Memory*" "\Process*" "\PhysicalDisk*" "\Processor*" "\System*" "SQLServer:*" "\Thread*" -si 00:00:15 -cnf 24:00:00 -v mmddhhmm -o C:\Users\admin\Downloads\MSSQL_Perf_Monitor_Total.blg

解释：

-f bin：指定输出格式为二进制文件。
-max 5：指定日志文件的最大大小为 300 MB，当日志文件大小超过此值时，日志将被分割。
-c：列出要监控的计数器，包括：

- \Memory*：所有与内存相关的计数器。
- \Process*：所有与进程相关的计数器。
- \PhysicalDisk*：所有与物理磁盘相关的计数器。
- \Processor*：所有与处理器相关的计数器。
- \System*：所有与系统相关的计数器。
- SQLServer:*：所有以“SQLServer:”开头的计数器（包括 SQL Server 的各类计数器）。
- \Thread*：所有与线程相关的计数器。

-si 00:00:15：设置数据收集的时间间隔为 15 秒。
-cnf 24:00:00：设置日志收集的持续时间为 24 小时。
-v mmddhhmm：设置日志文件的命名格式为月日小时分钟（例如，12091430）。
-o C:\Users\admin\Downloads\MSSQL_Perf_Monitor_Total.blg：指定日志文件的输出路径和文件名。

启动计数器

logman.exe start MSSQL_Perf_Monitor_Total

停止计数器

logman.exe stop MSSQL_Perf_Monitor_Total

结合前面的文章，可以创建多个收集器，比如按内存分析、CPU分析、I/O分析等。

内存分析

logman.exe create counter MSSQL_Perf_Monitor_Memory -f bin -max 500 -c "\Memory\Committed Bytes" "\Memory\Commit Limit" "\Memory\Available MBytes" "\Memory\Page File:% Usage" "\Memory\Page File:% Usage Peak" "\Memory\Pages/sec" "\Memory\Cache Bytes" "\Memory\System Cache Resident Bytes" "\Memory\Pool Paged Resident Bytes" "\Memory\System Driver Resident Bytes" "\Memory\System Code Resident Bytes" "\Process(*)% Processor Time" "\Process(*)\Working Set" "\Process(*)\Virtual Bytes" "\Process(*)\Private Bytes" "\Process(*)\Page Faults/sec" "\Process(*)\Handle Count" "\Process(*)\Thread Count" "\Process(*)\Pool Paged Bytes" "\Process(*)\Pool Nonpaged Bytes" "SQLServer:Memory Manager\Total Server Memory" "SQLServer:Memory Manager\Target Server Memory" "SQLServer:Memory Manager\SQL Cache Memory" "SQLServer:Memory Manager\Lock Memory" "SQLServer:Memory Manager\Connection Memory" "SQLServer:Memory Manager\Granted Workspace Memory" "SQLServer:Memory Manager\Memory Grants Pending" "SQLServer:Buffer Manager\Buffer Cache Hit Ratio" "SQLServer:Buffer Manager\Checkpoint pages/sec" "SQLServer:Buffer Manager\Database pages" "SQLServer:Buffer Manager\Free pages" "SQLServer:Buffer Manager\Lazy writes/sec" "SQLServer:Buffer Manager\Page life expectancy" "SQLServer:Buffer Manager\Page reads/sec" "SQLServer:Buffer Manager\Page writes/sec" "SQLServer:Buffer Manager\Stolen pages" "SQLServer:Buffer Manager\Target Pages" "SQLServer:Buffer Manager\Total Pages" -si 00:01:00 -cnf 24:00:00 -v mmddhhmm -o C:\Users\admin\Downloads\MSSQL_Perf_Monitor_Memory.blg

2. I/O分析

logman.exe create counter MSSQL_Perf_Monitor_IO -f bin -max 500 -c "\PhysicalDisk% idle time" "\PhysicalDisk% disk time" "\PhysicalDisk% disk read time" "\PhysicalDisk% disk write time" "\PhysicalDisk\Avg. disk sec/read" "\PhysicalDisk\Avg. disk sec/write" "\PhysicalDisk\Avg. disk sec/transfer" "\PhysicalDisk\Avg. disk bytes/transfer" "\PhysicalDisk\Avg. Disk Queue Length" "\PhysicalDisk\Avg. disk read queue length" "\PhysicalDisk\Avg. disk write queue length" "\PhysicalDisk\Disk Bytes/sec" "\PhysicalDisk\Disk Transfers/sec" "\PhysicalDisk\Disk Reads/sec" "\PhysicalDisk\Disk Writes/sec" "\PhysicalDisk\Disk Read Bytes/sec" "\PhysicalDisk\Disk Write Bytes/sec" "\PhysicalDisk\Current Disk Queue Length" "\SQLServer:Buffer Manager\Page Reads/sec" "\SQLServer:Buffer Manager\Page Writes/sec" "\SQLServer:Buffer Manager\Lazy Writes/sec" "\SQLServer:Buffer Manager\Checkpoint Writes/sec" "\SQLServer:Buffer Manager\Readahead Pages/sec" "\SQLServer:Access Methods\Freespace Scans/sec" "\SQLServer:Access Methods\Page Splits/sec" "\SQLServer:Access Methods\Page Allocations/sec" "\SQLServer:Access Methods\Workfiles/sec" "\SQLServer:Access Methods\Worktables/sec" "\SQLServer:Access Methods\Full Scans/sec" "\SQLServer:Access Methods\Index Searches/sec" "\SQLServer:Databases\Log Flushes/sec" "\SQLServer:Databases\Log Bytes Flushed/sec" "\SQLServer:Databases\Log Flush Wait Time" "\SQLServer:Databases\Log Flush Waits/sec" "\SQLServer:SQL Statistics\Batch Requests/sec" "\SQLServer:Databases\Active Transactions" -si 00:01:00 -cnf 24:00:00 -v mmddhhmm -o C:\Users\admin\Downloads\MSSQL_Perf_Monitor_IO.blg

剩下的可以按需自行收集，只需要替换-c后面的参数即可。

4. SQL Trace

4.1. Client Trace

SQL Trace 文件可以使用 SQL Server Profiler 工具收集，下面是常见的事件组及其收集选项的简化总结：

Database 事件组

- 用于监控数据文件和日志文件的自动增长与收缩。
- 常用事件：Data File Auto Grow、Data File Auto Shrink、Log File Auto Grow、Log File Auto Shrink。
- 关注文件大小变化时，可使用 SQL Trace 或定期运行 T-SQL 脚本。

Errors and Warnings 事件组

- 捕捉 SQL Server 错误和警告信息。
- 常用事件：Attention（客户端请求取消）、Command Timeout、Hash Warning、Missing Column Statistics、Missing Join Predicate。
- 全部收集对性能问题有帮助，尤其在 SQL Server 异常时。

Locks 事件组

- 用于分析死锁和阻塞。
- 常用事件：Deadlock Graph、Lock:Escalation。
- 阻塞相关事件（如 Lock:Timeout）生成日志量大，建议只在需要时收集。

Performance 事件组

- 主要关注执行计划和自动统计信息。
- 常用事件：Auto Stats、Showplan Statistics Profile（大日志，只有在必要时收集）。

Security Audit 事件组

- 监控 SQL Server 的安全事件。
- 常用事件：Audit Login、Audit Login Failed、Audit Logout、Audit Backup/Restore Event。

Server 事件组

- 记录服务器相关事件，如内存变化。
- 常用事件：Server Memory Change。

Sessions 事件组

- 用于记录已有连接。
- 常用事件：ExistingConnection。

Stored Procedures 事件组

- 用于监控存储过程的执行。
- 常用事件：RPC:Completed、SP:Completed、SP:StmtCompleted。
- 收集 SP:StmtStarting 和 SP:StmtCompleted 时会生成大量日志，建议按需收集。

TSQL 事件组

- 用于捕捉 T-SQL 执行。
- 常用事件：SQL:BatchCompleted、SQL:StmtCompleted、SQL:StmtRecompile。
- 避免收集 SQL:StmtStarting 以减少日志量。

Transactions 事件组

记录事务生命周期。
常用事件：DTCTransaction、SQLTransaction。
SQLTransaction 会生成大量日志，只有在分析事务问题时才收集。

4.1.1. 一般性问题建议收集的事件：

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。
Security Audit：Audit Login、Audit Login Failed、Audit Logout、Audit Server Starts and Stops、Audit Backup/Restore Event。
Sessions：ExistingConnection。
Stored Procedures：RPC:Completed、RPC:Starting。
TSQL：SQL:BatchCompleted、SQL:BatchStarting、Prepare SQL、Unprepare SQL、SQL:StmtRecompile。
Transactions：DTCTransaction。

4.1.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。

若需要减少日志生成量，可以省略 RPC:Starting、SQL:BatchStarting、SP:StmtStarting 等事件。

收集的Trace文件保留建议：

设置Trace将要以文件形式保存，指定文件的名字和路径。
设置文件的最大上限，并且设置File rollover。

4.2. Server Trace

在 SQL Server 性能分析中，使用 SQL Server Profiler 工具的一个重要注意事项是：它是 客户端工具，即 SQL Server 会将事件主动发送到 Profiler 工具。这意味着在高负载的生产环境中，当开启 Profiler 进行性能排查时，可能会导致 额外的性能开销，进一步加剧性能问题。因此，在处理繁忙业务的性能问题时，使用 Profiler 可能反而会影响系统的表现。

为了解决这个问题，SQL Server 提供了 服务端 Trace 功能。服务端 Trace 通过调用 SQL Server 内部的系统存储过程来实现跟踪和收集性能数据，而不是将事件发送到客户端。虽然这种方式的脚本相对较为复杂，但在 高性能压力环境下，这是最合适的选择，因为它能 最小化对系统性能的干扰。

收集方法也很简单，就是在图形化Profile界面收集，点击开始之后立马停止，随后就可以在[文件/导出/编写跟踪定义的脚本]导出对应的脚本定义

生成的Trace 脚本如下（以下只是案例，随便选了几个指标，不要用于生产收集），主要用于解释重要的指标

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- 请把它改成需要的最大大小
exec @rc = sp_trace_create @TraceID output, 2, N'C:\ test.trc', @maxfilesize, NULL
-- 请把它改成服务器上要存放Trace文件的地方
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
  exec sp_trace_setevent @TraceID, 14, 10, @on
  exec sp_trace_setevent @TraceID, 14, 14, @on
-- 这里是在设置收取什么事件，以及它们的哪些数据字段
-- 如果选的事件比较多，会很长，不用去修改它们
  ……………………………………
  -- Set the Filters
  declare @intfilter int
  declare @bigintfilter bigint
  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 9e636f4c-7a9f-4dac-
  9703-1e506054706c'
-- 这里是设置过滤条件，也不用修改
  -- Set the trace status to start
  exec sp_trace_setstatus @TraceID, 1
-- 运行了这句话，Trace就被开启了
  -- display trace id for future references
  select TraceID=@TraceID
  -- 这句话会返回一个数字，也就是这个Server Side Trace的编号，
   -- 因为一个SQL可以开启多个Trace.
   -- 一定要记录下这个编号，关闭Trace的时候要使用它
  goto finish
  error:
  select ErrorCode=@rc
  finish:
  go

如果要关闭这个Trace，要运行下面的两句话：

  exec sp_trace_setstatus ＜TraceID＞, 0
  -- 停止Trace
  exec sp_trace_setstatus ＜TraceID＞, 2
  -- 完全关闭Trace，并且删除这个定义

Trace文件分析方法，下面给出常用的字段解释：

EventClass：这个事件的名字。
TextData：事件的内容。如果事件代表的是一个指令，这里能够看到指令的所有内容。
SPID：运行这个事件的连接SPID编号。在SQL Server里，根据这个编号可以跟踪一个连接。
CPU：完成语句或某个动作所消耗的CPU时间。从这里可以找到消耗CPU比较多的连接和语句。
Reads和Writes：完成语句或某个动作所做的读/写次数。注意的是，这里的单位并不是Page，也不是K或KB。SQL Server里在做读和写时，会运行到某一段特定的代码。每调用一次这个代码，Reads/Write就会加1。所以这个值比较大，那语句一定做了比较多的I/O。但是不能通过这个值计算出I/O的绝对数量。另外，这个值反映的是逻辑读写量，不是物理读写量。
Duration：完成语句或某个动作所消耗的时间，也就是从开始到结束的时间差。和CPU不同的是，如果语句在运行过程中遇到等待，等待的时间不会记录在CPU里，但是会记录在Duration里。通过这个值可以很容易找到运行缓慢的语句。

还可以在收集时做一些过滤，比如只收集某个应用发过来的SQL

另外一个比较常用的功能是fn_trace_gettable ( filename , number_files )函数，用法如下：

select * into Sample
from fn_trace_gettable('c:\trace\test.trc',default)
where eventclass in (10, 12)

fn_trace_gettable 函数用于读取 SQL Server 跟踪文件中的数据，并返回结果。具体使用方式如下：

读取多个文件：

- fn_trace_gettable('c:\trace\test.trc', 3)：读取 c:\trace 路径下的 test.trc、test1.trc 和 test2.trc 三个文件。
- fn_trace_gettable('c:\trace\test.trc', default)：读取 test.trc 及其后续所有跟踪文件。

字段和过滤条件：

- 该函数返回跟踪日志中的所有字段，可以在查询中设置过滤条件（例如通过 eventclass 字段筛选特定的事件）。
- 例如，筛选 RPC:Completed（事件编号 10）和 SQL:BatchCompleted（事件编号 12）事件，并将其存储到 Sample 表中。

有空也可以研究下自动化分析SQL Trace工具。

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

5. 系统管理视图

前面已经介绍过四个视图

sys.sysprocesses
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_sessions

其他几个核心动态性能视图：

sys.dm_exec_query_stats：关于现在SQL Server缓存有执行计划的指令，它们的执行历史记录，包括CPU、Reads、Writes、编译重编译等。从这个视图可以了解语句执行历史。
sys.dm_db_missing_index_details：查询优化器在编译和优化SQL语句的时候，会发现该SQL语句缺少哪些索引，并将这些缺失的索引记录在动态视图中。
sys.dm_db_index_operational_stats（NULL，NULL，NULL，NULL）和sys.dm_db_index_usage_ stats：表格及其索引的查询、修改情况，以及上面发生过的I/O，阻塞等统计信息。从这个视图可用了解数据库里到底哪些表最繁忙。
sys.dm_io_virtual_file_stats（null，null）：以文件为单位，统计各个文件上发生过的I/O及其I/O等待。可以知道在SQL Server层面，哪个数据文件最繁忙。
sys.dm_db_file_space_usage，sys.dm_db_session_space_usage，sys.dm_db_task_space_ usage：可以计算出Tempdb的使用情况。
sys.dm_os_buffer_descriptors：内存里数据页面的缓存情况。

参考文档：

learn.microsoft.com/zh-cn/troub…

6. SQLDIAG工具

SQLDIAG是一个SQL Server自带的信息收集工具。默认的安装路径是C:\Program Files\Microsoft SQL Server\110\Tools\Binn\Sqldiag.exe （以SQL Server 2012为例）。

SQLDIAG可以收集的信息包括：

SQL Server的default trace。
Windows事件日志。
SQL Server曾经产生的Dump文件。
服务器系统配置信息。
同时包含有系统和SQL Server性能计数器的性能日志。
服务器端Trace。
SQLDiag文本文件。

其中，SQLDIAG文本文件中包含SQL Server所有错误日志的内容，SQL Server配置信息，以及一些重要的动态视图：

  -＞ sys.dm_exec_sessions
  -＞ sys.master_files
  -＞ sys.dm_tran_database_transactions
  -＞ sys.dm_tran_active_transactions
  -＞ sys.dm_os_sys_info
  -＞ sys.dm_os_schedulers
  -＞ sys.dm_os_threads
  -＞ sys.dm_os_workers
  -＞ sys.dm_os_tasks
  -＞ sys.dm_io_pending_io_requests
  -＞ sys.dm_io_virtual_file_stats
  -＞ sys.dm_os_latch_stats
  -＞ sys.dm_os_spinlock_stats
  -＞ sys.dm_os_sublatches
  -＞ sys.dm_os_memory_pools
  -＞ sys.dm_os_memory_clerks
  -＞ sys.dm_os_memory_brokers
  -＞ sys.dm_os_memory_nodes
  -＞ sys.dm_os_wait_stats
-＞ sys.dm_os_waiting_tasks
-＞ sys.dm_os_loaded_modules
-＞ sys.dm_os_cluster_nodes
-＞ sys.dm_os_nodes
-＞ sys.dm_os_process_memory
-＞ sysprocesses
-＞ sys.dm_os_sys_memory
-＞ sys.dm_io_cluster_shared_drives
-＞ sys.dm_clr_appdomains
-＞ sys.dm_clr_loaded_assemblies
-＞ sys.dm_clr_properties
-＞ sys.dm_clr_tasks
-＞ sys.assemblies
-＞ sys.assembly_modules
-＞ sys.assembly_types
-＞ sys.database_files
-＞ sys.dm_db_file_space_usage
-＞ sys.dm_db_session_space_usage
-＞ sys.dm_db_task_space_usage
-＞ sys.dm_exec_query_optimizer_info
-＞ sys.dm_exec_query_memory_grants
-＞ sys.dm_exec_query_resource_semaphores
-＞ sys.dm_exec_query_transformation_stats
-＞ sys.dm_broker_activated_tasks
-＞ sys.dm_broker_connections
-＞ sys.dm_broker_queue_monitors
-＞ sys.dm_resource_governor_configuration
-＞ sys.dm_resource_governor_resource_pools
-＞ sys.dm_resource_governor_workload_groups
-＞ sys.dm_database_encryption_keys
-＞ sys.dm_filestream_file_io_handles
-＞ sys.dm_filestream_file_io_requests
-＞ sys.dm_fts_active_catalogs
  -＞ sys.dm_fts_index_population
  -＞ sys.dm_fts_memory_pools
  -＞ sys.dm_fts_population_ranges

win键后输入sqldiag，注意用管理员身份运行

首次运行会出现这个界面，当出现“SQLDIAG Collection started. Press Ctrl+C to stop”信息以后，就按Ctrl+C组合键，终止这个工具的执行。SQLDiag工具会在Binn目录下产生三个XML文件，并且产生一个SQLDIAG子目录，存放刚才收集的信息

目录是：

C:\Program Files\Microsoft SQL Server\150\Tools\Binn

使用默认配置（即SQLDiag.XML），只会收到：

SQLDIAG文本文件（＜server＞_sp_sqldiag_Shutdown.OUT）。
SQL Server曾经产生的Dump记录（＜server＞_SQLDUMPER_ERRORLOG.log）。
服务器系统配置信息（＜server＞_MSINFO32.TXT）。
SQL Server的Default Trace文件（log_XXX.trc）。

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

SQLDiag /I ＜configure_file＞ /O ＜output_file＞

例如：

SQLDiag /I SD_General.xml –O c:\SQLDiagout

使用管理员身份打开CMD，执行如上命令即可。

还有一个工具叫PSSDIAG，有兴趣可以研究一下（我没玩儿过）。

7. Performance Dashboard

SSMS提供了性能仪表盘，打开方式如下：

首次碰到问题，可以优先使用这个报表。

8. 小结

SQL Server Errorlog

- 作用: 在任何问题排查中（包括性能问题和其他问题）都需要收集Errorlog，它能够提供直接或间接的信息来帮助定位问题。
- 性能问题中的作用: 能够显示系统性能瓶颈所在，是排查整体性能不佳问题的重要突破口。

Windows事件日志

- 主要用途: 排查SQL Server异常宕机、无法启动等问题。
- 次要用途: 其他问题中作用较小，但收集日志无副作用，有条件时建议收集。

PSSDIAG工具

- 优势:

- - 排查性能问题的强力工具，能够缩小问题范围甚至直接解决问题。

- 局限性:

1. 1. 日志较大，不适合长时间运行，尤其在性能问题随机且瞬间消失的情况下。
  2. 带来一定负载，可能加剧性能问题或导致系统宕机。特别是在排查100% CPU问题时需要慎用。

- 核心问题: 日志体积和负载高主要源于SQL Trace的加入。

未完待续。。。（20241222）