Tabel dan indeks yang dipartisi

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Database SQL di Microsoft Fabric

Data tabel dan indeks yang dipartisi dibagi menjadi satuan yang mungkin tersebar di lebih dari satu grup file dalam database atau disimpan dalam satu grup file. Ketika beberapa file ada dalam grup file, data tersebar di seluruh file menggunakan algoritma isian proporsional. 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.

Manfaat pemartisian

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 detik, alih-alih menit dan jam yang dibutuhkan operasi saat data tidak dipartisi.
Anda dapat melakukan operasi pemeliharaan atau retensi data pada satu atau beberapa partisi dengan lebih cepat. Operasi ini lebih efisien karena hanya menargetkan subset data ini, bukan seluruh tabel. Misalnya, Anda dapat memilih untuk memadatkan data dalam satu atau beberapa partisi, membangun kembali satu atau beberapa partisi indeks, atau memotong data dalam satu partisi. Anda juga dapat mengalihkan partisi individual dari satu tabel dan ke dalam tabel arsip.
Anda mungkin meningkatkan performa kueri, berdasarkan jenis kueri yang sering Anda jalankan. Misalnya, pengoptimal kueri dapat memproses kueri equijoin antara dua atau lebih tabel yang dipartisi dengan lebih cepat ketika kolom pemartisiannya sama dengan kolom yang digunakan untuk menggabungkan tabel. Untuk informasi selengkapnya, lihat bagian tentang Kueri.
Anda dapat meningkatkan kelangsungan beban kerja dengan mengatur eskalasi penguncian di tingkat partisi daripada pada tingkat tabel. Ini dapat mengurangi perebutan kunci pada tabel. Untuk mengurangi kontensi penguncian dengan mengizinkan eskalasi kunci ke partisi, atur opsi LOCK_ESCALATION dari pernyataan ALTER TABLE ke AUTO.

Komponen dan konsep

Istilah berikut berlaku untuk partisi tabel dan indeks.

Fungsi partisi

Fungsi partisi adalah objek database yang menentukan bagaimana baris tabel atau indeks dipetakan ke sekumpulan partisi berdasarkan nilai kolom tertentu, yang disebut kolom pemartisian. Setiap nilai dalam kolom partisi adalah input ke fungsi partisi, yang mengembalikan nilai partisi.

Fungsi partisi menentukan jumlah partisi dan batas partisi yang dimiliki tabel. Misalnya, mengingat tabel yang berisi data pesanan penjualan, Anda mungkin ingin mempartisi tabel menjadi 12 partisi (bulanan) berdasarkan kolom tanggalwaktu seperti tanggal penjualan.

Jenis rentang (baik LEFT atau RIGHT), menentukan bagaimana nilai batas fungsi partisi ditempatkan dalam partisi yang dihasilkan:

Rentang LEFT menunjukkan bahwa nilai batas termasuk dalam sisi kiri dari interval batas nilai ketika nilai interval diurutkan secara menaik oleh mesin database dari kiri ke kanan. Dengan kata lain, nilai batas tertinggi disertakan dalam partisi.
Rentang RIGHT menentukan bahwa nilai batas berada di sisi kanan interval nilai batas ketika nilai interval diurutkan oleh mesin database secara naik dari kiri ke kanan. Dengan kata lain, nilai batas terendah disertakan dalam partisi.

Jika LEFT atau RIGHT tidak ditentukan, LEFT jenis rentang adalah default.

Misalnya, fungsi partisi berikut membagi tabel atau indeks menjadi 12 partisi, satu untuk setiap bulan dari nilai selama satu tahun dalam kolom datetime. RIGHT Jenis rentang digunakan, menunjukkan bahwa nilai batas berfungsi sebagai nilai pembatas yang lebih rendah di setiap partisi. RIGHT Rentang partisi sering lebih mudah digunakan ketika mempartisi tabel berdasarkan kolom jenis data datetime, datetime2, atau datetimeoffset, karena baris dengan nilai tengah malam disimpan dalam partisi yang sama dengan baris dengan nilai lebih tinggi pada hari yang sama. Demikian pula, jika menggunakan jenis data tanggal dan menggunakan partisi sebulan atau lebih, RIGHT rentang menyimpan hari pertama dalam sebulan dalam partisi yang sama seperti hari selanjutnya di bulan tersebut. Ini membantu eliminasi partisi secara tepat saat mengkueri data selama satu hari penuh.

