Umpan balik estimasi kardinalitas (CE)

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x) dan yang lebih baru.

Dimulai dengan SQL Server 2022 (16.x), umpan balik Kardinalitas Estimasi (CE) adalah bagian dari keluarga fitur pemrosesan kueri cerdas dan membahas rencana eksekusi kueri suboptimal untuk kueri berulang ketika masalah ini disebabkan oleh asumsi model CE yang salah. Skenario ini membantu mengurangi risiko regresi yang terkait dengan CE default saat meningkatkan dari versi Mesin Database yang lebih lama.

Karena tidak ada satu set model dan asumsi CE yang dapat mengakomodasi beragam beban kerja pelanggan dan distribusi data, umpan balik CE memberikan solusi yang dapat disesuaikan berdasarkan karakteristik runtime kueri. Umpan balik CE akan mengidentifikasi dan menggunakan asumsi model yang lebih sesuai dengan kueri dan distribusi data tertentu untuk meningkatkan kualitas rencana eksekusi kueri. Saat ini, Umpan Balik CE dapat mengidentifikasi operator paket di mana perkiraan jumlah baris dan jumlah baris aktual sangat berbeda. Umpan balik diterapkan ketika kesalahan estimasi model yang signifikan terjadi, dan ada model alternatif yang layak untuk dicoba.

Untuk fitur umpan balik kueri lainnya, lihat Umpan balik pemberian memori dan Umpan balik Tingkat paralelisme (DOP).

Memahami umpan balik estimasi kardinalitas (CE)

Estimasi kardinalitas (CE) adalah bagaimana Pengoptimal Kueri dapat memperkirakan jumlah total baris yang diproses di setiap tingkat rencana kueri. Estimasi kardinalitas di SQL Server terutama berasal dari histogram yang dibuat ketika indeks atau statistik dibuat, baik secara manual atau otomatis. Terkadang, SQL Server juga menggunakan informasi batasan dan penulisan ulang kueri logis untuk menentukan kardinalitas.

Versi Mesin Database yang berbeda menggunakan asumsi model CE yang berbeda berdasarkan bagaimana data didistribusikan dan dikueri. Untuk informasi selengkapnya, lihat versi CE.

Implementasi umpan balik estimasi kardinalitas (CE)

Umpan balik estimasi kardinalitas (CE) mempelajari asumsi model CE mana yang optimal dari waktu ke waktu, dan kemudian menerapkan asumsi yang paling benar secara historis:

  1. Umpan balik CE mengidentifikasi asumsi terkait model dan mengevaluasi apakah mereka akurat untuk kueri berulang.

  2. Jika asumsi terlihat salah, eksekusi kueri yang sama berikutnya diuji dengan rencana kueri yang menyesuaikan asumsi model CE yang berdampak dan memverifikasi apakah itu membantu. Kami mengidentifikasi kekeliruan dengan melihat baris aktual vs. perkiraan dari operator paket. Tidak semua kesalahan dapat dikoreksi oleh varian model yang tersedia dalam umpan balik CE.

  3. Jika meningkatkan kualitas rencana, rencana kueri lama diganti dengan rencana kueri yang menggunakan petunjuk kueri USE HINT yang sesuai yang menyesuaikan model estimasi, yang diimplementasikan melalui mekanisme petunjuk Penyimpanan Kueri.

Hanya umpan balik terverifikasi yang bertahan. Umpan balik CE tidak digunakan untuk kueri tersebut jika asumsi model yang disesuaikan menghasilkan regresi performa. Dalam konteks ini, kueri yang dibatalkan pengguna juga dianggap sebagai regresi.

Skenario umpan balik estimasi kardinalitas (CE)

Tanggapan estimasi kardinalitas (CE) membahas masalah regresi yang dirasakan yang dihasilkan dari asumsi model CE yang salah saat menggunakan CE default (CE120 atau lebih tinggi) dan dapat secara selektif menggunakan asumsi model yang berbeda. Skenarionya termasuk korelasi, Penahanan Gabungan, dan tujuan baris Pengoptimal.

Korelasi umpan balik estimasi kardinalitas (CE)

