Implementasi Partisi di Dalam SQL Server 2005

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 contoh

Partisi 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 scheme
Partition 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

Sumber: http://www.detikinet.com/index.php/detik.read/tahun/2006/bulan/09/tgl/21/time/103946/idnews/679842/idkanal/349

Tinggalkan Balasan

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s


%d blogger menyukai ini: