Estimasi Kardinalitas (SQL Server)

  • Artikel
  • 19 menit untuk membaca

Berlaku untuk:ya SQL Server (semua versi yang didukung) Ya Azure SQL Database Ya Azure SQL Managed Instance

Pengoptimal Kueri SQL Server adalah Pengoptimal Kueri berbasis biaya. Ini berarti bahwa ia memilih rencana kueri yang memiliki perkiraan biaya pemrosesan terendah untuk dijalankan. Pengoptimal Kueri menentukan biaya eksekusi rencana kueri berdasarkan dua faktor utama:

  • Jumlah total baris yang diproses di setiap tingkat rencana kueri, disebut sebagai kardinalitas rencana.
  • Model biaya algoritma yang ditentukan oleh operator yang digunakan dalam kueri.

Faktor pertama, kardinalitas, digunakan sebagai parameter input dari faktor kedua, model biaya. Oleh karena itu, kardinalitas yang ditingkatkan menyebabkan perkiraan biaya yang lebih baik dan, pada gilirannya, rencana eksekusi yang lebih cepat.

Estimasi kardinalitas (CE) dalam SQL Server berasal terutama dari histogram yang dibuat saat indeks atau statistik dibuat, baik secara manual maupun otomatis. Terkadang, SQL Server juga menggunakan informasi batasan dan penulisan ulang kueri logis untuk menentukan kardinalitas.

Dalam kasus berikut, SQL Server tidak dapat menghitung kardinalitas secara akurat. Ini menyebabkan perhitungan biaya yang tidak akurat yang dapat menyebabkan rencana kueri suboptimal. Menghindari konstruksi ini dalam kueri dapat meningkatkan performa kueri. Terkadang, rumusan kueri alternatif atau langkah-langkah lain dimungkinkan dan ini ditujukan:

  • Kueri dengan predikat yang menggunakan operator perbandingan antara kolom yang berbeda dari tabel yang sama.
  • Kueri dengan predikat yang menggunakan operator, dan salah satu hal berikut ini benar:
    • Tidak ada statistik pada kolom yang terlibat di kedua sisi operator.
    • Distribusi nilai dalam statistik tidak seragam, tetapi kueri mencari kumpulan nilai yang sangat selektif. Situasi ini bisa sangat benar jika operator adalah apa pun selain operator kesetaraan (=).
    • Predikat menggunakan operator perbandingan yang tidak sama dengan (!=) atau NOT operator logis.
  • Kueri yang menggunakan salah satu fungsi bawaan SQL Server atau fungsi bernilai skalar yang ditentukan pengguna yang argumennya bukan nilai konstanta.
  • Kueri yang melibatkan gabungan kolom melalui operator perangkaian aritmatika atau string.
  • Kueri yang membandingkan variabel yang nilainya tidak diketahui saat kueri dikompilasi dan dioptimalkan.

Artikel ini menggambarkan bagaimana Anda dapat menilai dan memilih konfigurasi CE terbaik untuk sistem Anda. Sebagian besar sistem mendapat manfaat dari CE terbaru karena paling akurat. CE memprediksi berapa banyak baris yang kemungkinan akan dikembalikan oleh kueri Anda. Prediksi kardinalitas digunakan oleh Pengoptimal Kueri untuk menghasilkan rencana kueri yang optimal. Dengan estimasi yang lebih akurat, Pengoptimal Kueri biasanya dapat melakukan pekerjaan yang lebih baik untuk menghasilkan rencana kueri yang lebih optimal.

Sistem aplikasi Anda mungkin dapat memiliki kueri penting yang rencananya diubah menjadi rencana yang lebih lambat karena perubahan CE di seluruh versi. Anda memiliki teknik dan alat untuk mengidentifikasi kueri yang berkinerja lebih lambat karena masalah CE. Dan Anda memiliki opsi untuk cara mengatasi masalah performa berikutnya.

Versi CE