Saat Pengoptimal Kueri memperkirakan pemilihan predikat pada tabel atau tampilan tertentu, atau jumlah baris yang memenuhi predikat tersebut, ia menggunakan asumsi model korelasi. Asumsi-asumsi ini bisa menjadi predikat itu adalah:

  • Sepenuhnya independen (default untuk CE70), di mana kardinalitas dihitung dengan mengalikan selektivitas semua predikat.

  • Berkorelasi sebagian (default untuk CE120 dan yang lebih tinggi), di mana kardinalitas dihitung menggunakan variasi pada backoff eksponensial, mengurutkan selektivitas dari sebagian besar hingga predikat paling tidak selektif.

  • Sepenuhnya berkorelasi, di mana kardinalitas dihitung dengan menggunakan selektivitas minimum untuk semua predikat.

Contoh berikut menggunakan korelasi parsial saat kompatibilitas database diatur ke 120 atau lebih tinggi:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Ketika kompatibilitas database diatur ke 160, dan korelasi default digunakan, umpan balik CE mencoba memindahkan korelasi ke arah yang benar satu langkah pada satu waktu berdasarkan apakah estimasi kardinalitas dianggap remeh atau berlebihan dibandingkan dengan jumlah baris aktual. Gunakan korelasi penuh jika jumlah baris aktual lebih besar dari perkiraan kardinalitas. Gunakan kemandirian penuh jika jumlah baris aktual lebih kecil dari perkiraan kardinalitas.

Untuk informasi selengkapnya, lihat versi CE.

Penahanan gabungan umpan balik kardinalitas (CE)

Saat Pengoptimal Kueri memperkirakan pemilihan predikat gabungan dan predikat filter yang berlaku, pengoptimal kueri menggunakan asumsi model penahanan. Asumsi-asumsi ini adalah:

  • Penahanan sederhana (default untuk CE70) mengasumsikan bahwa predikat gabungan sepenuhnya berkorelasi, di mana selektivitas filter dihitung terlebih dahulu, dan kemudian selektivitas gabungan diperhitungkan.

  • Penahanan dasar (default untuk CE120 dan yang lebih tinggi) mengasumsikan tidak ada korelasi antara predikat gabungan dan filter hilir, di mana selektivitas gabungan dihitung terlebih dahulu, dan kemudian selektivitas filter diperhitungkan.

Contoh berikut menggunakan penahanan dasar saat kompatibilitas database diatur ke 120 atau lebih tinggi:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Untuk informasi selengkapnya, lihat versi CE.

Umpan balik estimasi kardinalitas (CE) dan tujuan baris pengoptimal kueri

Ketika Pengoptimal Kueri memperkirakan kardinalitas rencana eksekusi, biasanya mengasumsikan bahwa semua baris yang memenuhi syarat dari semua tabel harus diproses. Namun, beberapa pola kueri menyebabkan Pengoptimal Kueri mencari paket yang akan mengembalikan jumlah baris yang lebih kecil untuk mengurangi I/O. Jika kueri menentukan jumlah target baris (tujuan baris) yang mungkin diharapkan saat runtime dengan menggunakan TOPkata kunci , IN atau EXISTS , FAST petunjuk kueri, atau SET ROWCOUNT pernyataan, tujuan baris tersebut digunakan sebagai bagian dari proses pengoptimalan kueri seperti dalam contoh berikut:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Saat rencana tujuan baris diterapkan, perkiraan jumlah baris dalam rencana kueri berkurang karena Pengoptimal Kueri mengasumsikan bahwa jumlah baris yang lebih kecil harus diproses untuk mencapai tujuan baris.

Meskipun tujuan baris adalah strategi pengoptimalan yang bermanfaat untuk pola kueri tertentu, jika data tidak didistribusikan secara seragam, lebih banyak halaman mungkin dipindai dari perkiraan, yang berarti bahwa tujuan baris menjadi tidak efisien. Umpan balik CE dapat menonaktifkan pemindaian tujuan baris dan mengaktifkan pencarian ketika inefisiensi ini terdeteksi.

Dalam rencana eksekusi, tidak ada atribut khusus untuk umpan balik CE, tetapi akan ada atribut yang tercantum untuk petunjuk Penyimpanan Kueri. Carilah QueryStoreStatementHintSource untuk menjadi CE feedback.

