SQL Server 运维实战：从入门到精通

前言

SQL Server 是企业级数据库的核心组件之一，运维工作直接关系到系统的稳定性和数据安全性。本文总结 SQL Server 运维中的实战经验，涵盖日常监控、性能优化、备份恢复、故障排查等核心场景。

一、日常监控与检查

1.1 检查服务器状态

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

-- 查看服务器启动时间
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

-- 查看当前连接数
SELECT COUNT(*) AS ActiveConnections FROM sys.dm_exec_sessions;

1.2 监控数据库空间

-- 查看各数据库文件大小和使用情况
SELECT DB_NAME(database_id) AS DatabaseName,
    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
FROM sys.master_files
GROUP BY database_id
ORDER BY SizeMB DESC;

1.3 检查长时间运行的查询

-- 查看当前正在执行的查询
SELECT r.session_id, r.status, r.command, r.cpu_time,
    r.total_elapsed_time, t.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running'
ORDER BY r.total_elapsed_time DESC;

二、性能优化实战

2.1 识别性能瓶颈

-- 查看最消耗 CPU 的查询
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.execution_count, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgCPUTime DESC;

2.2 查找阻塞和死锁

-- 查看当前阻塞情况
SELECT l.request_session_id AS BlockedSessionID,
    l.blocking_session_id AS BlockingSessionID,
    DB_NAME(l.resource_database_id) AS DatabaseName
FROM sys.dm_tran_locks l
WHERE l.blocking_session_id IS NOT NULL;

三、备份与恢复

3.1 完整备份

BACKUP DATABASE [YourDatabase]
TO DISK = 'C:\Backup\YourDatabase_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

3.2 差异备份

BACKUP DATABASE [YourDatabase]
TO DISK = 'C:\Backup\YourDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

3.3 事务日志备份

BACKUP LOG [YourDatabase]
TO DISK = 'C:\Backup\YourDatabase_Log.trn'
WITH COMPRESSION, STATS = 10;

3.4 恢复数据库

RESTORE DATABASE [YourDatabase]
FROM DISK = 'C:\Backup\YourDatabase_Full.bak'
WITH REPLACE, RECOVERY;

四、维护计划

4.1 重建索引

ALTER INDEX ALL ON [YourTable] REBUILD WITH (ONLINE = ON);

4.2 更新统计信息

EXEC sp_updatestats;

4.3 检查数据库一致性

DBCC CHECKDB ('[YourDatabase]') WITH NO_INFOMSGS, ALL_ERRORMSGS;

五、故障排查

5.1 查看错误日志

EXEC sp_readerrorlog 0, 1, 'error';

5.2 内存压力分析

SELECT (physical_memory_in_use_kb / 1024) AS memory_used_mb,
    memory_utilization_percentage
FROM sys.dm_os_process_memory;

六、安全加固

6.1 检查 sysadmin 角色成员

SELECT name, type_desc FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;

七、最佳实践总结

日常运维清单

• 检查错误日志
• 验证备份完成情况
• 监控磁盘空间
• 检查长时间运行的查询
• 查看作业执行状态

每周任务

• 运行 DBCC CHECKDB
• 分析索引使用情况
• 审查安全日志

每月任务

• 执行索引重建/重组
• 更新统计信息
• 性能基线对比
• 灾难恢复演练

结语

SQL Server 运维是一项系统性工作，需要建立规范的流程和自动化的工具。预防胜于治疗，良好的日常维护可以避免大部分故障。

提示：所有脚本请在测试环境验证后再应用于生产环境。