ALTER INDEX (Transact-SQL)

Artikel
05/23/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Memodifikasi tabel atau indeks tampilan yang ada (rowstore, columnstore, atau XML) dengan menonaktifkan, membangun kembali, atau mengatur ulang indeks; atau dengan mengatur opsi pada indeks.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, dan Azure SQL Managed Instance.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Sintaks untuk Azure Synapse Analytics dan Analytics Platform System (PDW).

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Argumen

index_name

Nama indeks. Nama indeks harus unik dalam tabel atau tampilan tetapi tidak harus unik dalam database. Nama indeks harus mengikuti aturan pengidentifikasi.

SEMUA

Menentukan semua indeks yang terkait dengan tabel atau tampilan terlepas dari jenis indeks. Menentukan ALL penyebab pernyataan gagal jika satu atau beberapa indeks berada dalam grup file offline atau baca-saja atau operasi yang ditentukan tidak diizinkan pada satu atau beberapa jenis indeks. Tabel berikut mencantumkan operasi indeks dan jenis indeks yang tidak diizinkan.

Menggunakan kata kunci ALL dengan operasi ini	Gagal jika tabel memiliki satu atau beberapa
`REBUILD WITH ONLINE = ON`	Indeks XML Indeks spasial Indeks ^{penyimpan kolom 1}
`REBUILD PARTITION = <partition_number>`	Indeks nonpartisi, indeks XML, indeks spasial, atau indeks yang dinonaktifkan
`REORGANIZE`	Indeks dengan `ALLOW_PAGE_LOCKS` diatur ke `OFF`
`REORGANIZE PARTITION = <partition_number>`	Indeks nonpartisi, indeks XML, indeks spasial, atau indeks yang dinonaktifkan
`IGNORE_DUP_KEY = ON`	Indeks XML Indeks spasial Indeks ^{penyimpan kolom 1}
`ONLINE = ON`	Indeks XML Indeks spasial Indeks ^{penyimpan kolom 1}
`RESUMABLE = ON` ²	Indeks yang dapat diulang tidak didukung dengan `ALL` kata kunci

¹ Berlaku untuk SQL Server 2012 (11.x) dan versi yang lebih baru, dan Azure SQL Database.

² Berlaku untuk SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Jika ALL ditentukan dengan PARTITION = <partition_number>, semua indeks harus diratakan. Ini berarti bahwa partisi dipartisi berdasarkan fungsi partisi yang setara. Menggunakan ALL dengan PARTITION menyebabkan semua partisi indeks dengan partisi yang sama <partition_number> untuk dibangun kembali atau diorganisir ulang. Untuk informasi selengkapnya tentang indeks yang dipartisi, lihat Tabel dan indeks yang dipartisi.

Untuk informasi selengkapnya tentang operasi indeks yang dapat dilakukan secara online, lihat Panduan untuk operasi indeks online.

database_name

Nama database.

schema_name

Nama skema tempat tabel atau tampilan berada.

table_or_view_name

Nama tabel atau tampilan yang terkait dengan indeks. Untuk menampilkan laporan indeks pada objek, gunakan tampilan katalog sys.indexes .

SQL Database mendukung format <database_name>.[schema_name].<table_or_view_name> nama tiga bagian ketika database_name adalah database saat ini atau database_name adalah tempdb dan table_or_view_name dimulai dengan #.

MEMBANGUN KEMBALI [ DENGAN ( <rebuild_index_option> [ ,... n ] ) ]

Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru, dan Azure SQL Database

Menentukan bahwa indeks dibangun kembali menggunakan kolom, jenis indeks, atribut keunikan, dan urutan pengurutan yang sama. Klausa ini setara dengan DBCC DBREINDEX. REBUILD mengaktifkan indeks yang dinonaktifkan. Membangun kembali indeks berkluster tidak membangun kembali indeks noncluster terkait kecuali kata kunci ALL ditentukan. Jika opsi indeks tidak ditentukan, nilai opsi indeks yang ada yang disimpan dalam sys.indexes diterapkan. Untuk opsi indeks apa pun yang nilainya tidak disimpan di sys.indexes, default yang ditunjukkan dalam definisi argumen opsi berlaku.

Jika ALL ditentukan dan tabel yang mendasar adalah tumpukan, REBUILD operasi tidak berpengaruh pada tabel. Indeks noncluster apa pun yang terkait dengan tabel dibangun kembali.

REBUILD Operasi dapat dicatat secara minimal jika model pemulihan database diatur ke yang dicatat secara massal atau sederhana.

Catatan

Saat Anda membangun kembali indeks XML utama, tabel pengguna yang mendasar tidak tersedia selama durasi operasi indeks.

Untuk indeks penyimpan kolom, REBUILD operasi:

Tidak menggunakan urutan pengurutan.
Memperoleh kunci eksklusif pada tabel atau partisi saat REBUILD terjadi. Data "offline" dan tidak tersedia selama REBUILD, bahkan saat menggunakan NOLOCK, baca isolasi rekam jepret yang diterapkan (RCSI), atau isolasi rekam jepret (SI).
Mengkompresi ulang semua data ke dalam penyimpan kolom. Ada dua salinan indeks penyimpan kolom saat REBUILD sedang berlangsung. REBUILD Setelah selesai, SQL Server menghapus indeks penyimpan kolom asli.

Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

PARTISI

Menentukan bahwa hanya satu partisi indeks yang dibangun kembali atau diorganisir ulang. PARTITION tidak dapat ditentukan jika index_name bukan indeks yang dipartisi.

PARTITION = ALL membangun kembali semua partisi.

Peringatan

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. Microsoft merekomendasikan untuk hanya menggunakan indeks yang selaras saat jumlah partisi melebihi 1.000.

partition_number

Nomor partisi indeks yang dipartisi yang akan dibangun kembali atau diorganisir ulang. partition_number adalah ekspresi konstanta yang dapat mereferensikan variabel. Ini termasuk variabel jenis atau fungsi yang ditentukan pengguna dan fungsi yang ditentukan pengguna, tetapi tidak dapat mereferensikan pernyataan Transact-SQL. partition_number harus ada atau pernyataan gagal.
DENGAN ( <single_partition_rebuild_index_option> )

SORT_IN_TEMPDB, , MAXDOPDATA_COMPRESSION, dan XML_COMPRESSION adalah opsi yang dapat ditentukan ketika Anda REBUILD satu partisi (PARTITION = partition_number). Indeks XML tidak dapat ditentukan dalam satu operasi partisi REBUILD .

MENONAKTIFKAN

Menandai indeks sebagai dinonaktifkan dan tidak tersedia untuk digunakan oleh Mesin Database. Indeks apa pun dapat dinonaktifkan. Definisi indeks indeks yang dinonaktifkan tetap berada di katalog sistem tanpa data indeks yang mendasar. Menonaktifkan indeks berkluster mencegah akses pengguna ke data tabel yang mendasar. Untuk mengaktifkan indeks, gunakan ALTER INDEX REBUILD atau CREATE INDEX WITH DROP_EXISTING. Untuk informasi selengkapnya, lihat Menonaktifkan indeks dan batasan dan Mengaktifkan Indeks dan Batasan.

MENGATUR ULANG indeks rowstore

Untuk indeks rowstore, REORGANIZE menentukan untuk mengatur ulang tingkat daun indeks. Operasinya REORGANIZE adalah:

Selalu dilakukan secara online. Ini berarti kunci tabel pemblokiran jangka panjang tidak disimpan dan kueri atau pembaruan pada tabel yang mendasarinya dapat dilanjutkan ALTER INDEX REORGANIZE selama transaksi.
Tidak diperbolehkan untuk indeks yang dinonaktifkan.
Tidak diperbolehkan ketika ALLOW_PAGE_LOCKS diatur ke OFF.
Tidak digulung balik ketika dilakukan dalam transaksi dan transaksi digulung balik.

Catatan

Ketika ALTER INDEX REORGANIZE menggunakan transaksi eksplisit (misalnya, ALTER INDEX di dalam BEGIN TRAN ... COMMIT/ROLLBACK) alih-alih mode transaksi implisit default, perilaku penguncian REORGANIZE menjadi lebih ketat, berpotensi menyebabkan pemblokiran. Untuk informasi selengkapnya tentang transaksi implisit, lihat MENGATUR IMPLICIT_TRANSACTIONS.

Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

