Table Partition Pada SQL Server

Secara singkat Table Partition adalah membagi data kedalam unit yang lebih kecil sehingga lebih mudah di kelola tanpa harus membuat tabel terpisah untuk setiap unit. Data dalam tabel yang dipartisi secara fisik disimpan dalam kelompok baris yang disebut partisi dan setiap partisi dapat diakses dan dikelola secara terpisah. Namun demikian semua partisi dari indeks atau tabel harus berada dalam database yang sama. Partisi tidak terlihat oleh pengguna akhir, tabel yang dipartisi berperilaku seperti satu tabel logis ketika dilakukan query.

Ada banyak manfaat yang bisa kita peroleh dari mempartisi Table besar diantaranya dapat meningkatkan performa baca dan tulis data, dapat melakukan perawatan operasional pada satu atau beberapa partisi tertentu tanpa harus satu tabel penuh, meningkatkan performa Query dan lain-lain. Namun, mengimplementasikan partisi tabel bukanlah tugas yang mudah, kita perlu pemahaman yang baik tentang cara kerjanya untuk menerapkan dan menggunakannya dengan benar. Perlu diingat bahwa fitur Table Partition tidak terdapat pada semua edisi SQL Server kecuali mulai versi 2016 SP1, jadi sebelum mengimplementasikannya pastikan fitur Table Partition terdapat pada edisi SQL yang di gunakan.

Partisi Kolom

Data dalam tabel yang dipartisi, dipartisi berdasarkan kolom tunggal, kolom partisi biasa disebut kunci partisi (Partition Key). Hanya satu kolom yang dapat digunakan sebagai kolom partisi, tetapi dimungkinkan untuk menggunakan computed column.

Dalam contoh ilustrasi, kolom tanggal digunakan sebagai kolom partisi. SQL Server menempatkan baris dalam partisi yang benar berdasarkan nilai di kolom tanggal. Semua baris dengan tanggal sebelum atau pada tahun 2012 ditempatkan di partisi pertama, semua baris dengan tanggal pada tahun 2013 ditempatkan di partisi kedua, semua baris dengan tanggal pada tahun 2014 ditempatkan di partisi ketiga, dan semua baris dengan tanggal pada tahun 2015 atau setelah ditempatkan di partisi keempat. Jika nilai kolom partisi adalah NULL, baris ditempatkan di partisi pertama.

Penting untuk memilih kolom partisi yang hampir selalu digunakan sebagai filter dalam kueri. Ketika kolom partisi digunakan sebagai filter dalam kueri, SQL Server hanya mengakses partisi yang relevan. Ini disebut eliminasi partisi dan dapat sangat meningkatkan kinerja ketika mengeksekusi query pada tabel besar.

Fungsi Partisi

Fungsi partisi mendefinisikan cara mempartisi data berdasarkan kolom partisi. Fungsi partisi tidak secara eksplisit mendefinisikan partisi dan baris mana yang ditempatkan di setiap partisi. Sebaliknya, fungsi partisi menentukan nilai-nilai batas, titik-titik antara partisi. Jumlah total partisi selalu jumlah total nilai batas + 1.

Dalam contoh ilustrasi ada tiga nilai batas. Nilai batas pertama adalah antara 2012 dan 2013, nilai batas kedua adalah antara 2013 dan 2014, dan nilai batas ketiga adalah antara 2014 dan 2015. Tiga nilai batas membuat empat partisi. (Partisi pertama juga mencakup semua baris dengan tanggal sebelum 2012 dan partisi terakhir juga mencakup semua baris setelah 2015, tetapi contohnya tetap sederhana dengan hanya empat tahun untuk saat ini.)

Tapi apa nilai batas aktual yang digunakan dalam contoh? Bagaimana kita tahu nilai tanggal mana yang menjadi poin antara dua tahun? Apakah 31 Desember atau 1 Januari? Jawabannya adalah dapat berupa tanggal 31 Desember atau 1 Januari, tergantung pada apakah menggunakan rentang kiri atau rentang kanan pada fungsi partisi yang tepat.

Fungsi partisi dibuat sebagai rentang kiri atau rentang kanan untuk menentukan apakah nilai batas milik partisi kiri atau kanannya:
• Rentang kiri berarti nilai batas yang sebenarnya milik partisi kirinya, yaitu nilai terakhir di partisi kiri
• Rentang kanan berarti bahwa nilai batas yang sebenarnya milik partisi kanannya,yaitu nilai pertama di partisi kanan.

