SQLServer 状态查看与诊断

在 SQL Server 运维过程中，及时、准确地查看和诊断系统状态是确保数据库稳定运行的关键。本文将介绍 SQL Server 状态查看与诊断的常用方法、工具和最佳实践。

实例级状态查看

数据库引擎服务状态

SQL Server 服务状态检查

sql

-- 使用系统视图查看服务状态
EXEC xp_servicecontrol 'querystate', 'MSSQLSERVER';

-- 或使用 PowerShell
-- Get-Service -Name MSSQLSERVER

SQL Server 版本与配置信息

sql

-- 查看 SQL Server 版本信息
SELECT @@VERSION AS 'SQL Server 版本';

-- 查看 SQL Server 配置信息
EXEC sp_configure;

-- 查看高级配置信息
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure;

实例资源使用状态

CPU 使用率

sql

-- 使用动态管理视图查看 CPU 使用率
SELECT
    TOP 10
    DB_NAME(st.dbid) AS '数据库名称',
    OBJECT_NAME(st.objectid, st.dbid) AS '对象名称',
    SUM(qs.total_worker_time) AS '总 CPU 时间(微秒)',
    SUM(qs.execution_count) AS '执行次数',
    SUM(qs.total_worker_time) / SUM(qs.execution_count) AS '平均 CPU 时间(微秒/次)'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY st.dbid, st.objectid
ORDER BY SUM(qs.total_worker_time) DESC;

内存使用情况

sql

-- 查看 SQL Server 内存使用情况
SELECT
    physical_memory_in_use_kb / 1024 AS '物理内存使用(MB)',
    locked_page_allocations_kb / 1024 AS '锁定页面分配(MB)',
    virtual_address_space_committed_kb / 1024 AS '已提交虚拟地址空间(MB)',
    virtual_address_space_available_kb / 1024 AS '可用虚拟地址空间(MB)'
FROM sys.dm_os_process_memory;

-- 查看内存节点使用情况
SELECT
    node_id,
    physical_memory_in_use_kb / 1024 AS '物理内存使用(MB)',
    committed_kb / 1024 AS '已提交内存(MB)'
FROM sys.dm_os_memory_nodes
WHERE node_id <> 64;

磁盘 I/O 状态

sql

-- 查看磁盘 I/O 统计信息
SELECT
    DB_NAME(vfs.database_id) AS '数据库名称',
    vfs.file_id,
    mf.physical_name AS '物理文件名',
    mf.type_desc AS '文件类型',
    vfs.num_of_reads AS '读取次数',
    vfs.num_of_writes AS '写入次数',
    vfs.total_bytes_read / 1024 / 1024 AS '总读取量(MB)',
    vfs.total_bytes_written / 1024 / 1024 AS '总写入量(MB)',
    vfs.io_stall_read_ms AS '读取延迟(毫秒)',
    vfs.io_stall_write_ms AS '写入延迟(毫秒)'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;

数据库级状态查看

数据库基本状态

数据库状态检查

sql

-- 查看所有数据库的状态
SELECT
    name AS '数据库名称',
    state_desc AS '状态',
    recovery_model_desc AS '恢复模式',
    compatibility_level AS '兼容级别',
    collation_name AS '排序规则'
FROM sys.databases;

-- 查看特定数据库的详细信息
EXEC sp_helpdb 'AdventureWorks';

数据库空间使用情况

sql

-- 查看数据库空间使用情况
EXEC sp_spaceused;

-- 查看特定表的空间使用情况
EXEC sp_spaceused 'dbo.Employee';

-- 查看数据库文件空间使用情况
SELECT
    name AS '文件名称',
    type_desc AS '文件类型',
    size * 8 / 1024 AS '总大小(MB)',
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS '已使用空间(MB)',
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS '可用空间(MB)'
FROM sys.database_files;

数据库日志状态

事务日志使用情况

sql

-- 查看事务日志使用情况
DBCC SQLPERF(logspace);

-- 查看特定数据库的事务日志统计信息
SELECT
    name AS '日志文件名称',
    size * 8 / 1024 AS '总大小(MB)',
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS '已使用空间(MB)',
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS '可用空间(MB)'
FROM sys.database_files
WHERE type = 1;

事务日志链状态

sql

-- 查看事务日志备份链
BACKUP LOG AdventureWorks TO DISK = 'NUL' WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, CHECKSUM;
GO
SELECT
    database_name AS '数据库名称',
    backup_finish_date AS '备份完成时间',
    first_lsn AS '第一个 LSN',
    last_lsn AS '最后一个 LSN',
    checkpoint_lsn AS '检查点 LSN',
    differential_base_lsn AS '差异基准 LSN',
    backup_type AS '备份类型'
FROM msdb.dbo.backupset
WHERE database_name = 'AdventureWorks' AND type = 'L'
ORDER BY backup_finish_date DESC;

连接与会话状态

连接统计信息

查看连接数量

sql

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

-- 按数据库分组查看连接数
SELECT
    DB_NAME(dbid) AS '数据库名称',
    COUNT(*) AS '连接数'
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid
ORDER BY COUNT(*) DESC;

