SQLServer 常用诊断工具

在 SQL Server 运维过程中，选择合适的诊断工具对于快速定位和解决问题至关重要。本文将介绍 SQL Server 常用的诊断工具、它们的特点、使用场景和最佳实践。

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) 是 SQL Server 最常用的管理和诊断工具，提供了图形化界面和丰富的功能。

主要功能

活动监视器
- 实时查看 SQL Server 实例的性能和资源使用情况
- 显示进程、等待统计信息、数据文件 I/O、最近昂贵查询等
- 可用于快速识别性能瓶颈和阻塞问题
查询编辑器
- 执行 T-SQL 诊断脚本
- 查看查询执行计划
- 分析查询性能
- 调试存储过程和函数
对象资源管理器
- 查看和管理数据库对象
- 检查数据库和表的属性
- 查看索引和统计信息
数据库引擎优化顾问
- 分析查询工作负载
- 提供索引和分区建议
- 辅助性能优化
SQL Server 日志
- 查看和分析 SQL Server 错误日志
- 查看 SQL Server 代理日志
- 配置日志属性

使用场景

日常管理和监控
执行 T-SQL 诊断脚本
查看和分析查询执行计划
快速识别性能瓶颈
管理数据库对象

最佳实践

使用最新版本的 SSMS，以获得最新功能和改进
利用活动监视器快速识别常见性能问题
使用查询编辑器的执行计划功能分析查询性能
定期检查 SQL Server 日志，及时发现异常

SQL Server Profiler

SQL Server Profiler 是一个图形化工具，用于捕获和分析 SQL Server 事件。

主要功能

事件捕获
- 捕获 SQL Server 各种事件，如 SQL 语句执行、登录、死锁等
- 可自定义事件选择和过滤条件
- 支持将事件保存到文件或数据库中
事件分析
- 实时查看捕获的事件
- 按各种条件排序和筛选事件
- 查看事件详细信息，包括执行计划、CPU 时间、I/O 等
工作负载分析
- 捕获查询工作负载
- 可将工作负载导入到数据库引擎优化顾问进行分析
- 辅助性能优化

使用场景

性能调优和故障诊断
捕获和分析慢查询
监控和分析数据库活动
调试应用程序与数据库的交互
安全审计和合规性检查

注意事项

SQL Server Profiler 是一个较重的工具，可能会对生产环境性能产生影响
SQL Server 2016 及以上版本推荐使用扩展事件替代 Profiler
在生产环境使用时，应限制捕获的事件数量和持续时间
避免在高峰时段使用 Profiler

Extended Events

Extended Events 是 SQL Server 2008 及以上版本提供的轻量级事件捕获系统，用于诊断各种性能问题。

主要功能

轻量级事件捕获
- 比 Profiler 更轻量级，对系统性能影响更小
- 支持捕获各种 SQL Server 事件
- 可自定义事件选择和过滤条件
灵活的目标
- 支持将事件存储到文件、内存环缓冲区、事件计数器等
- 支持实时查看事件
- 可将事件导出为各种格式
丰富的事件库
- 提供数百个预定义事件
- 支持自定义事件
- 可捕获详细的执行上下文信息

使用场景

性能调优和故障诊断
捕获和分析慢查询
监控和分析数据库活动
调试复杂的性能问题
死锁分析

创建和使用 Extended Events 会话

sql

-- 创建死锁监控会话
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Deadlock_Monitor.xel')
WITH (STARTUP_STATE=ON);
GO

-- 启动会话
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE=START;
GO

-- 查看会话状态
SELECT * FROM sys.dm_xe_sessions;
GO

-- 停止会话
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE=STOP;
GO

-- 删除会话
DROP EVENT SESSION [Deadlock_Monitor] ON SERVER;
GO

最佳实践

对于 SQL Server 2016 及以上版本，优先使用 Extended Events 替代 Profiler
只捕获必要的事件和数据，以减少性能影响
合理设置事件过滤条件，避免捕获过多无关事件
定期清理事件文件，避免占用过多磁盘空间

动态管理视图和函数 (DMVs/DMFs)

动态管理视图和函数 (DMVs/DMFs) 是 SQL Server 提供的一组系统视图和函数，用于实时查看和分析 SQL Server 的状态和性能。

主要类型

服务器范围内的 DMVs
- sys.dm_os_performance_counters：性能计数器
- sys.dm_os_wait_stats：等待统计信息
- sys.dm_exec_sessions：会话信息
- sys.dm_exec_connections：连接信息
数据库范围内的 DMVs
- sys.dm_db_index_usage_stats：索引使用情况
- sys.dm_db_missing_index_details：缺失索引建议
- sys.dm_db_partition_stats：分区统计信息
执行相关的 DMVs
- sys.dm_exec_query_stats：查询执行统计信息
- sys.dm_exec_sql_text：SQL 文本
- sys.dm_exec_query_plan：查询执行计划

