Bagikan melalui


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:

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

  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

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

  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 atau melalui Dasbor Performa SQL Server.
  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 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. 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 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 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)).

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

  1. 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
    )
    
  2. Jika lebih kompleks, pertimbangkan untuk menggunakan hasil menengah yang disimpan dalam tabel Memori-Dioptimalkan atau tabel sementara.