การสร้าง Partitioning Table บน MS Sql Server 2005

ว่าด้วยเรื่องการจัดเก็บข้อมูลบนฐานข้อมูล MS-SQL Sever 2005 ในกรณีที่องค์กรของท่าน ๆ ทั้งหลาย มีข้อมูลเป็นจำนวนมากเก็บสุม ๆ ไว้เป็นเวลานาน ไฟล์บน Database ที่แบ่งแค่ Data file กับ Log file ตาม default ของการติดตั้ง โตขึ้น ๆ ทุกวัน จน Database Server เริ่มอืด

MS SQL 2005 มี feature ให้เล่น ซึ่งผมคิดว่ามีประโยชน์มาก ๆ นั่นก็คือการจัดสรรค์ข้อมูลลงไฟล์แบบกระจายโดยอัตโนมัติ หรือเรียกว่าการทำ Partioning ครับ หลักการโดยทั่วไปก็คือ การกระจาย Record ออกในแนวราบ ลง File group หลาย ๆ ไฟล์ ที่เราสร้างไว้รองรับโดยอัตโนมัติ รับรองว่าข้อมูลที่เคยมีมากมายมหาศาล ข้อมูลที่ Access แต่ละทีอืดมากมาย จะเร็วขึ้นทันตาเห็น โดยไม่ต้องทำการเพิ่มทรัพยากรด้านฮาร์ดแวร์

ขั้นตอนและวิธ๊การ ขอยกตัวอย่างจาก Adventureworks แล้วกันนะครับ

– ทำการสร้าง partitioning functions.
– ตัวอย่างนี้จะทำการสร้าง Partition function แยกส่วนของข้อมูลเป็น 2 ช่วงคือ ข้อมูลก่อนและหลังวันที่ ’2005-01-01′

create partition function YearPF(datetime)
as range right for values (’20050101′);

– ต่อมาต้องทำการเพิ่ม file group ลงไปใน database ใครถนัด GUI ก็ใช้ GUI ได้นะครับ case นี้ใช้เป็น Command Line ดังนี้
alter database AdventureWorks add filegroup YearFG1;
alter database AdventureWorks add filegroup YearFG2;

– ต่อมาก็ต้องทำการ add file ลงแต่ละ file group
alter database AdventureWorks add file (name = ‘YearF1′, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF1.ndf’) to filegroup YearFG1;
alter database AdventureWorks add file (name = ‘YearF2′, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF2.ndf’) to filegroup YearFG2;

– ต่อมาก็ต้องทำการเชื่อม file group เข้ากับ partition function โดยใช้ Partitioning Scheme
create partition scheme YearPS
as partition YearPF to (YearFG1, YearFG2)

– สุดท้ายก็ทำการสร้าง Table โดยอ้างอิงถึง Partition Scheme ที่เราทำการสร้างไว้ ข้อมูลก็จะถูกกระจายโดยอัตโนมัติ
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate) –ข้อมูลจะถูกแบ่งลงแต่ละ file group โดยใช้ field ‘DueDate’ เป็นเงื่อนไขในการกระจาย Data

– ทดสอบการ insert ข้อมูล
insert into PartitionedOrders values(’2010-09-01′)
insert into PartitionedOrders values(’1900-02-07′)
insert into PartitionedOrders values(getdate())
insert into PartitionedOrders values(’1998-10-30′)
insert into PartitionedOrders values(’2005-01-01′)

– ทีนี้เรามาตรวจสอบดูว่าข้อมูลแต่ละ Record ไปหล่นใส่ partition ไหนบ้างด้วยคำสั่งนี้เลยครับ
select *, $partition.YearPF(DueDate) from PartitionedOrders

– และสามารถดูจำนวน Partition ที่สร้างไว้ด้วยคำสั่งนี้ครับ
select * from sys.partitions where object_id = object_id(‘PartitionedOrders’)

จากการใช้งานที่ผ่านมาสรุปว่าการ ทำ Partition Table มีประโยชน์มาก ๆ ครับ เหมาะกับฐานข้อมูลที่มี Data เยอะไฟล์บวม Partition Table ช่วยท่านได้ แต่ถ้าท่านมี Storage ที่สามารถแยก แต่ละ Partition ลงฮาร์ดดิสก์คนละก้อนได้ละก็ ประสิทธิภาพของ Database โดยรวมจะเพิ่มขึ้นแบบเห็น ๆ โดยไม่ต้องลงทุนเปลี่ยน Database Server ใหม่ครับ