SQL Server 常用运维命令整理

SQL Server 常用运维命令整理

一、基础信息查询类

1. 版本信息

   SELECT @@VERSION;  -- 基础版本号
   SELECT SERVERPROPERTY('Edition') AS Edition,  -- 企业版/标准版等
          SERVERPROPERTY('Collation') AS Collation,  -- 数据库字符集
          SERVERPROPERTY('ServerName') AS ServerName;  -- 实例名
   

2. 系统状态监控

   -- 当前连接数
   SELECT COUNT(*) AS [ConnectionCount] FROM sys.dm_exec_connections;
   
   -- 磁盘空间使用（需执行存储过程）
   EXEC master.dbo.xp_fixeddrives;  -- 各盘符剩余空间(GB)
   EXEC sp_spaceused;  -- 当前数据库大小及剩余空间
   
   -- 日志文件利用率
   DBCC SQLPERF(LOGSPACE);  -- 查看各数据库日志空间占比
   

3. 性能关键指标

   -- CPU占用TOP会话
   SELECT TOP 10 
       spid, cmd, cpu, physical_io, memusage,
       (SELECT TOP 1 [text] FROM ::fn_get_sql(sql_handle)) AS sql_text
   FROM master..sysprocesses
   ORDER BY cpu DESC, physical_io DESC;
   
   -- 缓存查询分析（内存占用高但使用率低的查询）
   SELECT TOP 100 
       usecounts, objtype, size_in_bytes/1024.0 AS Size_KB,
       [sql].[text] AS QueryText
   FROM sys.dm_exec_cached_plans p
   OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql
   ORDER BY usecounts, size_in_bytes DESC;
   

二、阻塞与锁管理

1. 阻塞链诊断

   -- 阻塞进程树（需结合两个视图）
   SELECT 
       t1.resource_type, t1.request_mode, t1.request_session_id AS BlockingSPID,
       t2.blocking_session_id AS VictimSPID,
       DB_NAME(t1.resource_database_id) AS DatabaseName
   FROM sys.dm_tran_locks t1
   INNER JOIN sys.dm_os_waiting_tasks t2 ON t1.lock_owner_address = t2.resource_address
   WHERE t1.request_session_id <> @@SPID;
   
   -- 查看阻塞进程的SQL文本
   EXEC sp_who2 142;  -- 替换142为实际SPID
   DBCC INPUTBUFFER(142);  -- 获取阻塞进程正在执行的SQL
   

2. 锁冲突解决

   -- 强制终止阻塞进程（谨慎使用）
   KILL 142;  -- 替换142为实际SPID
   

三、数据库维护操作

1. 备份与恢复

   -- 完整数据库备份
   BACKUP DATABASE [AdventureWorks] 
   TO DISK = 'D:\Backup\AdventureWorks_Full.bak' 
   WITH COMPRESSION, STATS = 10;
   
   -- 差异备份
   BACKUP DATABASE [AdventureWorks] 
   TO DISK = 'D:\Backup\AdventureWorks_Diff.bak' 
   WITH DIFFERENTIAL, COMPRESSION;
   
   -- 时间点恢复（需结合事务日志备份）
   RESTORE DATABASE [AdventureWorks] 
   FROM DISK = 'D:\Backup\AdventureWorks_Full.bak' 
   WITH NORECOVERY, REPLACE;
   
   RESTORE LOG [AdventureWorks] 
   FROM DISK = 'D:\Backup\AdventureWorks_Log.trn' 
   WITH STOPAT = '2025-08-12 14:00:00', RECOVERY;
   

2. 索引维护

   -- 重建碎片化索引（碎片率>30%）
   ALTER INDEX [IX_Orders_OrderDate] ON [Sales].[Orders] REBUILD;
   
   -- 重组轻度碎片化索引（碎片率10-30%）
   ALTER INDEX [IX_Customers_Name] ON [Sales].[Customers] REORGANIZE;
   
   -- 查看索引碎片率
   SELECT 
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       s.avg_fragmentation_in_percent AS Fragmentation
   FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') s
   JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
   WHERE s.avg_fragmentation_in_percent > 10
   ORDER BY Fragmentation DESC;
   

