SQL Server2008存儲結構之基本系統視圖

之所以寫SQL Server 2008存儲結構，很大程度上是因為看了《SQL Server 2005技術內幕存儲引擎》和《SQL Server 2008 Internals》，其次主要是為了滿足自己的好奇心和虛榮心。

　　說實話，了解SQLServer2008的存儲結構，也許並不會提高你的SQL技能，也許也不會提升你對SQL Server性能優化的能力。出於好玩的目的，希望能夠和大家分享一下。

　　本文算是對兩本書的閱讀筆記，加上自己的動手實踐和領悟;如果涉及版權問題和原創問題概不負責。

　　從直觀的角度出發，我們可以觀察到SQL Server的物理存儲由若干數據庫構成，其中

數據庫類別 數據庫名稱 數據庫描述 系統數據庫 master master 數據庫記錄 SQL Server 系統的所有系統級信息。主要包括實例范圍的元數據、端點、鏈接服務器和系統配置設置以及記錄了所有其他數據庫的存在、數據庫文件的位置以及 SQL Server 的初始化信息。 model 提供了SQL Server 實例上創建的所有數據庫的模板。 msdb 主要由 SQL Server 代理用於計劃警報和作業 tempdb tempdb 系統數據庫是一個全局資源，可供連接到 SQL Server 實例的所有用戶使用，並可用於保存顯式創建的臨時用戶對象、SQL Server 數據庫引擎創建的內部對象，行版本數據等 戶數據庫 db1/db2  

 　　如果我們在數據庫處點擊右鍵，選擇屬性，可以在文件處看到：

　　每一個數據庫無論系統數據庫還是用戶數據庫都是由兩類數據庫文件構成，即行數據數據庫文件和日志文件;而行數據數據庫文件則有一個主要數據文件和N個次要數據文件構成。

　　我們還可以再考察一下文件組頁，每個數據庫都有一個Primary主文件組和N個用戶定義文件組構成。通過對表對象應用filegroup選項，能夠將不同的表分散到不同的磁盤上，以提高系統性能。

　　數據庫又主要由表、視圖、函數、存儲過程、觸發器、類型、規則、默認值等等構成。

　　當然我們主要考察的對象是表，每一個數據庫實際上都包含一系列系統表和一系列用戶表。

　　而表又包括一系列的列、主外鍵、約束、觸發器、索引等。

　　SQLServer2008中提供了相當豐富的系統視圖，能夠從宏觀到微觀，從靜態到動態反應數據庫對象的存儲結果、系統性能、系統等待事件等等。同時也保留了與早期版本兼容性的視圖，主要差別在於SQLServer2008提供的新系統視圖一是更加全面和豐富、二是更注重命名規則。

        SQLServer2008的幾乎所有對象信息都存在於sys.objects系統視圖中，同時又在不同的系統視圖中保留了相應的副本，對於函數、視圖、存儲過程、觸發器等相應的文本對象，把相應的對象的詳細資料存於新的sys.sql_modules視圖中。

序號 對象類型 對象類型描述 相關系統表 1 AF = 聚合函數 (CLR) AGGREGATE_FUNCTION N/A 2 C = CHECK 約束 CHECK_CONSTRAINT CHECK_CONSTRAINTS 3 D = DEFAULT（約束或獨立） DEFAULT_CONSTRAINT DEFAULT_CONSTRAINTS 4 F = FOREIGN KEY 約束 FOREIGN_KEY_CONSTRAINT FOREIGN_KEYS 5 FN = SQL 標量函數 SQL_SCALAR_FUNCTION SQL_MODULES 6 FS = 程序集 (CLR) 標量函數 CLR_SCALAR_FUNCTION N/A 7 FT = 程序集 (CLR) 表值函數 CLR_TABLE_VALUED_FUNCTION N/A 8 IF = SQL 內聯表值函數 SQL_INLINE_TABLE_VALUED_FUNCTION SQL_MODULES 9 IT = 內部表 INTERNAL_TABLE INTERNAL_TABLES 10 P = SQL 存儲過程 SQL_STORED_PROCEDURE PROCEDURES
SQL_MODULES
  11 PC = 程序集 (CLR) 存儲過程 CLR_STORED_PROCEDURE N/A 12 PG = 計劃指南 PLAN_GUIDE PLAN_GUIDES 13 PK = PRIMARY KEY 約束 PRIMARY_KEY_CONSTRAINT KEY_CONSTRAINTS 14 R = 規則（舊式，獨立） RULE SQL_MODULES 15 RF = 復制篩選過程 REPLICATION_FILTER_PROCEDURE SQL_MODULES 16 S = 系統基表 SYSTEM_TABLE OBJECTS 17 SN = 同義詞 SYNONYM SYNONYMS 18 SQ = 服務隊列 SERVICE_QUEUE SERVICE_QUEUESS 19 TA = 程序集 (CLR) DML 觸發器 CLR_TRIGGER N/A 20 TF = SQL 表值函數 SQL_TABLE_VALUED_FUNCTION SQL_MODULES 21 TR = SQL DML 觸發器 SQL_TRIGGER TRIGGERS
SQL_MODULES
  22 U = 表（用戶定義類型） USER_TABLE TABLES 23 UQ = UNIQUE 約束 UNIQUE_CONSTRAINT KEY_CONSTRAINTS 24 V = 視圖 VIEW VIEWS