Pada tahun 1998, pembaruan besar CE adalah bagian dari SQL Server 7.0, yang tingkat kompatibilitasnya adalah 70. Versi model CE ini diatur pada empat asumsi dasar:

  • Kemerdekaan: Distribusi data pada kolom yang berbeda diasumsikan independen satu sama lain, kecuali informasi korelasi tersedia dan dapat digunakan.

  • Keseragaman: Nilai yang berbeda diberi spasi merata dan semuanya memiliki frekuensi yang sama. Lebih tepatnya, dalam setiap langkah histogram , nilai yang berbeda tersebar secara merata dan setiap nilai memiliki frekuensi yang sama.

  • Penahanan (Sederhana): Kueri pengguna untuk data yang ada. Misalnya, untuk gabungan kesetaraan antara dua tabel, faktor dalam predikat selektivitas1 di setiap histogram input, sebelum bergabung dengan histogram untuk memperkirakan selektivitas gabungan.

  • Inklusi: Untuk predikat filter di mana Column = Constant, konstanta diasumsikan benar-benar ada untuk kolom terkait. Jika langkah histogram yang sesuai tidak kosong, salah satu nilai berbeda langkah diasumsikan cocok dengan nilai dari predikat .

    1 Jumlah baris yang memenuhi predikat.

Pembaruan berikutnya dimulai dengan SQL Server 2014 (12.x), yang berarti tingkat kompatibilitas 120 ke atas. Pembaruan CE untuk tingkat 120 ke atas menggabungkan asumsi dan algoritma yang diperbarui yang bekerja dengan baik pada pergudangan data modern dan pada beban kerja OLTP. Dari asumsi CE 70, asumsi model berikut diubah dimulai dengan CE 120:

  • Independensi menjadi Korelasi: Kombinasi nilai kolom yang berbeda belum tentu independen. Ini mungkin menyerupai kueri data kehidupan nyata.
  • Penahanan Sederhana menjadi Penahanan Dasar: Pengguna mungkin meminta data yang tidak ada. Misalnya, untuk gabungan kesetaraan antara dua tabel, kami menggunakan histogram tabel dasar untuk memperkirakan selektivitas gabungan, lalu memperhitungkan selektivitas predikat.

Menggunakan Penyimpanan Kueri untuk menilai versi CE

Dimulai dengan SQL Server 2016 (13.x), Query Store adalah alat yang berguna untuk memeriksa performa kueri Anda. Setelah Penyimpanan Kueri diaktifkan, kueri akan mulai melacak performa kueri dari waktu ke waktu, bahkan jika rencana eksekusi berubah. Pantau Penyimpanan Kueri untuk performa kueri bernilai tinggi atau regresi. Untuk informasi selengkapnya, lihat Memantau performa dengan menggunakan Penyimpanan Kueri.

Jika mempersiapkan peningkatan untuk SQL Server atau mempromosikan tingkat kompatibilitas database di platform SQL Server apa pun, pertimbangkan untuk Meningkatkan Database dengan menggunakan Asisten Penyetelan Kueri, yang dapat membantu membandingkan performa kueri dalam dua tingkat kompatibilitas yang berbeda.

Penting

Pastikan Penyimpanan Kueri dikonfigurasi dengan benar untuk database dan beban kerja Anda. Untuk informasi selengkapnya, lihat Praktik terbaik dengan Penyimpanan Kueri.

Menggunakan peristiwa yang diperluas untuk menilai versi CE

Opsi lain untuk melacak proses estimasi kardinalitas adalah menggunakan peristiwa yang diperluas bernama query_optimizer_estimate_cardinality. Sampel kode Transact-SQL berikut berjalan pada SQL Server. Ini menulis file .xel ke C:\Temp\ (meskipun Anda dapat mengubah jalur). Saat Anda membuka file .xel di Management Studio, informasi terperincinya ditampilkan dengan cara yang mudah digunakan.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO

Catatan

Peristiwa 'sqlserver.query_optimizer_estimate_cardinality' tidak tersedia untuk database Azure SQL.

