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 mencakup skenario yang khusus bagi migrasi SQL Server ke SQL Server (dari versi yang lebih lama ke versi yang lebih baru), serta migrasi dari platform lain (seperti Oracle, DB2, MySQL, dan Sybase) ke SQL Server.

Penurunan performa kueri akibat 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 mulai dari SQL Server 2014 (12.x), semua perubahan pada Pengoptimal Kueri terkait dengan tingkat kompatibilitas database terbaru, sehingga rencana kueri tidak langsung berubah saat peningkatan dilakukan, melainkan ketika pengguna mengubah opsi database COMPATIBILITY_LEVEL ke tingkat terbaru. Kemampuan ini, bila dipadukan dengan Query Store, memberi Anda tingkat kontrol yang tinggi atas kinerja 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:

Diagram memperlihatkan alur kerja pemutakhiran yang direkomendasikan.

Untuk informasi selengkapnya tentang artikel ini, lihat Menjaga stabilitas performa selama peningkatan ke SQL Server yang lebih baru.

Sensitivitas terhadap parameter sniffing

Berlaku untuk: Migrasi dari platform lain (seperti Oracle, DB2, MySQL, dan Sybase) ke 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 as-is tidak mengatasi skenario ini.

SQL Server mengompilasi rencana kueri untuk prosedur tersimpan dengan melakukan sniffing terhadap parameter input pada kompilasi pertama, sehingga menghasilkan rencana berparameter yang dapat digunakan kembali dan dioptimalkan untuk distribusi data input tersebut. Bahkan jika tidak disimpan prosedur, sebagian besar pernyataan yang menghasilkan rencana sepele diparameterkan. Setelah rencana eksekusi pertama kali disimpan dalam cache, setiap eksekusi berikutnya akan menggunakan rencana eksekusi yang sebelumnya disimpan dalam cache.

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

  1. Gunakan petunjuk RECOMPILE. Rencana dihitung setiap kali disesuaikan dengan nilai setiap parameter.

  2. Tulis ulang prosedur tersimpan untuk menggunakan opsi (OPTIMIZE FOR(<input parameter> = <value>)). Tentukan nilai yang akan digunakan yang paling sesuai untuk sebagian besar beban kerja yang relevan, dengan membuat dan memelihara satu rencana yang efisien untuk nilai yang diparameterkan.

  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

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.
  • Perhatian khusus terhadap indeks yang tidak digunakan saat melakukan pembaruan.

Langkah-langkah untuk mengatasi masalah

  1. Gunakan rencana eksekusi grafis untuk referensi Indeks yang Hilang.

  2. Saran pengindeksan yang dihasilkan oleh Konsultan Penyetelan Mesin Database.

  3. Gunakan sys.dm_db_missing_index_details.

  4. Gunakan skrip yang sudah ada sebelumnya yang dapat menggunakan DMV yang ada untuk memberikan wawasan tentang indeks yang hilang, duplikat, redundan, jarang digunakan dan benar-benar 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 as-is tidak 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.

Catatan

Istilah SARGable dalam basis data relasional mengacu pada predikat Search ARGumentable yang dapat menggunakan indeks untuk mempercepat eksekusi kueri. Untuk informasi selengkapnya, lihat Panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Beberapa contoh predikat non-SARGable :

  • Konversi data implisit, seperti varchar ke nvarchar, atau int ke varchar. Cari peringatan saat proses berjalan 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 tidak WHERE UnitPrice < 320 * 200 * 32.

  • Ekspresi menggunakan fungsi, seperti WHERE ABS(ProductID) = 771 atau WHERE UPPER(LastName) = 'Smith'

  • String dengan karakter wildcard di awal, 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 yang ditentukan pengguna, atau tampilan) dengan tabel sistem yang menyimpan informasi tentang jenis data yang digunakan dalam tabel dasar (seperti sys.columns).

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

  3. Uraikan kegunaan konstruksi berikut:

    • Fungsi yang digunakan sebagai predikat;
    • Pencarian kartubebas;
    • Ekspresi kompleks berdasarkan data kolom - evaluasi apakah perlu membuat kolom terhitung persisten yang dapat diindeks;

Catatan

Semua langkah ini dapat dilakukan secara terprogram.

Penggunaan fungsi bernilai tabel (banyak pernyataan vs baris tunggal)

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 as-is tidak 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.

Karena tabel output dari fungsi bernilai tabel multistatement (MSTVF) tidak dibuat saat kompilasi, pengoptimal kueri SQL Server mengandalkan heuristik, bukan statistik aktual, untuk memperkirakan jumlah 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 MSTVF hanya terdiri dari satu pernyataan, konversikan ke fungsi bernilai tabel inline.

    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 dalam baris ditampilkan selanjutnya.

    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.

Konten terkait

  • Last updated on