常用查询示例

查看当前连接数

sql

SELECT COUNT(*) AS '当前连接数' FROM sys.dm_exec_connections;

查看等待统计信息

sql

SELECT
    wait_type AS '等待类型',
    wait_time_ms AS '总等待时间(毫秒)',
    signal_wait_time_ms AS '信号等待时间(毫秒)',
    wait_time_ms - signal_wait_time_ms AS '资源等待时间(毫秒)',
    waiting_tasks_count AS '等待任务数'
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN')
ORDER BY wait_time_ms DESC;

查看最近昂贵查询

sql

SELECT
    TOP 10
    DB_NAME(st.dbid) AS '数据库名称',
    OBJECT_NAME(st.objectid, st.dbid) AS '对象名称',
    qs.execution_count AS '执行次数',
    qs.total_elapsed_time / 1000 AS '总耗时(毫秒)',
    qs.total_elapsed_time / qs.execution_count / 1000 AS '平均耗时(毫秒)',
    qs.total_worker_time / 1000 AS '总 CPU 时间(毫秒)',
    qs.total_logical_reads AS '总逻辑读取次数',
    qs.total_logical_writes AS '总逻辑写入次数',
    st.text AS 'SQL 语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.dbid > 0
ORDER BY qs.total_elapsed_time DESC;

使用场景

实时监控 SQL Server 状态和性能
分析查询性能
识别性能瓶颈
查看索引使用情况和缺失索引建议
监控等待统计信息

最佳实践

熟悉常用 DMVs 的用途和语法
结合多个 DMVs 进行综合分析
定期执行 DMV 查询，建立性能基线
避免在生产环境执行过于复杂的 DMV 查询，以减少性能影响
使用 DMV 查询结果建立监控和告警机制

SQL Server 数据收集器

SQL Server 数据收集器是一个内置的性能数据收集和分析工具，用于长期监控 SQL Server 性能。

主要功能

数据收集
- 收集 SQL Server 性能数据，如 CPU 使用率、内存使用情况、磁盘 I/O 等
- 支持自定义数据收集器
- 可按计划自动收集数据
数据存储
- 将收集的数据存储到管理数据仓库 (MDW)
- 支持长期数据保留
- 可配置数据清理策略
数据报告
- 提供预定义的性能报告
- 可自定义报告
- 支持趋势分析

使用场景

长期性能监控
性能趋势分析
容量规划
识别周期性性能问题

配置和使用

启用数据收集器

sql

EXEC msdb.dbo.sp_syscollector_enable_collector;

创建管理数据仓库

sql

-- 在 SSMS 中，右键点击 "管理" -> "数据收集器" -> "配置管理数据仓库"

启用系统数据收集组
- 磁盘使用情况收集组
- 查询统计信息收集组
- 服务器活动收集组

最佳实践

根据实际需求配置数据收集频率，避免收集过多无关数据
定期清理管理数据仓库，避免占用过多磁盘空间
利用数据收集器的报告功能进行趋势分析
结合其他诊断工具使用，以获得更全面的性能视图

Performance Monitor

Performance Monitor (perfmon.msc) 是 Windows 系统自带的性能监控工具，可用于监控 SQL Server 性能计数器。

主要功能

性能计数器监控
- 监控 SQL Server 相关的性能计数器
- 支持实时监控和日志记录
- 可配置告警阈值
数据收集和分析
- 收集性能数据到日志文件
- 支持数据导出和分析
- 可生成性能报告

常用 SQL Server 性能计数器

SQLServer:General Statistics：批处理请求/秒、用户连接数等
SQLServer:Buffer Manager：缓冲区缓存命中率、页面读取/秒、页面写入/秒等
SQLServer:SQL Statistics：SQL 编译/秒、SQL 重新编译/秒等
SQLServer:Wait Statistics：等待时间、等待任务数等
SQLServer:Database Mirroring：镜像状态、日志发送队列大小等
SQLServer:Always On Availability Groups：可用性组状态、同步状态等

使用场景

实时监控 SQL Server 性能
长期性能趋势分析
识别性能瓶颈
配置性能告警

最佳实践

选择关键性能计数器进行监控，避免监控过多计数器
建立性能基线，用于比较和识别异常
配置合理的告警阈值，及时发现性能问题
定期分析性能日志，识别趋势和潜在问题

PowerShell

PowerShell 是 Windows 系统的命令行工具，可用于自动化 SQL Server 诊断任务。

主要功能

