萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server DBA 必備:常用管理與維護 T-SQL 指令速查手冊

整理 SQL Server 日常運維、疑難排查、效能調優高頻使用的系統預存程序與查詢語句,按功能分類,可直接複製執行或納入自動化巡檢腳本。

1. 版本與環境資訊

-- 完整版本資訊
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);

2. 資料庫層級

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

-- 重命名資料庫 (需單一使用者模式)
EXEC sp_renamedb 'old_db', 'new_db';

3. 登入與權限

-- 所有登入帳號資訊
EXEC sp_helplogins;

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

-- 修復遷移後孤立使用者
EXEC sp_change_users_login 'Auto_Fix', 'username';

-- 變更物件擁有者
EXEC sp_changeobjectowner 'schema.object', 'new_owner';

-- 備份登入帳號 (含 SID/密碼雜湊) → 遷移腳本
-- 參考 sp_help_revlogin (Microsoft 官方腳本)

4. 連結/遠端伺服器

-- 連結伺服器登入對應
EXEC sp_helplinkedsrvlogin;

-- 遠端登入資訊
EXEC sp_helpremotelogin;

5. 空間與物件大小

-- 指定物件空間使用 (資料/索引/未使用)
EXEC sp_spaceused @objname = 'dbo.BigTable';

-- Top N 最大資料表 (預設 50)
EXEC sp_toptables;

-- 索引資訊
EXEC sp_helpindex @objname = 'dbo.BigTable';
-- 更詳細
EXEC sp_helpindex @objname = 'dbo.BigTable'; -- 2005+ 可用 sys.dm_db_index_physical_stats

-- 約束資訊
EXEC sp_helpconstraint @objname = 'dbo.BigTable';

6. 程式碼物件

-- 目前資料庫所有預存程序/函數
EXEC sp_stored_procedures;

-- 檢視程式碼
EXEC sp_helptext 'dbo.usp_MyProc';

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

-- 建立加密程序
CREATE PROC dbo.usp_Secret WITH ENCRYPTION AS SELECT 1;

-- 解密 (需第三方工具或 DAC 連線)

7. 索引策略小撇步

  • 高頻 DML 表:偏好 非叢集索引,避免叢集鍵更新導致頁分裂
  • Fill Factor:讀多寫少設 90-100;寫入頻繁設 70-80 預留空間
  • 叢集索引鍵:窄、唯一、遞增、靜態 (NUSI 原則)

8. 快速巡檢腳本範例

-- 單次執行獲取核心健康指標
SELECT 
    @@SERVERNAME AS Server,
    (SELECT COUNT(*) FROM sys.databases WHERE state_desc = 'ONLINE') AS OnlineDBs,
    (SELECT SUM(size*8/1024) FROM master.sys.master_files) AS TotalSizeMB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters 
     WHERE counter_name = 'Buffer cache hit ratio') AS BCHR,
    (SELECT cntr_value FROM sys.dm_os_performance_counters 
     WHERE counter_name = 'Page life expectancy') AS PLE_sec;

建議將上述指令整理為個人片段庫或 `snippets.json`,配合 `dbatools` (PowerShell) 或 `sqlcmd` 批次執行,打造專屬的自動化巡檢體系。

copyright © 萬盛學電腦網 all rights reserved