Estimasi Kardinalitas (SQL Server)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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 pada setiap tingkat rencana kueri, yang disebut sebagai kardinalitas rencana tersebut.
• 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, peningkatan kardinalitas menyebabkan perkiraan biaya yang lebih baik dan, pada gilirannya, rencana eksekusi yang lebih cepat.

Estimasi kardinalitas (CE) 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.

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, formulasi kueri alternatif atau langkah lain dapat dilakukan, dan hal ini ditunjukkan:

• Kueri dengan predikat yang menggunakan operator perbandingan antara kolom yang berbeda dari tabel yang sama.
• Kueri dengan predikat yang menggunakan operator, dan salah satu kondisi berikut terpenuhi:
  • Tidak ada statistik untuk kolom yang terlibat di kedua sisi operator.
  • Distribusi nilai dalam statistik tidak seragam, tetapi kueri mencari kumpulan nilai yang sangat selektif. Situasi ini terutama berlaku jika operator yang digunakan bukan operator sama dengan (=).
  • Predikat menggunakan operator perbandingan tidak sama (!=) atau operator logis NOT.
• Kueri yang menggunakan salah satu fungsi bawaan SQL Server atau fungsi bernilai skalar yang ditentukan pengguna yang argumennya bukan nilai konstanta.
• Kueri yang melibatkan penggabungan kolom melalui operator aritmatika atau penggabungan 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 besar akan dihasilkan 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 memiliki kueri penting yang rencana eksekusinya berubah menjadi lebih lambat karena perubahan CE antarversi. Anda memiliki teknik dan alat untuk mengidentifikasi kueri yang berkinerja lebih lambat karena masalah CE. Dan Anda memiliki opsi untuk cara mengatasi masalah performa yang sedang terjadi.

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:

• Independensi: 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 merata dan setiap nilai memiliki frekuensi yang sama.

• Penahanan (Sederhana): Kueri pengguna untuk data yang ada. Misalnya, untuk join kesetaraan antara dua tabel, perhitungkan selektivitas predikat¹ pada setiap histogram masukan sebelum menggabungkan histogram untuk memperkirakan selektivitas join.

• Penyertaan: 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 pada langkah tersebut dianggap cocok dengan nilai dalam predikat.

  ¹ 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 lebih menyerupai kueri data di dunia nyata.
• Pembatasan Sederhana berubah menjadi Pembatasan Dasar: Pengguna mungkin melakukan kueri terhadap data yang tidak ada. Misalnya, untuk gabungan kesetaraan antara dua tabel, kita menggunakan tabel dasar histogram untuk memperkirakan selektivitas gabungan, lalu memperhitungkan selektivitas predikat.

Menggunakan Query Store untuk menilai versi CE

Mulai SQL Server 2016 (13.x), Query Store adalah alat yang berguna untuk menganalisis performa kueri Anda. Setelah Query Store diaktifkan, fitur ini akan mulai melacak kinerja kueri seiring waktu, bahkan jika rencana eksekusi berubah. Pantau Query Store untuk kinerja kueri berbiaya tinggi atau yang mengalami penurunan performa. Untuk informasi selengkapnya, lihat Memantau kinerja dengan menggunakan Query Store.

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

Gunakan Extended Events 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 di 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 ramah pengguna.

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

Untuk informasi tentang extended event yang disesuaikan untuk SQL Database, lihat Extended event 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 memiliki LEGACY_CARDINALITY_ESTIMATIONkonfigurasi yang diatur ke OFF.

   4. Kosongkan Query Store Anda. Dalam database Anda, pastikan Query Store 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 grafis ditampilkan.

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

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

   • CardinalityEstimationModelVersion.

   • Perkiraan Jumlah Baris.

   • Biaya I/O Estimasi, dan beberapa properti Estimasi serupa yang berkaitan dengan kinerja aktual, bukan prediksi jumlah baris.

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

   • Mode Eksekusi Sebenarnya. 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 selain langkah pendahuluan.

13. Bandingkan nilai properti CE dari dua eksekusi.

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

    • Apakah kueri Anda menggunakan rencana eksekusi yang berbeda dengan dua estimasi CE yang berbeda?

    • Apakah kueri Anda berjalan lebih lambat di bawah CE terbaru?

    • Kecuali kueri Anda berjalan lebih baik dan dengan rencana eksekusi yang berbeda saat menggunakan CE versi lama, Anda hampir pasti sebaiknya menggunakan CE terbaru.

    • Namun, jika kueri Anda berjalan dengan rencana eksekusi yang lebih cepat saat menggunakan CE versi lama, pertimbangkan untuk memaksa sistem agar menggunakan rencana eksekusi yang lebih cepat tersebut dan mengabaikan CE. Dengan cara ini, Anda dapat menggunakan CE terbaru untuk semuanya, sambil tetap menggunakan paket yang lebih cepat dalam satu kasus khusus.

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 dapat Anda gunakan untuk mengaktifkan paket yang lebih baik, diurutkan dari lingkup terbesar hingga 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 dapat menggunakan LEGACY_CARDINALITY_ESTIMATION opsi konfigurasi tercakup database, agar seluruh database menggunakan CE yang lebih lama, sambil mempertahankan peningkatan lain dalam pengoptimal kueri.

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