3. 统计信息更新

   -- 自动更新所有统计信息（适用于数据量大的表）
   UPDATE STATISTICS [Sales].[Orders] WITH FULLSCAN;
   
   -- 增量更新（适用于数据变化较少的表）
   UPDATE STATISTICS [Production].[Products] WITH SAMPLE 20 PERCENT;
   

四、高级诊断命令

1. 执行计划分析

   -- 获取当前运行的查询计划
   SELECT 
       r.session_id, r.status, r.command,
       DB_NAME(r.database_id) AS DatabaseName,
       qt.text AS QueryText,
       qp.query_plan AS ExecutionPlanXML
   FROM sys.dm_exec_requests r
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
   CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
   WHERE r.session_id > 50;  -- 排除系统进程
   

2. 长时间运行事务监控

   -- 识别运行超过1小时的事务
   SELECT 
       t.transaction_id,
       DB_NAME(t.database_id) AS DatabaseName,
       t.transaction_begin_time,
       DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS Duration_Minutes,
       at.transaction_description
   FROM sys.dm_tran_active_transactions t
   LEFT JOIN sys.dm_tran_database_transactions dt ON t.transaction_id = dt.transaction_id
   LEFT JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
   LEFT JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
   LEFT JOIN sys.dm_tran_locks l ON t.transaction_id = l.request_owner_id
   LEFT JOIN sys.allocation_units au ON l.resource_associated_entity_id = au.allocation_unit_id
   LEFT JOIN sys.partitions p ON au.container_id = p.hobt_id
   LEFT JOIN sys.dm_tran_commit_table at ON t.transaction_id = at.transaction_id
   WHERE DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) > 60
   ORDER BY Duration_Minutes DESC;
   

五、自动化运维脚本示例

1. 自动收缩日志文件

   -- 自动收缩日志文件到1GB（需在业务低峰期执行）
   DECLARE @dbname NVARCHAR(128) = 'AdventureWorks';
   DECLARE @logfilename NVARCHAR(128);
   
   SELECT @logfilename = name 
   FROM sys.master_files 
   WHERE database_id = DB_ID(@dbname) AND type_desc = 'LOG';
   
   IF @logfilename IS NOT NULL
   BEGIN
       DBCC SHRINKFILE(@logfilename, 1024);  -- 收缩到1GB
       PRINT '日志文件收缩完成';
   END
   ELSE
   BEGIN
       PRINT '未找到日志文件';
   END
   

2. 定期维护计划模板

   -- 每周日凌晨2点执行完整维护
   USE msdb;
   GO
   
   EXEC dbo.sp_add_job
       @job_name = N'Weekly Maintenance';
   
   EXEC sp_add_jobstep
       @job_name = N'Weekly Maintenance',
       @step_name = N'Update Statistics',
       @subsystem = N'TSQL',
       @command = N'EXEC sp_msforeachtable ''UPDATE STATISTICS ? WITH FULLSCAN''',
       @database_name = N'master';
   
   EXEC sp_add_schedule
       @schedule_name = N'Sunday 2AM',
       @freq_type = 8,  -- 每周
       @freq_interval = 1,  -- 周日
       @active_start_time = 020000;  -- 02:00:00
   
   EXEC sp_attach_schedule
       @job_name = N'Weekly Maintenance',
       @schedule_name = N'Sunday 2AM';
   
   EXEC sp_add_jobserver
       @job_name = N'Weekly Maintenance';
   

运维建议

1. 备份策略：采用完整备份+差异备份+事务日志备份的组合，保留最近7天的备份
2. 索引维护：对OLTP系统建议每周重组索引，每月重建索引
3. 统计信息：对数据量大的表建议每周更新统计信息
4. 监控告警：设置CPU使用率>80%、磁盘空间<10%等关键指标的告警
5. 变更管理：所有维护操作应通过变更窗口执行，并提前通知业务部门

这些命令覆盖了SQL Server运维的核心场景，实际使用时需根据具体环境调整参数。对于生产环境，建议先在测试环境验证命令效果，特别是涉及数据修改的操作（如KILL进程、数据库恢复等）。