Implementasi Partisi di Dalam SQL Server 2005

Sumber: Detik.com

Salah satu inovasi yang patut diperhatikan di dalam fitur SQL Server 2005 adalah kemampuan partisi pada table dan index. Namun sebelum dibahas lebih teknis mengenai penerapan partisi di dalam SQL Server 2005. Tujuan penggunaan partisi di dalam table agar mempercepat respon query database dan mempermudah manajemen aktivitas yang berhubungan dengan backup dan pemeliharaan index. Jenis-jenis partisi secara umum meliputi 3 jenis:


Partisi secara hardware

• Meliputi:Multiprosesor, yang memungkinkan sub operasi dari sebuah query yang melibatkan join beberapa table dapat terjadi secara parallel.
• RAID (redundant array of independent disk) akan mempercepat akses data baik membaca ataupun menulis ke dalam disk secara bersamaan karena distribusi data menyebar ke beberapa disk drive. Performance ini akan terlihat ketika terjadi operasi join yang melibatkan beberapa table di mana masing-masing table terletak di drive yang terpisah.

Partisi vertical
Implementasi partisi vertical adalah membagi kolom di dalam sebuah table (biasanya kolomnya terlalu banyak) menjadi dua atau lebih table. Tujuannya agar dapat menampung row sebanyak mungkin di dalam satu page pada sebuah table. Tipe partisi vertical ini dilakukan dengan cara normalisasi dan row splitting.

Partisi horizontal
Partisi ini akan melakukan pengelompokan satu table yang memiliki row yang sangat banyak (biasanya jutaan row) menjadi beberapa table yang masing-masing menampung row yang lebih sedikit. Pembahasan ini akan lebih menitik beratkan implementasi partisi secara horizontal.

Penerapan partisi sudah dilakukan pada versi SQL Server 2005 dan sebelumnya (versi 2000), namun terdapat perbedaan implementasi partisi pada versi 2000 and 2005. Penerapan partisi pada SQL Server 2000 dilakukan dengan cara membagi row dari 1 buah table menjadi beberapa table. Masing-masing table digabungkan lewat view, sehingga user melihat view itu sebagai satu table. Konsekuensi dari implementasi tersebut adalah tidak efisien karena DBA perlu melakukan maintaining beberapa table.

Karena terdapat variasi distribusi index maka query optimizer akan mengenerate execution plan untuk tiap table dan membutuhkan waktu lama untuk melakukan optimasi atas execution plan total. Penerapan partisi pada SQL Server 2005 dilakukan betul-betul secara internal ke dalam sebuah table sehingga mengurangi overhead yang ditimbulkan pada maintenance banyak table (pada SQL Server 2000). Partisi dapat diterapkan pada sistem OLTP (pembagian row yang akan menempati filegroup tertentu di dalam sebuah database) dan juga OLAP (partisi didalam fact table sehingga pemrosesan cube dapat dilakukan secara efisien hanya untuk partisi-partisi yang perlu diupdate saja) Namun perlu diingat bahwa fitur partisi hanya ada untuk SQL Server 2005 edisi enterprise dan developer.

Selanjutnya saya akan memberikan gambaran berupa contoh implementasi partisi ke dalam sebuah database. Membuat database contohPartisi dari sebuah table dapat diletakkan menyebar di antara filegroup. Berikut ini contoh pembuatan database dengan 5 filegroup (PRIMARY, sales2, sales3, sales4 dan sales5).

CREATE DATABASE TestDB
ON PRIMARY
(
NAME = sales_dat1,
FILENAME = 'C:\sales.mdf',
SIZE =10,
MAXSIZE =100,
FILEGROWTH =5%
),
FILEGROUP sales2
(
NAME = sales_dat2,
FILENAME = 'D:\sales2.ndf',
SIZE =10,
MAXSIZE =100,
FILEGROWTH =5%
),
FILEGROUP sales3
(
NAME = sales_dat3,
FILENAME = 'E:\sales3.ndf',
SIZE =10,
MAXSIZE =100,
FILEGROWTH =5%
),
FILEGROUP sales4
(
NAME = sales_dat4,
FILENAME = 'F:\sales4.ndf',
SIZE =10,
MAXSIZE =100,
FILEGROWTH =5%
),
FILEGROUP sales5
(
NAME = sales_dat5,
FILENAME = 'G:\sales5.ndf',
SIZE =10,
MAXSIZE =100,
FILEGROWTH =5%
)

