數據庫是存儲在系統中的已定義空間,用于對用戶對象進行分組。此空間可以拆分為多個按組組織的文件。
在SQL Server中,每個實例的數據庫限制為32,767個,每個數據庫可以存儲超過20億個對象,物理大小可達524,272太字節!
在這篇文章中,我將更深入地解釋SQL Server數據文件的物理結構。遺憾的是,我無法編寫關于此主題的所有假設,行為和詳細信息,但您將閱讀文檔結構的良好概述,以及更好地理解索引內部,文件碎片,事務日志內部等等的良好起點...
在我們開始之前,讓我們記住一些重要的概念。
您可以將數據庫文件視為普通操作系統文件。數據庫必須至少有兩個文件:一個用于數據,一個用于t-logs。
數據文件分為兩種類型:主數據文件和輔助數據文件。每個數據庫只能有一個主數據文件,它會跟蹤數據庫中的所有其他文件。按照慣例,主數據文件具有.mdf擴展名,輔助文件具有.ndf。
對于日志文件,擴展名為.ldf。每個數據庫必須至少有一個。
在Microsoft中,SQL Server中基本的數據存儲單元是頁面。分配給數據庫中的數據文件(.mdf或.ndf)的磁盤空間在邏輯上被劃分為從0到n連續編號的頁面。磁盤I / O操作在頁面級別執行。也就是說,SQL Server讀取或寫入整個數據頁。
SQL Server中的所有頁面都具有相同的結構。在頁面的頂部有一個96字節的標題,它包括PageID,頁面所屬的結構類型,頁面中的記錄數以及指向上一頁和下一頁的指針。因此,我們可以存儲8096個字節的記錄。但是,數據記錄的最大長度為8060字節,如頁面底部(最新的36個字節),位于包含行偏移信息(每行2個字節)的插槽數組中。此數組可以根據記錄的大小從底部到頂部增長,因此可以在頁面中容納更多記錄,并且偏移表將占用更多空間。這些插槽按索引鍵定義的順序存儲。對于堆,沒有特別的訂單。
SQL Server中的空間分配在稱為“范圍”的部分中進行管理,這些部分基本上是一組八個邏輯上連續的頁面。所以,很多64 KB(8 * 8 KB /頁)。
擴展區的使用使分配系統更有效。這些單位可分為兩種類型:
從混合范圍開始為新表或索引分配頁面。當對象增長到8頁時,所有將來的分配都將使用統一的范圍。
有關擴展區的所有信息都在GAM,SGAM和IAM頁面中進行跟蹤,也稱為“分配位圖”。
基本上,SQL Server數據文件具有以下基本結構,我們將分析:
所有文件都在頁碼0中有一個標題。此標頭存儲有關該特定文件的元數據,并且checkdb無法恢復。如果發生損壞,您必須恢復整個文件。
您可以使用以下DBCC命令瀏覽某些數據庫的標頭:
DBCC fileheader [({'dbname'| dbid} [,fileid])
您將在RedoStartLSN,BindingId,SectorSize,Status和Growth記錄中找到有用的信息。
PFS識別分配狀態并確定可用空間量。這些頁面包含每頁1個字節,覆蓋文件中的8,088頁。
基本的PFS結構是:
每個文件的第一頁是PFS,在第一個PFS之后每8,000頁分配另一個PFS。
將擴展區分配給對象后,數據庫引擎使用PFS頁面記錄擴展區中的哪些頁面已分配或空閑。僅當SQL Server找不到具有足夠空間來容納數據的頁面時,它才會分配新的擴展區。
SQL Server使用兩種特殊類型的頁面來記錄已分配的范圍以及范圍可用的使用類型(混合或統一):
全局分配映射(GAM)頁面:跟蹤任何類型的擴展區的分配。GAM對于所涵蓋的區間中的每個范圍都有一點。位0表示相應的范圍正在使用中,而在另一方面,該位是1個軌道空閑范圍。GAM可以覆蓋大約64,000個范圍,或幾乎4 GB的數據。因此,每4 GB文件大小有一個GAM頁面。
共享全局分配映射(SGAM)頁面:這與GAM相同,但它僅指混合擴展區。SGAM在其覆蓋的時間間隔內的每個范圍都有一點。如果該位為1,則使用的范圍是混合范圍并且具有空閑頁面,并且0位表示非混合范圍或其頁面全部在使用中的混合范圍。
這是第7頁(第6頁)。它跟蹤自上次完整數據庫備份以來文件中的哪些擴展區已被修改。
與GAM和SGAM一樣,BCM頁面在其文件的覆蓋扇區中的每個范圍都有1位。第一個BCM頁面位于每個數據文件的第七頁,此后每511,230頁。
也稱為最小記錄映射(ML Map),它是第8頁(第7頁),在文件中的范圍用于最小或批量記錄操作時使用。
BCM頁面上的每個位表示一個范圍,如果該位為1,則自上次事務日志備份以來,此范圍已由最小日志記錄的批量操作更改。第一個BCM頁面位于每個數據文件的第八頁,此后每511,230頁。每次發生日志備份時,BCM頁面上的所有位都將重置為0。
文件1中的第9頁可能是數據庫中最重要的頁面 - 引導頁面。有關整個數據庫的基本元數據存儲在此頁面中。如果此頁面已損壞,則checkdb無法修復此頁面,并且需要還原文件編號1才能恢復數據庫。
要獲取有關數據庫引導頁面的信息,可以執行DBCC PAGE指向文件1中的第9頁或DBCC dbinfo [('dbname')],始終激活跟蹤標志3604(DBCC TRACEON(3604))。
IAM頁面的功能是跟蹤屬于表或索引的范圍。IAM是一個位圖,它將范圍引用到對象。這種頁面覆蓋4 GB范圍,屬于GAM間隔,并且不位于文件中的已知位置。
對于每個4 GB范圍的數據,分區和分配單元類型,需要IAM頁面進行跟蹤。
順便說一下,分配單元是一組基于頁面類型管理數據的頁面,可以有三種類型:
例如,具有所有三種類型的數據(行內,行和行溢出)的四個分區上的表具有至少12個IAM頁面。
上述頁面有四種可能的組合有效:
知道單個IAM頁面覆蓋文件中的4-GB范圍(512.000頁)并且屬于單個GAM間隔,如果分配單元包含來自多個文件的范圍或多于一個4-GB范圍,則會發生什么?文件?將在IAM鏈中鏈接多個IAM頁面,這是IAM頁面的無序序列。
基本上,IAM頁面具有包含關于其屬于哪個GAM間隔的信息的頭部,序列號和鏈接信息。
對于這些演示,我將使用AdventureWorks2008R2數據庫進行更簡單的再現。您可以在http://msftdbprodsamples.codeplex.com/上下載此示例數據庫和其他數據庫。
那么讓我們開始演示分配位圖。%% lockres %%和%% physloc %%機制將返回行的物理位置。所以我將使用以下查詢返回數據庫日志表的前5行,顯示每行的物理位置。
SELECT TOP (5 ) a 。%% physloc %% 作為 地址,
a 。%% lockres %% 作為 LockHashValue ,
a 。*
來自 dbo 。databaselog AS 一
我得到了以下幾行,我將探索第二行。
要探索第二行內部,我將使用數據庫控制臺命令(DBCC)PAGE。請注意,1:150:1表示FILE:PAGE:SLOT。所以這是命令,使用第150頁:
DBCC traceon (3604 ) - 我們需要在執行DBCC PAGE之前激活此標志
DBCC 頁 (adventureworks2008r2 , 1 , 150 , 2 )
DBCC traceoff (3604 )
執行返回了一個很長的結果,但是現在讓我們專注于Header部分中的Allocation Status:
分配狀態
GAM(1:2)=分配的SGAM(1:3)=
未分配的PFS(1:1)= 0x63 MIXED_EXT已分配95_PCT_FULL DIFF(1:6)=已更改
ML(1:7)=未MIN_LOGGED
基本上我們可以獲得有關此頁面的所有分配信息以及跟蹤此頁面的PFS,GAM,SGAM,DCM和BCM。
在頁面標題之后,我們有數據。這是一個示例:
lot 0,Offset 0x60,Length 2732,DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 2732
內存轉儲@ 0x0000000009ABA060
0000000000000000:30001000 01000000 5d85e400 23a00000†0 ......。]ä。#..
0000000000000010:08000006 0027003f 00450055 00db03ac†... ..'。?。EUÛ.¬0000000000000020
:0a640062 006f0043 00520045 00410054†.dboCREAT
0000000000000030:0045005f 00540041 0042004c 00450064† .E ._。TABLEd
0000000000000040:0062006f 00450072 0072006f 0072004c†.boErrorL
0000000000000050:006f0067 00430052 00450041 00540045†.ogCREATE
0000000000000060:00200054 00410042 004c0045 0020005b†。.TABLE。[
0000000000000070:00640062 006f005d 002e005b 00450072†.dbo] ... [.Er
0000000000000080:0072006f 0072004c 006f0067 005d0028†.rorLog]。(
0000000000000090:000d000a 00200020 00200020 005b0045†... ... ... [。E
00000000000000A0:00720072 006f0072 004c006f 00670049†.rrorLogI
并得出結論,行偏移。這樣,您可以看到頁面解剖的所有三個部分,如本文前面所述:
OFFSET表:
行 - 偏移量
5(0x5) - 7017(0x1b69)
4(0x4) - 6774(0x1a76)
3(0x3) - 5713(0x1651)
2(0x2) - 4560(0x11d0)
1(0x1) - 2828(0xb0c)
0(0x0) - 96(0x60)
現在我將執行一個查詢,它將返回表DatabaseLog的所有分配單元。 為此,我將關聯sys.partitions和sys.system_internals_allocation_units DMV,對于每個分配單元,我們可以獲取頁面信息,這將引導我們進入IAM信息/鏈。
我正在使用由Kimberley Tripp完成的頁面轉換算法(http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work .aspx)。
所以,這里的查詢:
SELECT OBJECT_NAME(p 。OBJECT_ID ) AS '表名' ,
我。將 AS 命名為'indexName' ,
p 。partition_number ,
au 。type_desc ,
CONVERT (VARCHAR(6 ), CONVERT (INT, 子串 (AU 。first_page , 6 , 1 ) +
子串 (
AU 。first_page , 5 , 1 )))
+ ':'
+ CONVERT (VARCHAR(20 ), CONVERT (INT, 子串 (AU 。first_page , 4 , 1 ) +
子串 (AU 。first_page , 3 , 1 ) + 子串 (AU 。first_page , 2 , 1 ) +
子 (
AU 。first_page , 1 , 1 ))) AS 'FIRSTPAGE' ,
CONVERT (VARCHAR(6 ), CONVERT (INT, 子串 (AU 。root_page , 6 , 1 ) +
子 (
AU 。root_page , 5 , 1 )))
+ ':'
+ CONVERT (VARCHAR(20 ), CONVERT (INT, 子串 (AU 。root_page , 4 , 1 ) +
子串
(AU 。root_page , 3 , 1 ) + 子串 (AU 。root_page , 2 , 1 ) + 子串
(AU 。root_page ,
1 , 1 ))) AS 'rootPage' ,
CONVERT (VARCHAR(6 ), CONVERT (INT, 子串 (AU 。first_iam_page , 6 , 1 ) +
子串 (AU 。first_iam_page , 5 , 1 )))
+ ':'
+ CONVERT (VARCHAR(20 ), CONVERT (INT, Substring (au。first_iam_page , 4 , 1 )
+
子串 (AU 。first_iam_page , 3 , 1 ) + 子串 (AU 。first_iam_page , 2 , 1 )
+
子串 (AU 。first_iam_page , 1 , 1 ))) AS 'firstIamPage'
FROM SYS 。索引 AS i
JOIN sys 。分區 AS p
ON i 。object_id = p 。object_id
和 我。index_id = p 。index_id
JOIN sys 。system_internals_allocation_units AS au
ON p 。hobt_id = au 。container_id
WHERE p 。object_id = Object_id('databaselog' )
ORDER BY tablename ;
這是結果(請注意,在執行DBCC IND時,您可以在更加分散的布局中使用相同的信息):
如果我們在選項3的根頁面中執行DBCC PAGE,我們可以獲得屬于分配單元的所有IAM鏈。在這種情況下,我選擇屬于聚簇索引PK_DatabaseLog_DatabaseLogID的IN_ROW_DATA。
DBCC TRACEON (3604 )
DBCC 頁 (adventureworks2008r2 , 1 , 1270 , 3 )
DBCC TRACEOFF (3604 )
在輸出中,檢查ChildPageID,可以按正確的順序觀察鏈所擁有的所有頁面:
現在,您可以使用DBCC PAGE瀏覽屬于此分配單元的所有數據。在此IAM演示中,可以驗證所引用表的所有分配單元,在本例中為4,并檢查其中一個分配單元的IAM鏈。
存儲過程,視圖,功能在哪里?顯然,存儲到相同的文件結構中!但是......我們怎么做到這一點?
為了證明這一點,我們需要使用專用管理員連接(DAC)進行連接才能訪問sys.sysobjvalues。在這種情況下,我將sys.sysobjvalues與sysobjects表交叉,只顯示類型為“P”的對象(存儲過程)。這是查詢:
SELECT v 。%% physloc %% 作為 地址,
CONVERT (VARCHAR(6 ), CONVERT (INT, 子串 (v 。%% physloc %% , 6 , 1 ) +
子串 (
v 。%% physloc %% , 5 , 1 )))
+ ':'
+ CONVERT (VARCHAR(20 ), CONVERT (INT, 子串 (v 。%% physloc %% , 4 , 1 ) +
子串 (v 。%% physloc %% , 3 , 1 ) + 子串 (v 。%% physloc %% , 2 , 1 ) +
子串 (v
。%% physloc %% , 1 , 1 ))) AS 'rootPage',
o 。命名 對象名,
鑄造(v 。imageval AS VARCHAR(最大值)) 代碼
FROM SYS 。對象 o
INNER JOIN 系統。sysobjvalues v
ON o 。object_id = v 。objid
WHERE o 。type = 'P'
這是輸出:
沃利亞!現在您知道對象存儲的確切頁面,您可以瀏覽它。如果過程是加密的,也可以解密代碼。我建議閱讀這篇文章:http://williamsorellana.org/page/2/
讓我們探索對象“uspPrintError”,它存儲在第257頁:
DBCC TRACEON (3604 )
DBCC 頁 (adventureworks2008r2 , 1 , 257 , 3 )
DBCC TRACEOFF (3604 )
所以,在那個巨大輸出的中間,我們可以在插槽13上找到對象(我只會輸出一部分):
插槽13偏移量0x1a6b長度765
記錄類型= PRIMARY_RECORD記錄屬性= NULL_BITMAP VARIABLE_COLUMNS
記錄大小= 765
內存轉儲@ 0x0000000009ABBA6B
0000000000000000:30001100 0102797f 4d010000 00000000†0 ...... ..yM ......
0000000000000010:000600c0 02002000 fd023801 02000000†... ........ ... ...
0000000000000020:0d0a0d0a 2d2d2075 73705072 696e7445†... .- uspPrintE
0000000000000030:72726f72 20707269 6e747320 6572726f†rror prints erro
0000000000000040:7220696e 666f726d 6174696f 6e206162†r information ab
0000000000000050 :6f757420 74686520 6572726f 72207468†出錯誤第
0000000000000060:61742063 61757365 64200d0a 2d2d2065†在引起..- E
0000000000000070:78656375 74696f6e 20746f20 6a756d70†xecution跳
0000000000000080:20746f20 74686520 43415443 4820626c†到CATCH BL
0000000000000090:6f636b20 6f662061 20545259 2e2e2e43 †嘗試... C
00000000000000A0:41544348 20636f6e 73747275 63742e20†ATCH構造。
00000000000000B0:0d0a2d2d 2053686f 756c6420 62652065†..-應通過電子郵件
00000000000000C0:78656375 74656420 20776974 66726f6d†從機智xecuted
00000000000000D0:68696e20 74686520 73636f70 65206f66†欣的范圍
00000000000000E0:20612043 41544348 20626c6f 636b206f†CATCH塊ø
00000000000000F0:74686572 77697365 200d0a2d 2d206974†therwise ..- it
0000000000000100:2077696c 6c207265 7475726e 20776974†將返回
0000000000000110:686f7574 20707269 6e74696e 6720616e†hout打印
0000000000000120:79206572 726f7220 696e666f 726d6174†y錯誤信息
0000000000000130:696f6e2e 0d0a4352 45415445 2050524f†ion ... CREATE PRO
0000000000000140:43454455 5245205b 64626f5d 2e5b7573†CEDURE [dbo]。[us
0000000000000150:70507269 6e744572 726f725d 200d0a41†pPrintError] ..A
那么現在,如果我們......
DROP PROCEDURE uspprinterror
...并快速閱讀頁面內容標題:
DBCC TRACEON (3604 )
DBCC 頁 (adventureworks2008r2 , 1 , 257 , 3 )
DBCC TRACEOFF (3604 )
我們將得到以下輸出:
PAGE HEADER:
第@ 0x0000000082582000頁
m_pageId =(1:257)m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId(AllocUnitId.idObj)= 60 m_indexId(AllocUnitId.idInd)= 1元數據:AllocUnitId = 281474980642816
元數據:PartitionId = 281474980642816元數據: IndexId = 1
元數據:ObjectId = 60 m_prevPage =(1:23236)m_nextPage =(1:22959)
pminlen = 17 m_slotCnt = 35 m_freeCnt = 2889
m_freeData = 7528 m_reservedCnt = 0 m_lsn =(554:35134:9)
m_xactReserved = 0 m_xdesId =(0:1586506)m_ghostRecCnt = 1
我們發現有一個Ghost記錄(m_ghostRecCnt = 1),我們在第13個插槽中有以下信息:
插槽13偏移量0x1a6b長度765
記錄類型= GHOST_DATA_RECORD記錄屬性= NULL_BITMAP VARIABLE_COLUMNS
記錄大小= 765
內存轉儲@ 0x0000000009ABBA6B
0000000000000000:3c001100 0102797f 4d010000 00000000†<... ..%......
0000000000000010:000600c0 02002000 fd023801 02000000†... ........ ... ...
0000000000000020:0d0a0d0a 2d2d2075 73705072 696e7445†... .- uspPrintE
0000000000000030:72726f72 20707269 6e747320 6572726f†rror prints erro
0000000000000040:7220696e 666f726d 6174696f 6e206162†r information ab
0000000000000050 :6f757420 74686520 6572726f 72207468†出錯誤第
0000000000000060:61742063 61757365 64200d0a 2d2d2065†在引起..- E
0000000000000070:78656375 74696f6e 20746f20 6a756d70†xecution跳
0000000000000080:20746f20 74686520 43415443 4820626c†到CATCH BL
0000000000000090:6f636b20 6f662061 20545259 2e2e2e43 †嘗試... C
00000000000000A0:41544348 20636f6e 73747275 63742e20†ATCH構造。
00000000000000B0:0d0a2d2d 2053686f 756c6420 62652065†..-應為e
00000000000000C0:78656375 74656420 66726f6d 20776974†xffcuted from wit
00000000000000D0:68696e20 74686520 73636f70 65206f66†hin范圍
00000000000000E0:20612043 41544348 20626c6f 636b206f†a CATCH塊o
讓我們等一下再檢查一下......
DBCC traceon (3604 )
DBCC 頁 (adventureworks2008r2 , 1 , 257 , 3 )
DBCC traceoff (3604 )
輸出:
PAGE HEADER:
第@ 0x0000000082582000頁
m_pageId =(1:257)m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId(AllocUnitId.idObj)= 60 m_indexId(AllocUnitId.idInd)= 1元數據:AllocUnitId = 281474980642816
元數據:PartitionId = 281474980642816元數據:IndexId = 1
元數據:ObjectId = 60 m_prevPage =(1:23236)m_nextPage =(1:22959)
pminlen = 17 m_slotCnt = 34 m_freeCnt = 3656
m_freeData = 7528 m_reservedCnt = 0 m_lsn =(554:35140:3)
m_xactReserved = 0 m_xdesId =(0:1586506)m_ghostRecCnt = 0
m_tornBits = -1445838435
而對于Slot 13:
插槽13偏移0x330長度40
記錄類型= PRIMARY_RECORD記錄屬性= NULL_BITMAP VARIABLE_COLUMNS
記錄大小= 40
內存轉儲@ 0x0000000009ABA330
0000000000000000:30001100 01aaa153 4e000000 00000000†0 ....a¡N......
0000000000000010:00060000 02002000 28003801 02000000†......(。8 ...
0000000000000020:2828302e 30302929††††††††††††††††††††††††
記錄m_ghostRecCnt現在為0,我們再也找不到過程代碼了。此外,objid記錄指向另一個項目。
無論如何,如果你在選項2中運行DBCC PAGE,你仍然可以找到一些對象軌道!
基本上,頁面現在是“免費的”,但字節保持不變,直到將某些內容寫入該空間。所以,幸運的是,你可以在緊急情況下恢復對象。真棒!:)