REORGANISASI DENGAN ( LOB_COMPACTION = { ON | OFF } )

Berlaku untuk indeks rowstore.

LOB_COMPACTION = ON

Menentukan untuk memampatkan semua halaman yang berisi data jenis data objek besar (LOB) ini: gambar, teks, ntext, varchar(max), nvarchar(max), varbinary(max), dan xml. Memampatkan data ini dapat mengurangi ukuran data pada disk.
Untuk indeks berkluster, ini memampatkan semua kolom LOB yang terkandung dalam tabel.
Untuk indeks nonkluster, ini memadatkan semua kolom LOB yang merupakan kolom non-kunci (disertakan) dalam indeks.
REORGANIZE ALL melakukan LOB_COMPACTION pada semua indeks. Untuk setiap indeks, ini memadatkan semua kolom LOB dalam indeks berkluster, tabel yang mendasar, atau kolom yang disertakan dalam indeks non-kluster.

LOB_COMPACTION = OFF

Halaman yang berisi data objek besar tidak dikompresi.
OFF tidak berpengaruh pada tumpukan.

MENGATUR ULANG indeks penyimpan kolom

Untuk indeks penyimpan kolom, REORGANIZE kompres setiap CLOSED grup baris delta ke dalam penyimpan kolom sebagai grup baris terkompresi. REORGANIZE Operasi ini selalu dilakukan secara online. Ini berarti kunci tabel pemblokiran jangka panjang tidak disimpan dan kueri atau pembaruan pada tabel yang mendasarinya dapat dilanjutkan ALTER INDEX REORGANIZE selama transaksi. Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

REORGANIZE tidak diperlukan untuk memindahkan CLOSED grup baris delta ke grup baris terkompresi. Proses tuple-mover latar belakang (TM) bangun secara berkala untuk memadatkan CLOSED grup baris delta. Sebaiknya gunakan REORGANIZE saat tuple-mover tertinggal. REORGANIZE dapat memadatkan grup baris secara lebih agresif.
Untuk memadatkan semua OPEN grup baris dan CLOSED , lihat REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) opsi di bagian ini.

Untuk indeks penyimpan kolom di SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database, REORGANIZE melakukan pengoptimalan defragmentasi tambahan berikut secara online:

Secara fisik menghapus baris dari grup baris ketika 10% atau lebih baris telah dihapus secara logis. Byte yang dihapus diklaim kembali di media fisik. Misalnya, jika grup baris terkompresi 1 juta baris memiliki 100.000 baris yang dihapus, SQL Server menghapus baris yang dihapus dan memadatkan ulang grup baris dengan baris 900k. Ini menyimpan pada penyimpanan dengan menghapus baris yang dihapus.
Menggabungkan satu atau beberapa grup baris terkompresi untuk meningkatkan baris per grup baris hingga maksimum 1.048.576 baris. Misalnya, jika Anda mengimpor secara massal 5 batch dari 102.400 baris, Anda mendapatkan 5 grup baris terkompresi. Jika Anda menjalankan REORGANIZE, grup baris ini akan digabungkan menjadi 1 grup baris terkompresi dengan ukuran 512.000 baris. Ini mengasumsikan tidak ada ukuran kamus atau batasan memori.
Untuk grup baris di mana 10% atau lebih baris telah dihapus secara logis, SQL Server mencoba menggabungkan grup baris ini dengan satu atau beberapa grup baris. Misalnya, grup baris 1 dikompresi dengan 500.000 baris dan grup baris 21 dikompresi dengan maksimum 1.048.576 baris. Grup Baris 21 memiliki 60% baris yang dihapus yang meninggalkan 409.830 baris. SQL Server lebih memilih menggabungkan kedua grup baris ini untuk memadatkan grup baris baru yang memiliki 909.830 baris.

MENGATUR ULANG DENGAN ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Berlaku untuk indeks penyimpan kolom.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database

COMPRESS_ALL_ROW_GROUPS menyediakan cara untuk memaksa OPEN atau CLOSED delta rowgroups ke dalam columnstore. Dengan opsi ini, tidak perlu membangun kembali indeks penyimpan kolom untuk mengosongkan grup baris delta. Ini, dikombinasikan dengan fitur defragmentasi penghapusan dan penggabungan lainnya membuatnya tidak lagi perlu membangun kembali indeks dalam sebagian besar situasi.

ON memaksa semua grup baris ke dalam penyimpan kolom, terlepas dari ukuran dan status (CLOSED atau OPEN).
OFF memaksa semua CLOSED grup baris ke dalam penyimpan kolom.

Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

SET ( <opsi> set_index [ ,... n ] )

Menentukan opsi indeks tanpa membangun kembali atau mengatur ulang indeks. SET tidak dapat ditentukan untuk indeks yang dinonaktifkan.

PAD_INDEX = { ON | NONAKTIF }

Menentukan padding indeks. Default adalah OFF.

AKTIF

Persentase ruang kosong yang ditentukan oleh FILLFACTOR diterapkan ke halaman tingkat menengah indeks. Jika FILLFACTOR tidak ditentukan pada saat PAD_INDEX yang sama diatur ke ON, nilai faktor pengisian yang disimpan dalam sys.indexes digunakan.
OFF atau fillfactor tidak ditentukan

Halaman tingkat menengah diisi hingga mendekati kapasitas. Ini menyisakan ruang yang cukup untuk setidaknya satu baris dari ukuran maksimum yang dapat dimiliki indeks, berdasarkan kumpulan kunci pada halaman perantara.

Untuk informasi selengkapnya, lihat MEMBUAT INDEKS.

FILLFACTOR = fillfactor

Menentukan persentase yang menunjukkan seberapa penuh Mesin Database harus membuat tingkat daun setiap halaman indeks selama pembuatan atau perubahan indeks. Nilai untuk fillfactor harus berupa nilai bilangan bulat dari 1 hingga 100. Defaultnya adalah 0. Nilai faktor pengisian 0 dan 100 sama dalam semua hal.

Pengaturan eksplisit FILLFACTOR hanya berlaku saat indeks pertama kali dibuat atau dibangun ulang. Mesin Database tidak secara dinamis menyimpan persentase ruang kosong yang ditentukan di halaman. Untuk informasi selengkapnya, lihat MEMBUAT INDEKS.

Untuk melihat pengaturan faktor pengisian, gunakan fill_factor di sys.indexes.

Penting

Membuat atau mengubah indeks berkluster dengan FILLFACTOR nilai memengaruhi jumlah ruang penyimpanan yang ditempati data, karena Mesin Database mendistribusikan ulang data saat membuat indeks berkluster.

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

Menentukan apakah akan menyimpan hasil pengurutan di tempdb. Defaultnya adalah OFF kecuali untuk Azure SQL Database Hyperscale. Untuk semua operasi pembangunan ulang indeks di Hyperscale, SORT_IN_TEMPDB selalu AKTIF, terlepas dari opsi yang ditentukan kecuali pembangunan ulang indeks yang dapat diulang digunakan.

AKTIF

Hasil pengurutan menengah yang digunakan untuk membangun indeks disimpan di tempdb. Jika tempdb berada pada sekumpulan disk yang berbeda dari database pengguna, ini mungkin mengurangi waktu yang diperlukan untuk membuat indeks. Namun, ini meningkatkan jumlah ruang disk yang digunakan selama build indeks.
TIDAK AKTIF

Hasil pengurutan menengah disimpan dalam database yang sama dengan indeks.

Jika operasi pengurutan tidak diperlukan, atau jika pengurutan dapat dilakukan dalam memori, SORT_IN_TEMPDB opsi diabaikan.

Untuk informasi selengkapnya, lihat Opsi SORT_IN_TEMPDB Untuk Indeks.

IGNORE_DUP_KEY = { AKTIF | NONAKTIF }

Menentukan respons kesalahan saat operasi sisipkan mencoba menyisipkan nilai kunci duplikat ke dalam indeks unik. Opsi IGNORE_DUP_KEY hanya berlaku untuk menyisipkan operasi setelah indeks dibuat atau dibangun kembali. Default adalah OFF.

