FileGroup 檔案群組 :
一個「資料庫(database)」可對應一或多個 FileGroup,一個 FileGroup 可由一或多個 file (.ndf) 構成。 FileGroup 可讓 SQL Server 彈性地調整空間大小,亦可達到讓不同的磁碟 I/O,來幫助分流、提升效能,例如筆數極大的「資料表(table)」,可用 FileGroup 做「水平資料分割 (Horizontal Partitioning)」,內地稱為「表分區」。 Horizontal Partitioning 的相關指令如下 :

alter database NORTHWND add filegroup FileGroup1;
alter database NORTHWND add filegroup FileGroup2;
alter database NORTHWND add filegroup FileGroup3; alter database NORTHWND add file(name=FG11, filename='C:\test\FG11.ndf') to filegroup FileGroup1;
alter database NORTHWND add file(name=FG21, filename='C:\test\FG21.ndf', size=10MB),
(name=FG22, filename='C:\test\FG22.ndf', size=10MB) to filegroup FileGroup2;
alter database NORTHWND add file(name=FG31, filename='C:\test\FG31.ndf') to filegroup FileGroup3; --依「訂單日期」欄位,自動將 record 儲存至不同的 FileGroup、實體 file
create partition function partitionFunction_OrderDate(datetime) as range RIGHT for values('1997/01/01','1998/01/01','1999/01/01'); --1997/01/01 之前的舊 record,存至 FileGroup1 (FG11.ndf)
--1997/01/01~1998/12/31 的 record,存至 FileGroup2 (FG21.ndf、FG22.ndf)
--1999/01/01 及之後的 record,存至 FileGroup3 (FG31.ndf)
create partition scheme partitionScheme_OrderDate as partition partitionFunction_OrderDate to (FileGroup1, FileGroup2, FileGroup2, FileGroup3);
create table Orders2(
OrderID int not null,
OrderDate datetime not null default getdate(),
CustID varchar(6),
EmpID varchar(6)) ON partitionScheme_OrderDate(OrderDate) --從 Orders 資料表,匯入測試用資料 (都是早期年份,2000 年之前的記錄)
INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) SELECT OrderID, OrderDate, CustomerID, EmployeeID FROM Orders
--新增幾筆 OrderDate 較新的資料,供測試用 (2014 年份)
INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12001, getdate(), '2014/7', '2014/7')
INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12002, getdate(), '2014/8', '2014/8')
INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12003, getdate(), '2014/9', '2014/9') --檢查 Orders2 資料表中的資料,其水平切割、分組情形,或者由 sys.partitions 檢視表中查詢每個 partition 的資料筆數
SELECT index_id, partition_number, rows FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number; --測試日後新增 partition 時 (模擬企業在每年進入新的年份,都要持續新建 partition,供搬移歷史舊資料)
alter database NORTHWND add filegroup FileGroup4;
alter database NORTHWND add file(name=FG41, filename='C:\test\FG41.ndf') to filegroup FileGroup4; --split 新 partition (以 2000/01/01 年為分界點)
alter partition scheme partitionScheme_OrderDate NEXT USED [FileGroup4];
alter partition function partitionFunction_OrderDate() SPLIT range ('2000/01/01'); --create partition scheme partitionScheme_OrderDate as partition partitionFunction_OrderDate to (FileGroup1, FileGroup2, FileGroup2, FileGroup3); --split 新 partition 之後,既有的記錄 (2000/01/01 之後的記錄) 會自動搬移 (視 OrderDate 欄位的年份自動對應),不用資訊人員手動搬移
SELECT index_id, partition_number, rows FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number; --日後再 insert 的記錄 (2000/01/01 之後的記錄),會自動改寫至 FileGroup4
INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12005, getdate(), '2014/9', '2014/9') --SELECT index_id, partition_number, rows FROM sys.partitions
--WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number; --SELECT * FROM Orders2 ORDER BY OrderDate desc

--------------------------------------------------------------
當經費不足,無法購置 SAN / 硬體 RAID 時,亦可考慮 FileGroup,但非不得已才棄 RAID 而就 FileGroup,因後者的效能不如前者,且前者才有「快取(cache)」,後者不支援「容錯」。

FileGroup 的原理,是靠多顆硬碟同時存取,將資料打散在多顆硬碟上、存取時多顆硬碟一起運作,可提升效能 (參考圖片)。如下圖,可透過 FileGroup 的設定,將不同種類的資料,放在不同的實體硬碟上。如左下方的 C 槽,可放系統資料表,或常要維護(寫入)的一般常用資料 (.mdf)。下方的 D 槽,可存放純供查詢用的歷史性資料 (OrderHistory1.ndf、OrderHistory2.ndf、...、OrderHistoryX.ndf)。而 Log 檔 (.ldf) 可放右下方的 E 槽上。

當然,若公司的經費充足,也可透過多台 RAID,再搭配 FileGroup 一起使用,如此一來,既能容錯,又兼顧效能。

測試大型資料表的 Horizontal Partitioning 水平切割-LMLPHP

--------------------------------------------------------------
讀書隨手筆記-其他 (系統資料庫 tempdb) :

* 系統資料庫 tempdb 可改放置在快速的磁碟上,或分散到多部磁碟上,與存放一般資料的資料庫放在不同的磁碟,以確保效能。
* 可為 tempdb 資料庫,建立多個 file,數目可與伺服器 CPU 數目相同,減少 CPU 存取 tempdb 時的資源衝突,以確保效能。

相關 SQL 指令 (書中有附實際可執行程式碼) :
可透過 ALTER DATABASE tempdb MODIFY FILE 指令,將 tempdb 移至不同位置 (.mdf、.ldf)。
可透過 ALTER DATABASE tempdb ADD FILE 指令,增加、建立多個 file (.mdf、.ldf),但需要重新啟動 SQL Server。

--------------------------------------------------------------
參考書籍 :
SQL Server 2005 Performance Tuning 效能調校 (作者: 胡百敬、劉承修... 等人, 台灣書籍)
SQL Server 2012 Performance Tuning 效能調校 (作者: 胡百敬、劉承修... 等人, 台灣書籍)
http://www.tenlong.com.tw/items/9865740419?item_id=892851
SQL Server 效能調校 (作者: 陳祥輝... 等人, 台灣書籍)
http://www.tenlong.com.tw/items/9862019026?item_id=889295

04-29 01:12