CREATE PARTITION FUNCTION [myDateRangePF1](DATETIME)
    AS RANGE RIGHT
    FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
                '2022-05-01', '2022-06-01', '2022-07-01',
                '2022-08-01', '2022-09-01', '2022-10-01',
                '2022-11-01', '2022-12-01');

Tabel berikut ini memperlihatkan bagaimana tabel atau indeks yang menggunakan fungsi partisi ini pada kolom partisi datecol dipartisi. 1 Februari adalah titik batas pertama yang ditentukan dalam fungsi. Karena jenis rentang RIGHT digunakan, 1 Februari adalah batas bawah partisi 2.

Partition	1	2	...	11	12
Values	Datecol<`2022-02-01 12:00AM`	datecol>= `2022-02-01 12:00AM` DAN datecol<`2022-03-01 12:00AM`		datecol>= `2022-11-01 12:00AM` DAN col1<`2022-12-01 12:00AM`	Datecol>= `2022-12-01 12:00AM`

Untuk dan RANGE LEFTRANGE RIGHT, partisi paling kiri memiliki nilai minimum jenis data sebagai batas bawahnya, dan partisi paling kanan memiliki nilai maksimum jenis data sebagai batas atasnya.

Untuk contoh lain fungsi partisi yang menggunakan jenis rentang LEFT dan RIGHT, lihat CREATE PARTITION FUNCTION.

Skema partisi

Skema partisi adalah objek database yang memetakan partisi fungsi partisi ke satu grup file atau ke beberapa grup file.

Temukan contoh sintaks untuk membuat skema partisi di CREATE PARTITION SCHEME.

Filegroups

Ada dua alasan untuk menggunakan skema partisi dengan beberapa grup file:

Saat menggunakan penyimpanan bertingkat, menggunakan beberapa grup file memungkinkan Anda menetapkan partisi tertentu ke tingkat penyimpanan tertentu, misalnya untuk menempatkan partisi yang lebih lama dan kurang sering diakses pada penyimpanan yang lebih lambat dan lebih murah.
Anda dapat mencadangkan dan memulihkan setiap grup file secara independen. Ini berarti Anda dapat melewati pencadangan berulang partisi yang tidak berubah, atau mempersingkat waktu pemulihan ketika hanya data di beberapa partisi yang perlu dipulihkan.

Semua manfaat partisi lainnya berlaku terlepas dari jumlah grup file yang digunakan atau penempatan partisi pada grup file tertentu.

Mengelola file dan grup file untuk tabel yang dipartisi mungkin menambahkan kompleksitas yang signifikan ke tugas administratif dari waktu ke waktu. Jika prosedur pencadangan dan pemulihan Anda tidak mendapat manfaat dari penggunaan beberapa grup file, dan jika Anda tidak menggunakan penyimpanan berjenjang, satu grup file untuk semua partisi disarankan. Aturan yang sama untuk merancang file dan grup file berlaku untuk objek yang dipartisi seperti yang berlaku untuk objek yang tidak dipartisi.

Untuk informasi selengkapnya tentang membuat grup file di SQL Server dan Azure SQL Managed Instance, lihat ALTER DATABASE (Transact-SQL) Opsi File dan Grup File.

Kolom partisi

Kolom tabel atau indeks yang merupakan input ke fungsi partisi. Pertimbangan berikut berlaku saat memilih kolom partisi:

Kolom komputasi yang berpartisipasi dalam fungsi partisi harus dibuat secara eksplisit sebagai PERSISTED.
- Karena hanya satu kolom yang dapat digunakan sebagai kolom partisi, dalam beberapa kasus perangkaian beberapa kolom dalam kolom komputasi mungkin berguna.
Kolom dari semua jenis data yang valid untuk digunakan sebagai kolom kunci indeks dapat digunakan sebagai kolom partisi, kecuali timestamp.
Kolom tipe data objek besar (LOB), seperti ntext, teks, gambar, xml, varchar(max), nvarchar(max), dan varbinary(max) tidak dapat ditentukan.
Kolom yang menggunakan jenis data yang ditentukan pengguna CLR dan jenis data alias tidak dapat digunakan.

Untuk mempartisi tabel atau indeks, tentukan skema partisi dan kolom partisi dalam CREATE TABLEpernyataan , ALTER TABLE, dan CREATE INDEX .

Saat membuat indeks non-kluster, jika skema partisi atau grup file tidak ditentukan dan tabel dipartisi, indeks ditempatkan dalam skema partisi yang sama, menggunakan kolom partisi yang sama, sebagai tabel yang mendasar. Untuk mengubah bagaimana indeks yang ada dipartisi, gunakan CREATE INDEX dengan DROP_EXISTING klausa. Ini memungkinkan Anda mempartisi indeks yang tidak dipartisi, membuat indeks yang dipartisi tanpa partisi, atau mengubah skema partisi indeks.

