Tabel dan indeks yang dipartisi

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server, Azure SQL Database, dan Azure SQL Managed Instance mendukung tabel dan partisi indeks. 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.

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 SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022. Tabel dan indeks yang dipartisi tersedia di semua tingkat layanan Azure SQL Database dan Azure SQL Managed Instance.

Pemartisian tabel juga tersedia di kumpulan SQL khusus di Azure Synapse Analytics, dengan beberapa perbedaan sintaksis. Pelajari selengkapnya di Tabel partisi di kumpulan SQL khusus.

Penting

Mesin database mendukung hingga 15.000 partisi secara default. Dalam versi yang lebih lama dari SQL Server 2012 (11.x), jumlah partisi dibatasi hingga 1.000 secara default.

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 dapat meningkatkan performa kueri, berdasarkan jenis kueri yang sering Anda jalankan. Misalnya, pengoptimal kueri dapat memproses kueri yang sama antara dua atau lebih tabel yang dipartisi lebih cepat ketika kolom partisi sama dengan kolom tempat tabel digabungkan. Lihat Kueri di bawah ini untuk informasi lebih lanjut.

Anda dapat meningkatkan performa dengan mengaktifkan eskalasi kunci di tingkat partisi alih-alih seluruh tabel. Ini dapat mengurangi ketidakcocokan penguncian pada tabel. Untuk mengurangi ketidakcocokan penguncian dengan mengizinkan eskalasi kunci ke partisi, atur LOCK_ESCALATION opsi ALTER TABLE pernyataan ke OTOMATIS.

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 akan 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 (LEFT atau RIGHT), menentukan bagaimana nilai batas fungsi partisi akan dimasukkan ke dalam partisi yang dihasilkan:

  • Rentang LEFT menentukan bahwa nilai batas milik sisi kiri interval nilai batas saat nilai interval diurutkan oleh mesin database dalam urutan naik dari kiri ke kanan. Dengan kata lain, nilai batas tertinggi akan disertakan dalam partisi.
  • Rentang KANAN menentukan bahwa nilai batas milik sisi kanan interval nilai batas saat nilai interval diurutkan oleh mesin database dalam urutan naik dari kiri ke kanan. Dengan kata lain, nilai batas terendah akan disertakan dalam setiap partisi.

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

Misalnya, fungsi partisi berikut mempartisi tabel atau indeks menjadi 12 partisi, satu untuk setiap bulan nilai setahun dalam kolom tanggalwaktu . Rentang RIGHT digunakan, menunjukkan bahwa nilai batas akan berfungsi sebagai nilai batas yang lebih rendah di setiap partisi. Rentang RIGHT sering kali lebih sederhana untuk dikerjakan saat mempartisi tabel berdasarkan kolom jenis data datetime atau datetime2 , karena baris dengan nilai tengah malam akan disimpan dalam partisi yang sama dengan baris dengan nilai selanjutnya pada hari yang sama. Demikian pula, jika menggunakan jenis data tanggal dan menggunakan partisi sebulan atau lebih, rentang RIGHT menyimpan hari pertama dalam sebulan dalam partisi yang sama seperti hari selanjutnya di bulan tersebut. Ini membantu dalam eliminasi Partisi yang tepat saat mengkueri data sepanjang hari.

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 akan dipartisi. 1 Februari adalah titik batas pertama yang didefinisikan dalam fungsi, sehingga bertindak sebagai batas bawah partisi 2.

Partisi 1 2 ... 11 12
Nilai datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Untuk RENTANG KIRI dan RENTANG KANAN, partisi paling kiri memiliki nilai minimum jenis data sebagai batas bawahnya, dan partisi paling kanan memiliki nilai maksimum jenis data sebagai batas atasnya.

Temukan contoh fungsi partisi LEFT dan RIGHT lainnya di CREATE PARTITION FUNCTION (Transact-SQL).

Skema partisi

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

Temukan sintaks contoh untuk membuat skema partisi di CREATE PARTITION SCHEME (Transact-SQL).

Grup File

Alasan utama untuk menempatkan partisi Anda pada beberapa grup file adalah untuk memastikan bahwa Anda dapat secara independen melakukan operasi pencadangan dan pemulihan pada partisi. Ini karena Anda dapat melakukan pencadangan pada grup file individual. 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. 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 dapat 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, 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.

Catatan

Pemartisian tidak sepenuhnya didukung di Azure SQL Database. Karena hanya PRIMARY grup file yang didukung di Azure SQL Database, semua partisi harus ditempatkan pada PRIMARY grup file.

Temukan kode contoh untuk membuat grup file untuk SQL Server dan Azure SQL Managed Instance di File ALTER DATABASE (Transact-SQL) dan Opsi Grup File.

Kolom partisi

Kolom tabel atau indeks yang digunakan fungsi partisi untuk mempartisi tabel atau indeks. 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 dengan kolom komputasi dapat berguna.
  • Kolom semua jenis data yang valid untuk digunakan sebagai kolom kunci indeks dapat digunakan sebagai kolom partisi, kecuali tanda waktu.
  • Kolom tipe data objek besar (LOB), seperti ntext, teks, gambar, xml, varchar(max), nvarchar(max), dan varbinary(max), tidak dapat ditentukan.
  • Jenis jenis yang ditentukan pengguna dan jenis data alias Microsoft .NET Framework common language runtime (CLR) tidak dapat ditentukan.