AKTIF

Pesan peringatan terjadi ketika nilai kunci duplikat disisipkan ke dalam indeks unik. Hanya baris yang melanggar batasan keunikan yang gagal.
TIDAK AKTIF

Pesan kesalahan terjadi ketika nilai kunci duplikat disisipkan ke dalam indeks unik. Seluruh INSERT operasi digulung balik.

IGNORE_DUP_KEY tidak dapat diatur ke ON untuk indeks yang dibuat pada tampilan, indeks non-unik, indeks XML, indeks spasial, dan indeks yang difilter.

Untuk melihat IGNORE_DUP_KEY, gunakan sys.indexes.

Dalam sintaksis kompatibel mundur, WITH IGNORE_DUP_KEY setara dengan WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { AKTIF | NONAKTIF }

Nonaktifkan atau aktifkan opsi pembaruan statistik otomatis, AUTO_STATISTICS_UPDATE, untuk statistik yang terkait dengan indeks yang ditentukan. Default adalah OFF.

AKTIF

Pembaruan statistik otomatis dinonaktifkan setelah indeks dibangun kembali.
TIDAK AKTIF

Pembaruan statistik otomatis diaktifkan setelah indeks dibangun kembali.

Untuk memulihkan pembaruan statistik otomatis, atur ke OFF, atau jalankan STATISTICS_NORECOMPUTE UPDATE STATISTICS tanpa NORECOMPUTE klausa.

Peringatan

Jika Anda menonaktifkan pembaruan statistik otomatis, mungkin mencegah Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri yang melibatkan tabel. Anda harus menggunakan opsi ini dengan hemat, dan hanya oleh administrator database yang memenuhi syarat.

Pengaturan ini tidak mencegah pembaruan otomatis dengan fullscan statistik terkait indeks, selama operasi pembangunan ulang.

STATISTICS_INCREMENTAL = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

Ketika ON, statistik yang dibuat adalah statistik per partisi. Ketika OFF, pohon statistik dijatuhkan dan SQL Server mengolah ulang statistik. Default adalah OFF.

Jika statistik per partisi tidak didukung, opsi diabaikan dan peringatan dibuat. Statistik inkremental tidak didukung untuk jenis statistik berikut:

Statistik dibuat dengan indeks yang tidak selaras dengan tabel dasar
Statistik yang dibuat pada database sekunder yang dapat dibaca grup ketersediaan
Statistik yang dibuat pada database baca-saja
Statistik yang dibuat pada indeks yang difilter
Statistik dibuat pada tampilan
Statistik yang dibuat pada tabel internal
Statistik yang dibuat dengan indeks spasial atau indeks XML

ONLINE = { AKTIF | OFF } <seperti yang berlaku untuk rebuild_index_option>

Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Default adalah OFF.

Untuk indeks XML atau indeks spasial, hanya ONLINE = OFF didukung, dan jika ONLINE diatur ke ON kesalahan dimunculkan.

Penting

Operasi indeks online tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

AKTIF

Kunci tabel jangka panjang tidak ditahan selama operasi indeks. Selama fase utama operasi indeks, hanya kunci Berbagi Niat (IS) yang ditahan pada tabel sumber. Ini memungkinkan kueri atau pembaruan pada tabel dan indeks yang mendasarinya untuk melanjutkan. Pada awal operasi, kunci Bersama (S) disimpan secara singkat pada objek sumber. Di akhir operasi, kunci S disimpan secara singkat pada sumber jika indeks non-kluster sedang dibuat. Kunci modifikasi skema (Sch-M) diperoleh ketika indeks berkluster dibuat atau dihilangkan secara online, dan ketika indeks berkluster atau non-kluster sedang dibangun kembali. ONLINE tidak dapat diatur ke ON saat indeks sedang dibuat pada tabel sementara lokal.
TIDAK AKTIF

Kunci tabel diterapkan selama operasi indeks. Operasi indeks offline yang membuat, membangun kembali, atau menghilangkan indeks berkluster, spasial, atau XML, atau membangun kembali atau menghilangkan indeks non-kluster, memperoleh kunci Modifikasi Skema (Sch-M) pada tabel. Ini mencegah semua akses pengguna ke tabel yang mendasar selama operasi. Operasi indeks offline yang membuat indeks nonclustered memperoleh kunci Bersama (S) pada tabel. Ini mencegah pembaruan pada tabel yang mendasar tetapi memungkinkan operasi baca, seperti SELECT pernyataan.

Untuk informasi selengkapnya, lihat Melakukan operasi indeks secara online.

Indeks, termasuk indeks pada tabel sementara global, dapat dibangun kembali secara online kecuali untuk kasus-kasus berikut:

Indeks XML
Indeks pada tabel sementara lokal
Indeks kluster unik awal pada tampilan
Panduan Indeks Penyimpan Kolom
Indeks berkluster, jika tabel yang mendasar berisi jenis data LOB (gambar, ntext, teks) dan jenis data spasial
kolom varchar(max) dan varbinary(max) tidak dapat menjadi bagian dari indeks. Di SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Azure SQL Database, ketika tabel berisi kolom varchar(max) atau varbinary(max), indeks berkluster yang berisi kolom lain dapat dibangun atau dibangun kembali menggunakan ONLINE opsi . Azure SQL Database tidak mengizinkan ONLINE opsi saat tabel dasar berisi kolom varchar(max) atau varbinary(max)

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

XEvents berikut terkait dengan ALTER TABLE ... SWITCH PARTITION dan pembangunan ulang indeks online.

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

XEvent progress_report_online_index_operation yang ada untuk operasi indeks online mencakup partition_number dan partition_id.

DAPAT DI-RESUMABLE = { ON | OFF}

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Menentukan apakah operasi indeks online dapat diulang.

AKTIF

Operasi indeks dapat diulang.
TIDAK AKTIF

Operasi indeks tidak dapat diulang.

MAX_DURATION = waktu [ MINUTES ] digunakan dengan `RESUMABLE = ON` (memerlukan `ONLINE = ON`)

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Menunjukkan waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi indeks online yang dapat dilanjutkan dijalankan sebelum dijeda.

Penting

Untuk informasi selengkapnya tentang operasi indeks yang dapat dilakukan secara online, lihat Panduan untuk operasi indeks online.

Catatan

Pembangunan ulang indeks online yang dapat diulang tidak didukung pada indeks penyimpan kolom.

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

Menentukan apakah kunci baris diizinkan. Default adalah ON.

AKTIF

Kunci baris diperbolehkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan.
TIDAK AKTIF

Kunci baris tidak digunakan.

ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }

Menentukan apakah kunci halaman diizinkan. Default adalah ON.

AKTIF

Kunci halaman diizinkan saat Anda mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan.
TIDAK AKTIF

Kunci halaman tidak digunakan.

Catatan

Indeks tidak dapat diatur ulang saat ALLOW_PAGE_LOCKS diatur ke OFF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database

Menentukan apakah akan mengoptimalkan ketidakcocokan sisipan halaman terakhir atau tidak. Default adalah OFF. Untuk informasi selengkapnya, lihat Kunci Berurutan.

MAXDOP = max_degree_of_parallelism

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama operasi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat paralelisme maksimum (opsi konfigurasi server). Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

Penting

MAXDOP Meskipun opsi ini didukung secara sinaptis untuk semua indeks XML, untuk indeks spasial atau indeks XML utama, ALTER INDEX saat ini hanya menggunakan satu prosesor.

max_degree_of_parallelism dapat berupa:

1: Menekan pembuatan rencana paralel.
>1: Membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke angka yang ditentukan.
0 (default): Menggunakan jumlah prosesor aktual atau lebih sedikit berdasarkan beban kerja sistem saat ini.

Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Catatan

Operasi indeks paralel 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.

COMPRESSION_DELAY = { 0 | durasi [ menit ] }

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x))

Untuk tabel berbasis disk, penundaan menentukan jumlah menit minimum grup baris delta dalam CLOSED status harus tetap berada di grup baris delta sebelum SQL Server dapat mengompresinya ke dalam grup baris terkompresi. Karena tabel berbasis disk tidak melacak waktu sisipkan dan perbarui pada baris individual, SQL Server menerapkan penundaan ke grup baris delta dalam CLOSED status .

