使用 T-SQL 進行 SQL Server 資料庫日常維護操作指南

核心維護任務與對應 T-SQL

1. 索引維護

-- 檢查碎片
SELECT 
    OBJECT_NAME(ips.OBJECT_ID) AS TableName,
    si.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes si ON ips.OBJECT_ID = si.OBJECT_ID AND ips.index_id = si.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 重建（碎片 > 30%）
ALTER INDEX ALL ON dbo.LargeTable REBUILD WITH (ONLINE = ON, MAXDOP = 4, FILLFACTOR = 90);

-- 重組（10% ~ 30%）
ALTER INDEX ALL ON dbo.LargeTable REORGANIZE WITH (LOB_COMPACTION = ON);

2. 統計資料更新

-- 全資料庫更新（維護窗口執行）
EXEC sp_updatestats; -- 僅更新需更新的統計

-- 指定表全掃描更新（大表關鍵統計）
UPDATE STATISTICS dbo.FactSales WITH FULLSCAN, ALL;

3. 完整性檢查

-- 快速檢查（僅配置頁）
DBCC CHECKDB (MyDB) WITH PHYSICAL_ONLY, NO_INFOMSGS;

-- 完整檢查（維護窗口）
DBCC CHECKDB (MyDB) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

4. 備份腳本範例（Ola Hallengren 風格）

-- 完整備份
BACKUP DATABASE [MyDB] 
TO DISK = N'\\BackupShare\MyDB_FULL_20240115.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- 差異備份
BACKUP DATABASE [MyDB] 
TO DISK = N'\\BackupShare\MyDB_DIFF_20240115_1400.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- 記錄備份（每 15 分鐘）
BACKUP LOG [MyDB] 
TO DISK = N'\\BackupShare\MyDB_LOG_20240115_1415.trn'
WITH COMPRESSION, CHECKSUM;

5. 檔案與空間管理

-- 檢查資料庫空間使用
EXEC sp_spaceused @updateusage = N'TRUE';

-- 檔案自動增長調整（避免頻繁小增長）
ALTER DATABASE [MyDB] MODIFY FILE (NAME = MyDB_Data, SIZE = 200GB, FILEGROWTH = 4GB);
ALTER DATABASE [MyDB] MODIFY FILE (NAME = MyDB_Log, SIZE = 50GB, FILEGROWTH = 2GB);

-- 清理暫存資料庫（謹慎）
-- DBCC SHRINKFILE (tempdev, 10240); -- 僅在確認 tempdb 異常膨脹時

6. 監控與告警查詢

-- 長時間執行查詢
SELECT 
    r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
         ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.total_elapsed_time > 30000; -- >30 秒

-- 阻塞鏈
SELECT 
    blocking_session_id, blocked_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id > 0;

自動化部署建議

1. 採用 Ola Hallengren MaintenanceSolution（官網），涵蓋備份、完整性檢查、索引/統計維護，支援所有版本與版本。
2. 透過 SQL Server Agent 排程作業，設定 維護窗口（如每日 02:00-05:00）。
3. 啟用 Database Mail，作業失敗/長時間執行發送郵件告警。
4. 將維護腳本納入版本控管，變更走 Code Review 流程。

關鍵配置檢查清單