班浩呆的技術研究網站

[Proformace] SQL Server 2005 Data Split – SQL Server
資料表分割

在某些大型資料庫的的效能運作上,Microsoft的SQL Server 提供了一個功能叫資料表分割.這個功能可以讓您將資料庫中的資料表分別置放
於實體檔案上.在SQL Server建立起來的時候會建立一個對應的mdf檔案給你所建立的資料庫.我們也可以為這一些資料庫來建立實體的ndf群組檔案,您可以將這些群組檔案
分別置放於不同的硬碟上來提升硬碟IO效能.例如:有些零售產業,每個月會產生很旁大的銷售資料,我們可以設定一個切割規則,舉例來說.我們可以將一個龐大的銷售資料
以月份或是年份來切割,將資料分別至於不同的實體檔案來提高效能.

• 在資料庫要完成資料切割配置的工作會有三個步驟.
  1.建立切割資料的規則.[Function]
  2.建立對應資料切割規則的Schema
  3.建立資料庫,並且指定套用到哪一個資料規則Schema.

有些人會納悶,直接寫把規則寫在Schema不就好了,為什麼還要多一層Function ? 在這邊我用三張圖來解釋為什麼要這樣處理
?
首先參考的下圖,假設我有一個批次的銷售資料要寫到資料庫去,而我後端有三個實體檔案群組,分別用來歸檔2006,2007,2008年的銷售資料.假設我們將資料分割的
參考規則綁定在資料表上時,當這個規則只有給這個單一的資料庫使用的話,那當然是OK的,但是我們接著看到下一張圖:

根據上面的例子,如果我現在想要將進貨資料也分年來歸檔,分到不同的實體檔案來歸檔,但是剛才如果我們是把資料分割規則寫死綁定在資料表上,這樣我們就無法
抽離這個資料規則來給其他資料表使用,然後我在們看到下面一張圖:

如果您有寫過程式,那您一定對Function(資料庫函數)不陌生.在這邊觀念是相同的,我們可以將資 料規則抽離成一個Function,然後每一個資料表就可以撰寫一個Schema來套用到您所想要對應到的資料庫規則,這樣就可以達成資料庫規則重複利用 的型態,所以並非SQL
Server故意要把規則弄得這麼麻煩,接下來我們就進入到規則設定的操作步驟.

首先我們先建立一個資料庫,這邊我們就替它命名為 [DataBase]吧.

首先我們建立5個新的檔案群組,這邊我們先簡單化事件,稍後我們把要建立的ndf檔案指定在這些檔案群組上.

接著我們在建立資料群組的檔案,這邊我們建立5個ndf檔案,分別是2006Sales,2007Sales,2008Sales,2009Sales,2010Sales.顧名思義這邊我們要來分別置放不同年份的銷售資料.在這邊您可以將不同
ndf檔案置放到不同的硬碟上或是RAID裝置,利用硬碟IO來提升資料庫在硬碟上的效能,在預設值上我們給這些ndf檔案都初始值為1MB,成長一次也設定為1MB,這樣稍後我們會用來觀察
資料庫的成長.

這時我們可以在實體硬碟上看到我們剛剛建立的5個ndf實體檔案,每個檔案都是1MB.

接著我們要建立資料切割的準則,也就是[Function],建立準則時就是有四個概念. 其實這部分主要就是定義四個部分,如果您有寫過程式的經驗,一定不會陌生首先就是要定義function名子(總是要知道這個Function叫甚麼名子吧)!!
1.定義Function名子
2.定義要以甚麼型態來切割.例如以時間,或是以金額….
3.定義資料從左邊開始切割還是右邊.(簡單的說明的話就是 LEFT就是 =< 切割點 > , Right 就是 < 切割點
>= , 例如如果我們以年分來分割資料 , 假設我們要切割2003年的資料與2004年的資料 , 設定成LEFT時 , 也就是=<
在左邊,這樣我們切割條件就要寫成2003-13-31.代表2003年12月31日也包含在2003年之中.反之,如果我們條件設定為Right,那就要設定為2004-01-01,代表2004-01-01包括在2004年之中.
)
4.設下切割點.(例如我要將過去的銷售資料每年分開,或是按月來分)

下方我們建立一個名稱為[SaleDataSplit]的Function,然後我們將會以年分來分割這個資料.並且使用資料庫中的[smalldatetime]
的資料格式來比對要分割的資料.

建立完成可以在左邊的管理介面中的資料分割函數中看到我們剛剛建立起來的[SaleDataSplit]函數.

• 接下來我們要建立Schema,建立Schema時有三個要點.
• 1.這個Schema的名子,總是要知道這個Schema要叫甚麼名子吧.
• 2.這個Schema要套用那一個資料分割規則[Function].
• 這個Schema要對應到哪些檔案群組.

在這次範例我們採對應到群組的方式,並且必須要對應到五個群組.因為我們剛剛用時間點來切割資料,而我們分割的時間點為2007年,2008年,2009年,2010年,
四個時間分割點,而實際上我們的資料在判斷的時候有可能會出現小於2007年的資料,在這個範例中我們就把他小於2007年資料歸檔為[SDGOLD].
參照以下指令輸入來建立檔案.

建立完成後,我們可以在資料分割配置裡面看到一個 [ PS_SaleData ] .

接下來我們來建立資料庫了,這邊我們利用資料庫指令來建立,這部分比較要注意的是,建立資料庫的時候,在最後我們要指定這個資料庫套用
哪一個 [Schema],然後你套用的那個Schema會套用到一個 [ Function ],那個[ Function]會用一種資料型態來判斷切割資料庫,這個範例我們是用[
SmallDatetime ] ,所以再套用資料型態的時候,我們也要指定一個[ SmallDatatime ]的欄位當作資料輸入時的判斷依據.

這邊我們建立完成,可以看到我們剛剛建立的資料庫 .

接著,我要利用我自己事先建立過的一個資料庫,裡面有一個銷售資料表,我在裡面塞了快400萬筆的資料.這個資料都是存放在同一個
Mdf的實體檔案中,我要將這個資料表的資料匯入到我剛剛建立的有套用資料分割的資料表中,來驗證資料是否真的會被歸檔到不相同的實體ndf檔案之中.

資料匯出匯入精靈在這部分我們就不再墬訴.這個畫面可以成功看到我們成功匯出與匯入了392萬筆資料.

來到實體的ndf檔案之中,我們發現了每個ndf檔案之中被塞入了大小不等的資料了,並且也改變了實體ndf檔案的大小.

本篇文章參考以下網站資訊,.

提升查詢效能的資料分割設計

http://msdn.microsoft.com/zh-tw/library/ms177411.aspx

CREATE PARTITION FUNCTION (Transact-SQL)

http://msdn.microsoft.com/zh-tw/library/ms187802(SQL.90).aspx

CREATE PARTITION SCHEME (Transact-SQL)

http://msdn.microsoft.com/zh-tw/library/ms179854(SQL.90).aspx

The datetime data type and the smalldatetime data type are always evaluated
by using the us_english language environment when you use the CREATE PARTITION
FUNCTION statement in SQL Server 2005

http://support.microsoft.com/kb/917419/zh-tw