前言做 SQL Server DBA真正考验能力的不是会不会创建数据库而是在生产环境出现 CPU 飙高、SQL 卡顿、阻塞堆积、日志暴涨、Always On 延迟时能快速找到问题原因。SQL Server 提供了大量 DMVDynamic Management Views用于监控和诊断这些 DMV 是 DBA 日常排障最重要的工具。下面整理 100 条生产环境高频使用命令SQL Server 日常巡检性能问题定位阻塞与锁分析SQL 优化索引维护Always On备份恢复权限管理适用于 SQL Server 2016 / 2017 / 2019 / 2022。一、实例基础信息1-101. 查看 SQL Server 版本SELECT VERSION;2. 查看详细版本信息SELECT SERVERPROPERTY(ProductVersion) AS Version, SERVERPROPERTY(ProductLevel) AS Level, SERVERPROPERTY(Edition) AS Edition, SERVERPROPERTY(EngineEdition) AS EngineEdition;3. 查看实例名称SELECT SERVERPROPERTY(ServerName);4. 查看当前时间SELECT GETDATE();5. 查看 SQL Server 启动时间SELECT sqlserver_start_time FROM sys.dm_os_sys_info;6. 查看服务器 CPU 和内存SELECT cpu_count, physical_memory_kb/1024 AS memory_mb, virtual_machine_type_desc FROM sys.dm_os_sys_info;7. 查看 SQL Server 最大内存配置SELECT name, value_in_use FROM sys.configurations WHERE namemax server memory (MB);8. 查看当前数据库SELECT DB_NAME();9. 查看所有数据库状态SELECT name, state_desc, recovery_model_desc, compatibility_level FROM sys.databases;10. 查看数据库创建时间SELECT name, create_date FROM sys.databases;二、数据库空间管理11-2011. 查看数据库文件SELECT DB_NAME(database_id) AS database_name, name, physical_name, size*8/1024 AS size_mb FROM sys.master_files;12. 查看数据文件和日志文件SELECT DB_NAME(database_id) AS database_name, name, type_desc, size*8/1024 AS size_mb FROM sys.master_files;13. 查看数据库大小排行SELECT DB_NAME(database_id) AS database_name, SUM(size)*8/1024 AS size_mb FROM sys.master_files GROUP BY database_id ORDER BY size_mb DESC;14. 查看日志文件大小SELECT DB_NAME(database_id), name, size*8/1024 AS log_mb FROM sys.master_files WHERE type_descLOG;15. 查看日志使用率DBCC SQLPERF(LOGSPACE);16. 查看数据库空间使用EXEC sp_spaceused;17. 查看最大表SELECT TOP 20 OBJECT_NAME(object_id) AS table_name, SUM(reserved_page_count)*8/1024 AS size_mb FROM sys.dm_db_partition_stats GROUP BY object_id ORDER BY size_mb DESC;18. 查看表行数SELECT OBJECT_NAME(object_id), SUM(rows) FROM sys.partitions WHERE index_id IN (0,1) GROUP BY object_id;19. 查看文件增长设置SELECT name, growth, is_percent_growth FROM sys.database_files;20. 查看数据库恢复模式SELECT name, recovery_model_desc FROM sys.databases;三、Session 与连接排查21-3521. 查看当前连接SELECT * FROM sys.dm_exec_sessions;22. 查看正在执行 SQLSELECT session_id, status, command, cpu_time, total_elapsed_time, wait_type, blocking_session_id FROM sys.dm_exec_requests;23. 查看完整 SQL 文本SELECT r.session_id, t.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)t;24. 查看活动用户连接SELECT login_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY login_name;25. 查看客户端来源SELECT host_name, program_name, login_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY host_name, program_name, login_name;26. 查看长时间运行 SQLSELECT session_id, start_time, total_elapsed_time/1000 AS seconds, command FROM sys.dm_exec_requests ORDER BY total_elapsed_time DESC;27. 查看 CPU 消耗 SessionSELECT TOP 20 session_id, cpu_time, logical_reads FROM sys.dm_exec_requests ORDER BY cpu_time DESC;28. 查看当前等待SELECT session_id, wait_type, wait_time, blocking_session_id FROM sys.dm_exec_requests WHERE wait_type IS NOT NULL;29. 查看阻塞 SessionSELECT session_id, blocking_session_id, wait_type FROM sys.dm_exec_requests WHERE blocking_session_id0;30. 查看完整阻塞链SELECT blocking_session_id, session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id 0;31. 查看空闲连接SELECT session_id, status, last_request_start_time FROM sys.dm_exec_sessions WHERE statussleeping;32. 杀掉 SessionKILL 57;33. 查看连接限制SELECT name, value_in_use FROM sys.configurations WHERE nameuser connections;34. 查看登录失败EXEC xp_readerrorlog;35. 查看当前等待事件排行SELECT TOP 20 wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;四、锁、事务与阻塞36-5036. 查看当前锁SELECT * FROM sys.dm_tran_locks;37. 查看打开事务DBCC OPENTRAN;38. 查看活动事务SELECT * FROM sys.dm_tran_active_transactions;39. 查看长事务SELECT session_id, transaction_id, transaction_begin_time FROM sys.dm_tran_session_transactions;40. 查看锁等待SELECT request_session_id, resource_type, request_mode, request_status FROM sys.dm_tran_locks WHERE request_statusWAIT;41. 查看阻塞 SQLSELECT blocking_session_id, session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id0;42. 查看死锁SELECT * FROM system_health.session_targets;43. 查看隔离级别DBCC USEROPTIONS;44. 查看当前事务数量SELECT COUNT(*) FROM sys.dm_tran_active_transactions;45. 查看版本存储空间SELECT * FROM sys.dm_tran_version_store_space_usage;46. 查看 TempDB 版本存储SELECT * FROM sys.dm_db_file_space_usage;47. 查看锁数量SELECT COUNT(*) FROM sys.dm_tran_locks;48. 查看等待资源SELECT wait_type, resource_description FROM sys.dm_os_waiting_tasks;49. 查看当前死锁监控SELECT * FROM sys.dm_xe_sessions;50. 强制结束阻塞KILL session_id;五、SQL 性能分析51-6551. CPU 消耗最高 SQLSELECT TOP 20 qs.total_worker_time, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY qs.total_worker_time DESC;52. 执行次数最高 SQLSELECT TOP 20 execution_count, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY execution_count DESC;53. 平均耗时最高 SQLSELECT TOP 20 total_elapsed_time/execution_count, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY 1 DESC;54. 查看缓存执行计划SELECT * FROM sys.dm_exec_cached_plans;55. 查看执行计划SET SHOWPLAN_XML ON; GO SELECT * FROM table_name; GO SET SHOWPLAN_XML OFF;56. 查看 Query StoreSELECT * FROM sys.query_store_query;57. 查询历史高耗 SQLSELECT TOP 20 * FROM sys.query_store_runtime_stats ORDER BY avg_duration DESC;58. 查看逻辑读最高 SQLSELECT TOP 20 total_logical_reads, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY total_logical_reads DESC;59. 查看物理读最高 SQLSELECT TOP 20 total_physical_reads, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY total_physical_reads DESC;60. 查看缓存大小SELECT SUM(size_in_bytes)/1024/1024 AS MB FROM sys.dm_exec_cached_plans;六、索引与统计信息66-8061. 查看索引SELECT * FROM sys.indexes;62. 查看索引碎片SELECT OBJECT_NAME(object_id), avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( NULL,NULL,NULL,NULL,LIMITED );63. 重建索引ALTER INDEX ALL ON table_name REBUILD;64. 重组索引ALTER INDEX ALL ON table_name REORGANIZE;65. 更新统计信息UPDATE STATISTICS table_name;66. 查看缺失索引SELECT * FROM sys.dm_db_missing_index_details;67. 查看索引使用情况SELECT * FROM sys.dm_db_index_usage_stats;68. 查看未使用索引SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks0 AND user_scans0;69. 查看统计信息更新时间SELECT name, STATS_DATE(object_id,index_id) FROM sys.indexes;70. 创建索引CREATE INDEX idx_name ON table_name(column_name);七、Always On 高可用81-9071. 查看副本状态SELECT * FROM sys.dm_hadr_availability_replica_states;72. 查看同步状态SELECT * FROM sys.dm_hadr_database_replica_states;73. 查看同步延迟SELECT database_id, log_send_queue_size, redo_queue_size FROM sys.dm_hadr_database_replica_states;74. 查看 AG 配置SELECT * FROM sys.availability_groups;75. 查看监听器SELECT * FROM sys.availability_group_listeners;76. 查看 ReplicaSELECT * FROM sys.availability_replicas;77. 查看同步健康状态SELECT synchronization_health_desc FROM sys.dm_hadr_availability_replica_states;八、备份恢复91-9778. 查看备份历史SELECT database_name, backup_start_date, backup_finish_date, type FROM msdb.dbo.backupset ORDER BY backup_finish_date DESC;79. 备份数据库BACKUP DATABASE dbname TO DISKD:\backup\db.bak;80. 备份日志BACKUP LOG dbname TO DISKD:\backup\db.trn;81. 恢复数据库RESTORE DATABASE dbname FROM DISKD:\backup\db.bak;82. 查看最近备份SELECT TOP 10 * FROM msdb.dbo.backupset ORDER BY backup_finish_date DESC;83. 查看恢复历史SELECT * FROM msdb.dbo.restorehistory;九、权限管理98-10084. 查看登录账户SELECT * FROM sys.server_principals;85. 查看数据库用户SELECT * FROM sys.database_principals;86. 查看权限SELECT * FROM sys.database_permissions;87. 创建登录CREATE LOGIN user1 WITH PASSWORDPassword123;88. 创建数据库用户CREATE USER user1 FOR LOGIN user1;89. 授权读取ALTER ROLE db_datareader ADD MEMBER user1;90. 授权写入ALTER ROLE db_datawriter ADD MEMBER user1;91. 删除用户DROP USER user1;92. 删除登录DROP LOGIN user1;十、DBA 日常巡检补充93-10093. 查看 SQL Agent 状态SELECT * FROM msdb.dbo.sysjobs;94. 查看失败 JobSELECT * FROM msdb.dbo.sysjobhistory WHERE run_status1;95. 查看错误日志EXEC xp_readerrorlog;96. 查看 TempDB 使用SELECT * FROM sys.dm_db_file_space_usage;97. 查看内存压力SELECT * FROM sys.dm_os_memory_clerks;98. 查看 CPU 压力SELECT * FROM sys.dm_os_schedulers;99. 查看 IO 延迟SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL);100. 查看 SQL Server 等待统计SELECT TOP 20 wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER 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 条命令这个系列完整度会更高。