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

bq_wang發表於2010-10-20
原帖首發於it168專稿,連結為http://tech.it168.com/a2010/0902/1098/000001098556_1.shtml
  
之所以寫SQL Server 2008儲存結構,很大程度上是因為看了《SQL Server 2005技術內幕儲存引擎》和《SQL Server 2008 Internals》,其次主要是為了滿足自己的好奇心和虛榮心。

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

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

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

資料庫類別資料庫名稱資料庫描述
系統資料庫mastermaster 資料庫記錄 SQL Server 系統的所有系統級資訊。主要包括例項範圍的後設資料、端點、連結伺服器和系統配置設定以及記錄了所有其他資料庫的存在、資料庫檔案的位置以及 SQL Server 的初始化資訊。
model提供了SQL Server 例項上建立的所有資料庫的模板。
msdb主要由 SQL Server 代理用於計劃警報和作業
tempdbtempdb 系統資料庫是一個全域性資源,可供連線到 SQL Server 例項的所有使用者使用,並可用於儲存顯式建立的臨時使用者物件、SQL Server 資料庫引擎建立的內部物件,行版本資料等
戶資料庫db1/db2 


   如果我們在資料庫處點選右鍵,選擇屬性,可以在檔案處看到:

  每一個資料庫無論系統資料庫還是使用者資料庫都是由兩類資料庫檔案構成,即行資料資料庫檔案和日誌檔案;而行資料資料庫檔案則有一個主要資料檔案和N個次要資料檔案構成。

  我們還可以再考察一下檔案組頁,每個資料庫都有一個Primary主檔案組和N個使用者定義檔案組構成。透過對錶物件應用filegroup選項,能夠將不同的表分散到不同的磁碟上,以提高系統效能。

  資料庫又主要由表、檢視、函式、儲存過程、觸發器、型別、規則、預設值等等構成。

  當然我們主要考察的物件是表,每一個資料庫實際上都包含一系列系統表和一系列使用者表。

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

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

        SQLServer2008的幾乎所有物件資訊都存在於sys.objects系統檢視中,同時又在不同的系統檢視中保留了相應的副本,對於函式、檢視、 儲存過程、觸發器等相應的文字物件,把相應的物件的詳細資料存於新的sys.sql_modules檢視中。

序號物件型別物件型別描述相關係統表
1AF = 聚合函式 (CLR)AGGREGATE_FUNCTIONN/A
2C = CHECK 約束CHECK_CONSTRAINTCHECK_CONSTRAINTS
3D = DEFAULT(約束或獨立)DEFAULT_CONSTRAINTDEFAULT_CONSTRAINTS
4F = FOREIGN KEY 約束FOREIGN_KEY_CONSTRAINTFOREIGN_KEYS
5FN = SQL 標量函式SQL_SCALAR_FUNCTIONSQL_MODULES
6FS = 程式集 (CLR) 標量函式CLR_SCALAR_FUNCTIONN/A
7FT = 程式集 (CLR) 表值函式CLR_TABLE_VALUED_FUNCTIONN/A
8IF = SQL 內聯表值函式SQL_INLINE_TABLE_VALUED_FUNCTIONSQL_MODULES
9IT = 內部表INTERNAL_TABLEINTERNAL_TABLES
10P = SQL 儲存過程SQL_STORED_PROCEDUREPROCEDURES
SQL_MODULES
 
11PC = 程式集 (CLR) 儲存過程CLR_STORED_PROCEDUREN/A
12PG = 計劃指南PLAN_GUIDEPLAN_GUIDES
13PK = PRIMARY KEY 約束PRIMARY_KEY_CONSTRAINTKEY_CONSTRAINTS
14R = 規則(舊式,獨立)RULESQL_MODULES
15RF = 複製篩選過程REPLICATION_FILTER_PROCEDURESQL_MODULES
16S = 系統基表SYSTEM_TABLEOBJECTS
17SN = 同義詞SYNONYMSYNONYMS
18SQ = 服務佇列SERVICE_QUEUESERVICE_QUEUESS
19TA = 程式集 (CLR) DML 觸發器CLR_TRIGGERN/A
20TF = SQL 表值函式SQL_TABLE_VALUED_FUNCTIONSQL_MODULES
21TR = SQL DML 觸發器SQL_TRIGGERTRIGGERS
SQL_MODULES
 
22U = 表(使用者定義型別)USER_TABLETABLES
23UQ = UNIQUE 約束UNIQUE_CONSTRAINTKEY_CONSTRAINTS
24V = 檢視VIEWVIEWS
SQL_MODULES
 
25X = 擴充套件儲存過程EXTENDED_STORED_PROCEDUREEXTENDED_PROCEDURES

 對於資料庫層面的儲存結構,我們可以參看以下檢視:

#div_code img { border: 0px none; }
--資料庫例項的概要情況
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

 

  關於資料庫表的儲存資訊,透過以下系統表我們可以大致瞭解資料庫表在資料庫中是如何定義的。以下檢視提供了基本的資料庫物件資訊。

#div_code img { border: 0px none; }
--我們首先建立一張表和一些索引
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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6517/viewspace-676414/,如需轉載,請註明出處,否則將追究法律責任。

相關文章