SQL_MODULES
  25 X = 擴展存儲過程 EXTENDED_STORED_PROCEDURE EXTENDED_PROCEDURES

對於數據庫層面的存儲結構，我們可以參看以下視圖：

--數據庫實例的概要情況
SELECT * FROM SYS.SERVERS
WHERE SERVER_ID=0
--兼容性視圖SELECT * FROM SYS.SYSSERVERS

--各個數據庫的詳細信息
SELECT * FROM SYS.DATABASES
--兼容性視圖SELECT * FROM SYS.SYSDATABASES

--文件組的詳細信息
SELECT * FROM SYS.FILEGROUPS  
--兼容性視圖SELECT * FROM SYS.SYSFILEGROUPS

--各個數據庫文件的詳細信息
SELECT * FROM SYS.MASTER_FILES
--兼容性視圖SELECT * FROM SYS.SYSALTFILES

--當前數據庫文件的詳細信息
SELECT * FROM SYS.DATABASE_FILES
--兼容性視圖SELECT * FROM SYS.SYSFILES

--數據空間的詳細情況，可以是文件組或分區方案
SELECT * FROM SYS.DATA_SPACES

 

　　關於數據庫表的存儲信息，通過以下系統表我們可以大致了解數據庫表在數據庫中是如何定義的。以下視圖提供了基本的數據庫對象信息。

--我們首先創建一張表和一些索引
CREATE TABLE dbo.test
(
  id int IDENTITY(1,1) NOT NULL,
  name char(100) NULL,
CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id ASC)
)
CREATE NONCLUSTERED INDEX IX_test ON dbo.test(name)

--表和對象詳細信息，根據表名稱查詢出object_id為
--事實上幾乎所有的用戶對象都出自於SYS.OBJECTS表
SELECT * FROM SYS.OBJECTS
WHERE type_desc='USER_TABLE' AND NAME='TEST'
--兼容性視圖SYSOBJECTS
--如果要查詢與該表相關的其他所有對象，則可以執行以下語句
SELECT * FROM SYS.OBJECTS
WHERE type_desc='USER_TABLE' AND NAME='TEST' OR
       parent_object_id in
        (SELECT object_id FROM SYS.OBJECTS
          WHERE type_desc='USER_TABLE' AND NAME='TEST')
          
--表字段詳細信息，可以查詢出相關column_id
SELECT * FROM SYS.COLUMNS
WHERE OBJECT_ID=5575058
--兼容性視圖SYSCOLUMNS

--表索引詳細情況,可以清楚的看到存在兩個索引
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID=5575058
--兼容性視圖SYSINDEXES

--表分區情況，數據庫中所有表和索引的每個分區在表中各對應一行
--此處可以看到該表有兩個分區，聚集索引即表本身，還有一個是name的非聚集索引
--partition_id 即分區的ID
--hobt_id包含此分區的行的數據堆或B樹的ID  
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID=5575058

--分配單元情況，數據庫中的每個分配單元都在表中占一行
--該表只有和SYS.PARTITIONS配合使用才有意義
SELECT * FROM SYS.ALLOCATION_UNITS

--SYS.ALLOCATION_UNITS和SYS.PARTITIONS一起使用能夠反映出某個對象的頁面分配和使用情況
SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID AND P.OBJECT_ID=5575058
UNION ALL
SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID AND P.OBJECT_ID=5575058

--返回每個分區的頁和行計數信息
SELECT * FROM SYS.DM_DB_PARTITION_STATS WHERE OBJECT_ID=5575058

--返回索引的詳細字段情況
SELECT * FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID=5575058
--兼容性視圖SYSINDEXKEYS

--以下為根據某個索引名稱獲取其相關字段的語句
DECLARE @index_fIEld_names VARCHAR(500)
SET @index_fIEld_names='';
SELECT @index_fIEld_names=@index_fIEld_names+c.name+','
  FROM SYS.INDEX_COLUMNS a,SYS.INDEXES b,SYS.COLUMNS c
WHERE a.object_id=b.object_id AND a.index_id=b.index_id
   AND a.object_id=c.object_id AND a.column_id=c.column_id
   AND b.name='IX_test2'
ORDER BY a.index_column_id
SET @index_fIEld_names=LEFT(@index_fIEld_names,LEN(@index_fIEld_names)-1)
PRINT @index_fIEld_names

--CHECK約束，數據來源sys.objects.type = 'C'
SELECT * FROM SYS.CHECK_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性視圖SYSCONSTRAINTS

--數據來源sys.objects.type = D
SELECT * FROM SYS.DEFAULT_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性視圖SYSCONSTRAINTS

--主鍵或唯一約束,數據來源sys.objects.type PK 和UQ
SELECT * FROM SYS.KEY_CONSTRAINTS WHERE OBJECT_ID=?
--兼容性視圖SYSCONSTRAINTS

--外鍵，數據來源sys.object.type = F
SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID=?  
--兼容性視圖SYSREFERENCES

--觸發器
SELECT * FROM SYS.TRIGGERS WHERE OBJECT_ID=?  

--注釋
SELECT * FROM SYS.SQL_MODULES
--兼容性視圖SYSCOMMENTS

--數據庫用戶表
SELECT * FROM SYS.DATABASE_PRINCIPALS
--兼容性視圖SYSUSERS

--數據庫數據類型表
SELECT * FROM SYS.TYPES
--兼容性視圖SYSTYPES