SQL Server DBA 必備：常用管理與監控 T-SQL 指令速查表 (2)

續接上集，聚焦程序監控、鎖/死鎖分析、效能分析工具、資料庫修復與日誌縮減實戰指令。

7. 程序、鎖與死鎖監控

-- 所有使用者與程序
EXEC sp_who;

-- 僅作用中程序
EXEC sp_who 'active';

-- 鎖資訊（spid 1-50 為系統，>50 為使用者）
EXEC sp_lock;

-- 查看特定程序正在執行的 SQL 陳述式
DBCC INPUTBUFFER (spid);

-- 推薦：改良版 sp_who3 直接顯示 SQL 文字
EXEC sp_who3;

-- 死鎖分析專用
EXEC sp_who_lock;

8. 收縮資料庫日誌檔（簡單復原模式）

-- 截斷日誌並收縮至 5 MB
BACKUP LOG DatabaseName WITH NO_LOG;
DBCC SHRINKFILE (DatabaseName_Log, 5);

注意：完整復原模式請先執行日誌備份再收縮，否則會中斷備份鏈。

9. SQL 陳述式效能分析

-- 時間統計
SET STATISTICS TIME ON;  -- OFF 關閉

-- I/O 統計
SET STATISTICS IO ON;    -- OFF 關閉

-- 圖形化執行計畫（查詢分析器 Ctrl+L 或工具列圖示）

-- 文字版執行計畫
SET SHOWPLAN_ALL ON;     -- 詳細
SET SHOWPLAN_TEXT ON;    -- 簡潔
SET STATISTICS PROFILE ON; -- 執行後含實際統計

10. 資料庫不一致錯誤修復（3624 錯誤等）

1. 先註解應用程式對受損資料表的存取。
2. 在備份或測試機還原後再修復，避免生產環境資料遺失。

-- 切換單一使用者模式
ALTER DATABASE [ErrorDB] SET SINGLE_USER;

-- 修復特定資料表（允許資料遺失）
DBCC CHECKTABLE ('ErrorTable', REPAIR_ALLOW_DATA_LOSS);

-- 或修復整個資料庫（小型資料庫適用）
DBCC CHECKDB ('ErrorDB', REPAIR_ALLOW_DATA_LOSS);

-- 恢復多使用者模式
ALTER DATABASE [ErrorDB] SET MULTI_USER;

DBCC CHECKDB 修復等級說明

建議：修復完成後**立即完整備份**。若可能，優先從乾淨備份還原。

11. 實用腳本：檢查所有使用者資料表自動統計數量

SELECT OBJECT_NAME(id) AS TableName, COUNT(*) AS AutoStatsCount
FROM sysindexes
WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
  AND INDEXPROPERTY(id, name, 'IsAutoStatistics') = 1
GROUP BY OBJECT_NAME(id)
ORDER BY COUNT(*) DESC;

自動統計過多 = 索引不足，優先調校前幾名資料表。