Tabel dan Indeks yang Dipartisi
SQL Server mendukung partisi tabel dan indeks. Data tabel dan indeks yang dipartisi dibagi menjadi unit yang dapat tersebar di lebih dari satu grup file dalam database. Data dipartisi secara horizontal, sehingga grup baris dipetakan ke dalam partisi individual. Semua partisi indeks atau tabel tunggal harus berada di database yang sama. Tabel atau indeks diperlakukan sebagai entitas logis tunggal saat kueri atau pembaruan dilakukan pada data. Tabel dan indeks yang dipartisi tidak tersedia di setiap edisi MicrosoftSQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Fitur yang Didukung oleh Edisi SQL Server 2014.
Penting
SQL Server 2014 mendukung hingga 15.000 partisi secara default. Dalam versi yang lebih lama dari SQL Server 2012, jumlah partisi dibatasi hingga 1.000 secara default. Pada sistem berbasis x86, membuat tabel atau indeks dengan lebih dari 1000 partisi dimungkinkan, tetapi tidak didukung.
Manfaat Partisi
Mempartisi tabel atau indeks besar dapat memiliki manfaat pengelolaan dan performa berikut.
Anda dapat mentransfer atau mengakses subset data dengan cepat dan efisien, sambil mempertahankan integritas pengumpulan data. Misalnya, operasi seperti memuat data dari OLTP ke sistem OLAP hanya membutuhkan waktu beberapa detik, bukan menit dan jam yang diperlukan operasi saat data tidak dipartisi.
Anda dapat melakukan operasi pemeliharaan pada satu atau beberapa partisi dengan lebih cepat. Operasi lebih efisien karena hanya menargetkan subset data ini, bukan seluruh tabel. Misalnya, Anda dapat memilih untuk memadatkan data dalam satu atau beberapa partisi atau membangun kembali satu atau beberapa partisi indeks.
Anda dapat meningkatkan performa kueri, berdasarkan jenis kueri yang sering Anda jalankan dan pada konfigurasi perangkat keras Anda. Misalnya, pengoptimal kueri dapat memproses kueri equi-join antara dua atau lebih tabel yang dipartisi lebih cepat ketika kolom partisi dalam tabel sama, karena partisi itu sendiri dapat digabungkan.
Saat SQL Server melakukan pengurutan data untuk operasi I/O, ia mengurutkan data terlebih dahulu menurut partisi. SQL Server mengakses satu drive pada satu waktu, dan ini dapat mengurangi performa. Untuk meningkatkan performa pengurutan data, stripe file data partisi Anda di lebih dari satu disk dengan menyiapkan RAID. Dengan cara ini, meskipun SQL Server masih mengurutkan data berdasarkan partisi, ia dapat mengakses semua drive setiap partisi secara bersamaan.
Selain itu, Anda dapat meningkatkan performa dengan mengaktifkan eskalasi kunci di tingkat partisi alih-alih seluruh tabel. Ini dapat mengurangi ketidakcocokan kunci pada tabel.
Komponen dan Konsep
Istilah berikut berlaku untuk partisi tabel dan indeks.
Fungsi partisi
Objek database yang menentukan bagaimana baris tabel atau indeks dipetakan ke sekumpulan partisi berdasarkan nilai kolom tertentu, yang disebut kolom pemartisian. Artinya, fungsi partisi mendefinisikan jumlah partisi yang akan dimiliki tabel dan bagaimana batas partisi ditentukan. Misalnya, mengingat tabel yang berisi data pesanan penjualan, Anda mungkin ingin mempartisi tabel menjadi dua belas partisi (bulanan) berdasarkan datetime kolom seperti tanggal penjualan.
Skema partisi
Objek database yang memetakan partisi fungsi partisi ke sekumpulan grup file. Alasan utama untuk menempatkan partisi Anda pada grup file terpisah adalah untuk memastikan bahwa Anda dapat melakukan operasi pencadangan secara independen pada partisi. Ini karena Anda dapat melakukan pencadangan pada grup file individual.
Kolom partisi
Kolom tabel atau indeks yang digunakan fungsi partisi untuk mempartisi tabel atau indeks. Kolom komputasi yang berpartisipasi dalam fungsi partisi harus ditandai secara eksplisit PERSISTED. Semua jenis data yang valid untuk digunakan sebagai kolom indeks dapat digunakan sebagai kolom partisi, kecuali timestamp. Jenis ntextdata , , xmltextimage, varchar(max), nvarchar(max), atau varbinary(max) tidak dapat ditentukan. Selain itu, Microsoft .NET Framework jenis yang ditentukan pengguna runtime bahasa umum (CLR) dan kolom jenis data alias tidak dapat ditentukan.
Indeks yang diratakan
Indeks yang dibangun pada skema partisi yang sama dengan tabel yang sesuai. Saat tabel dan indeksnya dalam penyelarasan, SQL Server dapat mengalihkan partisi dengan cepat dan efisien sambil mempertahankan struktur partisi tabel dan indeksnya. Indeks tidak harus berpartisipasi dalam fungsi partisi bernama yang sama untuk diselaraskan dengan tabel dasarnya. Namun, fungsi partisi indeks dan tabel dasar pada dasarnya harus sama, dalam 1) argumen fungsi partisi memiliki jenis data yang sama, 2) mereka menentukan jumlah partisi yang sama, dan 3) mereka menentukan nilai batas yang sama untuk partisi.
Indeks yang tidak ditandatangani
Indeks dipartisi secara independen dari tabel yang sesuai. Artinya, indeks memiliki skema partisi yang berbeda atau ditempatkan pada grup file terpisah dari tabel dasar. Merancang indeks partisi yang tidak sejajar dapat berguna dalam kasus berikut:
Tabel dasar belum dipartisi.
Kunci indeks unik dan tidak berisi kolom partisi tabel.
Anda ingin tabel dasar berpartisipasi dalam gabungan yang dikolokasi dengan lebih banyak tabel menggunakan kolom gabungan yang berbeda.
Eliminasi partisi
Proses di mana pengoptimal kueri hanya mengakses partisi yang relevan untuk memenuhi kriteria filter kueri.
Pedoman Performa
Batas baru yang lebih tinggi dari 15.000 partisi memengaruhi memori, operasi indeks yang dipartisi, perintah DBCC, dan kueri. Bagian ini menjelaskan implikasi performa untuk meningkatkan jumlah partisi di atas 1.000 dan memberikan solusi sesuai kebutuhan. Dengan batas jumlah maksimum partisi yang ditingkatkan menjadi 15.000, Anda dapat menyimpan data untuk waktu yang lebih lama. Namun, Anda harus menyimpan data hanya selama diperlukan dan menjaga keseimbangan antara performa dan jumlah partisi.
Penggunaan dan Pedoman Memori
Kami menyarankan agar Anda menggunakan setidaknya 16 GB RAM jika sejumlah besar partisi sedang digunakan. Jika sistem tidak memiliki cukup memori, pernyataan Bahasa Manipulasi Data (DML), pernyataan Bahasa Definisi Data (DDL) dan operasi lainnya dapat gagal karena memori yang tidak mencukup. Sistem dengan RAM 16 GB yang menjalankan banyak proses intensif memori dapat kehabisan memori pada operasi yang berjalan pada sejumlah besar partisi. Oleh karena itu, semakin banyak memori yang Anda miliki lebih dari 16 GB, semakin kecil kemungkinan Anda mengalami masalah performa dan memori.
Keterbatasan memori dapat memengaruhi performa atau kemampuan SQL Server untuk membangun indeks yang dipartisi. Ini terutama terjadi ketika indeks tidak selaras dengan tabel dasarnya atau tidak selaras dengan indeks berklusternya, jika tabel sudah memiliki indeks berkluster yang diterapkan padanya.
Operasi Indeks Yang Dipartisi
Keterbatasan memori dapat memengaruhi performa atau kemampuan SQL Server untuk membangun indeks yang dipartisi. Ini terutama terjadi pada indeks yang tidak sejajar. Membuat dan membangun kembali indeks yang tidak ditandatangani pada tabel dengan lebih dari 1.000 partisi dimungkinkan, tetapi tidak didukung. Melakukannya dapat menyebabkan penurunan performa atau konsumsi memori yang berlebihan selama operasi ini.
Membuat dan membangun kembali indeks yang selaras dapat memakan waktu lebih lama untuk dijalankan saat jumlah partisi meningkat. Kami menyarankan agar Anda tidak menjalankan beberapa perintah buat dan bangun ulang indeks secara bersamaan karena Anda mungkin mengalami masalah performa dan memori.
Saat SQL Server melakukan pengurutan untuk membangun indeks yang dipartisi, pertama-tama membuat satu tabel pengurutan untuk setiap partisi. Kemudian menyusun tabel pengurutan baik di grup file masing-masing dari setiap partisi atau di tempdb, jika opsi indeks SORT_IN_TEMPDB ditentukan. Setiap tabel pengurutan memerlukan jumlah memori minimum untuk dibangun. Saat Anda membangun indeks yang dipartisi yang selaras dengan tabel dasarnya, tabel pengurutan dibangun satu per satu, menggunakan lebih sedikit memori. Namun, ketika Anda membangun indeks partisi yang tidak ditandatangani, tabel pengurutan dibangun pada saat yang sama. Akibatnya, harus ada memori yang cukup untuk menangani pengurutan bersamaan ini. Semakin besar jumlah partisi, semakin banyak memori yang diperlukan. Ukuran minimum untuk setiap tabel pengurutan, untuk setiap partisi, adalah 40 halaman, dengan 8 kilobyte per halaman. Misalnya, indeks partisi yang tidak ditandatangani dengan 100 partisi memerlukan memori yang cukup untuk mengurutkan 4.000 (40 * 100) halaman secara serial secara bersamaan. Jika memori ini tersedia, operasi build akan berhasil, tetapi performa mungkin menderita. Jika memori ini tidak tersedia, operasi build akan gagal. Atau, indeks partisi yang diratakan dengan 100 partisi hanya memerlukan memori yang cukup untuk mengurutkan 40 halaman, karena pengurutan tidak dilakukan pada saat yang sama.
Untuk indeks yang selaras dan tidak sejajar, persyaratan memori dapat lebih besar jika SQL Server menerapkan derajat paralelisme ke operasi build pada komputer multiprosesor. Ini karena semakin besar tingkat paralelisme, semakin besar persyaratan memori. Misalnya, jika SQL Server menetapkan derajat paralelisme ke 4, indeks partisi yang tidak ditandatangani dengan 100 partisi memerlukan memori yang cukup untuk empat prosesor untuk mengurutkan 4.000 halaman secara bersamaan, atau 16.000 halaman. Jika indeks yang dipartisi selaras, persyaratan memori dikurangi menjadi empat prosesor yang mengurutkan 40 halaman, atau 160 (4 * 40) halaman. Anda dapat menggunakan opsi indeks MAXDOP untuk mengurangi derajat paralelisme secara manual.
Perintah DBCC
Dengan jumlah partisi yang lebih besar, perintah DBCC dapat memakan waktu lebih lama untuk dijalankan saat jumlah partisi meningkat.
Kueri
Kueri yang menggunakan eliminasi partisi dapat memiliki performa yang sebanding atau lebih baik dengan jumlah partisi yang lebih besar. Kueri yang tidak menggunakan eliminasi partisi bisa memakan waktu lebih lama untuk dijalankan saat jumlah partisi meningkat.
Misalnya, asumsikan tabel memiliki 100 juta baris dan kolom A, B, dan C. Dalam skenario 1, tabel dibagi menjadi 1000 partisi pada kolom A. Dalam skenario 2, tabel dibagi menjadi 10.000 partisi pada kolom A. Kueri pada tabel yang memiliki pemfilteran klausa WHERE pada kolom A akan melakukan penghapusan partisi dan memindai satu partisi. Kueri yang sama dapat berjalan lebih cepat dalam skenario 2 karena ada lebih sedikit baris untuk dipindai dalam partisi. Kueri yang memiliki pemfilteran klausa WHERE pada kolom B akan memindai semua partisi. Kueri dapat berjalan lebih cepat dalam skenario 1 daripada dalam skenario 2 karena ada lebih sedikit partisi untuk dipindai.
Kueri yang menggunakan operator seperti TOP atau MAX/MIN pada kolom selain kolom partisi mungkin mengalami penurunan performa dengan partisi karena semua partisi harus dievaluasi.
Perubahan Perilaku dalam Komputasi Statistik Selama Operasi Indeks yang Dipartisi
Dimulai dengan SQL Server 2012, statistik tidak dibuat dengan memindai semua baris dalam tabel saat indeks yang dipartisi dibuat atau dibangun kembali. Sebaliknya, pengoptimal kueri menggunakan algoritma pengambilan sampel default untuk menghasilkan statistik. Setelah meningkatkan database dengan indeks yang dipartisi, Anda mungkin melihat perbedaan dalam data histogram untuk indeks ini. Perubahan perilaku ini mungkin tidak memengaruhi performa kueri. Untuk mendapatkan statistik pada indeks yang dipartisi dengan memindai semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan klausul FULLSCAN.
Tugas Terkait
| Tugas | Topik |
| Menjelaskan cara membuat fungsi partisi dan skema partisi lalu menerapkannya ke tabel dan indeks. | Membuat Tabel dan Indeks yang Dipartisi |
Konten terkait
Anda mungkin menemukan laporan resmi berikut pada tabel yang dipartisi dan strategi indeks dan implementasi yang berguna.