Panduan validasi dan pengoptimalan pascamigrasi
Berlaku untuk: SQL Server
Langkah pascamigrasi SQL Server sangat penting untuk merekonsiliasi akurasi dan kelengkapan data apa pun, dan mengungkap masalah performa dengan beban kerja.
Skenario kinerja umum
Berikut adalah beberapa skenario performa umum yang ditemui setelah bermigrasi ke Platform SQL Server dan cara mengatasinya. Ini termasuk skenario yang khusus untuk migrasi SQL Server ke SQL Server (versi lama ke versi yang lebih baru), dan platform asing (seperti Oracle, DB2, MySQL, dan Sybase) ke migrasi SQL Server.
Regresi kueri karena perubahan versi estimator kardinalitas (CE)
Berlaku untuk: Migrasi SQL Server ke SQL Server.
Saat bermigrasi dari versi SQL Server yang lebih lama ke SQL Server 2014 (12.x) atau versi yang lebih baru, dan meningkatkan tingkat kompatibilitas database ke yang terbaru tersedia, beban kerja mungkin terpapar risiko regresi performa.
Ini karena dimulai dengan SQL Server 2014 (12.x), semua perubahan Pengoptimal Kueri terkait dengan tingkat kompatibilitas database terbaru, sehingga paket tidak diubah tepat pada titik peningkatan melainkan ketika pengguna mengubah COMPATIBILITY_LEVEL
opsi database ke yang terbaru. Kemampuan ini, dalam kombinasi dengan Penyimpanan Kueri memberi Anda tingkat kontrol yang besar atas performa kueri dalam proses peningkatan.
Untuk informasi selengkapnya tentang perubahan Pengoptimal Kueri yang diperkenalkan di SQL Server 2014 (12.x), lihat Mengoptimalkan Rencana Kueri Anda dengan Estimator Kardinalitas SQL Server 2014.
Untuk informasi selengkapnya tentang CE, lihat Estimasi Kardinalitas (SQL Server).
Langkah-langkah untuk mengatasi masalah
Ubah tingkat kompatibilitas database ke versi sumber, dan ikuti alur kerja pemutakhiran yang direkomendasikan seperti yang diperlihatkan dalam gambar berikut:
Untuk informasi selengkapnya tentang artikel ini, lihat Menjaga stabilitas performa selama peningkatan ke SQL Server yang lebih baru.
Sensitivitas terhadap sniffing parameter
Berlaku untuk: Platform asing (seperti Oracle, DB2, MySQL, dan Sybase) ke migrasi SQL Server.
Catatan
Untuk migrasi SQL Server ke SQL Server, jika masalah ini ada di sumber SQL Server, migrasi ke versi SQL Server yang lebih baru apa adanya tidak akan mengatasi skenario ini.
SQL Server mengompilasi rencana kueri pada prosedur tersimpan dengan menggunakan sniffing parameter input pada kompilasi pertama, menghasilkan rencana parameter dan dapat digunakan kembali, dioptimalkan untuk distribusi data input tersebut. Bahkan jika tidak disimpan prosedur, sebagian besar pernyataan yang menghasilkan rencana sepele diparameterkan. Setelah rencana pertama kali di-cache, setiap eksekusi di masa mendatang memetakan ke rencana yang di-cache sebelumnya.
Potensi masalah muncul ketika kompilasi pertama tersebut tidak menggunakan set parameter yang paling umum untuk beban kerja biasa. Untuk parameter yang berbeda, rencana eksekusi yang sama menjadi tidak efisien. Untuk informasi selengkapnya tentang artikel ini, lihat Sensitivitas parameter.
Langkah-langkah untuk mengatasi masalah
RECOMPILE
Gunakan petunjuk. Paket dihitung setiap kali disesuaikan dengan setiap nilai parameter.- Tulis ulang prosedur tersimpan untuk menggunakan opsi
(OPTIMIZE FOR(<input parameter> = <value>))
. Tentukan nilai mana yang digunakan yang sesuai dengan sebagian besar beban kerja yang relevan, membuat dan memelihara satu paket yang menjadi efisien untuk nilai berparameter. - Tulis ulang prosedur yang disimpan menggunakan variabel lokal di dalam prosedur. Sekarang pengoptimal menggunakan vektor kepadatan untuk estimasi, menghasilkan rencana yang sama terlepas dari nilai parameter.
- Tulis ulang prosedur tersimpan untuk menggunakan opsi
(OPTIMIZE FOR UNKNOWN)
. Efek yang sama seperti menggunakan teknik variabel lokal. - Tulis ulang kueri untuk menggunakan petunjuk
DISABLE_PARAMETER_SNIFFING
. Efek yang sama seperti menggunakan teknik variabel lokal dengan menonaktifkan sniffing parameter sepenuhnya, kecualiOPTION(RECOMPILE)
,WITH RECOMPILE
atauOPTIMIZE FOR <value>
digunakan.
Tip
Gunakan fitur Analisis Rencana Studio Manajemen untuk mengidentifikasi apakah ini masalah dengan cepat. Untuk informasi selengkapnya, lihat Baru di SSMS: Pemecahan Masalah Performa Kueri menjadi lebih mudah!.
Indeks yang tidak ada
Berlaku untuk: Platform asing (seperti Oracle, DB2, MySQL, dan Sybase) dan SQL Server ke migrasi SQL Server.
Indeks yang salah atau hilang menyebabkan I/O tambahan yang menyebabkan memori tambahan dan CPU terbuang-. Ini mungkin karena profil beban kerja telah berubah, seperti menggunakan predikat yang berbeda, membatalkan desain indeks yang ada. Bukti strategi pengindeksan yang buruk atau perubahan profil beban kerja meliputi:
- Cari indeks duplikat, redundan, jarang digunakan, dan benar-benar tidak digunakan.
- Perawatan khusus dengan indeks yang tidak digunakan dengan pembaruan.
Langkah-langkah untuk mengatasi masalah
- Gunakan rencana eksekusi grafis untuk referensi Indeks yang Hilang.
- Saran pengindeksan yang dihasilkan oleh Konsultan Penyetelan Mesin Database.
- Gunakan sys.dm_db_missing_index_details atau melalui Dasbor Performa SQL Server.
- Gunakan skrip yang sudah ada sebelumnya yang dapat menggunakan DMV yang ada untuk memberikan wawasan tentang indeks yang hilang, duplikat, redundan, jarang digunakan dan sepenuhnya tidak digunakan, tetapi juga jika ada referensi indeks yang diisyaratkan/dikodekan secara permanen ke dalam prosedur dan fungsi yang ada dalam database Anda.
Ketidakmampuan untuk menggunakan predikat untuk memfilter data
Berlaku untuk: Platform asing (seperti Oracle, DB2, MySQL, dan Sybase) dan SQL Server ke migrasi SQL Server.
Catatan
Untuk migrasi SQL Server ke SQL Server, jika masalah ini ada di sumber SQL Server, migrasi ke versi SQL Server yang lebih baru apa adanya tidak akan mengatasi skenario ini.
Pengoptimal Kueri SQL Server hanya bisa memperhitungkan informasi yang diketahui pada waktu kompilasi. Jika beban kerja bergantung pada predikat yang hanya dapat diketahui pada waktu eksekusi, maka potensi pilihan rencana yang buruk meningkat. Untuk rencana yang berkualitas lebih baik, predikat harus SARGable, atau Search Argumentmampu.
Beberapa contoh predikat non-SARGable:
- Konversi data implisit, seperti varchar ke nvarchar, atau int ke varchar. Cari peringatan runtime
CONVERT_IMPLICIT
dalam Rencana Eksekusi Aktual. Mengonversi dari satu jenis ke jenis lain juga dapat menyebabkan hilangnya presisi. - Ekspresi kompleks yang tidak ditentukan seperti
WHERE UnitPrice + 1 < 3.975
, tetapi tidakWHERE UnitPrice < 320 * 200 * 32
. - Ekspresi menggunakan fungsi, seperti
WHERE ABS(ProductID) = 771
atauWHERE UPPER(LastName) = 'Smith'
- String dengan karakter wildcard terkemuka, seperti
WHERE LastName LIKE '%Smith'
, tetapi tidakWHERE LastName LIKE 'Smith%'
.
Langkah-langkah untuk mengatasi masalah
Selalu deklarasikan variabel/parameter sebagai jenis Data target yang dimaksudkan.
Ini mungkin melibatkan perbandingan konstruksi kode yang ditentukan pengguna yang disimpan dalam database (seperti prosedur tersimpan, fungsi yang ditentukan pengguna, atau tampilan) dengan tabel sistem yang menyimpan informasi tentang jenis data yang digunakan dalam tabel yang mendasar (seperti sys.columns (Transact-SQL)).
Jika tidak dapat melintasi semua kode ke titik sebelumnya, maka untuk tujuan yang sama, ubah jenis data pada tabel agar sesuai dengan deklarasi variabel/parameter apa pun.
Uraikan kegunaan konstruksi berikut:
- Fungsi yang digunakan sebagai predikat;
- Pencarian kartubebas;
- Ekspresi kompleks berdasarkan data kolumnar - mengevaluasi kebutuhan untuk membuat kolom komputasi yang dipertahankan, yang dapat diindeks;
Catatan
Semua langkah ini dapat dilakukan secara terprogram.
Penggunaan fungsi bernilai tabel (multi-pernyataan vs sebaris)
Berlaku untuk: Platform asing (seperti Oracle, DB2, MySQL, dan Sybase) dan SQL Server ke migrasi SQL Server.
Catatan
Untuk migrasi SQL Server ke SQL Server, jika masalah ini ada di sumber SQL Server, migrasi ke versi SQL Server yang lebih baru apa adanya tidak akan mengatasi skenario ini.
Fungsi Bernilai Tabel mengembalikan tipe data tabel yang bisa menjadi alternatif untuk tampilan. Meskipun tampilan terbatas pada satu SELECT
pernyataan, fungsi yang ditentukan pengguna dapat berisi pernyataan tambahan yang memungkinkan lebih banyak logika daripada yang dimungkinkan dalam tampilan.
Penting
Karena tabel output fungsi bernilai tabel multi-pernyataan (MSTVF) tidak dibuat pada waktu kompilasi, Pengoptimal Kueri SQL Server bergantung pada heuristik, dan bukan statistik aktual, untuk menentukan estimasi baris. Bahkan jika indeks ditambahkan ke tabel dasar, ini tidak akan membantu. Untuk MSTVF, SQL Server menggunakan estimasi tetap 1 untuk jumlah baris yang diharapkan dikembalikan oleh MSTVF (dimulai dengan SQL Server 2014 (12,x) estimasi tetap adalah 100 baris).
Langkah-langkah untuk mengatasi masalah
Jika MSTVF hanya pernyataan tunggal, konversi ke fungsi bernilai tabel sebaris.
CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int) RETURNS @tblAddress TABLE ([Address] VARCHAR(60) NOT NULL) AS BEGIN INSERT INTO @tblAddress ([Address]) SELECT TOP 1 [AddressLine1] FROM [Person].[Address] WHERE AddressID = @ID ORDER BY [ModifiedDate] DESC RETURN END
Contoh format sebaris ditampilkan berikutnya.
CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int) RETURNS TABLE AS RETURN ( SELECT TOP 1 [AddressLine1] AS [Address] FROM [Person].[Address] WHERE AddressID = @ID ORDER BY [ModifiedDate] DESC )
Jika lebih kompleks, pertimbangkan untuk menggunakan hasil menengah yang disimpan dalam tabel Memori-Dioptimalkan atau tabel sementara.
Konten terkait
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk