SQL Server 运维命令 100 条

前言

做 SQL Server DBA，真正考验能力的不是会不会创建数据库，而是在生产环境出现 CPU 飙高、SQL 卡顿、阻塞堆积、日志暴涨、Always On 延迟时，能快速找到问题原因。

SQL Server 提供了大量 DMV（Dynamic Management Views）用于监控和诊断，这些 DMV 是 DBA 日常排障最重要的工具。

下面整理 100 条生产环境高频使用命令：

SQL Server 日常巡检
性能问题定位
阻塞与锁分析
SQL 优化
索引维护
Always On
备份恢复
权限管理

适用于 SQL Server 2016 / 2017 / 2019 / 2022。

一、实例基础信息（1-10）

1. 查看 SQL Server 版本

SQL

1SELECT @@VERSION;

2. 查看详细版本信息

SQL

1SELECT
2SERVERPROPERTY('ProductVersion') AS Version,
3SERVERPROPERTY('ProductLevel') AS Level,
4SERVERPROPERTY('Edition') AS Edition,
5SERVERPROPERTY('EngineEdition') AS EngineEdition;

3. 查看实例名称

SQL

1SELECT SERVERPROPERTY('ServerName');

4. 查看当前时间

SQL

1SELECT GETDATE();

5. 查看 SQL Server 启动时间

SQL

1SELECT sqlserver_start_time
2FROM sys.dm_os_sys_info;

6. 查看服务器 CPU 和内存

SQL

1SELECT
2cpu_count,
3physical_memory_kb/1024 AS memory_mb,
4virtual_machine_type_desc
5FROM sys.dm_os_sys_info;

7. 查看 SQL Server 最大内存配置

SQL

1SELECT
2name,
3value_in_use
4FROM sys.configurations
5WHERE name='max server memory (MB)';

8. 查看当前数据库

SQL

1SELECT DB_NAME();

9. 查看所有数据库状态

SQL

1SELECT
2name,
3state_desc,
4recovery_model_desc,
5compatibility_level
6FROM sys.databases;

10. 查看数据库创建时间

SQL

1SELECT
2name,
3create_date
4FROM sys.databases;

二、数据库空间管理（11-20）

11. 查看数据库文件

SQL

1SELECT
2DB_NAME(database_id) AS database_name,
3name,
4physical_name,
5size*8/1024 AS size_mb
6FROM sys.master_files;

12. 查看数据文件和日志文件

SQL

1SELECT
2DB_NAME(database_id) AS database_name,
3name,
4type_desc,
5size*8/1024 AS size_mb
6FROM sys.master_files;

13. 查看数据库大小排行

SQL

1SELECT
2DB_NAME(database_id) AS database_name,
3SUM(size)*8/1024 AS size_mb
4FROM sys.master_files
5GROUP BY database_id
6ORDER BY size_mb DESC;

14. 查看日志文件大小

SQL

1SELECT
2DB_NAME(database_id),
3name,
4size*8/1024 AS log_mb
5FROM sys.master_files
6WHERE type_desc='LOG';

15. 查看日志使用率

SQL

1DBCC SQLPERF(LOGSPACE);

16. 查看数据库空间使用

SQL

1EXEC sp_spaceused;

17. 查看最大表

SQL

1SELECT TOP 20
2OBJECT_NAME(object_id) AS table_name,
3SUM(reserved_page_count)*8/1024 AS size_mb
4FROM sys.dm_db_partition_stats
5GROUP BY object_id
6ORDER BY size_mb DESC;

18. 查看表行数

SQL

1SELECT
2OBJECT_NAME(object_id),
3SUM(rows)
4FROM sys.partitions
5WHERE index_id IN (0,1)
6GROUP BY object_id;

19. 查看文件增长设置

SQL

1SELECT
2name,
3growth,
4is_percent_growth
5FROM sys.database_files;

20. 查看数据库恢复模式

SQL

1SELECT
2name,
3recovery_model_desc
4FROM sys.databases;