查看活跃会话

sql

-- 查看活跃会话
SELECT
    spid AS '会话 ID',
    status AS '状态',
    loginame AS '登录名',
    hostname AS '主机名',
    dbid AS '数据库 ID',
    DB_NAME(dbid) AS '数据库名称',
    cmd AS '命令',
    cpu AS 'CPU 时间(毫秒)',
    physical_io AS '物理 I/O 次数',
    last_batch AS '最后批处理时间',
    program_name AS '程序名称',
    nt_domain AS 'NT 域',
    nt_username AS 'NT 用户名'
FROM sys.sysprocesses
WHERE status <> 'sleeping';

-- 或使用动态管理视图
SELECT
    session_id AS '会话 ID',
    status AS '状态',
    login_name AS '登录名',
    host_name AS '主机名',
    database_id AS '数据库 ID',
    DB_NAME(database_id) AS '数据库名称',
    command AS '命令',
    cpu_time AS 'CPU 时间(毫秒)',
    logical_reads + physical_reads AS '总读取次数',
    last_request_end_time AS '最后请求结束时间',
    program_name AS '程序名称',
    nt_domain AS 'NT 域',
    nt_user_name AS 'NT 用户名'
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND status <> 'sleeping';

阻塞与死锁状态

查看阻塞情况

sql

-- 查看阻塞进程
SELECT
    blocking_session_id AS '阻塞会话 ID',
    session_id AS '被阻塞会话 ID',
    wait_type AS '等待类型',
    wait_time AS '等待时间(毫秒)',
    wait_resource AS '等待资源',
    DB_NAME(database_id) AS '数据库名称',
    program_name AS '程序名称',
    host_name AS '主机名',
    login_name AS '登录名'
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- 查看阻塞链
WITH BlockingChain AS (
    SELECT
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        program_name,
        host_name,
        login_name,
        1 AS 'Level'
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
    UNION ALL
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.program_name,
        r.host_name,
        r.login_name,
        bc.Level + 1 AS 'Level'
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.session_id = bc.blocking_session_id
)
SELECT * FROM BlockingChain ORDER BY Level DESC;

查看死锁信息

sql

-- 启用死锁跟踪标志
DBCC TRACEON(1222, -1);
DBCC TRACEON(1204, -1);

-- 或使用扩展事件查看死锁
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

性能状态诊断

查询性能状态

查看缓存的查询计划

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

-- 查看正在执行的查询
SELECT
    session_id AS '会话 ID',
    status AS '状态',
    command AS '命令',
    cpu_time AS 'CPU 时间(毫秒)',
    total_elapsed_time AS '总耗时(毫秒)',
    logical_reads AS '逻辑读取次数',
    physical_reads AS '物理读取次数',
    writes AS '写入次数',
    blocking_session_id AS '阻塞会话 ID',
    wait_type AS '等待类型',
    wait_time AS '等待时间(毫秒)',
    wait_resource AS '等待资源',
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS '当前执行语句',
    st.text AS '完整 SQL 语句'
FROM sys.dm_exec_requests AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.session_id > 50;

索引使用状态

查看索引使用情况

sql

-- 查看索引使用情况
SELECT
    DB_NAME(database_id) AS '数据库名称',
    OBJECT_NAME(object_id, database_id) AS '表名称',
    index_id AS '索引 ID',
    index_type_desc AS '索引类型',
    user_seeks AS '用户查找次数',
    user_scans AS '用户扫描次数',
    user_lookups AS '用户查找次数',
    user_updates AS '用户更新次数',
    last_user_seek AS '最后用户查找时间',
    last_user_scan AS '最后用户扫描时间',
    last_user_lookup AS '最后用户查找时间',
    last_user_update AS '最后用户更新时间'
FROM sys.dm_db_index_usage_stats
WHERE database_id > 4 -- 排除系统数据库
ORDER BY (user_seeks + user_scans + user_lookups) DESC;

查看缺失索引建议

sql

-- 查看缺失索引建议
SELECT
    DB_NAME(mid.database_id) AS '数据库名称',
    OBJECT_NAME(mid.object_id, mid.database_id) AS '表名称',
    mid.equality_columns AS '等值列',
    mid.inequality_columns AS '不等值列',
    mid.included_columns AS '包含列',
    migs.avg_total_user_cost AS '平均用户成本',
    migs.avg_user_impact AS '平均用户影响',
    migs.user_seeks AS '用户查找次数',
    migs.user_scans AS '用户扫描次数',
    migs.last_user_seek AS '最后用户查找时间',
    migs.last_user_scan AS '最后用户扫描时间'
FROM sys.dm_db_missing_index_details AS mid
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC, migs.avg_total_user_cost DESC;

系统级诊断

系统事件日志

查看 Windows 事件日志

powershell

# 使用 PowerShell 查看 SQL Server 相关的事件日志
Get-EventLog -LogName Application -Source "MSSQLSERVER" -Newest 50
Get-EventLog -LogName System -Source "Service Control Manager" -Newest 20

查看 SQL Server 错误日志

sql

-- 查看 SQL Server 错误日志
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, 'DESC';