Indeks yang disejajarkan

Indeks yang dibangun pada skema partisi yang sama dengan tabel terkait disebut indeks yang selaras. Ketika tabel dan indeks nonclustered-nya selaras, mesin database dapat mengalihkan partisi masuk atau keluar dari tabel dengan cepat dan efisien sambil mempertahankan struktur partisi tabel dan indeksnya. Indeks tidak harus berpartisipasi dalam fungsi partisi yang sama untuk diselaraskan dengan tabel dasarnya. Namun, fungsi partisi indeks dan tabel dasar pada dasarnya harus sama, dalam hal ini:

Argumen fungsi partisi memiliki jenis data yang sama.
Mereka menentukan jumlah partisi yang sama.
Mereka menentukan nilai batas yang sama untuk partisi.

Mempartisi indeks berkluster

Saat mempartisi indeks berkluster, kunci pengklusteran harus berisi kolom partisi. Saat Anda mempartisi indeks berkluster nonunique dan kolom partisi tidak ditentukan secara eksplisit dalam kunci pengklusteran, mesin database menambahkan kolom partisi secara default ke daftar kunci indeks berkluster. Jika indeks berkluster unik, Anda harus secara eksplisit menambahkan kolom partisi ke kunci indeks berkluster. Untuk informasi selengkapnya tentang indeks berkluster dan arsitektur indeks, lihat Panduan Desain Indeks Berkluster.

Mempartisi indeks nonkluster

Saat mempartisi indeks nonclustered unik, kunci indeks harus berisi kolom partisi tersebut. Saat mempartisi indeks nonunique dan nonclustered, mesin database secara default menambahkan kolom partisi sebagai kolom nonkunci yang disertakan dalam indeks untuk memastikan bahwa indeks tersebut selaras dengan tabel basis. Mesin database tidak menambahkan kolom partisi ke indeks jika sudah ada dalam indeks. Untuk informasi selengkapnya tentang indeks nonclustered dan arsitektur indeks, lihat Panduan Desain Indeks Nonclustered.

Indeks yang tidak sejajar

Indeks yang tidak sejajar dipartisi secara berbeda dari tabel yang sesuai. Artinya, indeks menggunakan fungsi partisi dengan definisi batas partisi yang berbeda atau menggunakan kolom partisi yang berbeda. Membuat indeks partisi yang tidak beralur mungkin berguna dalam kasus berikut:

Tabel dasar belum dipartisi.
Kunci indeks unik, tidak berisi kolom partisi tabel, dan keunikan indeks harus dipertahankan.
Anda ingin menggunakan join kolokasi antara satu tabel dengan beberapa tabel lainnya yang partisinya berbeda.

Penghapusan partisi

Saat predikat kueri mereferensikan kolom partisi, mesin database mungkin dapat menghilangkan, atau melewati, beberapa partisi saat membaca tabel atau indeks yang dipartisi. Ini dapat meningkatkan performa kueri.

Pelajari selengkapnya tentang penghapusan partisi dan konsep terkait dalam Penyempurnaan Pemrosesan Kueri pada Tabel dan Indeks yang Dipartisi.

Limitations

Sebelum SQL Server 2016 (13.x) SP1, tabel dan indeks yang dipartisi tidak tersedia di setiap edisi SQL Server. Untuk daftar fitur yang didukung oleh edisi di SQL Server, lihat Edisi dan fitur yang didukung dari SQL Server 2025.
Tabel dan indeks yang dipartisi tersedia di semua tingkat layanan Azure SQL Database, database SQL di Fabric, dan Azure SQL Managed Instance.
- Di Azure SQL Database dan database SQL di Fabric, semua partisi harus ditempatkan pada PRIMARY grup file karena hanya PRIMARY grup file yang disediakan.
Pemartisian tabel tersedia di kumpulan SQL khusus di Azure Synapse Analytics, dengan beberapa perbedaan sintaksis. Pelajari selengkapnya di Mempartisi tabel di kumpulan SQL khusus.
Cakupan fungsi dan skema partisi terbatas pada database tempat mereka telah dibuat. Dalam database, fungsi partisi berada di namespace terpisah dari fungsi lain. Fungsi partisi dan skema partisi bukan milik skema.
Jika ada baris dalam tabel yang dipartisi memiliki NULL di kolom partisi, baris ini ditempatkan di partisi paling kiri. Namun, jika NULL ditentukan sebagai nilai batas pertama dan RANGE RIGHT ditentukan dalam definisi fungsi partisi, maka partisi paling kiri tetap kosong, dan NULL ditempatkan di partisi kedua.
Mesin database mendukung hingga 15.000 partisi. Dalam versi yang lebih lama dari SQL Server 2012 (11.x), jumlah partisi dibatasi hingga 1.000 secara default.