Untuk informasi tentang peristiwa yang diperluas sebagai disesuaikan untuk SQL Database, lihat Acara yang diperluas di SQL Database.

Langkah-langkah untuk menilai versi CE

Selanjutnya adalah langkah-langkah yang dapat Anda gunakan untuk menilai apakah salah satu kueri Anda yang paling penting berkinerja lebih buruk di bawah CE terbaru. Beberapa langkah dilakukan dengan menjalankan sampel kode yang disajikan di bagian sebelumnya.

  1. Buka SQL Server Management Studio (SSMS). Pastikan database SQL Server Anda diatur ke tingkat kompatibilitas tertinggi yang tersedia.

  2. Lakukan langkah-langkah awal berikut:

    1. Buka SQL Server Management Studio (SSMS).

    2. Jalankan Transact-SQL untuk memastikan bahwa database SQL Server Anda diatur ke tingkat kompatibilitas tertinggi yang tersedia.

    3. Pastikan database Anda telah menonaktifkan konfigurasinya LEGACY_CARDINALITY_ESTIMATION .

    4. Hapus Penyimpanan Kueri Anda. Pastikan Penyimpanan Kueri Anda AKTIF.

    5. Jalankan pernyataan: SET NOCOUNT OFF;

  3. Jalankan pernyataan: SET STATISTICS XML ON;

  4. Jalankan kueri penting Anda.

  5. Di panel hasil, pada tab Pesan , perhatikan jumlah baris aktual yang terpengaruh.

  6. Di panel hasil pada tab Hasil , klik dua kali sel yang berisi statistik dalam format XML. Rencana kueri grafik ditampilkan.

  7. Klik kanan kotak pertama dalam rencana kueri grafik, lalu pilih Properti.

  8. Untuk perbandingan nanti dengan konfigurasi yang berbeda, perhatikan nilai untuk properti berikut:

    1. KardinalitasEstimasiModelVersion.

    2. Estimasi Jumlah Baris.

    3. Estimasi Biaya I/O, dan beberapa properti Estimasi serupa yang melibatkan performa aktual daripada prediksi jumlah baris.

    4. Operasi Logis dan Operasi Fisik. Paralelisme adalah nilai yang baik.

    5. Mode Eksekusi Aktual. Batch adalah nilai yang baik, lebih baik daripada Row.

  9. Bandingkan perkiraan jumlah baris dengan jumlah baris aktual. Apakah CE tidak akurat sebesar 1% (tinggi atau rendah), atau sebesar 10%?

  10. Jalankan: SET STATISTICS XML OFF;

  11. Jalankan Transact-SQL untuk mengurangi tingkat kompatibilitas database Anda sebesar satu tingkat (seperti dari 130 ke bawah menjadi 120).

  12. Jalankan ulang semua langkah non-awal.

  13. Bandingkan nilai properti CE dari dua eksekusi.

    1. Apakah persentase ketidakakuratan di bawah CE terbaru kurang dari di bawah CE yang lebih lama?

  14. Terakhir, bandingkan berbagai nilai properti performa dari dua eksekusi.

    1. Apakah kueri Anda menggunakan paket yang berbeda dengan dua estimasi CE yang berbeda?

    2. Apakah kueri Anda berjalan lebih lambat di bawah CE terbaru?

    3. Kecuali kueri Anda berjalan lebih baik dan dengan paket yang berbeda di bawah CE yang lebih lama, Anda hampir pasti menginginkan CE terbaru.

    4. Namun, jika kueri Anda berjalan dengan rencana yang lebih cepat di bawah CE yang lebih lama, pertimbangkan untuk memaksa sistem menggunakan rencana yang lebih cepat dan untuk mengabaikan CE. Dengan cara ini Anda dapat memiliki CE terbaru untuk semuanya, sambil menjaga rencana yang lebih cepat dalam satu kasus ganjil.

