SQL Server DBA 日常巡檢常用 SQL 全集：數據庫、文件、I/O、對象、tempdb、阻塞、性能

一、數據庫級巡檢

-- 1. 所有數據庫大小與狀態
EXEC sp_helpdb;

-- 2. 詳細狀態視圖
SELECT 
    name,
    user_access_desc,      -- 訪問模式
    state_desc,            -- 狀態 (ONLINE/OFFLINE/RESTORING...)
    recovery_model_desc,   -- 恢復模式
    page_verify_option_desc, -- 頁驗證 (CHECKSUM 推薦)
    log_reuse_wait_desc    -- 日志重用等待原因 (關鍵！)
FROM sys.databases
ORDER BY name;

-- 3. 單庫空間使用 (按頁統計，快但略有誤差)
USE YourDB;
EXEC sp_spaceused;

-- 4. 校正空間統計 (準確，有性能開銷)
DBCC UPDATEUSAGE (YourDB);
EXEC sp_spaceused @updateusage = 'true';

二、數據/日志文件巡檢

-- 1. 當前庫文件列表
EXEC sp_helpfile;

-- 2. 實例級所有文件路徑、大小、狀態
SELECT 
    DB_NAME(database_id) AS DBName,
    type_desc,             -- ROWS / LOG
    name AS LogicalName,
    physical_name,
    state_desc,
    size * 8.0 / 1024 AS SizeMB
FROM sys.master_files
ORDER BY DBName, type_desc;

-- 3. 文件大小 (Extent 統計，快、準、含 TempDB 數據文件)
DBCC SHOWFILESTATS;  -- 需在目標庫上下文

-- 4. 日志空間使用率 (所有庫)
DBCC SQLPERF (LOGSPACE);

三、文件 I/O 統計分析

-- 單庫數據+日志文件 I/O 統計 (含等待時間、吞吐、文件大小)
SELECT 
    database_id, file_id, file_handle,
    sample_ms,
    num_of_reads, num_of_bytes_read, io_stall_read_ms,
    num_of_writes, num_of_bytes_written, io_stall_write_ms,
    io_stall,                           -- 總等待毫秒
    size_on_disk_bytes                  -- 磁盤實際佔用
FROM sys.dm_io_virtual_file_stats(DB_ID('YourDB'), 1)  -- 數據文件
UNION ALL
SELECT 
    database_id, file_id, file_handle,
    sample_ms,
    num_of_reads, num_of_bytes_read, io_stall_read_ms,
    num_of_writes, num_of_bytes_written, io_stall_write_ms,
    io_stall, size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(DB_ID('YourDB'), 2);  -- 日志文件

-- 關鍵衍生指標：
-- 平均讀延遲 = io_stall_read_ms / NULLIF(num_of_reads, 0)
-- 平均寫延遲 = io_stall_write_ms / NULLIF(num_of_writes, 0)
-- 讀寫比例 = num_of_reads : num_of_writes

四、對象空間與碎片巡檢

-- 1. 單表空間 (快、略有誤差)
EXEC sp_spaceused 'YourTable';

-- 2. 單表空間 (準、有開銷)
EXEC sp_spaceused 'YourTable', @updateusage = 'true';

-- 3. 批量統計多表空間 (按分區頁統計，快、準)
SELECT 
    o.name AS TableName,
    SUM(p.reserved_page_count) * 8.0 / 1024 AS ReservedMB,
    SUM(p.used_page_count) * 8.0 / 1024 AS UsedMB,
    SUM(CASE WHEN p.index_id < 2 THEN p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count ELSE p.lob_used_page_count + p.row_overflow_used_page_count END) * 8.0 / 1024 AS DataMB,
    SUM(CASE WHEN p.index_id < 2 THEN p.row_count ELSE 0 END) AS RowCount
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON p.object_id = o.object_id
WHERE o.type = 'U'
GROUP BY o.name
ORDER BY ReservedMB DESC;

