Tambahkan batasan tabel yang dapat dilanjutkan
Berlaku untuk: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance
Operasi yang dapat dilanjutkan untuk pembuatan dan pembangunan ulang indeks online sudah didukung untuk SQL Server 2019, Azure SQL Database, dan Azure SQL Managed Instance. Operasi yang dapat diulang memungkinkan operasi indeks dijalankan saat tabel online (ONLINE=ON
) dan juga:
Menjeda dan memulai ulang operasi pembuatan atau pembangunan ulang indeks beberapa kali agar sesuai dengan jendela pemeliharaan
Memulihkan dari pembuatan indeks atau membangun kembali kegagalan, seperti failover database atau kehabisan ruang disk.
Aktifkan pemotongan log transaksi selama pembuatan indeks atau operasi pembangunan ulang.
Ketika operasi indeks dijeda, indeks asli dan yang baru dibuat memerlukan ruang disk dan perlu diperbarui selama operasi Bahasa Manipulasi Data (DML).
Ekstensi baru untuk SQL Server 2022, SQL Database, dan SQL Managed Instance memungkinkan operasi yang dapat dilanjutkan untuk perintah Bahasa Definisi Data (DDL) ALTER TABLE ADD CONSTRAINT dan menambahkan Kunci Primer atau Unik. Untuk informasi selengkapnya tentang menambahkan Kunci Primer atau Unik, lihat MENGUBAH TABLE_CONSTRAINT TABEL.
Catatan
Batasan tambahkan tabel yang dapat dilanjutkan hanya berlaku untuk batasan KUNCI PRIMER dan KUNCI UNIK. Batasan tambahkan tabel yang dapat dilanjutkan tidak didukung untuk batasan KUNCI ASING.
Operasi yang dapat diulang
Di versi SQL Server sebelumnya, ALTER TABLE ADD CONSTRAINT
operasi dapat dijalankan dengan ONLINE=ON
opsi . Namun, operasi mungkin memakan waktu berjam-jam agar tabel besar selesai, dan dapat menggunakan sejumlah besar sumber daya. Ada juga kemungkinan kegagalan atau gangguan selama eksekusi tersebut. Kami telah memperkenalkan kemampuan yang dapat dilanjutkan kepada ALTER TABLE ADD CONSTRAINT
pengguna untuk menjeda operasi selama jendela pemeliharaan, atau untuk memulai ulang dari tempatnya terganggu selama kegagalan eksekusi, tanpa memulai ulang operasi dari awal.
Skenario yang didukung
Kemampuan baru yang dapat dilanjutkan untuk ALTER TABLE ADD CONSTRAINT
mendukung skenario pelanggan berikut:
Jeda atau lanjutkan operasi yang berjalan
ALTER TABLE ADD CONSTRAINT
, seperti menjedanya untuk jendela pemeliharaan, dan melanjutkan operasi setelah jendela pemeliharaan selesai.Lanjutkan
ALTER TABLE ADD CONSTRAINT
operasi setelah kegagalan dan kegagalan sistem.ALTER TABLE ADD CONSTRAINT
Menjalankan operasi pada tabel besar meskipun ukuran log kecil tersedia.
Catatan
Operasi yang dapat diulang untuk ALTER TABLE ADD CONSTRAINT
mengharuskan ALTER
perintah dijalankan secara online (WITH ONLINE = ON
).
Fitur ini sangat berguna untuk tabel besar.
Sintaks T-SQL untuk ALTER TABLE
Untuk informasi tentang sintaks yang digunakan untuk mengaktifkan operasi yang dapat dilanjutkan pada batasan tabel, lihat sintaks dan opsi dalam ALTER TABLE (Transact-SQL).
Keterangan untuk ALTER TABLE
Klausa baru WITH <resumable_options telah ditambahkan ke sintaks T-SQL saat ini dalam ALTER TABLE (Transact-SQL).
Opsi RESUMABLE baru dan telah ditambahkan ke sintaks ALTER TABLE (Transact-SQL) yang ada.
MAX_DURATION
= waktu [MINUTES] digunakan denganRESUMABLE = ON
(memerlukanONLINE = ON
).MAX_DURATION
menunjukkan waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi batasan penambahan online yang dapat dilanjutkan dijalankan sebelum dijeda. Jika tidak ditentukan, operasi berlanjut hingga selesai.
Sintaks T-SQL untuk ALTER INDEX
Untuk menjeda, melanjutkan, atau membatalkan operasi batasan tabel yang dapat dilanjutkan untuk ALTER TABLE ADD CONSTRAINT
, gunakan sintaks T-SQL ALTER INDEX (Transact-SQL).
Untuk batasan yang dapat dilanjutkan, perintah ALTER INDEX ALL yang ada digunakan.
ALTER INDEX ALL ON <table_name>
{ RESUME [WITH (<resumable_index_options>,[...n])]
| PAUSE
| ABORT
}
<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 } )
}
Keterangan untuk ALTER INDEX
ALTER INDEX ALL ON <Table> PAUSE
- Menjeda operasi batasan tambahkan tabel yang dapat dilanjutkan dan online yang sedang dijalankan
ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]
- Lanjutkan operasi tambahkan batasan tabel yang dijeda secara manual atau karena kegagalan.
MAX_DURATION
digunakan dengan RESUMABLE=ON
- Waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi batasan tambahkan tabel 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 operasi batasan tambahkan tabel secara online setelah jeda harus menunggu operasi pemblokiran pada tabel ini.
WAIT_AT_LOW_PRIORITY
menunjukkan bahwa operasi tambahkan batasan tabel akan menunggu kunci prioritas rendah, memungkinkan operasi lain untuk melanjutkan saat operasi yang dapat dilanjutkan sedang menunggu.WAIT_AT_LOW_PRIORITY
Menghilangkan opsi setara denganWAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)
. Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY.
ALTER INDEX ALL ON <Table> ABORT
- Batalkan operasi tambahkan batasan tabel yang sedang berjalan atau dijeda yang dinyatakan sebagai dapat dilanjutkan. Operasi pembatalan harus dijalankan secara eksplisit sebagai
ABORT
perintah untuk mengakhiri operasi batasan yang dapat dilanjutkan. Kegagalan atau jeda operasi batasan tabel yang dapat dilanjutkan tidak mengakhiri eksekusinya. Sebaliknya, ia meninggalkan operasi dalam keadaan jeda yang tidak terbatas.
Untuk informasi selengkapnya tentang PAUSE
opsi , RESUME
, dan ABORT
yang tersedia untuk operasi yang dapat diulang, lihat ALTER INDEX (Transact-SQL).
Menampilkan status untuk operasi yang dapat diulang
Untuk melihat status untuk operasi batasan tabel yang dapat dilanjutkan, gunakan tampilan sys.index_resumable_operations.
Izin
ALTER
Memerlukan izin pada tabel.
Tidak diperlukan izin baru untuk dapat ALTER TABLE ADD CONSTRAINT
diulang.
Contoh
Berikut adalah beberapa contoh tentang menggunakan operasi batasan tambahkan tabel yang dapat dilanjutkan.
Contoh 1
Operasi yang dapat dilanjutkan ALTER TABLE
untuk menambahkan kunci utama yang diklusterkan pada kolom (a) dengan MAX_DURATION
240 menit.
ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
Contoh 2
Operasi yang dapat dilanjutkan ALTER TABLE
untuk menambahkan batasan unik pada dua kolom (a dan b) dengan MAX_DURATION
240 menit.
ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
Contoh 3
ALTER TABLE
operasi untuk menambahkan kunci utama yang diklusterkan sedang dijeda dan dilanjutkan.
Tabel di bawah ini menunjukkan dua sesi (Session #1
dan Session #2
) yang dijalankan secara kronologis menggunakan pernyataan T-SQL berikut. Session #1
menjalankan operasi yang dapat diulang ALTER TABLE ADD CONSTRAINT
membuat kunci utama pada kolom Col1
. Session #2
memeriksa status eksekusi untuk batasan yang sedang berjalan. Setelah beberapa waktu, operasi tersebut menjeda operasi yang dapat digunakan kembali. Session #2
memeriksa status untuk batasan yang dijeda. Terakhir, Session #1
lanjutkan batasan yang dijeda dan Session #2
periksa status lagi.
Sesi #1 | Sesi #2 | ||||||
---|---|---|---|---|---|---|---|
Jalankan tambahkan batasan yang dapat dilanjutkan ALTER TABLE TestConstraint ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1) WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30); |
|||||||
Periksa status batasan SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output memperlihatkan operasi
|
|||||||
Menjeda batasan yang dapat dilanjutkan ALTER INDEX ALL ON TestConstraint PAUSE; |
|||||||
Kesalahan Msg 1219, Level 16, State 1, Line 6 Your session has been disconnected because of a high priority DDL operation. Msg 1750, Level 16, State 1, Line 6 Could not create constraint or index. See previous errors. Msg 0, Level 20, State 0, Line 5 A severe error occurred on the current command. The results, if any, should be discarded. |
|||||||
Periksa status batasan SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output memperlihatkan operasi
|
|||||||
ALTER INDEX ALL ON TestConstraint RESUME; |
|||||||
Periksa status batasan SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output memperlihatkan operasi
|
Setelah operasi selesai, jalankan pernyataan T-SQL berikut untuk memeriksa batasan:
SELECT constraint_name, table_name, constraint_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO
Berikut set hasilnya:
constraint_name | table_name | constraint_type |
---|---|---|
PK_Constraint | TestConstraint | KUNCI PRIMER |