Pedoman kinerja

Mesin database mendukung hingga 15.000 partisi per tabel atau indeks. Namun, menggunakan sejumlah besar partisi memiliki implikasi pada memori, operasi indeks yang dipartisi, perintah DBCC, modifikasi skema, dan performa kueri. Bagian ini menjelaskan implikasi performa desain yang melibatkan sejumlah besar partisi dan menyediakan solusi sesuai kebutuhan.

Peringatan

Jika desain Anda menggunakan ratusan atau ribuan partisi per tabel atau indeks, pastikan Anda memahami implikasi performa, menguji dan memvalidasi skenario penggunaan penting, dan memiliki rencana untuk mengatasi dampak performa apa pun.

Hindari desain dengan jumlah partisi dalam ratusan atau ribuan kecuali diperlukan secara ketat.

Penggunaan dan panduan memori

Kami menyarankan agar Anda menggunakan setidaknya 16 GB RAM jika sejumlah besar partisi sedang digunakan. Jika sistem tidak memiliki memori yang cukup, pernyataan Data Manipulation Language (DML), pernyataan Data Definition Language (DDL), dan operasi lainnya dapat gagal karena memori yang tidak mencukup. Sistem dengan RAM 16 GB yang menjalankan banyak proses intensif memori mungkin 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 akan mengalami masalah performa dan memori.

Batasan memori dapat memengaruhi performa atau kemampuan mesin database untuk membangun indeks yang dipartisi. Ini terutama terjadi ketika indeks tidak selaras dengan tabel dasarnya atau tidak selaras dengan indeks berklusternya.

Di SQL Server dan Azure SQL Managed Instance, Anda dapat meningkatkan index create memory (KB) Opsi Konfigurasi Server. Untuk informasi selengkapnya, lihat Konfigurasi server: indeks membuat memori.

Untuk Azure SQL Database, pertimbangkan untuk sementara atau secara permanen meningkatkan ukuran komputasi database untuk mendapatkan lebih banyak memori.

Operasi indeks yang dipartisi

Membuat dan membangun kembali indeks tidak sejajar pada tabel dengan lebih dari 1.000 partisi bisa saja dilakukan, tetapi tidak didukung. Melakukannya dapat menyebabkan penurunan performa atau konsumsi memori yang berlebihan selama operasi ini.

Membuat dan membangun ulang indeks yang selaras dapat memerlukan waktu lebih lama seiring dengan meningkatnya jumlah partisi. Kami menyarankan agar Anda tidak menjalankan beberapa perintah buat dan bangun ulang indeks secara bersamaan karena Anda mungkin mengalami masalah performa dan memori.

Saat mesin database melakukan pengurutan untuk membangun indeks yang dipartisi, mesin database pertama-tama membangun satu tabel pengurutan untuk setiap partisi. Kemudian menyusun tabel pengurutan di dalam grup file masing-masing dari setiap partisi, atau di dalam tempdb jika opsi indeks SORT_IN_TEMPDB ditentukan. Setiap tabel pengurutan memerlukan jumlah memori minimum untuk dibuat. Saat Anda membangun indeks yang dipartisi yang selaras dengan tabel dasarnya, tabel pengurutan dibuat satu per satu, menggunakan lebih sedikit memori. Namun, saat Anda membangun indeks yang tidak dipartisi, tabel pengurutan dibuat secara bersamaan. Akibatnya, harus tersedia cukup memori untuk menangani pengurutan ini secara bersamaan. 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 terpartisi yang tidak selaras dengan 100 partisi memerlukan memori yang cukup untuk melakukan pengurutan serial terhadap 4.000 (40 * 100) halaman pada saat yang sama. Jika memori ini tersedia, operasi build berhasil, tetapi performanya mungkin menurun. Jika memori ini tidak tersedia, operasi build gagal. Atau, indeks terpartisi yang selaras dengan 100 partisi hanya memerlukan memori yang cukup untuk mengurutkan 40 halaman, karena proses pengurutan tidak dilakukan secara bersamaan.