Defaultnya adalah 0 menit.

Untuk rekomendasi tentang kapan harus menggunakan COMPRESSION_DELAY, lihat Mulai menggunakan Columnstore untuk analitik operasional real time.

DATA_COMPRESSION

Menentukan opsi kompresi data untuk indeks, nomor partisi, atau rentang partisi yang ditentukan. Opsinya meliputi:

NONE

Indeks atau partisi yang ditentukan tidak dikompresi. Ini tidak berlaku untuk indeks penyimpan kolom.
BARIS

Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan baris. Ini tidak berlaku untuk indeks penyimpan kolom.
PAGE

Indeks atau partisi tertentu dikompresi dengan menggunakan pemadatan halaman. Ini tidak berlaku untuk indeks penyimpan kolom.
PENYIMPAN KOLOM

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom nonclustered dan indeks penyimpan kolom berkluster. COLUMNSTORE menentukan untuk mendekompresi indeks atau partisi tertentu yang dikompresi dengan COLUMNSTORE_ARCHIVE opsi . Ketika data dipulihkan, data terus dikompresi dengan kompresi penyimpan kolom yang digunakan untuk semua indeks penyimpan kolom.
COLUMNSTORE_ARCHIVE

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom nonclustered dan indeks penyimpan kolom berkluster. COLUMNSTORE_ARCHIVE lebih lanjut memadatkan partisi yang ditentukan ke ukuran yang lebih kecil. Ini dapat digunakan untuk pengarsipan, atau untuk situasi lain yang memerlukan ukuran penyimpanan yang lebih kecil dan mampu lebih banyak waktu untuk penyimpanan dan pengambilan.

Untuk informasi selengkapnya tentang pemadatan, lihat Pemadatan data.

XML_COMPRESSION

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Menentukan opsi pemadatan XML untuk indeks yang ditentukan yang berisi satu atau beberapa kolom tipe data xml . Opsinya meliputi:

AKTIF

Indeks atau partisi yang ditentukan dikompresi dengan menggunakan kompresi XML.
TIDAK AKTIF

Indeks atau partisi yang ditentukan tidak dikompresi.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

Menentukan partisi yang diterapkan pengaturan DATA_COMPRESSION atau XML_COMPRESSION . Jika indeks tidak dipartisi, ON PARTITIONS argumen menghasilkan kesalahan. ON PARTITIONS Jika klausa tidak disediakan, DATA_COMPRESSION opsi atau XML_COMPRESSION berlaku untuk semua partisi indeks yang dipartisi.

<partition_number_expression> dapat ditentukan dengan cara berikut:

Berikan nomor untuk partisi, misalnya: ON PARTITIONS (2).
Berikan nomor partisi untuk beberapa partisi individual yang dipisahkan oleh koma, misalnya: ON PARTITIONS (1, 5).
Berikan rentang dan partisi individual: ON PARTITIONS (2, 4, 6 TO 8).

<range> dapat ditentukan sebagai nomor partisi yang dipisahkan oleh kata TO, misalnya: ON PARTITIONS (6 TO 8).

Untuk mengatur berbagai jenis kompresi data untuk partisi yang berbeda, tentukan DATA_COMPRESSION opsi lebih dari sekali, misalnya:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Anda juga dapat menentukan XML_COMPRESSION opsi lebih dari sekali, misalnya:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { AKTIF | OFF } <seperti yang berlaku untuk single_partition_rebuild_index_option>

Menentukan apakah indeks atau partisi indeks dari tabel yang mendasar dapat dibangun kembali secara online atau offline. Jika REBUILD ... ONLINE = ON dilakukan, data dalam tabel ini tersedia untuk kueri dan modifikasi data selama operasi indeks. Default adalah OFF.

AKTIF

Kunci tabel jangka panjang tidak ditahan selama operasi indeks. Selama fase utama operasi indeks, hanya kunci Berbagi Niat (IS) yang ditahan pada tabel sumber. Kunci Skema Stabilitas (Sch-S) pada tabel diperlukan saat memulai pembangunan ulang indeks, dan kunci Modifikasi Skema (Sch-M) diperlukan pada tabel di akhir pembangunan ulang indeks online. Meskipun kedua kunci metadata berdurasi pendek, kunci Sch-M terutama harus menunggu semua transaksi pemblokiran selesai. Selama waktu tunggu, kunci Sch-M memblokir semua transaksi lain yang menunggu di belakang kunci ini saat mengakses tabel yang sama.

Catatan

Pembangunan ulang indeks online dapat mengatur low_priority_lock_wait opsi, lihat WAIT_AT_LOW_PRIORITY dengan operasi indeks online.
TIDAK AKTIF

Kunci tabel diterapkan selama operasi indeks. Ini mencegah semua akses pengguna ke tabel yang mendasar selama operasi.

MELANJUTKAN

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Lanjutkan operasi indeks yang dijeda secara manual atau karena kegagalan.

MAX_DURATION digunakan dengan RESUMABLE = ON

Waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi indeks online yang dapat dilanjutkan dijalankan setelah dilanjutkan. Setelah waktu berakhir, operasi yang dapat dilanjutkan dijeda jika masih berjalan.
WAIT_AT_LOW_PRIORITY digunakan dengan RESUMABLE = ON dan ONLINE = ON.

Melanjutkan pembangunan ulang indeks online setelah jeda harus menunggu operasi pemblokiran pada tabel ini. WAIT_AT_LOW_PRIORITY menunjukkan bahwa operasi pembangunan ulang indeks online menunggu kunci prioritas rendah, memungkinkan operasi lain untuk melanjutkan saat operasi build indeks online menunggu. WAIT_AT_LOW_PRIORITY Menghilangkan opsi setara dengan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY.

JEDA

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Jeda operasi pembangunan ulang indeks online yang dapat diulang.

GAGAL

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Batalkan operasi indeks yang sedang berjalan atau dijeda yang dinyatakan dapat dilanjutkan. Anda harus secara eksplisit menjalankan ABORT perintah untuk mengakhiri operasi pembangunan ulang indeks yang dapat diulang. Kegagalan atau jeda operasi indeks yang dapat diulang tidak mengakhiri eksekusinya; sebaliknya, ia meninggalkan operasi dalam status jeda yang tidak terbatas.

Keterangan

ALTER INDEX tidak dapat digunakan untuk mempartisi ulang indeks atau memindahkannya ke grup file yang berbeda. Pernyataan ini tidak dapat digunakan untuk mengubah definisi indeks, seperti menambahkan atau menghapus kolom atau mengubah urutan kolom. Gunakan CREATE INDEX dengan DROP_EXISTING klausa untuk melakukan operasi ini.

Saat opsi tidak ditentukan secara eksplisit, pengaturan saat ini diterapkan. Misalnya, jika FILLFACTOR pengaturan tidak ditentukan dalam REBUILD klausa, nilai faktor pengisian yang disimpan dalam katalog sistem digunakan selama proses pembangunan ulang. Untuk melihat pengaturan opsi indeks saat ini, gunakan sys.indexes.

Nilai untuk ONLINE, MAXDOP, dan SORT_IN_TEMPDB tidak disimpan dalam katalog sistem. Kecuali ditentukan dalam pernyataan indeks, nilai default untuk opsi digunakan.

Pada komputer multiprosesor, seperti halnya kueri lain, ALTER INDEX REBUILD secara otomatis menggunakan lebih banyak prosesor untuk melakukan operasi pemindaian dan pengurutan yang terkait dengan memodifikasi indeks. Saat Anda menjalankan ALTER INDEX REORGANIZE, dengan atau tanpa LOB_COMPACTION, tingkat maksimum nilai paralelisme adalah operasi berulir tunggal. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Penting

Indeks tidak dapat diatur ulang atau dibangun ulang jika grup file tempat indeks berada offline atau diatur ke baca-saja. Ketika kata kunci ALL ditentukan dan satu atau beberapa indeks berada dalam grup file offline atau baca-saja, pernyataan gagal.