Cara mengaktifkan rencana kueri terbaik

Misalkan dengan CE 120 atau lebih tinggi, rencana kueri yang kurang efisien dihasilkan untuk kueri Anda. Berikut adalah beberapa opsi yang Anda miliki untuk mengaktifkan rencana yang lebih baik, dipesan dari cakupan terbesar ke yang terkecil:

  • Anda dapat mengatur tingkat kompatibilitas database ke nilai yang lebih rendah dari yang terbaru tersedia, untuk seluruh database Anda.

    • Misalnya, mengatur tingkat kompatibilitas 110 atau lebih rendah mengaktifkan CE 70, tetapi membuat semua kueri tunduk pada model CE sebelumnya.

    • Selanjutnya, mengatur tingkat kompatibilitas yang lebih rendah juga melewatkan sejumlah peningkatan dalam pengoptimal kueri untuk versi terbaru, dan memengaruhi semua kueri terhadap database.

  • Anda bisa menggunakan LEGACY_CARDINALITY_ESTIMATION opsi database, agar seluruh database menggunakan CE yang lebih lama, sambil mempertahankan peningkatan lain dalam pengoptimal kueri.

    • Selanjutnya, mengatur tingkat kompatibilitas yang lebih rendah juga melewatkan banyak peningkatan dalam pengoptimal kueri untuk versi terbaru, dan memengaruhi semua kueri terhadap database.

  • Anda bisa menggunakan LEGACY_CARDINALITY_ESTIMATION opsi database, agar seluruh database menggunakan CE yang lebih lama, sambil mempertahankan peningkatan lain dalam pengoptimal kueri.

  • Anda dapat menggunakan LEGACY_CARDINALITY_ESTIMATION petunjuk kueri, agar satu kueri menggunakan CE yang lebih lama, sambil mempertahankan peningkatan lain dalam pengoptimal kueri.

  • Anda bisa memberlakukan LEGACY_CARDINALITY_ESTIMATION melalui fitur petunjuk Penyimpanan Kueri, agar satu kueri menggunakan CE yang lebih lama tanpa mengubah kueri.

  • Paksa paket yang berbeda dengan Penyimpanan Kueri.

Tingkat kompatibilitas database

Anda dapat memastikan database Anda berada pada tingkat tertentu dengan menggunakan kode Transact-SQL berikut untuk COMPATIBILITY_LEVEL.

Penting

Nomor versi mesin database untuk SQL Server dan Azure SQL Database tidak sebanding satu sama lain, dan lebih tepatnya adalah nomor build internal untuk produk terpisah ini. Mesin database untuk Azure SQL Server didasarkan pada basis kode yang sama dengan mesin database SQL Server. Yang paling penting, mesin database di Azure SQL Database selalu memiliki bit mesin database SQL terbaru. Azure SQL Database sersi 12 lebih baru dari SQL Server versi 15. Pada November 2019, dalam Azure SQL Database, tingkat kompatibilitas default adalah 150 untuk database yang baru dibuat. Microsoft tidak memperbarui Tingkat Kompatibilitas Database untuk database yang sudah ada. Terserah pelanggan untuk melakukan atas kebijaksanaan mereka sendiri.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO

Untuk database yang sudah ada sebelumnya yang berjalan pada tingkat kompatibilitas yang lebih rendah, selama aplikasi tidak perlu memanfaatkan peningkatan yang hanya tersedia dalam tingkat kompatibilitas database yang lebih tinggi, ini adalah pendekatan yang valid untuk mempertahankan tingkat kompatibilitas database sebelumnya. Untuk pekerjaan pengembangan baru, atau ketika aplikasi yang ada memerlukan penggunaan fitur baru seperti Pemrosesan Kueri Cerdas, serta beberapa Transact-SQL baru, rencanakan untuk meningkatkan tingkat kompatibilitas database ke yang terbaru tersedia. Untuk informasi selengkapnya, lihat Tingkat kompatibilitas dan peningkatan Mesin Database.