Untuk indeks yang selaras dan tidak selaras, persyaratan memori dapat lebih besar jika mesin database menggunakan paralelisme kueri dalam operasi build indeks. Semakin besar tingkat paralelisme (DOP), semakin tinggi persyaratan memori. Misalnya, jika mesin database mengatur DOP ke 4, indeks partisi nonaligned dengan 100 partisi memerlukan memori yang memadai 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 tingkat paralelisme sebagai solusinya, dengan mengorbankan waktu build indeks yang berpotensi lebih lama.

Perintah DBCC

Dengan jumlah partisi yang lebih besar, perintah DBCC seperti DBCC CHECKDB dan DBCC CHECKTABLE dapat memakan waktu lebih lama untuk dieksekusi saat jumlah partisi meningkat.

Queries

Setelah mempartisi tabel atau indeks, kueri yang menggunakan eliminasi partisi dapat memiliki performa yang sebanding atau ditingkatkan. Kueri yang tidak menggunakan penghapusan partisi dapat memakan waktu lebih lama untuk dijalankan saat jumlah partisi meningkat.

Misalnya, asumsikan tabel memiliki 100 juta baris dan kolom A dan B.

Dalam skenario 1, tabel dibagi menjadi 1.000 partisi pada kolom A.
Dalam skenario 2, tabel dibagi menjadi 10.000 partisi pada kolom A.

Kueri pada tabel yang memiliki klausul WHERE yang memfilter kolom A akan melakukan eliminasi partisi dan memindai sebagian dari semua partisi. Kueri yang sama mungkin berjalan lebih cepat dalam skenario 2 karena ada lebih sedikit baris untuk dipindai dalam partisi. Kueri dengan WHERE klausa yang memfilter kolom B akan memindai semua partisi. Kueri mungkin berjalan lebih cepat dalam skenario 1 daripada dalam skenario 2 karena ada lebih sedikit partisi untuk dipindai.

Kueri yang menggunakan TOP, , MAXatau MIN pada kolom selain kolom partisi mungkin mengalami pengurangan performa dengan partisi karena semua partisi harus dievaluasi.

Demikian pula, kueri yang melakukan pencarian baris tunggal atau pemindaian rentang kecil membutuhkan waktu lebih lama terhadap tabel yang dipartisi daripada terhadap tabel yang tidak dipartisi jika predikat kueri tidak menyertakan kolom partisi, karena perlu melakukan sebanyak mungkin pencarian atau pemindaian karena ada partisi. Untuk alasan ini, pemartisian jarang meningkatkan performa dalam sistem OLTP di mana kueri tersebut umum.

Jika Anda sering menjalankan kueri yang melibatkan equijoin antara dua atau beberapa tabel yang dipartisi, kolom partisinya harus sama dengan kolom tempat tabel digabungkan. Selain itu, tabel, atau indeksnya, harus dikolokasi. Ini berarti bahwa mereka menggunakan fungsi partisi bernama yang sama, atau mereka menggunakan fungsi partisi yang berbeda yang pada dasarnya sama, karena mereka:

Memiliki jumlah parameter yang sama yang digunakan untuk pemartisian, dan parameter yang sesuai adalah jenis data yang sama.
Tentukan jumlah partisi yang sama.
Tentukan nilai batas yang sama untuk partisi.

Dengan cara ini, pengoptimal kueri dapat memproses gabungan lebih cepat, karena gabungan memproses data dari pasangan partisi yang dikolokasi. Jika kueri menggabungkan dua tabel yang tidak dikolokasikan atau tidak dipartisi pada bidang gabungan, keberadaan partisi mungkin benar-benar memperlambat pemrosesan kueri alih-alih mempercepatnya.

Anda mungkin merasa bermanfaat menggunakan $PARTITION dalam beberapa kueri. Untuk informasi selengkapnya, lihat $PARTITION.

Untuk informasi selengkapnya tentang penanganan partisi dalam pemrosesan kueri, termasuk strategi eksekusi kueri paralel untuk tabel dan indeks yang dipartisi, dan praktik terbaik tambahan, lihat Penyempurnaan Pemrosesan Kueri pada Tabel dan Indeks yang Dipartisi.

Komputasi statistik selama operasi indeks yang dipartisi

Ketika indeks nonpartisi dibuat atau dibangun kembali, mesin database juga membuat statistik pada indeks dengan memindai semua baris dalam indeks. Namun, ketika indeks yang dipartisi dibuat atau dibangun kembali, statistik dibuat menggunakan algoritma pengambilan sampel default.

Untuk membuat atau memperbarui statistik pada indeks yang dipartisi dengan memindai sampel yang lebih besar atau semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan SAMPLE klausa atau FULLSCAN .

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-07-20