Menyusun ulang indeks

Membangun ulang indeks turun dan membuat ulang indeks. Ini menghapus fragmentasi, merebut kembali ruang disk dengan memadamkan halaman berdasarkan pengaturan faktor pengisian yang ditentukan atau yang ada, dan menyusun ulang baris indeks di halaman yang berdekatan. Ketika ALL ditentukan, semua indeks pada tabel dihilangkan dan dibangun kembali dalam satu transaksi. Batasan kunci asing tidak harus dihilangkan terlebih dahulu. Ketika indeks dengan 128 tingkat atau lebih dibangun kembali, Mesin Database menuguhkan alokasi halaman aktual, dan kunci terkaitnya, sampai setelah transaksi dilakukan.

Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Mengatur ulang indeks

Mengatur ulang indeks menggunakan sumber daya sistem minimal. Ini mendefinisikan tingkat daun indeks berkluster dan non-kluster pada tabel dan tampilan dengan menyusun ulang halaman tingkat daun secara fisik agar sesuai dengan logis, kiri ke kanan, urutan simpul daun. Mengatur ulang juga memampatkan halaman indeks. Pemadatan didasarkan pada nilai faktor pengisian yang ada.

Ketika ALL ditentukan, indeks relasional, indeks berkluster dan non-kluster, dan XML pada tabel diatur ulang. Beberapa batasan berlaku saat menentukan ALL, lihat definisi untuk ALL di bagian Argumen di artikel ini.

Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Penting

Untuk tabel Azure Synapse Analytics dengan indeks penyimpan kolom berkluster yang diurutkan, ALTER INDEX REORGANIZE tidak mengurutkan ulang data. Untuk menggunakan data ALTER INDEX REBUILD.

Nonaktifkan indeks

Menonaktifkan indeks mencegah akses pengguna ke indeks, dan untuk indeks berkluster, ke data tabel yang mendasar. Definisi indeks tetap berada dalam katalog sistem. Menonaktifkan indeks non-kluster atau indeks berkluster pada tampilan akan menghapus data indeks secara fisik. Menonaktifkan indeks berkluster mencegah akses ke data, tetapi data tetap tidak tertanam di pohon B hingga indeks dihilangkan atau dibangun kembali. Untuk menampilkan status indeks yang diaktifkan atau dinonaktifkan, kueri is_disabled kolom dalam sys.indexes tampilan katalog.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Jika tabel berada dalam publikasi replikasi transaksional, Anda tidak dapat menonaktifkan indeks apa pun yang terkait dengan kolom kunci utama. Indeks ini diperlukan oleh replikasi. Untuk menonaktifkan indeks, Anda harus terlebih dahulu menghilangkan tabel dari publikasi. Untuk informasi selengkapnya, lihat Menerbitkan Data dan Objek Database.

ALTER INDEX REBUILD Gunakan pernyataan atau CREATE INDEX WITH DROP_EXISTING pernyataan untuk mengaktifkan indeks. Membangun kembali indeks berkluster yang dinonaktifkan tidak dapat dilakukan dengan opsi yang ONLINE diatur ke ON. Untuk informasi selengkapnya, lihat Menonaktifkan indeks dan batasan.

Atur opsi

Anda dapat mengatur opsi ALLOW_ROW_LOCKS, , ALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY, dan STATISTICS_NORECOMPUTE untuk indeks tertentu tanpa membangun kembali atau mengatur ulang indeks tersebut. Nilai yang dimodifikasi segera diterapkan ke indeks. Untuk melihat pengaturan ini, gunakan sys.indexes. Untuk informasi selengkapnya, lihat Mengatur Opsi Indeks.

Opsi penguncian baris dan halaman

Ketika ALLOW_ROW_LOCKS = ON dan ALLOW_PAGE_LOCK = ON, tingkat baris, tingkat halaman, dan kunci tingkat tabel diizinkan saat Anda mengakses indeks. Mesin Database memilih kunci yang sesuai dan dapat meningkatkan kunci dari baris atau kunci halaman ke kunci tabel.

Ketika ALLOW_ROW_LOCKS = OFF dan ALLOW_PAGE_LOCK = OFF, hanya kunci tingkat tabel yang diizinkan saat Anda mengakses indeks.

Jika ALL ditentukan saat opsi kunci baris atau halaman diatur, pengaturan diterapkan ke semua indeks. Saat tabel yang mendasar adalah tumpukan, pengaturan diterapkan dengan cara berikut:

Opsi	Detail
`ALLOW_ROW_LOCKS = ON or OFF`	Ke tumpukan dan indeks nonclustered terkait.
`ALLOW_PAGE_LOCKS = ON`	Ke tumpukan dan indeks nonclustered terkait.
`ALLOW_PAGE_LOCKS = OFF`	Sepenuhnya ke indeks nonclustered. Ini berarti bahwa semua kunci halaman tidak diizinkan pada indeks nonclustered. Pada timbunan, hanya kunci bersama (S), pembaruan (U) dan eksklusif (X) untuk halaman yang tidak diizinkan. Mesin Database masih dapat memperoleh kunci halaman niat (IS, IU atau IX) untuk tujuan internal.

Operasi indeks online

Saat membangun ulang indeks dan ONLINE opsi diatur ke ON, objek yang mendasarinya, tabel dan indeks terkait, tersedia untuk kueri dan modifikasi data. Anda juga dapat membangun kembali secara online sebagian indeks yang berada di satu partisi. Kunci tabel eksklusif hanya ditahan untuk waktu yang singkat selama proses perubahan.

Mengatur ulang indeks selalu dilakukan secara online. Proses ini tidak mengunci jangka panjang dan, oleh karena itu, tidak memblokir kueri atau pembaruan yang sedang berjalan.

Anda dapat melakukan operasi indeks online bersamaan pada tabel atau partisi tabel yang sama hanya saat melakukan operasi berikut:

Membuat beberapa indeks nonclustered.
Mengatur ulang indeks yang berbeda pada tabel yang sama.
Mengatur ulang indeks yang berbeda saat membangun kembali indeks yang tidak tumpang tindih pada tabel yang sama.

Semua operasi indeks online lainnya yang dilakukan pada saat yang sama gagal. Misalnya, Anda tidak dapat membangun kembali dua atau beberapa indeks pada tabel yang sama secara bersamaan, atau membuat indeks baru saat membangun kembali indeks yang ada pada tabel yang sama.

Untuk informasi selengkapnya, lihat Melakukan operasi indeks secara online.

Operasi indeks yang dapat diulang

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Pembangunan ulang indeks online ditentukan sebagai dapat diulang menggunakan RESUMABLE = ON opsi .