Perhatian

Sebelum mengubah tingkat kompatibilitas database, tinjau Praktik Terbaik untuk meningkatkan Tingkat Kompatibilitas Database.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO

Untuk database SQL Server yang ditetapkan pada tingkat kompatibilitas 120 atau lebih tinggi, aktivasi bendera pelacakan 9481 memaksa sistem untuk menggunakan CE versi 70.

Penaksir kardinalitas lama

Untuk database SQL Server yang ditetapkan pada tingkat kompatibilitas 120 ke atas, estimator kardinalitas warisan (CE versi 70) dapat diaktifkan di tingkat database dengan menggunakan KONFIGURASI CAKUPAN ALTER DATABASE.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Mengubah kueri untuk menggunakan petunjuk

Dimulai dengan SQL Server 2016 (13.x) SP1, ubah kueri untuk menggunakan PetunjukUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') Kueri.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Mengatur petunjuk Penyimpanan Kueri

Kueri dapat dipaksa untuk menggunakan estimator kardinalitas warisan tanpa memodifikasi kueri, menggunakan fitur Petunjuk Penyimpanan Kueri (Pratinjau).

  1. Identifikasi kueri dalam tampilan katalog sys.query_store_query_text dan sys.query_store_query Query Store. Misalnya, cari kueri yang dijalankan berdasarkan fragmen teks:
SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
AND query_sql_text not like N'%query_store%';
  1. Contoh berikut menerapkan petunjuk Penyimpanan Kueri untuk memaksa estimator kardinalitas warisan pada query_id 39, tanpa memodifikasi kueri:
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Catatan

Untuk informasi selengkapnya, lihat Petunjuk Cerita Kueri (Pratinjau). Saat ini fitur ini hanya tersedia di Azure SQL DB.

Cara memaksa rencana kueri tertentu

Untuk kontrol terbaik, Anda dapat memaksa sistem untuk menggunakan rencana yang dihasilkan dengan CE 70 selama pengujian Anda. Setelah menyematkan paket pilihan Anda, Anda dapat mengatur seluruh database Anda untuk menggunakan tingkat kompatibilitas terbaru dan CE. Opsi ini dijabarkan berikutnya.

Penyimpanan Kueri memberi Anda berbagai cara agar Anda bisa memaksa sistem untuk menggunakan rencana kueri tertentu:

  • Jalankansys.sp_query_store_force_plan.

  • Di SQL Server Management Studio (SSMS), perluas simpul Penyimpanan Kueri Anda, klik kanan Simpul Penggunaan Sumber Daya Teratas, lalu pilih Tampilkan Simpul Penggunaan Sumber Daya Teratas. Tampilan menunjukkan tombol berlabel Paksa Rencana dan Batalkan Rencana.

Untuk informasi selengkapnya tentang Penyimpanan Kueri, lihat Memantau Performa Dengan Menggunakan Penyimpanan Kueri.

Evaluasi pelipatan dan ekspresi konstan selama Estimasi Kardinalitas

Mesin Database mengevaluasi beberapa ekspresi konstanta lebih awal untuk meningkatkan performa kueri. Ini disebut sebagai pelipatan konstanta. Konstanta adalah harfiah Transact-SQL, seperti 3, , 'ABC''2005-12-31', 1.0e3, atau 0x12345678. Untuk informasi selengkapnya, lihat Lipatan Konstan.

Selain itu, beberapa ekspresi yang tidak konstanta dilipat tetapi argumennya diketahui pada waktu kompilasi, baik argumen adalah parameter atau konstanta, dievaluasi oleh estimator ukuran tataan hasil (kardinalitas) yang merupakan bagian dari Pengoptimal Kueri selama pengoptimalan. Untuk informasi selengkapnya, lihat Evaluasi Ekspresi.

Praktik Terbaik: Menggunakan lipatan konstan dan evaluasi ekspresi waktu kompilasi untuk menghasilkan rencana kueri yang optimal

