SQL Server DBA 必備 T-SQL 命令速查手冊：版本、備份、鎖死鎖、效能調優一網打盡

系統資訊查詢

-- 版本資訊
SELECT @@VERSION;

-- 作業系統參數
EXEC master..xp_msver;

-- 啟動參數
EXEC sp_configure;

-- 啟動時間
SELECT CONVERT(VARCHAR(30), login_time, 120) FROM master..sysprocesses WHERE spid = 1;

-- 伺服器/實例名
PRINT 'Server Name: ' + CONVERT(VARCHAR(30), @@SERVERNAME);
PRINT 'Instance: ' + CONVERT(VARCHAR(30), @@SERVICENAME);

資料庫管理

-- 所有資料庫名稱與大小
EXEC sp_helpdb;

-- 重命名資料庫
EXEC sp_renamedb 'old_dbname', 'new_dbname';

登入與權限

-- 所有登入資訊
EXEC sp_helplogins;

-- 伺服器角色成員
EXEC sp_helpsrvrolemember;

-- 修復孤立用戶（遷移必備）
-- 使用 sp_change_users_login 或 fix_orphan_user 腳本

-- 變更物件擁有者
EXEC sp_changeobjectowner 'object_name', 'new_owner';

-- 備份登入腳本（跨伺服器遷移）
-- 使用 add_login_to_aserver 腳本

連結伺服器

EXEC sp_helplinkedsrvlogin;
EXEC sp_helpremotelogin;

空間與索引分析

-- 物件空間使用
EXEC sp_spaceused 'table_name';

-- 最大 N 個表（預設 50）
EXEC sp_toptables;

-- 索引資訊
EXEC sp_helpindex 'table_name';
EXEC SP_NChelpindex 'table_name';  -- 更詳細

-- 約束資訊
EXEC sp_helpconstraint 'table_name';

叢集索引：資料物理順序＝索引順序，空間較小。
非叢集索引：建議用於高頻 DML 鍵值，fillfactor 用預設值。

存儲過程與函數

-- 列出所有 SP/Function
USE db_name;
EXEC sp_stored_procedures;

-- 查看原始碼
EXEC sp_helptext 'proc_name';

-- 搜尋包含特定字串的物件
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE TEXT LIKE '%keyword%';

-- 加密建立
CREATE PROC proc_name WITH ENCRYPTION AS ...;

-- 解密（需第三方工具或 sp_decrypt）

活動監控與鎖分析

-- 所有進程
EXEC sp_who;

-- 僅活躍進程
EXEC sp_who 'active';

-- 鎖狀況
EXEC sp_lock;

-- SPID 1-50 為系統進程，>50 為用戶連線
-- 查看進程正在執行的 SQL
DBCC INPUTBUFFER(spid);

-- 推薦：改良版 sp_who3 直接顯示執行語句
EXEC sp_who3;

-- 死鎖檢查
EXEC sp_who_lock;

日誌收縮（簡單恢復模式）

BACKUP LOG db_name WITH NO_LOG;
DBCC SHRINKFILE (db_name_log, 5);  -- 目標 5MB

查詢效能分析

-- 統計時間與 IO
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- 圖形化執行計畫：Ctrl+L 或工具列圖示
-- 文字版執行計畫
SET SHOWPLAN_ALL ON;
SET SHOWPLAN_TEXT ON;
SET STATISTICS PROFILE ON;

損壞修復（謹慎操作，先備份）

-- 單用戶模式
ALTER DATABASE [db_name] SET SINGLE_USER;

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

-- 或修復整庫
DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS);

-- 恢復多用戶
ALTER DATABASE [db_name] SET MULTI_USER;

修復選項差異：

• REPAIR_ALLOW_DATA_LOSS：分配/取消分配頁、刪除損壞文本物件，可能遺失資料
• REPAIR_FAST：輕量修復（如非叢集索引鍵），不遺失資料
• REPAIR_REBUILD：含 FAST + 重建索引，不遺失資料

⚠️ 修復完成後必須立即完整備份。生產環境優先考慮從乾淨備份恢復，而非修復。