Opsi RESUMABLE tidak bertahan dalam metadata untuk indeks tertentu dan hanya berlaku untuk durasi pernyataan DDL saat ini. Oleh karena itu, RESUMABLE = ON klausul harus ditentukan secara eksplisit untuk memungkinkan kelanjutan.
MAX_DURATION opsi didukung untuk RESUMABLE = ON opsi atau low_priority_lock_wait opsi .
- MAX_DURATION untuk RESUMABLE opsi menentukan interval waktu untuk indeks yang sedang dibangun kembali. Setelah waktu ini digunakan, pembangunan kembali indeks dijeda atau menyelesaikan eksekusinya. Pengguna memutuskan kapan pembangunan ulang untuk indeks yang dijeda dapat dilanjutkan. Waktu (dalam menit) harus MAX_DURATION lebih besar dari 0 menit dan kurang atau sama dengan satu minggu (7 * 24 * 60 = 10080 menit). Memiliki jeda panjang untuk operasi indeks dapat memengaruhi performa DML pada tabel tertentu, serta kapasitas disk database, karena kedua indeks (yang asli dan yang baru dibuat) memerlukan ruang disk, dan perlu diperbarui selama operasi DML. Jika MAX_DURATION opsi dihilangkan, operasi indeks berlanjut hingga selesai atau sampai kegagalan terjadi.
- low_priority_lock_wait Opsi argumen memungkinkan Anda memutuskan bagaimana operasi indeks dapat dilanjutkan saat diblokir pada kunci Sch-M.
Menjalankan kembali pernyataan asli ALTER INDEX REBUILD dengan parameter yang sama melanjutkan operasi pembangunan ulang indeks yang dijeda. Anda juga dapat melanjutkan operasi pembangunan ulang indeks yang dijeda dengan menjalankan ALTER INDEX RESUME pernyataan.
Opsi SORT_IN_TEMPDB = ON tidak didukung untuk indeks yang dapat diulang
Perintah DDL dengan RESUMABLE = ON tidak dapat dijalankan di dalam transaksi eksplisit (tidak dapat menjadi bagian BEGIN TRAN ... COMMIT dari blok).
Hanya operasi indeks yang dijeda yang dapat dilanjutkan.
Saat memulai kembali operasi indeks yang dijeda, Anda dapat mengubah nilai menjadi MAXDOP nilai baru. Jika MAXDOP tidak ditentukan saat melanjutkan operasi indeks yang dijeda, nilai terakhir MAXDOP diambil. MAXDOP JIKA opsi tidak ditentukan sama sekali untuk operasi pembangunan ulang indeks, maka nilai default diambil.
Untuk segera menjeda operasi indeks, Anda dapat menghentikan perintah yang sedang berlangsung (Ctrl-C) atau Anda dapat menjalankan ALTER INDEX PAUSE perintah atau KILL <session_id> perintah . Setelah perintah dijeda, perintah dapat dilanjutkan menggunakan RESUME opsi.
Perintah ABORT mematikan sesi yang menghosting pembangunan ulang indeks asli dan membatalkan operasi indeks
Tidak ada sumber daya tambahan yang diperlukan untuk membangun kembali indeks yang dapat dilanjutkan kecuali untuk
- Ruang ekstra yang diperlukan untuk menjaga indeks tetap dibangun, termasuk waktu ketika indeks sedang dijeda
- Status DDL mencegah modifikasi DDL
Pembersihan hantu berjalan selama fase jeda indeks, tetapi dijeda selama eksekusi indeks. Fungsionalitas berikut dinonaktifkan untuk operasi pembangunan ulang indeks yang dapat dilanjutkan
- Membangun ulang indeks yang dinonaktifkan tidak didukung dengan RESUMABLE = ON
- perintah ALTER INDEX REBUILD ALL
- ALTER TABLE menggunakan pembangunan ulang indeks
- Perintah DDL dengan RESUMABLE = ON tidak dapat dijalankan di dalam transaksi eksplisit (tidak dapat menjadi bagian BEGIN TRAN ... COMMIT dari blok)
- Membangun kembali indeks yang memiliki komputasi atau TIMESTAMP kolom sebagai kolom kunci.
Jika tabel dasar berisi kolom LOB yang dapat dilanjutkan pembangunan ulang indeks berkluster memerlukan kunci Sch-M di Awal operasi ini

Catatan

Perintah DDL berjalan hingga selesai, dijeda, atau gagal. Jika perintah dijeda, kesalahan akan dikeluarkan yang menunjukkan bahwa operasi dijeda dan pembuatan indeks tidak selesai. Informasi selengkapnya tentang status indeks saat ini dapat diperoleh dari sys.index_resumable_operations. Seperti sebelumnya jika terjadi kegagalan, kesalahan juga akan dikeluarkan.

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

low_priority_lock_wait Sintaks memungkinkan untuk menentukan WAIT_AT_LOW_PRIORITY perilaku. WAIT_AT_LOW_PRIORITY hanya dapat digunakan ONLINE = ON .

Untuk menjalankan pernyataan DDL untuk pembangunan ulang indeks online, semua transaksi pemblokiran aktif yang berjalan pada tabel tertentu harus diselesaikan. Ketika pembangunan ulang indeks online dijalankan, ia memblokir semua transaksi baru yang siap untuk memulai eksekusi pada tabel ini. Meskipun durasi kunci untuk pembangunan ulang indeks online singkat, menunggu semua transaksi terbuka pada tabel tertentu untuk menyelesaikan dan memblokir transaksi baru untuk memulai, mungkin secara signifikan memengaruhi throughput, menyebabkan beban kerja melambat atau waktu habis, dan secara signifikan membatasi akses ke tabel yang mendasar.

Opsi ini WAIT_AT_LOW_PRIORITY memungkinkan DBA untuk mengelola kunci Skema Stabilitas (Sch-S) dan kunci Skema Modifikasi (Sch-M) yang diperlukan untuk pembangunan ulang indeks online dan memungkinkannya memilih salah satu dari dua opsi. Dalam kedua kasus, jika selama waktu MAX_DURATION = n [minutes]tunggu , tidak ada aktivitas pemblokiran, pembangunan ulang indeks online segera dijalankan tanpa menunggu dan pernyataan DDL selesai.

WAIT_AT_LOW_PRIORITY menunjukkan bahwa operasi pembangunan ulang indeks online menunggu kunci prioritas rendah, memungkinkan operasi lain untuk melanjutkan saat operasi build indeks online menunggu. WAIT AT LOW PRIORITY Menghilangkan opsi setara dengan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = waktu [ MENIT ]

Waktu tunggu (nilai bilangan bulat yang ditentukan dalam menit) yang kunci pembangunan ulang indeks online menunggu dengan prioritas rendah saat menjalankan perintah DDL. Jika operasi diblokir untuk waktu tersebut MAX_DURATION , tindakan yang ditentukan ABORT_AFTER_WAIT akan dijalankan. MAX_DURATION waktu selalu dalam hitungan menit, dan kata MINUTES dapat dihilangkan.

ABORT_AFTER_WAIT = [ NONE | SELF | PEMBLOKIR ]

NONE

Lanjutkan menunggu kunci dengan prioritas normal (reguler).
DIRI

Keluar dari operasi DDL pembangunan ulang indeks online yang saat ini sedang dijalankan tanpa mengambil tindakan apa pun. Opsi SELF tidak dapat digunakan dengan MAX_DURATION 0.
BLOCKER

Matikan semua transaksi pengguna yang memblokir operasi DDL pembangunan ulang indeks online sehingga operasi dapat dilanjutkan. Opsi BLOCKERS ini mengharuskan login memiliki ALTER ANY CONNECTION izin.

Pembatasan indeks spasial

Saat Anda membangun kembali indeks spasial, tabel pengguna yang mendasar tidak tersedia selama operasi indeks karena indeks spasial memegang kunci skema.

Batasan PRIMARY KEY dalam tabel pengguna tidak dapat dimodifikasi saat indeks spasial ditentukan pada kolom tabel tersebut. Untuk mengubah batasan PRIMARY KEY , jatuhkan terlebih dahulu setiap indeks spasial tabel. Setelah memodifikasi batasan PRIMARY KEY , Anda dapat membuat ulang setiap indeks spasial.

Dalam operasi pembangunan ulang partisi tunggal, Anda tidak dapat menentukan indeks spasial apa pun. Namun, Anda dapat menentukan indeks spasial dalam pembangunan ulang partisi lengkap.

Untuk mengubah opsi yang khusus untuk indeks spasial, seperti BOUNDING_BOX atau GRID, Anda dapat menggunakan CREATE SPATIAL INDEX pernyataan yang menentukan , atau menghilangkan indeks spasial DROP_EXISTING = ONdan membuat yang baru. Misalnya, lihat MEMBUAT INDEKS SPASIAL.

Pemadatan data

Untuk informasi selengkapnya tentang kompresi data, lihat Pemadatan data.

Untuk mengevaluasi bagaimana perubahan PAGE dan ROW pemadatan memengaruhi tabel, indeks, atau partisi, gunakan prosedur tersimpan sp_estimate_data_compression_savings .

Pembatasan berikut berlaku untuk indeks yang dipartisi:

Saat Anda menggunakan ALTER INDEX ALL ..., Anda tidak dapat mengubah pengaturan pemadatan satu partisi jika tabel memiliki indeks yang tidak ditandatangani.
Sintaks ALTER INDEX <index> ... REBUILD PARTITION ... membangun kembali partisi indeks yang ditentukan.
ALTER INDEX <index> ... REBUILD WITH ... Sintaks membangun kembali semua partisi indeks.

Statistik