SQL Server 管理模块
- SQLServer 模块：提供了丰富的 SQL Server 管理 cmdlet
- 支持连接到 SQL Server 实例
- 执行 T-SQL 命令
- 管理数据库对象
自动化诊断任务
- 编写脚本自动执行诊断任务
- 生成诊断报告
- 支持远程管理和批量操作
系统管理
- 管理 SQL Server 服务
- 配置 Windows 事件日志
- 监控系统资源

常用 PowerShell 示例

查看 SQL Server 服务状态

powershell

Import-Module SQLServer
Get-Service -Name MSSQLSERVER, SQLSERVERAGENT

执行 T-SQL 诊断脚本

powershell

Import-Module SQLServer
$server = "localhost"
$database = "master"
$query = "SELECT @@VERSION AS 'SQL Server 版本';"
Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query

获取数据库空间使用情况

powershell

Import-Module SQLServer
$server = "localhost"
$databases = Get-SqlDatabase -ServerInstance $server
foreach ($db in $databases) {
    Write-Host "数据库: $($db.Name)"
    $space = $db.EnumSpaceUsage()
    $space | Format-Table -AutoSize
}

使用场景

自动化诊断任务
批量管理多个 SQL Server 实例
生成诊断报告
远程管理 SQL Server

最佳实践

学习 SQLServer PowerShell 模块的常用 cmdlet
编写可重用的诊断脚本
结合任务计划程序自动执行诊断任务
使用 PowerShell 脚本生成格式化的诊断报告

SQL Server 故障转移集群管理器

SQL Server 故障转移集群管理器是用于管理 SQL Server 故障转移集群实例 (FCI) 的工具。

主要功能

集群状态监控
- 查看集群节点状态
- 查看集群资源状态
- 查看集群事件
故障转移管理
- 手动触发故障转移
- 配置故障转移策略
- 查看故障转移历史记录
集群配置
- 添加和移除集群节点
- 配置集群资源
- 配置集群网络

使用场景

监控 SQL Server 故障转移集群状态
管理和配置集群资源
执行手动故障转移
查看集群事件和故障转移历史记录

最佳实践

定期检查集群状态，确保所有节点和资源正常运行
熟悉故障转移集群的工作原理和配置
定期执行故障转移演练，验证集群的可用性
监控集群事件，及时发现和解决问题

Always On 仪表板

Always On 仪表板是 SSMS 中的一个功能，用于监控和管理 Always On 可用性组。

主要功能

可用性组状态监控
- 查看可用性组的整体状态
- 查看可用性副本状态
- 查看可用性数据库状态
性能监控
- 查看日志发送队列大小
- 查看重做队列大小
- 查看同步状态
故障转移管理
- 手动触发故障转移
- 查看故障转移历史记录

使用场景

监控 Always On 可用性组状态
查看可用性副本和数据库的同步状态
管理和配置可用性组
执行手动故障转移

最佳实践

定期检查 Always On 仪表板，确保可用性组正常运行
监控日志发送队列和重做队列大小，及时发现同步问题
熟悉 Always On 可用性组的故障转移机制
定期执行故障转移演练，验证可用性组的可用性

第三方诊断工具

除了 SQL Server 自带的诊断工具外，还有许多第三方工具可用于 SQL Server 诊断和监控。

常用第三方工具

Redgate SQL Monitor
- 实时监控 SQL Server 性能
- 提供丰富的性能报告和告警
- 支持云数据库和混合环境
SolarWinds Database Performance Monitor
- 监控 SQL Server 性能和可用性
- 提供可视化的性能指标
- 支持自动告警和根因分析
Quest Foglight for SQL Server
- 全面监控 SQL Server 性能
- 提供性能基准和趋势分析
- 支持自动性能优化建议
Idera SQL Diagnostic Manager
- 监控 SQL Server 性能和可用性
- 提供实时告警和性能报告
- 支持自动性能优化
SentryOne SQL Sentry
- 监控 SQL Server 性能和可用性
- 提供可视化的执行计划分析
- 支持自动性能优化建议

选择第三方工具的考虑因素

功能需求：根据实际需求选择合适的工具
易用性：工具的界面和操作是否简单易用
性能影响：工具对系统性能的影响程度
成本：工具的购买和维护成本
支持和更新：供应商的技术支持和更新频率
集成性：与现有监控系统的集成能力

诊断工具选择指南

在实际运维中，应根据具体情况选择合适的诊断工具。以下是一些选择建议：

快速诊断
- 使用 SSMS 活动监视器快速识别常见性能问题
- 使用 T-SQL 诊断脚本执行特定的诊断任务
- 使用 DMVs 查看实时性能数据
深入分析
- 使用 Extended Events 捕获和分析详细的事件数据
- 使用 SQL Server Profiler（适用于 SQL Server 2014 及以下版本）
- 使用查询编辑器的执行计划功能分析查询性能
长期监控
- 使用 SQL Server 数据收集器进行长期性能监控
- 使用 Performance Monitor 监控性能计数器
- 使用第三方监控工具进行全面监控
自动化诊断
- 使用 PowerShell 编写脚本自动执行诊断任务
- 使用 SQL Server 代理作业定期执行诊断脚本
- 使用第三方工具的自动化功能
高可用性和集群监控
- 使用 SQL Server 故障转移集群管理器监控 FCI
- 使用 Always On 仪表板监控可用性组
- 使用第三方工具监控高可用性环境