Partisi kiri dan kanan lebih masuk akal jika tabel diputar:

Rentang Kiri dan Rentang Kanan menggunakan Tanggal

Nilai batas pertama adalah antara 2012 dan 2013. Ini dapat dibuat dalam dua cara, baik dengan menentukan berbagai fungsi partisi kiri dengan 31 Desember sebagai nilai batas, atau sebagai fungsi partisi rentang kanan dengan 1 Januari sebagai nilai batas:

Fungsi partisi dibuat sebagai rentang kiri atau rentang kanan, tidak mungkin menggabungkan keduanya dalam fungsi partisi yang sama. Dalam berbagai fungsi partisi kiri, semua nilai batas adalah batas atas, yaitu nilai terakhir dalam partisi. Jika kita memilah berdasarkan tahun, kita gunakan 31 Desember. Jika kita memilah berdasarkan bulan, kita gunakan 31 Januari, 28 Februari / 29, 31 Maret, 30 April dan seterusnya. Dalam berbagai fungsi partisi kanan, semua nilai batas adalah batas yang lebih rendah, yaitu nilai-nilai pertama dalam partisi. Jika kita memilah berdasarkan tahun, kita gunakan 1 Januari. Jika kita memilah berdasarkan bulan, kita gunakan 1 Januari, 1 Februari, 1 Maret, 1 April dan seterusnya:

Rentang Kiri dan Rentang Kanan menggunakan Tanggal yang Salah

Jika menggunakan tanggal secara salah sebagai nilai batas, partisi terbagi dua kali untuk satu periode :

Skema Partisi

Skema partisi memetakan partisi logical ke filegroup fisik. Dimungkinkan untuk memetakan setiap partisi ke filegroupnya sendiri atau semua partisi ke satu filegroup.

Sebuah filegroup berisi satu atau lebih file data yang dapat tersebar pada satu atau lebih disk. Filegroup dapat disetel menjadi hanya-baca (Read-Only), dan filegroup dapat dilakukan Backup dan Restore secara individual. Ada banyak manfaat memetakan setiap partisi ke filegroupnya sendiri. Data yang kurang sering diakses dapat ditempatkan pada disk yang lebih lambat dan data yang lebih sering diakses dapat ditempatkan pada disk yang lebih cepat. Data historis dan tidak berubah dapat disetel menjadi hanya-baca dan kemudian dikecualikan dari pencadangan reguler. Jika data perlu dipulihkan, kita dapat mengembalikan partisi dengan data paling penting terlebih dahulu.

Bagaimana membuat partisi tabel?

Skrip berikut (untuk SQL Server 2012 dan yang lebih baru) pertama membuat beberapa fungsi tabel yang dibuat oleh Itzik Ben-Gan yang digunakan untuk memasukkan data uji. Script kemudian membuat fungsi partisi, skema partisi dan tabel yang dipartisi. (Penting untuk diperhatikan bahwa skrip ini dimaksudkan untuk mendemonstrasikan konsep dasar dari partisi tabel, tidak menciptakan indeks atau batasan apa pun dan memetakan semua partisi ke filegroup [PRIMARY]. Skrip ini tidak dimaksudkan untuk digunakan dalam proyek dunia nyata.) Akhirnya ini menyisipkan data uji dan menunjukkan informasi tentang tabel yang dipartisi.

Silahkan download skrip query untuk partisi table disini.

Kesimpulan

Fungsi partisi mendefinisikan bagaimana mempartisi suatu tabel berdasarkan nilai-nilai di kolom partisi. Tabel yang dipartisi dibuat pada skema partisi yang menggunakan fungsi partisi untuk memetakan partisi logical ke filegroup fisik. Jika setiap partisi dipetakan ke filegroup terpisah, partisi dapat ditempatkan pada disk yang lebih lambat atau lebih cepat berdasarkan seberapa sering mereka diakses, partisi historis dapat diatur ke hanya-baca, dan partisi dapat dicadangkan dan dipulihkan secara individual berdasarkan seberapa kritis datanya. Tulisan ini merupakan terjemahan bebas dari tulisan Cathrine Wilhelmsen dengan tambahan tulisan dari dokumentasi microsoft.