Untuk memastikan Anda menghasilkan rencana kueri yang optimal, yang terbaik adalah merancang kueri, prosedur tersimpan, dan batch sehingga Pengoptimal Kueri dapat secara akurat memperkirakan selektivitas kondisi dalam kueri Anda, berdasarkan statistik tentang distribusi data Anda. Jika tidak, Pengoptimal Kueri harus menggunakan perkiraan default saat memperkirakan selektivitas.

Untuk memastikan bahwa Estimator Kardinalitas Pengoptimal Kueri memberikan perkiraan yang baik, Anda harus terlebih dahulu memastikan bahwa opsi SET database AUTO_CREATE_STATISTICS dan AUTO_UPDATE_STATISTICS AKTIF (pengaturan default), atau Anda telah membuat statistik secara manual pada semua kolom yang direferensikan dalam kondisi kueri. Kemudian, saat Anda merancang kondisi dalam kueri Anda, lakukan hal berikut jika memungkinkan:

  • Hindari penggunaan variabel lokal dalam kueri. Sebagai gantinya, gunakan parameter, literal, atau ekspresi dalam kueri.

  • Batasi penggunaan operator dan fungsi yang disematkan dalam kueri yang berisi parameter untuk yang tercantum di bawah Evaluasi Ekspresi Compile-Time untuk Estimasi Kardinalitas.

  • Pastikan bahwa ekspresi konstanta saja dalam kondisi kueri Anda dapat dilipat secara konstanta, atau dapat dievaluasi pada waktu kompilasi.

  • Jika Anda harus menggunakan variabel lokal untuk mengevaluasi ekspresi yang akan digunakan dalam kueri, pertimbangkan untuk mengevaluasinya dalam cakupan yang berbeda dari kueri. Misalnya, mungkin berguna untuk melakukan salah satu opsi berikut:

    • Teruskan nilai variabel ke prosedur tersimpan yang berisi kueri yang ingin Anda evaluasi, dan minta kueri menggunakan parameter prosedur alih-alih variabel lokal.

    • Buat string yang berisi kueri berdasarkan sebagian pada nilai variabel lokal, lalu jalankan string dengan menggunakan SQL dinamis (EXEC atau sebaiknya sp_executesql).

    • Parameterisasi kueri dan jalankan dengan menggunakan sp_executesql, dan teruskan nilai variabel sebagai parameter ke kueri.

Umpan balik Estimasi Kardinalitas (CE)

Berlaku untuk:yes SQL Server 2022 (16.x) Preview Yes Azure SQL Database Yes Azure SQL Managed Instance

Dimulai dengan Pratinjau SQL Server 2022 (16.x), umpan balik Estimasi Kardinalitas (CE) adalah bagian dari keluarga fitur pemrosesan kueri cerdas dan mengatasi rencana eksekusi kueri suboptimal untuk mengulangi kueri ketika masalah ini diakibatkan 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 berbagai 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. Umpan balik diterapkan ketika kesalahan estimasi model yang signifikan yang mengakibatkan penurunan performa ditemukan.

Memahami Estimasi Kardinalitas

Estimasi Kardinalitas (CE) adalah bagaimana Pengoptimal Kueri dapat memperkirakan jumlah total baris yang diproses di setiap tingkat rencana kueri. Estimasi kardinalitas dalam SQL Server berasal terutama 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. Lihat versi CE untuk informasi selengkapnya.

Implementasi umpan balik CE

Umpan balik 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 mengulangi kueri.

  2. Jika asumsi terlihat salah, eksekusi berikutnya dari kueri yang sama diuji dengan rencana kueri yang menyesuaikan asumsi model CE yang berdampak dan memverifikasi apakah itu membantu.

  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 dipertahankan. 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 CE

Umpan balik CE mengatasi 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.

Korelasi