-- 或使用 SSMS 中的 "管理" -> "SQL Server 日志"

系统性能计数器

使用 Performance Monitor 查看性能计数器

打开 Performance Monitor (perfmon.msc)
添加以下 SQL Server 相关的性能计数器：
- SQLServer:General Statistics
- SQLServer:Buffer Manager
- SQLServer:SQL Statistics
- SQLServer:Wait Statistics
- SQLServer:Database Mirroring
- SQLServer:Always On Availability Groups

使用 T-SQL 查看性能计数器

sql

-- 查看性能计数器
SELECT
    object_name AS '对象名称',
    counter_name AS '计数器名称',
    instance_name AS '实例名称',
    cntr_value AS '计数器值',
    cntr_type AS '计数器类型'
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQLServer%'
  AND counter_name IN ('Batch Requests/sec', 'Page Reads/sec', 'Page Writes/sec', 'Buffer cache hit ratio');

诊断工具与最佳实践

常用诊断工具

SQL Server Management Studio (SSMS)
- 活动监视器：实时查看实例状态、进程、等待统计信息
- 查询编辑器：执行 T-SQL 诊断脚本
- 对象资源管理器：查看数据库对象状态
SQL Server Profiler
- 捕获和分析 SQL Server 事件
- 用于性能调优和故障诊断
- SQL Server 2016 及以上版本推荐使用扩展事件替代
Extended Events
- 轻量级事件捕获系统
- 可用于诊断各种性能问题
- 支持将事件存储到文件或内存中
Database Engine Tuning Advisor
- 分析查询工作负载并提供索引和分区建议
- 辅助性能优化
PowerShell
- 自动化状态检查和诊断任务
- 支持远程管理和批量操作

诊断最佳实践

定期执行状态检查
- 建立每日、每周、每月的状态检查计划
- 自动化执行并生成报告
建立基线
- 记录正常运行时的性能指标
- 用于比较和识别异常情况
关注关键指标
- CPU 使用率、内存使用情况、磁盘 I/O 延迟
- 阻塞和死锁情况
- 慢查询和高资源消耗查询
使用多种诊断方法
- 结合 T-SQL、性能计数器、事件日志等多种方法
- 全面了解系统状态
及时响应异常
- 建立告警机制，及时发现异常
- 制定故障响应流程
保持诊断工具更新
- 使用最新版本的 SSMS 和诊断工具
- 了解新版本中的新功能和改进

常见问题 (FAQ)

如何快速判断 SQL Server 是否正常运行？
可以通过以下方法快速判断：
- 检查 SQL Server 服务状态是否为 "运行中"
- 尝试使用 SSMS 连接到实例
- 执行简单的查询，如 SELECT @@VERSION
- 查看 Windows 事件日志中是否有相关错误
如何确定数据库性能瓶颈？
确定性能瓶颈的步骤：
- 检查 CPU、内存、磁盘 I/O 使用率
- 查看阻塞和死锁情况
- 分析慢查询和高资源消耗查询
- 检查等待统计信息
- 查看缺失索引建议

如何查看 SQL Server 的等待统计信息？

可以使用以下查询查看等待统计信息：

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 Server 的连接字符串？
SQL Server 不会存储完整的连接字符串，但可以通过以下方法获取相关信息：
- 查看应用程序配置文件
- 查看连接池信息
- 使用 SQL Server Profiler 或扩展事件捕获连接事件

如何监控 Always On Availability Groups 的状态？

可以使用以下查询监控 Always On Availability Groups 的状态：

sql

-- 查看可用性组状态
SELECT
    name AS '可用性组名称',
    replica_server_name AS '副本服务器名称',
    role_desc AS '角色',
    synchronization_health_desc AS '同步健康状态',
    connected_state_desc AS '连接状态'
FROM sys.dm_hadr_availability_replica_states;

-- 查看可用性数据库状态
SELECT
    db_name(database_id) AS '数据库名称',
    replica_server_name AS '副本服务器名称',
    synchronization_state_desc AS '同步状态',
    synchronization_health_desc AS '同步健康状态',
    last_hardened_lsn AS '最后硬化 LSN',
    last_redone_lsn AS '最后重做 LSN',
    redo_queue_size AS '重做队列大小',
    log_send_queue_size AS '日志发送队列大小'
FROM sys.dm_hadr_database_replica_states;

通过定期执行状态检查和诊断，可以及时发现 SQL Server 中的潜在问题，确保数据库系统的稳定运行。在实际运维中，应根据具体情况选择合适的诊断方法和工具，并建立完善的监控和告警机制。

SQLServer 状态查看与诊断 ​

实例级状态查看 ​

数据库引擎服务状态 ​

实例资源使用状态 ​

数据库级状态查看 ​

数据库基本状态 ​

数据库日志状态 ​

连接与会话状态 ​

连接统计信息 ​

阻塞与死锁状态 ​

性能状态诊断 ​

查询性能状态 ​

索引使用状态 ​

系统级诊断 ​

系统事件日志 ​

系统性能计数器 ​

诊断工具与最佳实践 ​

常用诊断工具 ​

诊断最佳实践 ​

常见问题 (FAQ) ​