-- 4. 索引碎片度 (推薦 DMF)
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 5. 盤區分配詳情 (找出需重建的表)
-- 步驟：創建臨時表 → DBCC EXTENTINFO → 分析理論區數 vs 實際區數
-- 見原文完整腳本

五、TempDB 專項巡檢 (關鍵！)

-- 1. TempDB 文件空間使用分解
SELECT 
    DB_NAME(database_id) AS DB,
    SUM(user_object_reserved_page_count) * 8.0 / 1024 AS UserObjectsMB,
    SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS InternalObjectsMB,
    SUM(version_store_reserved_page_count) * 8.0 / 1024 AS VersionStoreMB,
    SUM(unallocated_extent_page_count) * 8.0 / 1024 AS FreeMB,
    SUM(mixed_extent_page_count) * 8.0 / 1024 AS MixedExtentsMB
FROM sys.dm_db_file_space_usage
WHERE database_id = 2
GROUP BY DB_NAME(database_id);

-- 2. 會話級空間申釋 (找佔用大戶)
SELECT 
    t1.session_id,
    t1.user_objects_alloc_page_count,
    t1.internal_objects_alloc_page_count,
    t1.user_objects_dealloc_page_count,
    t1.internal_objects_dealloc_page_count,
    t.text AS CurrentSQL
FROM sys.dm_db_session_space_usage t1
JOIN sys.dm_exec_sessions t2 ON t1.session_id = t2.session_id
JOIN sys.dm_exec_requests t3 ON t2.session_id = t3.session_id
CROSS APPLY sys.dm_exec_sql_text(t3.sql_handle) t
WHERE t1.user_objects_alloc_page_count > 0 
   OR t1.internal_objects_alloc_page_count > 0
ORDER BY (t1.user_objects_alloc_page_count + t1.internal_objects_alloc_page_count) DESC;

-- 3. 任務級實時空間活動 (只有運行中任務有值)
SELECT 
    t.session_id, t.request_id, t.database_id,
    t.user_objects_alloc_page_count, t.user_objects_dealloc_page_count,
    t.internal_objects_alloc_page_count, t.internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage t
JOIN sys.dm_exec_sessions e ON t.session_id = e.session_id
JOIN sys.dm_exec_requests r ON t.session_id = r.session_id AND t.request_id = r.request_id;

六、阻塞與死鎖巡檢

-- 1. 當前阻塞鏈
SELECT 
    r.blocking_session_id AS BlockerSPID,
    r.session_id AS VictimSPID,
    r.wait_type, r.wait_time, r.wait_resource,
    DB_NAME(r.database_id) AS DB,
    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 BlockingSQL
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id <> 0;

-- 2. 死鎖圖 (需啟用 Trace Flag 1222 或 Extended Events)
-- XE 會話創建見附錄

七、性能基線指標巡檢

-- 1. 緩存命中率、PLE、目標內存
SELECT 
    cntr_value AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio' AND instance_name = '';

SELECT 
    cntr_value AS PageLifeExpectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy' AND instance_name = '';

SELECT 
    cntr_value / 1024 AS TargetMemoryMB
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Target Server Memory (KB)' AND instance_name = '';

-- 2. 批次請求/秒、編譯/重編譯/秒
SELECT 
    cntr_value AS BatchRequestsPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec' AND instance_name = '';

附錄：死鎖監控 Extended Events 會話 (推薦替代 Trace Flag)

CREATE EVENT SESSION [DeadlockMonitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_name)
)
ADD TARGET package0.event_file(SET filename=N'D:\XE\DeadlockMonitor.xel', max_file_size=50, max_rollover_files=10)
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS);
GO

-- 啟動
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;
GO

-- 查詢死鎖報告
SELECT 
    CAST(event_data AS XML) AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file('D:\XE\DeadlockMonitor*.xel', NULL, NULL, NULL)
WHERE event_data LIKE '%%';