Saat Anda menjalankan ALTER INDEX ALL ... pada tabel, hanya statistik yang terkait dengan indeks yang diperbarui. Statistik otomatis atau manual yang dibuat pada tabel (bukan indeks) tidak diperbarui.

Izin

Untuk menjalankan ALTER INDEX, minimal, ALTER izin pada tabel atau tampilan diperlukan.

Catatan versi

SQL Database tidak menggunakan opsi grup file dan aliran file.
Indeks penyimpan kolom tidak tersedia sebelum SQL Server 2012 (11.x).
Operasi indeks yang dapat dilanjutkan tersedia mulai dari SQL Server 2017 (14.x) dan Azure SQL Database.

Contoh sintaks dasar

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Contoh: Indeks penyimpan kolom

Contoh-contoh ini berlaku untuk indeks penyimpan kolom.

J. Mengatur ulang demo

Contoh ini menunjukkan cara ALTER INDEX REORGANIZE kerja perintah. Ini membuat tabel yang memiliki beberapa grup baris, lalu menunjukkan cara REORGANIZE menggabungkan grup baris.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Gunakan opsi TABLOCK untuk menyisipkan baris secara paralel. Dimulai dengan SQL Server 2016 (13.x), INSERT INTO operasi dapat berjalan secara paralel saat TABLOCK digunakan.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Jalankan perintah ini untuk melihat OPEN grup baris delta. Jumlah grup baris tergantung pada tingkat paralelisme.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Jalankan perintah ini untuk memaksa semua CLOSED grup baris dan OPEN ke dalam penyimpan kolom.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Jalankan perintah ini lagi dan Anda melihat bahwa grup baris yang lebih kecil digabungkan menjadi satu grup baris terkompresi.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Memadatkan grup baris delta TERTUTUP ke dalam penyimpan kolom

Contoh ini menggunakan REORGANIZE opsi untuk memadatkan setiap CLOSED grup baris delta ke dalam penyimpan kolom sebagai grup baris terkompresi. Ini tidak diperlukan, tetapi berguna ketika tuple-mover tidak memadatkan CLOSED grup baris dengan cukup cepat.

Anda dapat menjalankan kedua contoh dalam AdventureWorksDW2022 database sampel.

Sampel ini berjalan REORGANIZE pada semua partisi.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Sampel ini berjalan REORGANIZE pada partisi tertentu.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Memadatkan semua grup baris delta TERBUKA DAN TERTUTUP ke dalam penyimpan kolom

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database

Perintah REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) memadatkan setiap OPEN dan CLOSED delta rowgroup ke dalam columnstore sebagai grup baris terkompresi. Ini mengoreksi deltastore dan memaksa semua baris untuk dikompresi ke dalam penyimpan kolom. Ini berguna terutama setelah melakukan banyak operasi sisipan karena operasi ini menyimpan baris dalam satu atau beberapa grup baris delta.

REORGANIZE menggabungkan grup baris untuk mengisi grup baris hingga jumlah baris <maksimum = 1.024.576. Oleh karena itu, ketika Anda memadatkan semua OPEN dan CLOSED grup baris, Anda tidak berakhir dengan banyak grup baris terkompresi yang hanya memiliki beberapa baris di dalamnya. Anda ingin grup baris sepenuh mungkin untuk mengurangi ukuran terkompresi dan meningkatkan performa kueri.

Contoh berikut menggunakan AdventureWorksDW2022 database.

Contoh ini memindahkan semua OPEN dan CLOSED delta grup baris ke dalam indeks penyimpan kolom.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Contoh ini memindahkan semua OPEN grup baris dan CLOSED delta ke dalam indeks penyimpan kolom untuk partisi tertentu.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Defragmentasi indeks penyimpan kolom secara online

Tidak berlaku untuk: SQL Server 2012 (11.x) dan SQL Server 2014 (12.x).

Dimulai dengan SQL Server 2016 (13.x), REORGANIZE melakukan lebih dari kompres grup baris delta ke dalam penyimpan kolom. Ini juga melakukan defragmentasi online. Pertama, ini mengurangi ukuran penyimpan kolom dengan menghapus baris yang dihapus secara fisik ketika 10% atau lebih baris dalam grup baris telah dihapus. Kemudian, ini menggabungkan grup baris bersama-sama untuk membentuk grup baris yang lebih besar yang memiliki hingga maksimum 1.024.576 baris per grup baris. Semua grup baris yang diubah dikompresi ulang.

Catatan

Dimulai dengan SQL Server 2016 (13.x), membangun kembali indeks penyimpan kolom tidak lagi diperlukan dalam sebagian besar situasi karena REORGANIZE secara fisik menghapus baris yang dihapus dan menggabungkan grup baris. Opsi memaksa COMPRESS_ALL_ROW_GROUPS semua OPEN atau CLOSED grup baris delta ke dalam penyimpan kolom yang sebelumnya hanya dapat dilakukan dengan pembangunan ulang. REORGANIZE online dan terjadi di latar belakang sehingga kueri dapat dilanjutkan saat operasi terjadi.

Contoh berikut melakukan REORGANIZE defragmentasi indeks dengan menghapus baris yang telah dihapus secara logis dari tabel, dan menggabungkan grup baris.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Membangun kembali indeks penyimpan kolom terkluster secara offline

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x))

Tip

Dimulai dengan SQL Server 2016 (13.x) dan di Azure SQL Database, sebaiknya gunakan ALTER INDEX REORGANIZE alih-alih ALTER INDEX REBUILD untuk indeks penyimpan kolom.

Catatan

Di SQL Server 2012 (11.x) dan SQL Server 2014 (12.x), REORGANIZE hanya digunakan untuk memadatkan CLOSED grup baris ke dalam penyimpan kolom. Satu-satunya cara untuk melakukan operasi defragmentasi dan untuk memaksa semua grup baris delta ke dalam penyimpan kolom adalah dengan membangun kembali indeks.

Contoh ini menunjukkan cara membangun kembali indeks penyimpan kolom berkluster dan memaksa semua grup baris delta ke dalam penyimpan kolom. Langkah pertama ini menyiapkan tabel FactInternetSales2 dalam AdventureWorksDW2022 database dengan indeks penyimpan kolom berkluster, dan menyisipkan data dari empat kolom pertama.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Hasilnya memperlihatkan satu OPEN grup baris, yang berarti SQL Server menunggu lebih banyak baris ditambahkan sebelum menutup grup baris dan memindahkan data ke penyimpan kolom. Pernyataan berikutnya ini membangun kembali indeks penyimpan kolom berkluster, yang memaksa semua baris ke dalam penyimpan kolom.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Hasil SELECT pernyataan menunjukkan grup baris adalah COMPRESSED, yang berarti segmen kolom grup baris sekarang dikompresi dan disimpan di penyimpan kolom.

F. Membangun kembali partisi indeks penyimpan kolom berkluster secara offline

Berlaku untuk: SQL Server 2012 (11.x) dan versi yang lebih baru

Untuk membangun kembali partisi indeks penyimpan kolom berkluster besar, gunakan ALTER INDEX REBUILD dengan opsi partisi. Contoh ini membangun kembali partisi 12. Dimulai dengan SQL Server 2016 (13.x), sebaiknya ganti REBUILD dengan REORGANIZE.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Mengubah indeks penyimpan kolom berkluster untuk menggunakan kompresi pengarsipan

Tidak berlaku untuk: SQL Server 2012 (11.x)

Anda dapat memilih untuk mengurangi ukuran indeks penyimpan kolom berkluster lebih jauh dengan menggunakan COLUMNSTORE_ARCHIVE opsi kompresi data. Ini praktis untuk data lama yang ingin Anda simpan di penyimpanan yang lebih murah. Sebaiknya hanya gunakan ini pada data yang tidak sering diakses karena dekompresi lebih lambat daripada kompresi normal COLUMNSTORE .

Contoh berikut membangun kembali indeks penyimpan kolom berkluster untuk menggunakan kompresi pengarsipan, lalu menunjukkan cara menghapus kompresi pengarsipan. Hasil akhir hanya menggunakan kompresi penyimpan kolom.

