Teman-teman, pada akhir Januari, kami akan memulai kursus baru yang disebut "Pengembang MS SQL Server ". Untuk mengantisipasi peluncurannya, kami meminta guru kursus, Kristina Kucherova , untuk menyiapkan artikel penulis. Artikel ini akan berguna bagi Anda jika Anda memiliki tabel yang sangat populer di prod dengan akses 24/7 dan tiba-tiba Anda menyadari bahwa Anda sangat perlu menambahkan indeks dan tidak merusak apa pun dalam proses.Jadi apa yang harus dilakukan? Metode CREATE INDEX WITH WITH (ONLINE = ON) tradisional tidak cocok untuk Anda, karena, misalnya, menyebabkan crash sistem dan serangan jantung DBA Anda, semua puncak memantau waktu respons sistem Anda dan, jika meningkat, mereka mendatangi Anda dan DBA Anda untuk berbicara tentang angka-angka kompensasi Anda untuk pekerjaan yang berlebihan.
Skrip dan teknik yang dijelaskan digunakan pada sistem dengan beban permintaan 400 ribu per menit, versi SQL Server 2012 dan 2016 (Enterprise).
Ada dua pendekatan yang sangat berbeda untuk membuat indeks, yang digunakan tergantung pada ukuran tabel.
Kasus No. 1. Meja kecil tapi sangat populer
Sebuah tabel berisi 50 ribu catatan (kecil), tetapi sangat populer (beberapa ribu hit per menit). Anda memerlukan indeks baru dan downtime minimal dan mengunci di atas meja.
Dalam aplikasi, semua akses ke database hanya melalui prosedur.
Jika kesalahan terjadi, aplikasi akan mencoba kembali mengakses tabel.
Apa masalah menerapkan indeks ini secara sederhana, Anda bertanya? Dengan kalimat WITH ONLINE = ON (ya, kami beruntung, dan yang ini Enterprise).
Faktanya adalah bahwa dengan akses aktif seperti itu, perlu beberapa waktu untuk mendapatkan kunci (bahkan yang minimum diperlukan dengan opsi Online = ON). Dalam proses menunggu, permintaan baru antri, antrian bertambah, CPU bertambah, DBA berkeringat dan menyipit dengan gugup ke arah pengembang, sementara pada grafik pemantauan aplikasi, waktu respons Anda mulai meningkat dengan lancar, tetapi tak terhindarkan. Wakil Presiden Engeneering Anda dengan senang hati tertarik pada apakah, karena peningkatan waktu respons ini, akan ada semacam downtime sistem, bahwa pada akhir tahun ketersediaan aplikasi akan diperkirakan bukan 5 sembilan (99.999), tetapi lebih rendah? Dan kemudian perusahaan memiliki kontrak, kewajiban, dan denda besar jika ketersediaan berkurang, dan, tentu saja, kita tidak akan melupakan kehilangan reputasi.
Apa yang telah kita lakukan untuk menghindari situasi yang tidak menguntungkan ini?
Sistem masih membutuhkan indeks.
Mereka mengambil hak dari semua orang kecuali sesi saat ini di tabel ini.
Terapkan indeks.
Ya, solusinya memiliki minus: setiap orang yang beralih ke tabel dalam detik ini akan menerima Access Denied. Jika aplikasi Anda biasanya menangani situasi seperti itu dan mengulangi permintaan ke database, maka Anda harus melihat lebih dekat opsi ini. Dalam kasus proyek kami, metode ini bekerja dengan baik. Sekali lagi, Anda dapat menghapus ONLINE = ON dengan aman, karena kami tahu bahwa hanya sesi yang akan memiliki akses ke tabel selama pembuatan indeks.
Kode untuk menerapkan indeks:
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1] REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2] CREATE NONCLUSTERED INDEX IX_Users_Email_Status ON [dbo].[Users] ([Email],[Status]); GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1] GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2]
Jadwal waktu tanggapan dan persentase kesalahan selama pengujian di bawah beban.
Metode ini dapat diterapkan jika, seperti dalam kasus yang dijelaskan, Anda memiliki tabel kecil, dan Anda tahu bahwa tanpa memuat indeks akan dibuat dalam hitungan detik (atau dalam waktu yang dapat diterima untuk Anda). Pada saat yang sama, seperti yang Anda lihat dari grafik di atas, waktu respons aplikasi tidak akan meningkat, meskipun dapat dilihat bahwa tingkat kesalahan dalam hitungan detik tanpa akses ke tabel lebih tinggi.
Kasus No. 2. Meja besar
Jika Anda memiliki tabel besar dan Anda perlu mengubah indeks di atasnya, maka seringkali cara paling mudah untuk menjual adalah dengan membuat tabel di sebelahnya dengan indeks yang benar dan secara bertahap mentransfer data ke tabel baru.
Ada 2 cara:
- Jika Anda memiliki prosedur khusus untuk mengubah tabel, Anda cukup mengubah kode prosedur sehingga data baru dimasukkan hanya ke dalam tabel baru, penghapusan dari keduanya, pembaruan juga berlaku untuk keduanya, dan pemilihan dilakukan dari dua tabel dengan UNION ALL.
- Jika Anda memiliki banyak bagian kode yang berbeda di mana Anda dapat mengubah data dalam tabel, maka ada dua trik populer: lihat dengan pemicu atau tulis ulang semua bagian kode untuk memasukkan data ke dalam tabel baru, hapus dari keduanya dan perbarui kedua tabel. Tampilan dengan pemicu adalah opsi saat Anda membuat tampilan dengan dua tabel dan menamainya, mengubah nama tabel Anda saat ini menjadi TableOld, dan melihat ke Tabel. Maka Anda secara otomatis mendapatkan semua panggilan tabel ke tampilan, di sini dengan mengganti nama mungkin juga ada masalah, karena SchemaLock diperlukan, tetapi mengganti nama lewat sangat cepat.
Versi yang sedikit lebih mendetail tentang penulisan ulang panggilan ke tabel baru:
- Anda memiliki tabel Pesanan, buat tabel OrdersNew baru dengan skema yang sama, tetapi dengan indeks yang diinginkan. Pada saat yang sama, jika Anda menggunakan Indentity, maka Anda perlu mengatur nilai identitas pertama di tabel baru agar sama dengan nilai maksimum di tabel lama + langkah perubahan atau celah yang Anda mampu untuk menyimpang dari nilai maksimum dalam Pesanan.
- Buat OrdersView, di dalamnya ada pilihan dari UNI Pemesanan SEMUA Pesanan Baru
- Ubah semua prosedur / panggilan untuk memilih data dari tampilan, masukkan ke OrdersNew, hapus dan modifikasi kedua tabel.
- Migrasikan data dari tabel lama ke yang baru, misalnya, seperti ini:
DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF;
- Kembalikan semua prosedur ke versi sebelum migrasi - dengan satu tabel. Ini dapat dilakukan melalui alter atau melalui penghapusan dan pembuatan prosedur (maka jangan lupa tentang haknya), dan Anda dapat mengubah nama tabel baru menjadi Pesanan, menghapus tabel dan tampilan kosong.
Pada langkah 2, dimungkinkan, jika pemuatan memungkinkan, untuk mengubah nama Pesanan tabel utama -> OrdersOld, dan OrdersView -> Pesanan dan tampilan itu sendiri ke OrdersOld UNION ALL Orders Baru, maka Anda tidak perlu mengubah semua tempat di mana ada pilihan dari tabel.
Saat memindahkan blok dari satu tabel ke tabel lainnya, data akan terfragmentasi.
Jika tabel yang diubah secara aktif digunakan untuk membaca, tetapi data di dalamnya jarang berubah, Anda dapat kembali menggunakan pemicu - tulis salinan semua perubahan ke tabel ke-3 - transfer data dari tabel melalui bcp out dan bcp in (atau bulk insert) ke tabel baru , buat indeks di atasnya setelah transfer data dan kemudian terapkan perubahan dari tabel dengan log perubahan - dan alihkan satu tabel ke yang lain - yang sekarang, ganti nama ke TableOld, dan yang baru dari TableNew to Table.
Probabilitas kesalahan dalam situasi ini sedikit lebih tinggi, jadi uji penerapan perubahan dan berbagai kasus switching dalam kasus ini.
Opsi yang dijelaskan bukan satu-satunya. Mereka digunakan oleh saya pada database SQL Server yang banyak dimuat dan tidak menimbulkan masalah selama aplikasi, yang menyenangkan tim DBA kami. Memantul seperti itu biasanya tidak diperlukan untuk pangkalan dengan mode pemuatan yang lebih tenang, saat Anda dapat dengan aman menerapkan perubahan pada jam-jam dengan aktivitas yang paling sedikit. Pengguna proyek yang menggunakan pendekatan yang dijelaskan ini berlokasi di AS dan Eropa dan secara aktif menggunakan aplikasi pada hari kerja dan akhir pekan, dan tabel di mana perubahan diterapkan digunakan secara konstan dalam pekerjaan. Lebih banyak objek "lebih tenang" biasanya dimodifikasi oleh skrip otomatis yang dihasilkan melalui Redgate Toolkit setelah skrip ditinjau oleh pengembang dan salah satu dari DBA.
Baik untuk semua! Bagikan komentar jika Anda menggunakan metode ini atau jelaskan metode Anda! Kami juga mengundang Anda ke pelajaran terbuka dan hari terbuka kursus baru kami "Pengembang MS SQL Server"