• Anda dapat memaksakan LEGACY_CARDINALITY_ESTIMATION melalui fitur petunjuk Query Store agar satu kueri menggunakan CE yang lebih lama tanpa mengubah kueri.

• Paksa penggunaan rencana kueri yang berbeda dengan Query Store.

Tingkat kompatibilitas database

Anda dapat memastikan database Anda berada pada tingkat tertentu dengan menggunakan kode Transact-SQL berikut untuk ALTER DATABASE tingkat kompatibilitas (Transact-SQL).

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 menggunakan penyempurnaan 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 dalam database SQL, 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.

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 ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

Ubah kueri untuk menggunakan hint

Mulai dari SQL Server 2016 (13.x) SP1, ubah kueri untuk menggunakan Petunjuk KueriUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

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

Tetapkan petunjuk Query Store

Kueri dapat dipaksa untuk menggunakan estimator kardinalitas lama tanpa memodifikasi kueri, dengan menggunakan petunjuk Query Store.

1. Identifikasi kueri dalam tampilan katalog Query Store sys.query_store_query_text dan sys.query_store_query. Misalnya, cari kueri yang dijalankan menurut 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%';
   

2. Contoh berikut menggunakan hint Query Store untuk memaksa estimator kardinalitas lama pada query_id39, tanpa mengubah kueri:

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Cara memaksa rencana kueri tertentu

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

Query Store memberi Anda berbagai cara untuk memaksa sistem menggunakan rencana kueri tertentu:

• Jalankan sys.sp_query_store_force_plan.

• Di SQL Server Management Studio (SSMS), perluas node Query Store, klik kanan Top Resource Consuming Nodes, lalu pilih View Top Resource Consuming Nodes. Tampilan menunjukkan tombol berlabel Paksa Rencana dan Lepaskan Paksa Rencana.

Untuk informasi selengkapnya tentang Query Store, lihat Memantau kinerja menggunakan Query Store.

Pelipatan konstanta dan evaluasi ekspresi selama Estimasi Kardinalitas

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

Selain itu, beberapa ekspresi yang tidak disederhanakan sebagai konstanta, tetapi argumennya diketahui pada waktu kompilasi, baik argumen tersebut berupa parameter maupun konstanta, dievaluasi oleh penaksir ukuran kumpulan hasil (kardinalitas) yang menjadi 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 AUTO_CREATE_STATISTICS opsi database AUTO_UPDATE_STATISTICS dan SET adalah ON (pengaturan default), atau bahwa 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 Waktu Kompilasi untuk Estimasi Kardinalitas.

• Pastikan bahwa ekspresi yang hanya berisi konstanta dalam kondisi kueri Anda dapat disederhanakan melalui constant folding atau dapat dievaluasi saat 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.

Contoh peningkatan CE

Bagian ini menjelaskan contoh kueri yang mendapat manfaat dari peningkatan yang diterapkan di CE dalam rilis terbaru. Ini adalah informasi latar yang tidak memerlukan tindakan khusus dari 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 di OrderTable, yang memiliki data naik mungkin memiliki nilai yang lebih besar dari maksimum yang dicatat oleh statistik. Pemahaman ini meningkatkan rencana kueri untuk pernyataan Transact-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

Pada SELECT berikut, kita dapat melihat predikat terfilter 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 estimasi 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';

Konten terkait

• Umpan balik estimasi kardinalitas (CE)
• Monitor dan Selaraskan Kinerja
• Mengoptimalkan Rencana Kueri Anda dengan Estimator Kardinalitas SQL Server 2014
• Petunjuk kueri (Transact-SQL)
• GUNAKAN Petunjuk Kueri HINT
• Meningkatkan database menggunakan Asisten Penyetelan Kueri
• Memantau kinerja menggunakan Query Store
• Panduan arsitektur pemrosesan kueri
• Petunjuk penyimpanan kueri
• Pemrosesan kueri cerdas dalam database SQL
• Atur bendera pelacakan dengan DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints