Tambahkan batasan tabel yang dapat dilanjutkan

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL DatabaseAzure 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 dengan RESUMABLE = ON (memerlukan ONLINE = 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 dengan WAIT_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 PAUSEopsi , 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

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43.552
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

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)BERHENTI65.339
ALTER INDEX ALL ON TestConstraint RESUME;
Periksa status batasan

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Output memperlihatkan operasi

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING90.238

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

Lihat juga