萬盛學電腦網

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

深入解析 SQL Server 系統視圖:核心架構與常用分類

SQL Server 的系統視圖是資料庫管理員與開發人員洞察資料庫內部運作、監控效能、排查問題的關鍵工具。本文將系統性整理 SQL Server 2008 及後續版本中核心系統視圖的分類、用途與查詢技巧,幫助您從宏觀架構到微觀物件快速定位所需元數據。

一、 為什麼需要了解系統視圖?

SQL Server 將所有物件資訊(資料表、視圖、預存程序、函數、觸發器、索引、約束等)集中存放於系統目錄中。早期版本透過 sysobjects、syscolumns 等系統表直接暴露,但結構鬆散、命名不一、難以擴展。自 SQL Server 2005 起,微軟重構為系統視圖體系,優勢在於:

  • 命名規範統一:採用 sys. 前綴,分類清晰(如 sys.tables、sys.indexes)。
  • 資訊更全面:涵蓋靜態結構定義、動態執行狀態、等待統計、資源消耗等多維度。
  • 向後相容:保留 sysobjects 等舊視圖別名,舊腳本無需立即重寫。

二、 核心系統視圖分類速查表

以下表格整理自 sys.objects 的 type 欄位代碼,對應常見物件類型與主要關聯視圖:

代碼 物件類型 說明 主要關聯視圖
UUSER_TABLE使用者資料表sys.tables, sys.columns, sys.indexes
VVIEW視圖sys.views, sys.sql_modules
PSQL_STORED_PROCEDURESQL 預存程序sys.procedures, sys.sql_modules
FN / IF / TFSQL_SCALAR_FUNCTION / SQL_INLINE_TABLE_VALUED_FUNCTION / SQL_TABLE_VALUED_FUNCTION純量/內聯/多語句表值函數sys.sql_modules
TRSQL_TRIGGERDML 觸發器sys.triggers, sys.sql_modules
PK / FQ / UQ / C / DPRIMARY_KEY / FOREIGN_KEY / UNIQUE / CHECK / DEFAULT_CONSTRAINT各類約束sys.key_constraints, sys.check_constraints, sys.default_constraints
ITINTERNAL_TABLE內部表(如 XML 索引、Service Broker 隊列)sys.internal_tables
SSYSTEM_TABLE系統基底表sys.objects (type='S')
SNSYNONYM同義詞sys.synonyms
PC / FS / FT / TACLR_STORED_PROCEDURE / CLR_SCALAR_FUNCTION / CLR_TABLE_VALUED_FUNCTION / CLR_TRIGGERCLR 組件物件sys.assemblies, sys.assembly_modules
PGPLAN_GUIDE計畫指南sys.plan_guides
XEXTENDED_STORED_PROCEDURE擴展預存程序sys.extended_procedures

三、 三大核心視圖組合:結構、程式碼、執行狀態

1. 靜態結構:sys.objects + sys.columns + sys.indexes

-- 查詢某資料庫所有使用者資料表及其欄位、索引數量
SELECT 
    t.name AS TableName,
    COUNT(DISTINCT c.column_id) AS ColumnCount,
    COUNT(DISTINCT i.index_id) AS IndexCount
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
LEFT JOIN sys.indexes i ON i.object_id = t.object_id
WHERE t.type = 'U'
GROUP BY t.name
ORDER BY TableName;

2. 程式碼定義:sys.sql_modules

所有「含有程式碼」的物件(預存程序、函數、觸發器、視圖、預設值、規則、計畫指南)的原始 T-SQL 定義均儲存於 sys.sql_modules.definition(nvarchar(max)),取代舊版 syscomments 的 4000 字元限制。

-- 搜尋包含特定關鍵字的預存程序/函數
SELECT 
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%NOLOCK%'
  AND o.type IN ('P','FN','IF','TF','TR','V');

3. 動態管理視圖(DMV):sys.dm_* 系列

DMV 不屬於 sys.objects 目錄,但才是效能調校的主戰場。常用分類:

  • 執行狀態:sys.dm_exec_requests、sys.dm_exec_sessions、sys.dm_exec_connections
  • 查詢計畫與統計:sys.dm_exec_query_stats、sys.dm_exec_cached_plans、sys.dm_exec_query_plan
  • 索引使用與碎片:sys.dm_db_index_usage_stats、sys.dm_db_index_physical_stats
  • 等待與資源:sys.dm_os_wait_stats、sys.dm_os_waiting_tasks、sys.dm_tran_locks
  • 記憶體/IO/CPU:sys.dm_os_memory_clerks、sys.dm_io_virtual_file_stats、sys.dm_os_schedulers

四、 實戰查詢範例:快速掌握資料庫全貌

1. 列出所有使用者資料表大小與列數

SELECT 
    t.name AS TableName,
    p.rows AS RowCount,
    (SUM(a.total_pages) * 8) / 1024.0 AS TotalSpaceMB,
    (SUM(a.used_pages) * 8) / 1024.0 AS UsedSpaceMB,
    COUNT(c.column_id) AS ColumnCount
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.type = 'U' AND i.index_id IN (0,1) -- heap 或 clustered
GROUP BY t.name, p.rows
ORDER BY TotalSpaceMB DESC;

2. 找出未被使用的索引(候選刪除)

SELECT 
    DB_NAME() AS DatabaseName,
    o.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
    s.user_updates AS TotalWrites,
    s.last_user_seek, s.last_user_scan, s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
JOIN sys.objects o ON o.object_id = i.object_id
WHERE o.type = 'U'
  AND s.database_id = DB_ID()
  AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
  AND s.user_updates > 0
ORDER BY TotalWrites DESC;

3. 監控長時間執行的查詢

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads, r.writes, r.logical_reads,
    SUBSTRING(qt.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
         ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS RunningStatement,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50 -- 排除系統 session
  AND r.total_elapsed_time > 30000; -- 超過 30 秒

五、 相容性視圖與遷移建議

舊版系統表(sysobjects、syscolumns、sysindexes 等)在 SQL Server 2008+ 仍可查詢,實為視圖別名,微軟不保證未來版本持續提供。建議:

  1. 新專案一律使用 sys.* 視圖與 DMV。
  2. 現有維護腳本逐步改寫,優先替換高頻查詢。
  3. 利用 sys.system_objects 查詢系統內建物件,避免誤判使用者物件。

六、 小結

系統視圖是 SQL Server 的「X 光機」與「儀表板」。熟練掌握 sys.objects 靜態目錄、sys.sql_modules 程式碼倉庫、sys.dm_* 動態管理視圖三大體系,即可覆蓋 90% 以上的日常開發、運維、效能調校場景。建議收藏本文速查表,並在實際環境中多動手練習上述範例查詢,將元數據轉化為可執行的優化行動。

copyright © 萬盛學電腦網 all rights reserved