三、Session 与连接排查（21-35）

21. 查看当前连接

SQL

1SELECT *
2FROM sys.dm_exec_sessions;

22. 查看正在执行 SQL

SQL

1SELECT
2session_id,
3status,
4command,
5cpu_time,
6total_elapsed_time,
7wait_type,
8blocking_session_id
9FROM sys.dm_exec_requests;

23. 查看完整 SQL 文本

SQL

1SELECT
2r.session_id,
3t.text
4FROM sys.dm_exec_requests r
5CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)t;

24. 查看活动用户连接

SQL

1SELECT
2login_name,
3COUNT(*)
4FROM sys.dm_exec_sessions
5GROUP BY login_name;

25. 查看客户端来源

SQL

1SELECT
2host_name,
3program_name,
4login_name,
5COUNT(*)
6FROM sys.dm_exec_sessions
7GROUP BY
8host_name,
9program_name,
10login_name;

26. 查看长时间运行 SQL

SQL

1SELECT
2session_id,
3start_time,
4total_elapsed_time/1000 AS seconds,
5command
6FROM sys.dm_exec_requests
7ORDER BY total_elapsed_time DESC;

27. 查看 CPU 消耗 Session

SQL

1SELECT TOP 20
2session_id,
3cpu_time,
4logical_reads
5FROM sys.dm_exec_requests
6ORDER BY cpu_time DESC;

28. 查看当前等待

SQL

1SELECT
2session_id,
3wait_type,
4wait_time,
5blocking_session_id
6FROM sys.dm_exec_requests
7WHERE wait_type IS NOT NULL;

29. 查看阻塞 Session

SQL

1SELECT
2session_id,
3blocking_session_id,
4wait_type
5FROM sys.dm_exec_requests
6WHERE blocking_session_id<>0;

30. 查看完整阻塞链

SQL

1SELECT
2blocking_session_id,
3session_id,
4wait_type,
5wait_time
6FROM sys.dm_exec_requests
7WHERE blocking_session_id > 0;

31. 查看空闲连接

SQL

1SELECT
2session_id,
3status,
4last_request_start_time
5FROM sys.dm_exec_sessions
6WHERE status='sleeping';

32. 杀掉 Session

SQL

1KILL 57;

33. 查看连接限制

SQL

1SELECT
2name,
3value_in_use
4FROM sys.configurations
5WHERE name='user connections';

34. 查看登录失败

SQL

1EXEC xp_readerrorlog;

35. 查看当前等待事件排行

SQL

1SELECT TOP 20
2wait_type,
3waiting_tasks_count,
4wait_time_ms
5FROM sys.dm_os_wait_stats
6ORDER BY wait_time_ms DESC;

四、锁、事务与阻塞（36-50）

36. 查看当前锁

SQL

1SELECT *
2FROM sys.dm_tran_locks;

37. 查看打开事务

SQL

1DBCC OPENTRAN;

38. 查看活动事务

SQL

1SELECT *
2FROM sys.dm_tran_active_transactions;

39. 查看长事务

SQL

1SELECT
2session_id,
3transaction_id,
4transaction_begin_time
5FROM sys.dm_tran_session_transactions;

40. 查看锁等待

SQL

1SELECT
2request_session_id,
3resource_type,
4request_mode,
5request_status
6FROM sys.dm_tran_locks
7WHERE request_status='WAIT';

41. 查看阻塞 SQL

SQL

1SELECT
2blocking_session_id,
3session_id,
4wait_type,
5wait_time
6FROM sys.dm_exec_requests
7WHERE blocking_session_id<>0;

42. 查看死锁

SQL

1SELECT *
2FROM system_health.session_targets;

43. 查看隔离级别

SQL

1DBCC USEROPTIONS;

44. 查看当前事务数量

SQL

1SELECT
2COUNT(*)
3FROM sys.dm_tran_active_transactions;

45. 查看版本存储空间

SQL

1SELECT *
2FROM sys.dm_tran_version_store_space_usage;