Pertama, siapkan contoh dengan membuat tabel dengan indeks penyimpan kolom berkluster. Kemudian, kompres tabel lebih lanjut dengan menggunakan kompresi arsip.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Sampel ini menghapus kompresi arsip, dan hanya menggunakan kompresi penyimpan kolom.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Contoh: Indeks rowstore

J. Menyusun ulang indeks

Contoh berikut membangun kembali satu indeks pada Employee tabel dalam AdventureWorks2022 database.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Membangun ulang semua indeks pada tabel dan menentukan opsi

Contoh berikut menentukan kata kunci ALL. Ini membangun kembali semua indeks yang terkait dengan tabel Production.Product dalam AdventureWorks2022 database. Tiga opsi ditentukan.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Contoh berikut menambahkan opsi ONLINE termasuk opsi kunci prioritas rendah, dan menambahkan opsi pemadatan baris.

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Mengatur ulang indeks dengan pemadatan LOB

Contoh berikut mengatur ulang satu indeks berkluster dalam AdventureWorks2022 database. Karena indeks berisi jenis data LOB di tingkat daun, pernyataan juga memampatkan semua halaman yang berisi data objek besar. Menentukan WITH (LOB_COMPACTION = ON) opsi tidak diperlukan karena nilai defaultnya adalah AKTIF.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Mengatur opsi pada indeks

Contoh berikut mengatur beberapa opsi pada indeks AK_SalesOrderHeader_SalesOrderNumber dalam AdventureWorks2022 database.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Menonaktifkan indeks

Contoh berikut menonaktifkan indeks nonclustered pada Employee tabel dalam AdventureWorks2022 database.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Menonaktifkan batasan

Contoh berikut menonaktifkan PRIMARY KEY batasan dengan menonaktifkan PRIMARY KEY indeks dalam AdventureWorks2022 database. Batasan FOREIGN KEY pada tabel yang mendasar secara otomatis dinonaktifkan dan pesan peringatan ditampilkan.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Tataan hasil mengembalikan pesan peringatan ini.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Aktifkan batasan

Contoh berikut mengaktifkan batasan PRIMARY KEY dan FOREIGN KEY yang dinonaktifkan di Contoh F.

Batasan PRIMARY KEY diaktifkan dengan membangun PRIMARY KEY kembali indeks.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Batasan FOREIGN KEY kemudian diaktifkan.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Membangun kembali indeks yang dipartisi

Contoh berikut membangun kembali partisi tunggal, nomor 5partisi , dari indeks IX_TransactionHistory_TransactionDate yang dipartisi dalam AdventureWorks2022 database. Partisi 5 dibangun kembali dengan ONLINE=ON dan waktu tunggu 10 menit untuk kunci prioritas rendah berlaku secara terpisah untuk setiap kunci yang diperoleh oleh operasi pembangunan ulang indeks. Jika selama waktu ini kunci tidak dapat diperoleh untuk menyelesaikan pembangunan ulang indeks, pernyataan operasi pembangunan kembali itu sendiri dibatalkan, karena ABORT_AFTER_WAIT = SELF.

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, dan Azure SQL Database

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. Mengubah pengaturan pemadatan indeks

Contoh berikut membangun ulang indeks pada tabel rowstore yang tidak dipartisi.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

j. Mengubah pengaturan indeks dengan pemadatan XML

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Contoh berikut membangun ulang indeks pada tabel rowstore yang tidak dipartisi.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Untuk contoh kompresi data lainnya, lihat Pemadatan data.

K. Pembangunan ulang indeks yang dapat dilanjutkan online

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database

Contoh berikut menunjukkan cara menggunakan pembangunan ulang indeks yang dapat dilanjutkan secara online.

Jalankan pembangunan ulang indeks online sebagai operasi yang dapat diulang dengan MAXDOP = 1. Menjalankan perintah yang sama lagi setelah operasi indeks dijeda, secara otomatis melanjutkan operasi pembangunan ulang indeks.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Jalankan pembangunan ulang indeks online sebagai operasi yang dapat diulang dengan MAX_DURATION diatur ke 240 menit.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Jeda pembangunan ulang indeks online yang dapat diulang yang sedang berjalan.

ALTER INDEX test_idx on test_table PAUSE;

Lanjutkan pembangunan ulang indeks online untuk pembangunan ulang indeks yang dijalankan sebagai operasi yang dapat dilanjutkan yang menentukan nilai baru untuk MAXDOP diatur ke 4.

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

Lanjutkan operasi pembangunan ulang indeks online untuk pembangunan ulang online indeks yang dijalankan sebagai dapat dilanjutkan. Atur MAXDOP ke 2, atur waktu eksekusi untuk indeks yang berjalan sebagai dapat dilanjutkan menjadi 240 menit, dan jika indeks diblokir pada kunci, tunggu 10 menit dan setelah itu matikan semua pemblokir.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

Batalkan operasi pembangunan ulang indeks yang dapat dilanjutkan yang berjalan atau dijeda.

ALTER INDEX test_idx on test_table ABORT;

Bagikan melalui

ALTER INDEX (Transact-SQL)

Sintaks

Argumen

index_name

SEMUA

database_name

schema_name

table_or_view_name

MEMBANGUN KEMBALI [ DENGAN ( <rebuild_index_option> [ ,... n ] ) ]

PARTISI

MENONAKTIFKAN

MENGATUR ULANG indeks rowstore

REORGANISASI DENGAN ( LOB_COMPACTION = { ON | OFF } )

MENGATUR ULANG indeks penyimpan kolom

MENGATUR ULANG DENGAN ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

SET ( <opsi> set_index [ ,... n ] )

PAD_INDEX = { ON | NONAKTIF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

IGNORE_DUP_KEY = { AKTIF | NONAKTIF }

STATISTICS_NORECOMPUTE = { AKTIF | NONAKTIF }

STATISTICS_INCREMENTAL = { AKTIF | NONAKTIF }

ONLINE = { AKTIF | OFF } <seperti yang berlaku untuk rebuild_index_option>

DAPAT DI-RESUMABLE = { ON | OFF}

MAX_DURATION = waktu [ MINUTES ] digunakan dengan RESUMABLE = ON (memerlukan ONLINE = ON)

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { AKTIF | NONAKTIF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | durasi [ menit ] }

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

ONLINE = { AKTIF | OFF } <seperti yang berlaku untuk single_partition_rebuild_index_option>

MELANJUTKAN

JEDA

GAGAL

Keterangan

Menyusun ulang indeks

Mengatur ulang indeks

Nonaktifkan indeks

Atur opsi

Opsi penguncian baris dan halaman

Operasi indeks online

Operasi indeks yang dapat diulang

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

MAX_DURATION = waktu [ MENIT ]

ABORT_AFTER_WAIT = [ NONE | SELF | PEMBLOKIR ]

Pembatasan indeks spasial

Pemadatan data

Statistik

Izin

Catatan versi

Contoh sintaks dasar

Contoh: Indeks penyimpan kolom

J. Mengatur ulang demo

B. Memadatkan grup baris delta TERTUTUP ke dalam penyimpan kolom

C. Memadatkan semua grup baris delta TERBUKA DAN TERTUTUP ke dalam penyimpan kolom

D. Defragmentasi indeks penyimpan kolom secara online

E. Membangun kembali indeks penyimpan kolom terkluster secara offline

F. Membangun kembali partisi indeks penyimpan kolom berkluster secara offline

G. Mengubah indeks penyimpan kolom berkluster untuk menggunakan kompresi pengarsipan

Contoh: Indeks rowstore

J. Menyusun ulang indeks

B. Membangun ulang semua indeks pada tabel dan menentukan opsi

C. Mengatur ulang indeks dengan pemadatan LOB

D. Mengatur opsi pada indeks

E. Menonaktifkan indeks

F. Menonaktifkan batasan

G. Aktifkan batasan

H. Membangun kembali indeks yang dipartisi

I. Mengubah pengaturan pemadatan indeks

j. Mengubah pengaturan indeks dengan pemadatan XML

K. Pembangunan ulang indeks yang dapat dilanjutkan online

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan:

MAX_DURATION = waktu [ MINUTES ] digunakan dengan `RESUMABLE = ON` (memerlukan `ONLINE = ON`)