Pertimbangan untuk umpan balik estimasi kardinalitas (CE)

  • Untuk mengaktifkan umpan balik estimasi kardinalitas (CE), aktifkan tingkat kompatibilitas database 160 untuk database yang Anda sambungkan saat menjalankan kueri. Penyimpanan Kueri harus diaktifkan dan dalam mode READ_WRITE untuk setiap database tempat umpan balik CE digunakan.

  • Untuk menonaktifkan umpan balik CE di tingkat database, gunakan CE_FEEDBACKkonfigurasi terlingkup database. Misalnya, dalam database pengguna:

    ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

  • Untuk menonaktifkan umpan balik CE di tingkat kueri, gunakan DISABLE_CE_FEEDBACK petunjuk kueri.

Aktivitas umpan balik CE terlihat melalui query_feedback_analysis dan query_feedback_validation XEvents.

Petunjuk yang ditetapkan oleh umpan balik CE dapat dilacak menggunakan tampilan katalog sys.query_store_query_hints .

Informasi umpan balik dapat dilacak menggunakan tampilan katalog sys.query_store_plan_feedback .

Jika kueri memiliki rencana kueri yang dipaksa melalui Penyimpanan Kueri, umpan balik CE tidak akan digunakan untuk kueri tersebut.

Jika kueri menggunakan petunjuk kueri yang dikodekan secara permanen atau menggunakan petunjuk Penyimpanan Kueri yang ditetapkan oleh pengguna, umpan balik CE tidak akan digunakan untuk kueri tersebut. Untuk informasi selengkapnya, lihat Petunjuk (Transact-SQL) - Petunjuk Kueri dan Penyimpanan Kueri.

Dimulai dengan SQL Server 2022 (16.x), ketika Penyimpanan Kueri untuk replika sekunder diaktifkan, umpan balik CE tidak sadar replika untuk replika sekunder dalam grup ketersediaan. Umpan balik CE saat ini hanya menguntungkan replika utama. Saat failover, umpan balik yang diterapkan ke replika primer atau sekunder hilang. Untuk informasi selengkapnya, lihat Penyimpanan Kueri untuk replika sekunder.

Persistensi untuk umpan balik estimasi kardinalitas (CE)

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2022 (16.x))

Umpan balik estimasi kardinalitas (CE) dapat mendeteksi skenario ketika pengoptimalan tujuan baris harus dipertahankan, dan menyimpan perubahan ini dengan mempertahankannya di penyimpanan kueri dalam bentuk petunjuk penyimpanan kueri. Pengoptimalan baru akan digunakan untuk eksekusi kueri di masa mendatang. Umpan balik CE akan mempertahankan skenario lain di luar pola kueri pengoptimalan tujuan baris, sebagaimana dirinci dalam skenario umpan balik. Umpan balik CE saat ini menangani skenario pemilihan predikat yang digunakan oleh model korelasi CE, dan skenario predikat gabungan yang ditangani oleh model penahanan CE.

Fitur ini diperkenalkan di SQL Server 2022 (16.x), namun peningkatan performa ini tersedia untuk kueri yang beroperasi dalam tingkat kompatibilitas database 160 atau lebih tinggi, atau QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n petunjuk 160 dan yang lebih tinggi, dan ketika Penyimpanan Kueri diaktifkan untuk database dan berada dalam status "baca tulis".

Masalah yang diketahui dengan umpan balik estimasi kardinalitas (CE)

Masalah Tanggal ditemukan Status Tanggal diselesaikan
Performa SQL Server lambat setelah Anda menerapkan Pembaruan Kumulatif 8 untuk SQL Server 2022 (16.x) dalam kondisi tertentu. Anda mungkin mengalami pemanfaatan memori Plan Cache yang dramatis bersama dengan peningkatan penggunaan CPU yang tidak terduga saat umpan balik CE diaktifkan. Desember 2023 Memiliki solusi sementara

Detail masalah yang diketahui

Performa SQL Server lambat setelah Anda menerapkan Pembaruan Kumulatif 8 untuk SQL Server 2022 dalam kondisi tertentu

Dimulai dengan Pembaruan Kumulatif SQL Server 2022 (16.x) 8, SQL Server mungkin menunjukkan peningkatan penggunaan CPU dan memori yang tidak terduga. Selain itu, peningkatan RESOURCE_SEMAPHORE_QUERY_COMPILE tunggu juga dapat diamati. Anda mungkin juga melihat peningkatan yang stabil dalam jumlah objek Cache Paket yang digunakan yang mendekati batas Cache Paket dan menghapus Cache Paket secara manual dengan teknik seperti ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE, atau DBCC FREEPROCCACHE tidak memberikan bantuan. Perilaku ini hanya diamati oleh sejumlah kecil pelanggan.