Saat Pengoptimal Kueri memperkirakan selektivitas predikat pada tabel atau tampilan tertentu, atau jumlah baris yang memenuhi predikat tersebut, ia menggunakan asumsi model korelasi. Asumsi-asumsi ini dapat berupa predikat tersebut 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 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 akan mencoba memindahkan korelasi ke arah yang benar satu langkah pada satu waktu berdasarkan apakah perkiraan kardinalitas diremehkan atau terlalu diestimasi 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.

Lihat versi CE untuk informasi selengkapnya.

Menggabungkan Penahanan

Saat Pengoptimal Kueri memperkirakan selektivitas 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, lalu 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.

Tujuan baris pengoptimal

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

Pertimbangan

Untuk mengaktifkan umpan balik CE, aktifkan tingkat kompatibilitas database 160 untuk database yang anda sambungkan saat menjalankan kueri. Penyimpanan Kueri harus diaktifkan untuk setiap database tempat umpan balik CE digunakan.

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 sys.query_store_plan_feedback tampilan katalog.

Dimulai dengan umpan balik CE, atribut baru IsCEFeedbackAdjusted tersedia pada elemen StmtSimple untuk melihat apakah penyesuaian Umpan Balik CE digunakan.

[! NOTE} Properti ini belum tersedia.

Untuk menonaktifkan umpan balik CE di tingkat database, gunakan ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF konfigurasi lingkup database.

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

Jika kueri memiliki rencana kueri yang dipaksakan 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.

Untuk mengizinkan umpan balik CE mengambil alih petunjuk kueri yang dikodekan secara permanen dan petunjuk pengguna Penyimpanan Kueri, gunakan ALTER DATABASE SCOPED CONFIGURATION SET FORCE_CE_FEEDBACK = ON konfigurasi lingkup database.

Catatan

Konfigurasi ini belum tersedia.

Masalah Umpan Balik dan Pelaporan

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

Contoh peningkatan CE

Bagian ini menjelaskan contoh kueri yang mendapat manfaat dari peningkatan yang diterapkan di CE dalam rilis terbaru. Ini adalah informasi latar belakang yang tidak memanggil tindakan tertentu di bagian Anda.

Contoh A. CE memahami nilai maksimum mungkin lebih tinggi daripada saat statistik terakhir dikumpulkan

Misalkan statistik terakhir dikumpulkan untuk OrderTable pada 2016-04-30, ketika maksimum OrderAddedDate adalah 2016-04-30. CE 120 (dan versi di atas) memahami bahwa kolom dalam OrderTable, yang memiliki data naik mungkin memiliki nilai yang lebih besar dari maksimum yang direkam oleh statistik. Pemahaman ini meningkatkan rencana kueri untuk pernyataan T-SQL SELECT seperti berikut ini.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';

Contoh B. CE memahami bahwa predikat yang difilter pada tabel yang sama sering berkorelasi

Dalam SELECT berikut kita melihat predikat yang difilter pada Model dan ModelVariant. Kami secara intuitif memahami bahwa ketika Model adalah 'Xbox' ada kemungkinan ModelVariant adalah 'Satu', mengingat bahwa Xbox memiliki varian yang disebut Satu.

Dimulai dengan CE 120, SQL Server memahami mungkin ada korelasi antara dua kolom pada tabel yang sama, Model dan ModelVariant. CE membuat perkiraan yang lebih akurat tentang berapa banyak baris yang akan dikembalikan oleh kueri, dan pengoptimal kueri menghasilkan rencana yang lebih optimal.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';

Contoh C. CE tidak lagi mengasumsikan korelasi apa pun antara predikat yang difilter dari tabel yang berbeda

Penelitian baru yang luas tentang beban kerja modern dan data bisnis aktual mengungkapkan bahwa filter predikat dari tabel yang berbeda biasanya tidak berkorelasi satu sama lain. Dalam kueri berikut, CE mengasumsikan tidak ada korelasi antara s.type dan r.date. Oleh karena itu CE membuat perkiraan yang lebih rendah dari jumlah baris yang dikembalikan.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';

Lihat juga