常见问题 (FAQ)

SQL Server Profiler 和 Extended Events 哪个更好？
- 对于 SQL Server 2016 及以上版本，推荐使用 Extended Events，因为它更轻量级，对系统性能影响更小，功能更强大
- 对于 SQL Server 2014 及以下版本，可以继续使用 SQL Server Profiler
- Extended Events 提供了更多的事件类型和更灵活的配置选项
如何选择合适的 DMV 进行诊断？
- 根据具体问题选择合适的 DMV
- 例如，查看连接数使用 sys.dm_exec_connections
- 查看等待统计信息使用 sys.dm_os_wait_stats
- 查看查询性能使用 sys.dm_exec_query_stats
- 查看索引使用情况使用 sys.dm_db_index_usage_stats
如何使用 SSMS 查看查询执行计划？
- 在查询编辑器中编写 SQL 语句
- 点击 "显示估计的执行计划" 按钮（或按 Ctrl+L）查看估计的执行计划
- 或点击 "包括实际的执行计划" 按钮（或按 Ctrl+M）执行查询并查看实际的执行计划
- 在执行计划中，关注开销较大的操作，如表扫描、键查找等
如何配置 SQL Server 数据收集器？
- 在 SSMS 中，右键点击 "管理" -> "数据收集器" -> "配置管理数据仓库"
- 按照向导创建管理数据仓库
- 启用系统数据收集组，如磁盘使用情况收集组、查询统计信息收集组、服务器活动收集组
- 配置数据收集频率和保留策略
如何使用 PowerShell 自动化 SQL Server 诊断任务？
- 安装 SQLServer PowerShell 模块
- 编写 PowerShell 脚本，使用 SQLServer 模块的 cmdlet 执行诊断任务
- 使用任务计划程序定期执行脚本
- 将诊断结果输出到文件或数据库中，生成报告

选择合适的诊断工具并正确使用它们，对于提高 SQL Server 运维效率和快速解决问题至关重要。在实际运维中，应根据具体情况选择合适的工具，并结合多种工具进行综合分析，以获得更全面的性能视图和更准确的诊断结果。

SQLServer 常用诊断工具 ​

SQL Server Management Studio (SSMS) ​

主要功能 ​

使用场景 ​

最佳实践 ​

SQL Server Profiler ​

主要功能 ​

使用场景 ​

注意事项 ​

Extended Events ​

主要功能 ​

使用场景 ​

创建和使用 Extended Events 会话 ​

最佳实践 ​

动态管理视图和函数 (DMVs/DMFs) ​

主要类型 ​

常用查询示例 ​

使用场景 ​

最佳实践 ​

SQL Server 数据收集器 ​

主要功能 ​

使用场景 ​

配置和使用 ​

最佳实践 ​

Performance Monitor ​

主要功能 ​

常用 SQL Server 性能计数器 ​

使用场景 ​

最佳实践 ​

PowerShell ​

主要功能 ​

常用 PowerShell 示例 ​

使用场景 ​

最佳实践 ​

SQL Server 故障转移集群管理器 ​

主要功能 ​

使用场景 ​

最佳实践 ​

Always On 仪表板 ​

主要功能 ​

使用场景 ​

最佳实践 ​

第三方诊断工具 ​

常用第三方工具 ​

选择第三方工具的考虑因素 ​

诊断工具选择指南 ​

常见问题 (FAQ) ​

SQLServer 常用诊断工具

SQL Server Management Studio (SSMS)

主要功能

使用场景

最佳实践

SQL Server Profiler

主要功能

使用场景

注意事项

Extended Events

主要功能

使用场景

创建和使用 Extended Events 会话

最佳实践

动态管理视图和函数 (DMVs/DMFs)

主要类型

常用查询示例

使用场景

最佳实践

SQL Server 数据收集器

主要功能

使用场景

配置和使用

最佳实践

Performance Monitor

主要功能

常用 SQL Server 性能计数器

使用场景

最佳实践

PowerShell

主要功能

常用 PowerShell 示例

使用场景

最佳实践

SQL Server 故障转移集群管理器

主要功能

使用场景

最佳实践

Always On 仪表板

主要功能

使用场景

最佳实践

第三方诊断工具

常用第三方工具

选择第三方工具的考虑因素

诊断工具选择指南

常见问题 (FAQ)