LOG ON
(
NAME = sales_log,
FILENAME = 'H:\Sales_Log.ldf',
SIZE =10 ,
MAXSIZE =100 ,
FILEGROWTH = 5%
)
GO

Membuat partition function (create partition function)

USE TestDB
GO
CREATE PARTITION FUNCTION pf1 (INT) AS RANGE LEFT FOR VALUES (1,2,3,4);
GO

Dimana : RANGE LEFT didalam contoh ini adalah :
Partisi pertama akan mencakup nilai sampai dengan 1.
Partisi kedua akan mencakup nilai lebih besar dari 1 sampai dengan nilai 2.
Partisi ketiga akan mencakup nilai lebih besar dari 2 sampai dengan nilai 3.
Partisi keempat akan mencakup nilai lebih besar dari 3 sampai dengan nilai 4.
Partisi kelima akan mencakup nilai lebih besar dari 4.

Sebaliknya bila RANGE RIGHT didefinisikan di dalam contoh ini berarti :
Partisi pertama akan mencakup nilai kurang dari 1.
Partisi kedua akan mencakup nilai 1 sampai dengan nilai yang kurang dari 2.
Partisi ketiga akan mencakup nilai 2 sampai dengan nilai yang kurang dari 3.
Partisi keempat akan mencakup nilai 3 sampai dengan nilai yang kurang dari 4.
Partisi kelima akan mencakup nilai minimal 4 atau lebih besar.

Membuat partition schemePartition scheme ini akan memetakan nilai-nilai yang telah ditentukan didalam partition function ke dalam filegroup seperti contoh di bawah ini.

      CREATE PARTITION SCHEME ps1 AS PARTITION pf1 to ([PRIMARY],[sales2],[sales3],[sales4],[sales5]);

Implementasikan partition scheme ke dalam tabel

• Pembuatan table yang menerapkan partisi scheme
CREATE TABLE PartitionedTable (a INT, b INT) ON ps1(a);
• Lakukan Loading data ke dalam partisi Berikutnya akan dimasukkan data-data ke dalam table Test1 yang sudah dibuat seperti script di bawah ini :

DECLARE @i INT;
SET NOCOUNT ON;
SET @i=0;

WHILE @i < 1000
   BEGIN
          INSERT INTO PartitionedTable VALUES ( 6 * rand() , 100 * rand()); SET @i = @i + 1;
    END SELECT * FROM PartitionedTable;

Sampai dengan langkah di atas, anda telah melakukan partisi data di dalam sebuah table, lalu bagaimana mengetahui distribusi penyebaran data-data tersebut di dalam partisi? Anda bisa melihat penyebaran data tersebut melalui view system SQL Server yaitu view sys.partitions Untuk tabel pada contoh diatas, querynya adalah :

select object_name(object_id) as TableName, Partition_number, Rows from sys.partitions where object_name(object_id) like 'PartitionedTable%'

TableName	Partition_number	Rows
PartitionedTable	1	345
PartitionedTable	2	169
PartitionedTable	3	162
PartitionedTable	4	171
PartitionedTable	5	153

Anda bisa melihat banyaknya row yang ada di setiap partisi yang anda buat, bila dikehendaki hanya row yang berasosiasi dengan nilai 1 maka SQL Server dengan efisien hanya melihat ke partisi pertama saja.

Penulis, Kasim Wirama, MCSD.NET, MCDBA, adalah Database subject matter expert PT. Astra International, Tbk. dan penerima Most Valuable Professional Award dari Microsoft untuk SQL Server