Untuk mempartisi objek, tentukan skema partisi dan kolom partisi di pernyataan CREATE TABLE (Transact-SQL),ALTER TABLE (Transact-SQL), dan CREATE INDEX (Transact-SQL).

Saat membuat indeks non-kluster, jika partition_scheme_name 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 klausa DROP_EXISTING. Ini memungkinkan Anda mempartisi indeks non-partisi, membuat indeks partisi non-partisi, atau mengubah skema partisi indeks.

Indeks yang diratakan

Indeks yang dibangun pada skema partisi yang sama dengan tabel yang sesuai. Ketika tabel dan indeksnya 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 bernama 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 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 menentukan bahwa kunci indeks berkluster berisi kolom partisi. Untuk informasi selengkapnya tentang indeks berkluster dan arsitektur indeks, lihat Panduan Desain Indeks Berkluster.

Partisi indeks nonclustered

Saat mempartisi indeks nonclustered unik, kunci indeks harus berisi kolom partisi. Saat mempartisi indeks nonunique, nonclustered, mesin database menambahkan kolom partisi secara default sebagai kolom non-kunci (termasuk) indeks untuk memastikan indeks selaras dengan tabel dasar. 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 diratakan

Indeks yang tidak selaras dipartisi secara berbeda dari tabel yang sesuai. Artinya, indeks memiliki skema partisi berbeda yang menempatkannya pada grup file terpisah atau sekumpulan grup file dari tabel dasar. Merancang indeks partisi yang tidak selaras 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.

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

Pembatasan

  • Cakupan fungsi dan skema partisi terbatas pada database tempat mereka telah dibuat. Dalam database, fungsi partisi berada di namespace terpisah dari fungsi lain.

  • 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.

Pedoman performa

Mesin database mendukung hingga 15.000 partisi per tabel atau indeks. Namun, menggunakan lebih dari 1.000 partisi memiliki implikasi pada memori, operasi indeks yang dipartisi, perintah DBCC, dan kueri. Bagian ini menjelaskan implikasi performa menggunakan lebih dari 1.000 partisi dan memberikan solusi sesuai kebutuhan.

Dengan hingga 15.000 partisi yang diizinkan per tabel atau indeks yang dipartisi, Anda dapat menyimpan data untuk durasi panjang dalam satu tabel. Namun, Anda harus menyimpan data hanya selama diperlukan dan menjaga keseimbangan antara performa dan jumlah partisi.

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 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 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, jika tabel sudah memiliki indeks berkluster.

Di SQL Server dan Azure SQL Managed Instance, Anda dapat meningkatkan index create memory (KB) Opsi Konfigurasi Server. Untuk informasi selengkapnya, lihat Mengonfigurasi indeks membuat Opsi Konfigurasi Server memori. Untuk Azure SQL Database, pertimbangkan untuk sementara atau secara permanen meningkatkan tujuan tingkat layanan untuk database dalam portal Azure untuk mengalokasikan lebih banyak memori.

Operasi indeks yang dipartisi

Membuat dan membangun kembali indeks yang tidak selaras 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 mesin database melakukan pengurutan untuk membangun indeks yang dipartisi, mesin database pertama-tama membangun satu tabel pengurutan untuk setiap partisi. Kemudian menyusun tabel pengurutan baik di grup file masing-masing dari setiap partisi atau 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 ada cukup memori untuk menangani sortiran 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 nonaligned 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 yang dipartisi yang selaras 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 selaras, persyaratan memori dapat lebih besar jika mesin database menggunakan paralelisme kueri untuk operasi build pada komputer multiprosesor. Ini karena semakin besar tingkat paralelisme (DOP), semakin besar 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 secara manual.

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.

Kueri

Setelah mempartisi tabel atau indeks, kueri yang menggunakan eliminasi partisi dapat memiliki performa yang sebanding atau ditingkatkan dengan jumlah partisi yang lebih besar. 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, , Bdan C.

  • 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 WHERE pemfilteran klausa 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 WHERE pemfilteran klausa 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 pengurangan performa dengan partisi karena semua partisi harus dievaluasi.

Demikian pula, kueri yang melakukan pencarian baris tunggal atau pemindaian rentang kecil akan memakan 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 partisi itu sendiri dapat digabungkan. 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 berguna untuk digunakan $PARTITION dalam beberapa kueri. Pelajari selengkapnya di $PARTITION (Transact-SQL).

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.

Perubahan perilaku dalam komputasi statistik selama operasi indeks yang dipartisi

Di Azure SQL Database, Azure SQL Managed Instance, dan SQL Server 2012 (11.x) dan yang lebih tinggi, statistik tidak dibuat dengan memindai semua baris dalam tabel saat indeks yang dipartisi dibuat atau dibangun kembali. Sebagai gantinya, pengoptimal kueri menggunakan algoritma pengambilan sampel default untuk menghasilkan statistik.

Setelah meningkatkan database dengan indeks yang dipartisi dari versi SQL Server yang lebih rendah dari 2012 (11.x), Anda mungkin melihat perbedaan dalam data histogram untuk indeks ini. Perubahan perilaku ini dapat memengaruhi performa kueri. Untuk mendapatkan statistik pada indeks yang dipartisi dengan memindai semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan FULLSCAN klausul .

Konten terkait

Pelajari selengkapnya tentang tabel yang dipartisi dan strategi indeks dalam artikel berikut: