當Eva搭上DBA航班之後

SQL Server的資料庫檔案有三種： 

• Data File：
  • Primary Data File(主要資料檔)
    1. 只能有1個，副檔名通常使用 .mdf
    2. 存有可以找到其他Files位置的功能 (除了mdf會記錄, master DB也會記錄一份)
    3. 存資料的功能
  • Secondary Data File(次要資料檔)
    1. 可以有0個或1個以上，副檔名通常使用 .ndf
    2. 存資料的功能
• Log File(記錄檔)
  • 至少會有1個或1個以上，副檔名通常使用 .ldf
  • 存有可以用來recover所有transactions data的功能
• 查詢語法：
  • sys.database_files: 可以找到所有的data file, log file的資訊

PS: SQL Server 不會強制我們使用 .mdf、.ndf 及 .ldf 副檔名，這只是幫助我們識別不同種類的檔案及用途。

Q: 為什麼需要很多data files?
A: 
1. 方便restore DB時將data files存放在不同的disk，避免一個data file太大，沒有disk space能讓他restore。
2. 分散I/O在不同的disk，避免都只用一個disk，導致對DB做動作時速度慢。

 對資料庫的Files進行分類的東西，不同的Data file可以放在不同的Filegroups，Log file則無法放在檔案群組裡。

SQL Server的資料庫檔案群組有兩種：

• Primary：
  1. 每個資料庫都預設有一個
  2. 能包含.mdf及.ndf
• User-defined：
  1. 可以有很多個
  2. 在create table時可以assign不同table去我們自訂的不同filegroup，並且去不同的disk
  3. 可以拿來做Partially backup (只backup我們想要的filegroup，則可以只backup我們想要的tables，不需要一次backup一整個DB)

當你create data files時，SQL Server就會在data storage分配page和extents給你用。

以SQL Server來說分配空間的最小單位是extent，一個extent有8個page(1個page 8KB)，故一個extent有64KB。

SQL Server的extent有分兩種：如下圖

• Mixed extent (混和區)
  1. 資料表剛創建時，data page會存放進Mixed extent
  2. 此extent裡的Page可以被不同的對象用
  3. 如果一個table小於64KB，則會分配此區給它用
  4. 混和區裡面的Page頁碼是不連續的
• Uniform extent (統一區)
  1. 同是data page，或同是index page
  2. 當table越來越大，大到8個page放不下時，會多開一個uniform extent讓你存放
  3. 頁碼連續

Q: Why need have 2 kinds of extent?
A: 當create一個table1時，SQL Server會分配還有空Page的Mixed extent給你存資料，之後會等到你的Page長到此Mixed extent滿了，SQL Server才會再分配一個的Uniform extent給你，讓此Uniform extent全部都放你的page。
避免一口氣分配64KB的extent給你，多出許多不必要的空間。

DB & Data files & Extent & Page的詳細關係如下圖：

而上面說到，SQL Server會搜尋尚有空間的extent來取得可用的Page存放資料，此動作是利用GAM Page和SGAM Page來追蹤哪些extent可用或不可用。

SQL Server把Data file分成8K的Page，並且把Data file裡的第一頁標為0號，從0號開始依序編號...
而在data files裡面，前面10頁(0號~9號)的用途都是固定的。

GAM Page和SGAM Page中對應的每個extent都有一個bit值，每一個extent都會對應到這兩頁的一個值。

GAM Page(Global Allocation Map​​)

• 第三頁，頁號2，是第一個GAM頁
• 一個GAM頁裡有64000個extent的使用訊息 (當extent擴增到超過一個GAM頁時，會再增長第二個GAM頁)
• 記錄哪些區被分派出去了
• １：完全還沒被分配的Page、可用的Page
• ０：統一區或混和區

SGAM Page(Shared Global Allocation Map​)

• 第四頁，頁號3，是第一個SGAM頁
• 一個SGAM頁裡有64000個extent的使用訊息
• 用來追蹤哪些extent被用作混和區了
• １：至少還有一個Page以上可以用的混合區
• ０：統一區或已滿的混合區

例子：
當Create 一個新的Table，SQL Server會進去GAM  Page裡面找標記為１的extent，尋找可用的extent，並且把此extent的GAM位改標為０，表示此extent被用了；因為剛create Table時 SQL Server會分配混和區給它用，所以SQL Server會去找SGAM標記為１的區，或是尋找可用並標記為０的SGAM位，並且改標為１，最後使用裡面可用的Page

相反的，如果此Table的資料長大了，Page擴增導致目前的混合區滿了，SQL Server需要分配一個統一區給Table用，則SQL Server會從GAM Page開始找標記１的，並且SGAM中值也是０又可以用的；最後此extent對應的GAM位改標為０，SGAM的值則維持０不變

結論：
(通過檢查SGAM和GAM相同位置的bit組合，就可以知道空間使用狀態)
　要找統一區：找對應的GAM中的值為１，SGAM值為０的extent 
　要找混和區：找對應的GAM中的值為０，SGAM值為１的extent 

	ＧＡＭ位	ＳＧＡＭ位
未分配，可使用	１	０
統一區或已滿的混和區	０	０
尚有可分配空間的混和區	０	１

 

除了這兩個Page之外，其他８個Page的用途下章再談...

 

--

參考文章：http://www.cnblogs.com/woodytu/p/4487310.html