46. 查看 TempDB 版本存储

SQL

1SELECT *
2FROM sys.dm_db_file_space_usage;

47. 查看锁数量

SQL

1SELECT
2COUNT(*)
3FROM sys.dm_tran_locks;

48. 查看等待资源

SQL

1SELECT
2wait_type,
3resource_description
4FROM sys.dm_os_waiting_tasks;

49. 查看当前死锁监控

SQL

1SELECT
2*
3FROM sys.dm_xe_sessions;

50. 强制结束阻塞

SQL

1KILL session_id;

五、SQL 性能分析（51-65）

51. CPU 消耗最高 SQL

SQL

1SELECT TOP 20
2qs.total_worker_time,
3qt.text
4FROM sys.dm_exec_query_stats qs
5CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
6ORDER BY qs.total_worker_time DESC;

52. 执行次数最高 SQL

SQL

1SELECT TOP 20
2execution_count,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY execution_count DESC;

53. 平均耗时最高 SQL

SQL

1SELECT TOP 20
2total_elapsed_time/execution_count,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY 1 DESC;

54. 查看缓存执行计划

SQL

1SELECT *
2FROM sys.dm_exec_cached_plans;

55. 查看执行计划

SQL

1SET SHOWPLAN_XML ON;
2GO
3
4SELECT *
5FROM table_name;
6
7GO
8SET SHOWPLAN_XML OFF;

56. 查看 Query Store

SQL

1SELECT *
2FROM sys.query_store_query;

57. 查询历史高耗 SQL

SQL

1SELECT TOP 20
2*
3FROM sys.query_store_runtime_stats
4ORDER BY avg_duration DESC;

58. 查看逻辑读最高 SQL

SQL

1SELECT TOP 20
2total_logical_reads,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY total_logical_reads DESC;

59. 查看物理读最高 SQL

SQL

1SELECT TOP 20
2total_physical_reads,
3text
4FROM sys.dm_exec_query_stats
5CROSS APPLY sys.dm_exec_sql_text(sql_handle)
6ORDER BY total_physical_reads DESC;

60. 查看缓存大小

SQL

1SELECT
2SUM(size_in_bytes)/1024/1024 AS MB
3FROM sys.dm_exec_cached_plans;

六、索引与统计信息（66-80）

61. 查看索引

SQL

1SELECT *
2FROM sys.indexes;

62. 查看索引碎片

SQL

1SELECT
2OBJECT_NAME(object_id),
3avg_fragmentation_in_percent
4FROM sys.dm_db_index_physical_stats
5(
6NULL,NULL,NULL,NULL,'LIMITED'
7);

63. 重建索引

SQL

1ALTER INDEX ALL
2ON table_name
3REBUILD;

64. 重组索引

SQL

1ALTER INDEX ALL
2ON table_name
3REORGANIZE;

65. 更新统计信息

SQL

1UPDATE STATISTICS table_name;

66. 查看缺失索引

SQL

1SELECT *
2FROM sys.dm_db_missing_index_details;

67. 查看索引使用情况

SQL

1SELECT *
2FROM sys.dm_db_index_usage_stats;

68. 查看未使用索引

SQL

1SELECT *
2FROM sys.dm_db_index_usage_stats
3WHERE user_seeks=0
4AND user_scans=0;

69. 查看统计信息更新时间

SQL

1SELECT
2name,
3STATS_DATE(object_id,index_id)
4FROM sys.indexes;

70. 创建索引

SQL

1CREATE INDEX idx_name
2ON table_name(column_name);

七、Always On 高可用（81-90）

71. 查看副本状态

SQL

1SELECT *
2FROM sys.dm_hadr_availability_replica_states;

72. 查看同步状态

SQL

1SELECT *
2FROM sys.dm_hadr_database_replica_states;

73. 查看同步延迟

SQL

1SELECT
2database_id,
3log_send_queue_size,
4redo_queue_size
5FROM sys.dm_hadr_database_replica_states;

