Demistifikasi proses optimasi SQL Server

Nov 16 2020

Kami ingin melihat semua varian rencana kueri dipertimbangkan selama pengoptimalan kueri oleh pengoptimal SQL Server. SQL Server menawarkan wawasan yang cukup rinci menggunakan querytraceonopsi. Misalnya QUERYTRACEON 3604, QUERYTRACEON 8615memungkinkan kita untuk mencetak struktur MEMO dan QUERYTRACEON 3604, QUERYTRACEON 8619mencetak daftar aturan transformasi yang diterapkan selama proses pengoptimalan. Itu bagus, namun, kami memiliki beberapa masalah dengan keluaran jejak:

Tampaknya struktur MEMO hanya berisi varian akhir dari rencana kueri atau varian yang kemudian ditulis ulang menjadi varian terakhir. Adakah cara untuk menemukan rencana kueri yang "tidak berhasil / tidak menjanjikan"?
Operator di MEMO tidak berisi referensi ke bagian SQL. Misalnya, operator LogOp_Get tidak berisi referensi ke Tabel tertentu.
Aturan transformasi tidak berisi referensi yang tepat ke operator MEMO, oleh karena itu, kami tidak dapat memastikan operator mana yang diubah oleh aturan transformasi.

Izinkan saya menunjukkannya pada contoh yang lebih terperinci. Biarkan saya memiliki dua tabel buatan Adan B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

Saat ini, saya menjalankan satu kueri sederhana

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)

Saya mendapatkan keluaran yang cukup kompleks yang menggambarkan struktur MEMO (Anda dapat mencoba sendiri) memiliki 15 grup. Berikut adalah gambar yang memvisualisasikan struktur MEMO dengan menggunakan pohon.

Dari pohon seseorang dapat mengamati bahwa ada aturan tertentu yang diterapkan sebelum pengoptimal menemukan rencana kueri akhir. Misalnya join commute( JoinCommute), join to hash join( JNtoHS), atau Enforce sort( EnforceSort). Seperti yang disebutkan, dimungkinkan untuk mencetak seluruh rangkaian aturan penulisan ulang yang diterapkan oleh pengoptimal menggunakan QUERYTRACEON 3604, QUERYTRACEON 8619opsi. Masalah:

Kita mungkin menemukan JNtoSM( Join to sort merge) rewriting rule dalam daftar 8619, namun operator sort-merge tidak dalam struktur MEMO. Saya mengerti bahwa penggabungan-sortir mungkin lebih mahal, tetapi mengapa tidak di MEMO?
Bagaimana mengetahui apakah LogOp_Getoperator dalam referensi MEMO ke tabel A atau tabel B?
Jika saya melihat aturan GetToIdxScan - Get -> IdxScandalam daftar 8619, bagaimana cara memetakannya ke operator MEMO?

Ada sejumlah sumber daya tentang ini. Saya telah membaca banyak postingan blog Paul White tentang aturan transformasi dan MEMO, namun, pertanyaan di atas tetap tidak terjawab. Terima kasih atas bantuannya.

Jawaban

FrancescoMantovani Nov 26 2020 at 03:38

Saya akan mencoba menjawab pertanyaan Anda:

1. Tampaknya struktur MEMO hanya berisi varian akhir dari rencana kueri atau varian yang kemudian ditulis ulang menjadi varian terakhir. Adakah cara untuk menemukan rencana kueri yang "tidak berhasil / tidak menjanjikan"?

Tidak, sayangnya tidak ada cara untuk melakukan itu. @Ronaldo menempelkan tautan yang bagus di komentar. Saran saya adalah menggunakanInclude Live Query Statistics

dan coba cari tahu apakah Anda melihat rencana kueri yang berbeda. Gunakan top 10,, top 1000atau *dan Anda akan melihat bahwa rencana kueri yang berbeda akan diusulkan. Anda juga dapat menggunakan query hintdan memaksa rencana kueri Anda ke pola yang berbeda. Pada dasarnya "lakukan rencana kueri yang Anda buang"

2. Operator di MEMO tidak berisi referensi ke bagian SQL. Misalnya, operator LogOp_Get tidak berisi referensi ke Tabel tertentu.

Gunakan QUERYTRACEON 8605, saya dapat melihat referensi ke tabel:

3. Aturan transformasi tidak berisi referensi yang tepat ke operator MEMO, oleh karena itu, kami tidak dapat memastikan operator mana yang diubah oleh aturan transformasi

Saya tidak melihat satu pun GetToIdxScan - Get -> IdxScandalam kueri yang Anda berikan. Saran saya gunakan Use QUERYTRACEON 8605, atau QUERYTRACEON 8606, harus ada referensi di sana.

EDIT:

Jadi "... apakah mungkin untuk melihat informasi lebih lanjut tentang rencana kandidat di SQL Server."

Jawabannya tidak , karena tidak ada rencana permintaan kandidat lainnya. Faktanya, adalah kesalahpahaman umum bahwa SQL Server mengembalikan Anda rencana kueri terbaik . SQL Server tidak dapat menghitung untuk Anda semua kemungkinan solusi: itu akan memakan waktu ... Saya tidak tahu ... menit ...? jam...? Menghitung setiap solusi tidak mungkin dilakukan.