Masalah ini tidak memengaruhi semua beban kerja, dan tergantung pada jumlah paket berbeda yang telah dihasilkan serta jumlah paket yang memenuhi syarat agar fitur umpan balik CE terlibat. Selama periode waktu umpan balik CE menganalisis operator rencana di mana kesalahan penilaian model yang signifikan terjadi, ada skenario di mana selama fase analisis ini, rencana yang sedang direferensikan dapat menjadi dereferensi dalam memori tanpa memungkinkan rencana untuk kemudian dihapus dari memori dengan algoritma Normal Least Recently Used (LRU). Mekanisme LRU salah satu cara SQL Server memberlakukan kebijakan pengeluaran rencana. SQL Server juga akan menghapus rencana dari memori jika sistem berada di bawah tekanan memori. Ketika SQL Server mencoba menghapus rencana yang telah didereferensikan secara tidak benar, tidak dapat menghapus rencana tersebut dari cache rencana, yang menyebabkan cache terus bertambah. Cache yang berkembang mungkin mulai menyebabkan kompilasi tambahan yang pada akhirnya akan menggunakan lebih banyak CPU dan memori. Untuk informasi selengkapnya, lihat Merencanakan Cache Internal.

Gejala: Jumlah entri cache paket yang digunakan dan ditandai kotor dari Paket SQL atau Paket Objek meningkat dari waktu ke waktu menjadi 50.000 atau lebih. Jika Anda mengamati entri cache rencana yang mulai mendekati tingkat ini bersama dengan peningkatan pemanfaatan CPU yang tidak terduga, sistem Anda mungkin mengalami masalah ini. Perbaikan terkait disediakan di SQL Server 2022 (16.x) Pembaruan Kumulatif 9. Lihat KB5030731. Perbaikan mencoba mengatasi masalah di mana entri cache paket dikeluarkan ketika umpan balik Kardinalitas Estimasi (CE) mencoba mendapatkan profil terkait, yang menyebabkan kerusakan memori. Perbaikan tambahan untuk masalah ini akan tersedia dalam Pembaruan Kumulatif yang akan datang.

Untuk memantau jumlah entri cache paket yang digunakan sistem Anda, contoh berikut dapat digunakan sebagai tampilan titik waktu jumlah entri cache paket yang ada. Sebagai contoh, menonton jumlah entri cache paket yang ditandai sebagai kotor, secara berkala dari waktu ke waktu adalah salah satu cara untuk memantau fenomena ini.

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp 
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

Serangkaian kueri lain yang juga akan memberikan informasi yang sama dengan contoh sebelumnya sambil juga memungkinkan Anda mengamati metrik performa tambahan. Rasio hit Cache rencana akan berkurang, serta jumlah kompilasi sehubungan dengan jumlah permintaan batch/detik. Kueri berikut dapat digunakan untuk memantau sistem Anda dari waktu ke waktu. Mengawasi Rasio Hit Singgahan (penurunan yang tidak diantisipasi), Objek Cache yang digunakan (peningkatan jumlah ke tingkat mendekati 50.000 tanpa penurunan) dan rasio Permintaan/detik Batch yang lebih rendah dari yang diharapkan dibandingkan dengan kenaikan Kompilasi/detik.

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec' 
);

Solusi sementara: Fitur Umpan Balik CE dapat dinonaktifkan di tingkat database hingga perbaikan tambahan tersedia jika sistem Anda mengalami gejala yang telah dijelaskan sebelumnya. Untuk mengklaim kembali memori cache paket yang telah diambil oleh masalah ini, diperlukan hidupkan ulang instans SQL Server. Tindakan mulai ulang ini dapat diambil setelah fitur Umpan Balik CE dinonaktifkan. Untuk menonaktifkan umpan balik CE di tingkat database, gunakan CE_FEEDBACKkonfigurasi terlingkup database. Misalnya, dalam database pengguna:

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

Masalah umpan balik dan pelaporan

Untuk umpan balik atau pertanyaan, kirim email CEFfeedback@microsoft.com

Konten terkait