Panduan Validasi dan Pengoptimalan Pasca-migrasi

  • Artikel

Berlaku untuk:SQL Server

Langkah pascamigrasi SQL Server sangat penting untuk merekonsiliasi akurasi dan kelengkapan data apa pun, serta mengungkap masalah performa dengan beban kerja.

Skenario Performa Umum

Di bawah ini 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 yang lebih lama ke versi yang lebih baru), serta platform asing (seperti Oracle, DB2, MySQL dan Sybase) ke migrasi SQL Server.

Regresi kueri karena perubahan versi 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 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.

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:

Diagram showing the recommended upgrade workflow.

Untuk informasi selengkapnya tentang topik 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 akan 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 mungkin 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 topik ini, lihat Sniffing Parameter.

Langkah-langkah untuk mengatasi masalah

  1. RECOMPILE Gunakan petunjuk. Paket dihitung setiap kali disesuaikan dengan setiap nilai parameter.
  2. 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.
  3. 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.
  4. Tulis ulang prosedur tersimpan untuk menggunakan opsi (OPTIMIZE FOR UNKNOWN). Efek yang sama seperti menggunakan teknik variabel lokal.
  5. Tulis ulang kueri untuk menggunakan petunjuk DISABLE_PARAMETER_SNIFFING. Efek yang sama seperti menggunakan teknik variabel lokal dengan menonaktifkan sniffing parameter sepenuhnya, kecuali OPTION(RECOMPILE), WITH RECOMPILE atau OPTIMIZE FOR <value> digunakan.

Tip

Manfaatkan fitur Analisis Rencana Studio Manajemen untuk mengidentifikasi dengan cepat apakah ini adalah masalah. Informasi selengkapnya tersedia di sini.

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 sia-sia. 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

  1. Manfaatkan rencana eksekusi grafis untuk referensi Indeks yang Hilang.
  2. Saran pengindeksan yang dihasilkan oleh Database Engine Tuning Advisor.
  3. Manfaatkan DMV Indeks yang Hilang atau melalui Dasbor Performa SQL Server.
  4. Manfaatkan 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.

Tip

Contoh skrip yang sudah ada sebelumnya termasuk Pembuatan Indeks dan Informasi Indeks.

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. Carilah peringatan CONVERT_IMPLICIT runtime 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 tidak WHERE UnitPrice < 320 * 200 * 32.
  • Ekspresi menggunakan fungsi, seperti WHERE ABS(ProductID) = 771 atau WHERE UPPER(LastName) = 'Smith'
  • String dengan karakter wildcard terkemuka, seperti WHERE LastName LIKE '%Smith', tetapi tidak WHERE LastName LIKE 'Smith%'.

Langkah-langkah untuk mengatasi masalah

  1. 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 atau tampilan yang ditentukan pengguna) dengan tabel sistem yang menyimpan informasi tentang jenis data yang digunakan dalam tabel yang mendasar (seperti sys.columns).
  1. 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.
  2. 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 hal di atas 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 MSTVF (Fungsi Bernilai Tabel Multi-Pernyataan) 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

  1. Jika TVF Multi-Pernyataan hanya merupakan pernyataan tunggal, konversi ke Inline TVF.

    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
)

  2. Jika lebih kompleks, pertimbangkan untuk menggunakan hasil menengah yang disimpan dalam tabel Memori-Dioptimalkan atau tabel sementara.

Bacaan Tambahan

Praktik Terbaik dengan Penyimpanan Kueri
Tabel yang Dioptimalkan Memori
Fungsi yang Ditentukan Pengguna
Variabel Tabel dan Estimasi Baris - Bagian 1
Variabel Tabel dan Estimasi Baris - Bagian 2
Penembolokan dan Penggunaan Kembali Rencana Eksekusi