74. 查看 AG 配置

SQL

1SELECT *
2FROM sys.availability_groups;

75. 查看监听器

SQL

1SELECT *
2FROM sys.availability_group_listeners;

76. 查看 Replica

SQL

1SELECT *
2FROM sys.availability_replicas;

77. 查看同步健康状态

SQL

1SELECT
2synchronization_health_desc
3FROM sys.dm_hadr_availability_replica_states;

八、备份恢复（91-97）

78. 查看备份历史

SQL

1SELECT
2database_name,
3backup_start_date,
4backup_finish_date,
5type
6FROM msdb.dbo.backupset
7ORDER BY backup_finish_date DESC;

79. 备份数据库

SQL

1BACKUP DATABASE dbname
2TO DISK='D:\backup\db.bak';

80. 备份日志

SQL

1BACKUP LOG dbname
2TO DISK='D:\backup\db.trn';

81. 恢复数据库

SQL

1RESTORE DATABASE dbname
2FROM DISK='D:\backup\db.bak';

82. 查看最近备份

SQL

1SELECT TOP 10 *
2FROM msdb.dbo.backupset
3ORDER BY backup_finish_date DESC;

83. 查看恢复历史

SQL

1SELECT *
2FROM msdb.dbo.restorehistory;

九、权限管理（98-100）

84. 查看登录账户

SQL

1SELECT *
2FROM sys.server_principals;

85. 查看数据库用户

SQL

1SELECT *
2FROM sys.database_principals;

86. 查看权限

SQL

1SELECT *
2FROM sys.database_permissions;

87. 创建登录

SQL

1CREATE LOGIN user1
2WITH PASSWORD='Password@123';

88. 创建数据库用户

SQL

1CREATE USER user1
2FOR LOGIN user1;

89. 授权读取

SQL

1ALTER ROLE db_datareader
2ADD MEMBER user1;

90. 授权写入

SQL

1ALTER ROLE db_datawriter
2ADD MEMBER user1;

91. 删除用户

SQL

1DROP USER user1;

92. 删除登录

SQL

1DROP LOGIN user1;

十、DBA 日常巡检补充（93-100）

93. 查看 SQL Agent 状态

SQL

1SELECT *
2FROM msdb.dbo.sysjobs;

94. 查看失败 Job

SQL

1SELECT *
2FROM msdb.dbo.sysjobhistory
3WHERE run_status<>1;

95. 查看错误日志

SQL

1EXEC xp_readerrorlog;

96. 查看 TempDB 使用

SQL

1SELECT *
2FROM sys.dm_db_file_space_usage;

97. 查看内存压力

SQL

1SELECT *
2FROM sys.dm_os_memory_clerks;

98. 查看 CPU 压力

SQL

1SELECT *
2FROM sys.dm_os_schedulers;

99. 查看 IO 延迟

SQL

1SELECT *
2FROM sys.dm_io_virtual_file_stats(NULL,NULL);

100. 查看 SQL Server 等待统计

SQL

1SELECT TOP 20
2wait_type,
3wait_time_ms
4FROM sys.dm_os_wait_stats
5ORDER BY wait_time_ms DESC;

总结

SQL Server DBA 的核心能力，不是记住多少 T-SQL，而是面对生产问题时能够建立正确的排查路径。

例如：

CPU 高 → 不应该先看 CPU，而应该看等待和高耗 SQL；
数据库慢 → 不应该马上加索引，而应该分析执行计划；
日志暴涨 → 不应该直接扩容，而应该检查事务、备份链和恢复模式；
Always On 延迟 → 不应该只看延迟秒数，而应该分析日志发送队列和 redo 队列。

真正成熟的 SQL Server DBA，掌握的是这些命令背后的诊断逻辑。

这篇和前面的 MySQL、PostgreSQL 可以形成你的 《DBA 三大数据库 100 条命令系列》。建议后续补一篇 Oracle DBA 实用 100 条命令，这个系列完整度会更高。