Umpan balik estimasi kardinalitas (CE)

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed Instance

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 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 dalam rencana eksekusi yang perkiraan jumlah barisnya dan jumlah baris aktualnya sangat berbeda. Umpan balik diterapkan ketika terjadi kesalahan estimasi model yang signifikan, dan ada model alternatif yang layak untuk dicoba.

Untuk fitur umpan balik kueri lainnya, lihat umpan balik pemberian memori dan umpan balik derajat 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, 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. Skenario tersebut mencakup korelasi, cakupan join, dan sasaran baris pengoptimal.

Korelasi umpan balik untuk 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 dapat berupa predikat berikut:

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

• Berkorelasi parsial (default pada CE120 dan yang lebih tinggi), dengan kardinalitas yang dihitung menggunakan variasi metode exponential backoff, dengan mengurutkan selektivitas dari predikat yang paling selektif hingga yang 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 menggeser korelasi ke arah yang benar selangkah demi selangkah berdasarkan apakah kardinalitas estimasi terlalu rendah atau terlalu tinggi dibandingkan dengan jumlah baris aktual. Gunakan korelasi penuh jika jumlah baris aktual lebih besar dari perkiraan kardinalitas. Gunakan kemandirian penuh jika jumlah baris sebenarnya lebih kecil daripada kardinalitas yang diperkirakan.

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 tertentu menyebabkan Pengoptimal Kueri mencari rencana eksekusi yang menghasilkan jumlah baris lebih sedikit 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 target baris merupakan strategi pengoptimalan yang bermanfaat untuk pola kueri tertentu, jika data tidak terdistribusi secara merata, jumlah halaman yang dipindai bisa lebih banyak daripada yang diperkirakan, sehingga target 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 yang khusus untuk umpan balik CE, tetapi ada atribut yang tercantum untuk petunjuk Query Store. 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. Query Store harus diaktifkan dan berada dalam mode READ_WRITE untuk setiap database yang menggunakan umpan balik CE.

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

  ALTER DATABASE SCOPED CONFIGURATION
  SET CE_FEEDBACK = OFF;
  

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

Aktivitas umpan balik CE dapat dilihat melalui query_feedback_analysis dan query_feedback_validation XEvents.

Hint 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 sebuah kueri memiliki rencana eksekusi kueri yang dipaksakan melalui Query Store, umpan balik CE tidak 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 digunakan untuk kueri tersebut. Untuk informasi selengkapnya, lihat Petunjuk Kueri dan Petunjuk Query Store.

Mulai dengan SQL Server 2022 (16.x), saat Query Store untuk replika sekunder diaktifkan, umpan balik CE tidak mempertimbangkan 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. Penyimpanan Kueri tersedia pada replika grup ketersediaan sekunder mulai dari SQL Server 2025 (17.x). Untuk informasi selengkapnya, lihat Query Store untuk sekunder yang dapat diakses.

Persistensi pada umpan balik estimasi kardinalitas (CE)

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Umpan balik estimasi kardinalitas (CE) dapat mendeteksi skenario saat pengoptimalan target baris perlu dipertahankan, dan menyimpan perubahan ini secara permanen dalam Query Store dalam bentuk petunjuk Query Store. Pengoptimalan baru digunakan untuk eksekusi kueri di masa mendatang. Umpan balik CE tetap ada dalam skenario lain di luar pola kueri pengoptimalan sasaran baris, seperti yang dirinci dalam skenario umpan balik. Umpan balik CE saat ini menangani skenario selektivitas predikat yang digunakan oleh model korelasi CE, serta skenario predikat join yang ditangani oleh model kontainmen CE.

Fitur ini diperkenalkan dalam SQL Server 2022 (16.x), namun peningkatan kinerja ini tersedia untuk kueri yang berjalan pada tingkat kompatibilitas database 160 atau lebih tinggi, atau hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 160 atau yang lebih tinggi, serta saat Query Store diaktifkan untuk database dan berada dalam status "baca-tulis".

Masalah yang diketahui terkait umpan balik estimasi kardinalitas (CE)

Masalah	Tanggal ditemukan	Status	Tanggal penyelesaian
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	Diselesaikan	22 April 2024 (CU 12)

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 waktu tunggu RESOURCE_SEMAPHORE_QUERY_COMPILE juga dapat diamati. Anda mungkin juga melihat peningkatan yang konsisten dalam jumlah objek Plan Cache yang digunakan hingga mendekati batas Plan Cache, dan mengosongkan Plan Cache secara manual dengan teknik seperti ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE, atau DBCC FREEPROCCACHE tidak membantu. Perilaku ini hanya diamati oleh beberapa pelanggan.

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

Gejala: Jumlah entri cache rencana yang sedang digunakan dan ditandai sebagai kotor dari Rencana SQL atau Rencana Objek meningkat dari waktu ke waktu hingga 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 disediakan dengan Pembaruan Kumulatif SQL Server 2022 (16.x) 12. Lihat KB5033663.

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, memantau jumlah entri cache rencana 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 memberikan informasi yang sama dengan contoh sebelumnya sambil juga memungkinkan Anda mengamati metrik performa tambahan. Rasio hit Plan Cache menurun, begitu pula jumlah kompilasi dibandingkan dengan jumlah permintaan batch per detik. Kueri berikut dapat digunakan untuk memantau sistem Anda seiring waktu. Memantau Cache Hit Ratio (penurunan tak terduga), Objek Cache yang digunakan (peningkatan jumlah hingga mendekati 50.000 tanpa menurun), serta rasio Permintaan Batch/dtk yang lebih rendah dari perkiraan dibandingkan dengan kenaikan Kompilasi/dtk.

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

Jika sistem Anda terus mengalami gejala yang dijelaskan sebelumnya, setelah menerapkan Pembaruan Kumulatif 12 KB5033663, fitur umpan balik CE dapat dinonaktifkan di tingkat database.

Untuk membebaskan kembali memori cache rencana yang terpakai akibat masalah ini, instans SQL Server harus dimulai ulang. Tindakan mulai ulang ini dapat diambil setelah fitur umpan balik CE dinonaktifkan. Untuk menonaktifkan umpan balik CE pada tingkat database, gunakan CE_FEEDBACKkonfigurasi cakupan 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

• Umpan Balik Estimasi Kardinalitas di SQL Server 2022
• Pemrosesan kueri cerdas dalam database SQL
• Fitur pemrosesan kueri cerdas secara rinci
• Estimasi Kardinalitas (SQL Server)
• RECONFIGURE (Transact-SQL)
• Monitor dan Selaraskan Kinerja
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)