Fitur pemrosesan kueri cerdas secara rinci

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

Artikel ini berisi deskripsi mendalam tentang berbagai fitur pemrosesan kueri cerdas (IQP), catatan rilis, dan detail selengkapnya. Keluarga fitur pemrosesan kueri cerdas (IQP) mencakup fitur dengan dampak luas yang meningkatkan performa beban kerja yang ada dengan upaya implementasi minimal untuk diadopsi.

Anda dapat membuat beban kerja memenuhi syarat secara otomatis untuk pemrosesan kueri cerdas dengan mengaktifkan tingkat kompatibilitas database yang berlaku untuk database. Anda dapat mengatur ini menggunakan Transact-SQL. Misalnya, untuk mengatur tingkat kompatibilitas database ke SQL Server 2022 (16.x):

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 160;

Untuk informasi selengkapnya tentang perubahan yang diperkenalkan dengan versi baru, lihat:

Gabungan Adaptif mode Batch

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)), Azure SQL Database

Fitur Adaptive Joins mode batch memungkinkan pemilihan metode Hash Join atau Nested Loops Join ditunda hingga setelah input pertama dipindai, dengan menggunakan satu rencana yang tersimpan di cache. Operator Gabungan Adaptif menentukan ambang batas yang digunakan untuk memutuskan kapan harus beralih ke paket Nested Loops. Oleh karena itu, rencana Anda dapat secara dinamis beralih ke strategi join yang lebih baik selama eksekusi.

Untuk informasi selengkapnya, termasuk cara menonaktifkan gabungan Adaptif tanpa mengubah tingkat kompatibilitas, lihat Memahami gabungan Adaptif.

Eksekusi berselang-seling untuk MSTVFs

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)), Azure SQL Database

Fungsi bernilai tabel multi-pernyataan (MSTVF) adalah jenis fungsi yang ditentukan pengguna yang dapat menerima parameter, menjalankan beberapa pernyataan T-SQL, dan RETURN tabel.

Eksekusi tersisip membantu mengatasi masalah performa beban kerja yang disebabkan oleh perkiraan kardinalitas tetap yang terkait dengan MSTVF. Dengan eksekusi interleaved, jumlah baris sebenarnya dari fungsi digunakan untuk membuat keputusan rencana kueri berikutnya yang lebih tepat.

MSTVF memiliki perkiraan kardinalitas tetap sebesar 100 mulai SQL Server 2014 (12.x), dan sebesar 1 untuk versi SQL Server yang lebih lama.

Eksekusi berselang-seling mengubah batas searah antara fase pengoptimalan dan eksekusi dalam eksekusi kueri tunggal serta memungkinkan rencana beradaptasi berdasarkan estimasi kardinalitas yang direvisi. Selama pengoptimalan, jika mesin database menemukan kandidat untuk eksekusi interleaved yang menggunakan fungsi bernilai tabel multi-pernyataan (MSTVF), pengoptimalan dijeda, mengeksekusi subtree yang terkait, menangkap perkiraan kardinalitas yang akurat, dan kemudian melanjutkan pengoptimalan untuk operasi berikutnya.

Gambar berikut menampilkan output Statistik Kueri Langsung, yang merupakan suatu subset dari keseluruhan rencana eksekusi yang menunjukkan dampak perkiraan kardinalitas tetap dari MSTVF

Anda dapat melihat alur baris aktual vs. perkiraan baris. Ada tiga area penting dari rencana (alurnya dari kanan ke kiri):

  • MSTVF Table Scan memiliki estimasi tetap sebanyak 100 baris. Namun, dalam contoh ini, ada 527.597 baris yang melewati operasi Table Scan MSTVF ini, seperti yang terlihat di Statistik Kueri Langsung melalui 527597 of 100 aktual dibanding estimasi - sehingga estimasi tetap tersebut meleset secara signifikan.
  • Untuk operasi Nested Loops, diasumsikan bahwa hanya 100 baris akan dikembalikan oleh sisi luar join. Mengingat tingginya jumlah baris yang benar-benar dikembalikan oleh MSTVF, Anda kemungkinan lebih baik dengan algoritma penggabungan yang berbeda sama sekali.
  • Untuk operasi Hash Match, perhatikan simbol peringatan kecil, yang dalam hal ini menunjukkan tumpahan ke disk.

Diagram alur baris rencana eksekusi versus perkiraan baris.

Bandingkan rencana sebelumnya dengan rencana aktual yang dihasilkan saat eksekusi berselang-seling diaktifkan:

Diagram rencana eksekusi yang berselang-seling.

  • Pemindaian tabel MSTVF sekarang mencerminkan perkiraan kardinalitas yang akurat. Perhatikan juga urutan ulang pemindaian tabel ini dan operasi lainnya.
  • Dan mengenai algoritma gabungan, kami telah beralih dari operasi Perulangan Berlapis ke operasi Hash Match sebagai gantinya, yang lebih optimal mengingat banyaknya baris yang terlibat.
  • Perhatikan juga bahwa kita tidak lagi memiliki peringatan spill, karena kita mengalokasikan lebih banyak memori berdasarkan jumlah baris aktual yang berasal dari pemindaian tabel MSTVF.

Pernyataan yang memenuhi syarat eksekusi interleaved

Pernyataan yang mereferensikan MSTVF dalam eksekusi interleaved saat ini harus hanya-baca dan tidak boleh merupakan bagian dari operasi modifikasi data. Selain itu, MSTVFs tidak memenuhi syarat untuk eksekusi interleaved jika MSTVFs tersebut tidak menggunakan konstanta runtime.

Manfaat eksekusi berselang-seling

Secara umum, semakin besar selisih antara jumlah baris yang diperkirakan dan jumlah baris aktual, ditambah dengan semakin banyaknya operasi hilir dalam rencana eksekusi, semakin besar dampaknya terhadap performa.

Secara umum, kueri mendapat manfaat dari eksekusi interleaved dalam kondisi berikut:

  • Ada perbedaan besar antara perkiraan dibandingkan dengan jumlah baris aktual untuk kumpulan hasil sementara (dalam hal ini, MSTVF).

  • Dan kueri keseluruhan sensitif terhadap perubahan ukuran hasil perantara. Ini biasanya terjadi ketika terdapat pohon kompleks di atas subtree tersebut dalam rencana kueri.

    Dasar SELECT * dari MSTVF tidak mendapat manfaat dari eksekusi yang saling berhubungan.

Overhead eksekusi berselang-seling

Overhead harus seminimal mungkin, bahkan tidak ada. MSTVF sudah dimaterialisasi sebelum diperkenalkannya eksekusi interleaved, namun bedanya sekarang kami memungkinkan optimisasi tertunda dan kemudian menggunakan perkiraan kardinalitas dari kumpulan baris yang dimaterialisasi. Seperti halnya paket eksekusi apa pun yang melibatkan perubahan, beberapa paket dapat berubah sedemikian rupa sehingga, meskipun kardinalitas untuk subtree menjadi lebih baik, kita justru memperoleh paket eksekusi yang lebih buruk untuk kueri secara keseluruhan. Mitigasi dapat mencakup mengembalikan level kompatibilitas atau dengan menggunakan Query Store untuk memaksa versi rencana yang tidak mengalami regresi.

Eksekusi berselang-seling dan eksekusi berturut-turut

Setelah rencana eksekusi interleaved disimpan di cache, rencana dengan perkiraan yang direvisi pada eksekusi pertama digunakan untuk eksekusi berikutnya tanpa membuat instance eksekusi interleaved kembali.

Lacak aktivitas eksekusi berselang-seling

Anda dapat melihat atribut penggunaan dalam rencana eksekusi kueri aktual:

Atribut Rencana Eksekusi Description
ContainsInterleavedExecutionCandidates Berlaku untuk simpul QueryPlan . Jika benar, berarti rencana tersebut berisi kandidat eksekusi yang saling berhubungan.
IsInterleavedExecuted Atribut dari elemen RuntimeInformation di bawah RelOp tersebut untuk simpul TVF. Ketika benar, berarti operasi diwujudkan sebagai bagian dari operasi eksekusi yang saling berhubungan.

Anda juga dapat melacak kemunculan eksekusi berselang-seling melalui Extended Events berikut:

XEvent Description
interleaved_exec_status Event ini dipicu ketika eksekusi interleaved sedang berlangsung.
interleaved_exec_stats_update Kejadian ini menjelaskan perkiraan kardinalitas yang diperbarui oleh eksekusi yang saling berhubungan.
Interleaved_exec_disabled_reason Kejadian ini diaktifkan ketika kueri dengan kandidat yang mungkin untuk eksekusi interleaved sebenarnya tidak mendapatkan eksekusi interleaved.

Kueri harus dijalankan agar eksekusi yang diselingi dapat merevisi perkiraan kardinalitas MSTVF. Namun, rencana eksekusi perkiraan tetap menunjukkan saat terdapat kandidat eksekusi yang berselang-seling melalui atribut showplan ContainsInterleavedExecutionCandidates.

Penyimpanan cache eksekusi berselang-seling

Jika rencana dihapus atau dikeluarkan dari cache, setelah eksekusi kueri, ada kompilasi baru yang menggunakan eksekusi interleaved. Pernyataan yang menggunakan OPTION (RECOMPILE) membuat rencana baru menggunakan eksekusi interleaved dan tidak menyimpannya ke cache.

Eksekusi berselang-seling dan interoperabilitas Query Store

Rencana menggunakan eksekusi interleaved dapat dipaksa. Rencananya adalah versi yang telah mengoreksi perkiraan kardinalitas berdasarkan eksekusi awal.

Nonaktifkan eksekusi interleaved tanpa mengubah tingkat kompatibilitas

Eksekusi berselang-seling dapat dinonaktifkan pada cakupan database atau cakupan pernyataan, dengan tetap mempertahankan tingkat kompatibilitas database 140 atau lebih tinggi. Untuk menonaktifkan eksekusi interleaved untuk semua eksekusi kueri yang berasal dari database, jalankan hal berikut dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;

Saat diaktifkan, pengaturan ini muncul sebagai diaktifkan di sys.database_scoped_configurations. Untuk mengaktifkan kembali eksekusi interleaved untuk semua eksekusi kueri yang berasal dari database, jalankan hal berikut dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;

Anda juga dapat menonaktifkan eksekusi interleaved untuk kueri tertentu dengan menetapkan DISABLE_INTERLEAVED_EXECUTION_TVF sebagai petunjuk kueri USE HINT. Contohnya:

SELECT [fo].[Order Key],
       [fo].[Quantity],
       [fol].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
     INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession', '1-01-2013', '10-15-2014') AS [fol]
         ON [fo].[Order Key] = [fol].[Order Key]
        AND [fo].[City Key] = [fol].[City Key]
        AND [fo].[Customer Key] = [fol].[Customer Key]
        AND [fo].[Stock Item Key] = [fol].[Stock Item Key]
        AND [fo].[Order Date Key] = [fol].[Order Date Key]
        AND [fo].[Picked Date Key] = [fol].[Picked Date Key]
        AND [fo].[Salesperson Key] = [fol].[Salesperson Key]
        AND [fo].[Picker Key] = [fol].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

Petunjuk kueri USE HINT lebih diprioritaskan daripada konfigurasi berlingkup database atau pengaturan trace flag.

Inlining UDF skalar

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)), Azure SQL Database

Inlining UDF skalar secara otomatis mengubah UDF skalar menjadi ekspresi relasional. Sistem tersebut menyisipkannya ke dalam kueri SQL pemanggil. Transformasi ini meningkatkan performa beban kerja yang memanfaatkan UDF skalar. Scalar UDF inlining memungkinkan pengoptimalan operasi berdasarkan biaya di dalam UDF. Hasilnya efisien, berorientasi pada set, dan paralel, bukan rencana eksekusi yang tidak efisien, iteratif, dan serial. Fitur ini diaktifkan secara default di bawah tingkat kompatibilitas database 150 atau lebih tinggi.

Untuk informasi selengkapnya, lihat Scalar UDF inlining.

Kompilasi yang ditangguhkan variabel tabel

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)), Azure SQL Database

Kompilasi yang ditangguhkan variabel tabel meningkatkan kualitas rencana dan performa keseluruhan untuk kueri yang mereferensikan variabel tabel. Selama pengoptimalan dan kompilasi rencana awal, fitur ini menyebarluaskan perkiraan kardinalitas yang didasarkan pada jumlah baris variabel tabel aktual. Informasi jumlah baris yang tepat ini kemudian digunakan untuk mengoptimalkan operasi rencana hilir.

Dengan kompilasi yang ditangguhkan variabel tabel, kompilasi pernyataan yang mereferensikan variabel tabel ditangguhkan hingga eksekusi pernyataan aktual pertama. Perilaku kompilasi yang ditangguhkan ini identik dengan perilaku tabel sementara. Perubahan ini menghasilkan penggunaan kardinalitas aktual alih-alih tebakan satu baris asli.

Untuk mengaktifkan kompilasi yang ditangguhkan variabel tabel, aktifkan tingkat kompatibilitas database 150 atau lebih tinggi untuk database yang Anda sambungkan saat kueri berjalan.

Kompilasi yang ditangguhkan variabel tabel tidak mengubah karakteristik variabel tabel lainnya. Misalnya, fitur ini tidak menambahkan statistik kolom ke variabel tabel.

Kompilasi yang ditangguhkan variabel tabel tidak meningkatkan frekuensi kompilasi ulang. Sebaliknya, yang berubah adalah tempat kompilasi awal dilakukan. Rencana yang di-cache tersebut dihasilkan berdasarkan jumlah baris variabel tabel pada kompilasi tertunda awal. Rencana yang di-cache digunakan kembali oleh kueri berturut-turut. Ini akan digunakan kembali hingga rencana eksekusi dikeluarkan dari cache atau dikompilasi ulang.

Jumlah baris variabel tabel yang digunakan untuk kompilasi rencana awal mewakili nilai umum yang mungkin berbeda dari perkiraan jumlah baris tetap. Jika berbeda, operasi hilir mendapat manfaat. Performa mungkin tidak ditingkatkan oleh fitur ini jika jumlah baris variabel tabel bervariasi secara signifikan di seluruh eksekusi.

Menonaktifkan kompilasi yang ditangguhkan variabel tabel tanpa mengubah tingkat kompatibilitas

Nonaktifkan kompilasi tertunda untuk variabel tabel di tingkat database atau pernyataan, dengan tetap mempertahankan tingkat kompatibilitas database 150 atau lebih tinggi. Untuk menonaktifkan kompilasi yang ditangguhkan variabel tabel untuk semua eksekusi kueri yang berasal dari database, jalankan contoh berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

Untuk mengaktifkan kembali kompilasi yang ditangguhkan variabel tabel untuk semua eksekusi kueri yang berasal dari database, jalankan contoh berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

Anda juga dapat menonaktifkan kompilasi yang ditangguhkan variabel tabel untuk kueri tertentu dengan menetapkan DISABLE_DEFERRED_COMPILATION_TV sebagai petunjuk kueri USE HINT. Contohnya:

DECLARE @LINEITEMS TABLE (
    L_OrderKey INT NOT NULL,
    L_Quantity INT NOT NULL);

INSERT @LINEITEMS
SELECT L_OrderKey,
       L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT O_OrderKey,
       O_CustKey,
       O_OrderStatus,
       L_QUANTITY
FROM ORDERS, @LINEITEMS
WHERE O_ORDERKEY = L_ORDERKEY
      AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

Pengoptimalan Rencana Sensitivitas Parameter

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

Pengoptimalan Parameter Sensitivity Plan (PSP) adalah bagian dari rangkaian fitur pemrosesan kueri cerdas. Ini membahas skenario di mana satu paket cache untuk kueri berparameter tidak optimal untuk semua kemungkinan nilai parameter masuk. Ini adalah kasus dengan distribusi data nonuniform.

Pemrosesan kueri aproksimatif

Pemrosesan kueri perkiraan merupakan keluarga fitur baru. Ini melakukan agregasi pada kumpulan data besar ketika daya tanggap lebih penting daripada presisi absolut. Contohnya adalah menghitung COUNT(DISTINCT()) di seluruh 10 miliar baris, untuk ditampilkan di dasbor. Dalam hal ini, presisi absolut tidak penting, tetapi responsivitas sangat penting.

Perkiraan Jumlah Unik

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)), Azure SQL Database

Fungsi agregat APPROX_COUNT_DISTINCT baru mengembalikan perkiraan jumlah nilai non-null unik dalam grup.

Fitur ini tersedia dimulai dengan SQL Server 2019 (15.x), terlepas dari tingkat kompatibilitasnya.

Untuk informasi selengkapnya, lihat APPROX_COUNT_DISTINCT.

Perkiraan Persentil

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

Fungsi agregat ini menghitung persentil untuk himpunan data besar dengan batas kesalahan berbasis peringkat yang dapat diterima untuk membantu membuat keputusan cepat dengan menggunakan perkiraan fungsi agregat persentil.

Untuk informasi selengkapnya, lihat APPROX_PERCENTILE_DISC dan APPROX_PERCENTILE_CONT

Mode batch pada rowstore

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)), Azure SQL Database

Mode batch pada rowstore memungkinkan eksekusi mode batch untuk beban kerja analitik tanpa memerlukan indeks penyimpan kolom. Fitur ini mendukung eksekusi dalam mode batch dan filter bitmap untuk heap di disk dan indeks B-tree. Mode batch pada rowstore memungkinkan dukungan untuk semua operator yang diaktifkan mode batch yang ada.

Kueri yang ada yang dapat memperoleh sebagian besar manfaat dari mode batch di rowstore meliputi:

  • Gabungan hash di antara tabel rowstore besar
  • Kueri dengan GROUP BY pada banyak nilai berbeda
  • Fungsi agregat seperti SUM, , COUNT, MINMAX,AVG
  • Kueri fungsi jendela dengan OVER, , dan PARTITION BY, termasuk fungsi agregat, ORDER BYROW_NUMBERdanRANK

Untuk informasi selengkapnya tentang performa fungsi jendela, lihat Pertimbangan performa dalam OVER referensi klausa.

Note

Dokumentasi secara umum menggunakan istilah B-tree untuk merujuk pada indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Gambaran umum eksekusi mode batch

SQL Server 2012 (11.x) memperkenalkan fitur baru untuk mempercepat beban kerja analitik: indeks penyimpan kolom. Kasus penggunaan dan performa indeks penyimpan kolom meningkat di setiap rilis SQL Server berikutnya. Membuat indeks penyimpan kolom pada tabel dapat meningkatkan performa untuk beban kerja analitis. Namun, ada dua set teknologi yang terkait tetapi berbeda:

  • Dengan indeks penyimpan kolom, kueri analitik hanya mengakses data di kolom yang mereka butuhkan. Pemadatan halaman dalam format penyimpan kolom juga lebih efektif daripada kompresi dalam indeks rowstore tradisional.
  • Dengan pemrosesan mode batch, operator kueri memproses data dengan lebih efisien. Mereka memproses sekumpulan baris alih-alih satu per satu. Banyak peningkatan skalabilitas lainnya terkait dengan pemrosesan mode batch. Untuk informasi selengkapnya tentang mode batch, lihat mode eksekusi.

Dua set fitur bekerja sama untuk meningkatkan input/output (I/O) dan pemanfaatan CPU:

  • Dengan menggunakan indeks columnstore, lebih banyak data Anda dapat dimuat ke dalam memori. Itu mengurangi beban kerja I/O.
  • Pemrosesan mode batch menggunakan CPU lebih efisien.

Kedua teknologi tersebut saling memanfaatkan jika memungkinkan. Misalnya, agregat mode batch dapat dievaluasi sebagai bagian dari pemindaian indeks penyimpan kolom. Selain itu, data columnstore yang dikompresi diproses jauh lebih efisien dengan menggunakan pengodean run-length bersama join mode batch dan agregasi mode batch.

Namun, penting untuk dipahami bahwa kedua fitur tersebut independen:

  • Anda bisa mendapatkan paket mode baris yang menggunakan indeks penyimpan kolom.
  • Anda bisa memperoleh rencana mode batch yang hanya menggunakan indeks rowstore.

Anda biasanya mendapatkan hasil terbaik saat menggunakan dua fitur tersebut bersama-sama. Sebelum SQL Server 2019 (15.x), pengoptimal kueri SQL Server mempertimbangkan pemrosesan mode batch hanya untuk kueri yang melibatkan setidaknya satu tabel dengan indeks penyimpan kolom.

Indeks penyimpan kolom mungkin tidak sesuai untuk beberapa aplikasi. Aplikasi mungkin menggunakan beberapa fitur lain yang tidak didukung dengan indeks penyimpan kolom. Misalnya, modifikasi langsung tidak kompatibel dengan kompresi penyimpanan berformat kolom. Oleh karena itu, pemicu tidak didukung pada tabel dengan indeks penyimpan kolom berkluster. Lebih penting lagi, indeks columnstore menambah overhead untuk pernyataan DELETE dan UPDATE.

Untuk beberapa beban kerja transaksional-analitis hibrida, beban tambahan dari beban kerja transaksional lebih besar daripada manfaat yang diperoleh dari penggunaan indeks penyimpanan kolom. Skenario tersebut dapat memperoleh manfaat dari peningkatan penggunaan CPU dengan menggunakan pemrosesan mode batch saja. Itulah sebabnya fitur batch-mode-on-rowstore mempertimbangkan penggunaan mode batch untuk semua kueri, terlepas dari jenis indeks yang digunakan.

Beban kerja yang mungkin mendapat manfaat dari mode batch di rowstore

Beban kerja berikut mungkin mendapat manfaat dari mode batch di rowstore:

  • Bagian penting dari beban kerja terdiri dari kueri analitik. Biasanya, kueri ini menggunakan operator seperti gabungan atau agregat yang memproses ratusan ribu baris atau lebih.
  • Beban kerja terikat CPU. Jika hambatannya adalah I/O, disarankan agar Anda mempertimbangkan indeks penyimpan kolom, jika memungkinkan.
  • Membuat indeks columnstore menimbulkan overhead yang terlalu besar pada bagian transaksional dari beban kerja Anda. Atau, membuat indeks penyimpan kolom tidak layak karena aplikasi Anda bergantung pada fitur yang belum didukung dengan indeks penyimpan kolom.

Note

Mode batch pada rowstore hanya membantu dengan mengurangi konsumsi CPU. Jika hambatan Anda terkait I/O, dan data belum di-cache (cache "dingin"), mode batch pada rowstore tidak meningkatkan waktu penyelesaian kueri. Demikian pula, jika tidak ada memori yang cukup pada komputer untuk menyimpan semua data, peningkatan performa tidak mungkin terjadi.

Perubahan apa dengan mode batch pada rowstore?

Mode batch pada rowstore mengharuskan database berada pada tingkat kompatibilitas 150.

Bahkan jika kueri tidak mengakses tabel apa pun dengan indeks penyimpan kolom, prosesor kueri menggunakan heuristik untuk memutuskan apakah akan mempertimbangkan mode batch. Heuristik terdiri dari pemeriksaan ini:

  1. Pemeriksaan awal terhadap ukuran tabel, operator yang digunakan, dan perkiraan kardinalitas dalam kueri masukan.
  2. Titik pemeriksaan tambahan, saat pengoptimal menemukan rencana baru yang lebih murah untuk kueri. Jika rencana alternatif ini tidak menggunakan mode batch secara signifikan, pengoptimal berhenti menjelajahi alternatif mode batch.

Jika mode batch pada rowstore digunakan, Anda akan melihat mode eksekusi aktual sebagai mode batch di rencana kueri. Operator pemindaian menggunakan mode batch untuk tumpukan pada disk dan indeks pohon B. Pemindaian dalam mode batch ini dapat mengevaluasi filter bitmap dalam mode batch. Anda mungkin juga melihat operator lain dalam mode batch pada rencana eksekusi. Contohnya adalah gabungan hash, agregat berbasis hash, pengurutan, agregat jendela, filter, perangkaian, dan operator skalar komputasi.

Remarks

Rencana kueri tidak selalu menggunakan mode batch. Pengoptimal Kueri mungkin memutuskan bahwa mode batch tidak bermanfaat untuk kueri.

Ruang pencarian Pengoptimal Kueri berubah. Jadi, jika Anda mendapatkan rencana mode baris, rencana tersebut mungkin tidak sama dengan rencana yang Anda peroleh pada tingkat kompatibilitas yang lebih rendah. Dan jika Anda mendapatkan rencana mode batch, rencana tersebut mungkin tidak sama dengan rencana yang Anda peroleh dengan indeks penyimpanan kolom.

Rencana eksekusi mungkin juga berubah untuk kueri yang menggabungkan indeks columnstore dan rowstore karena pemindaian rowstore mode batch yang baru.

Ada batasan saat ini untuk mode batch baru pada pemindaian rowstore:

  • Ini tidak akan dimulai untuk tabel OLTP dalam memori atau untuk indeks apa pun selain tumpukan pada disk dan pohon B.
  • Ini juga tidak akan berlaku jika kolom objek besar (LOB) diambil atau difilter. Batasan ini mencakup kumpulan kolom jarang dan kolom XML.

Ada kueri yang tidak digunakan mode batch bahkan dengan indeks penyimpan kolom. Contohnya adalah kueri yang melibatkan kursor. Pengecualian yang sama ini juga berlaku untuk batch mode pada rowstore.

Mengonfigurasi mode batch pada rowstore

Konfigurasi BATCH_MODE_ON_ROWSTORE cakupan database aktif secara default.

Anda dapat menonaktifkan mode batch pada rowstore tanpa mengubah tingkat kompatibilitas database:

-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

Anda dapat menonaktifkan mode batch pada rowstore melalui konfigurasi cakupan database. Tetapi Anda masih bisa mengambil alih pengaturan di tingkat kueri dengan menggunakan ALLOW_BATCH_MODE petunjuk kueri. Contoh berikut mengaktifkan mode batch pada rowstore meskipun fitur tersebut dinonaktifkan melalui konfigurasi cakupan database:

SELECT [Tax Rate],
       [Lineage Key],
       [Salesperson Key],
       SUM(Quantity) AS SUM_QTY,
       SUM([Unit Price]) AS SUM_BASE_PRICE,
       COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key] <= DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION (RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

Anda juga dapat menonaktifkan mode batch pada rowstore untuk kueri tertentu dengan menggunakan DISALLOW_BATCH_MODE petunjuk kueri. Lihat contoh berikut:

SELECT [Tax Rate],
       [Lineage Key],
       [Salesperson Key],
       SUM(Quantity) AS SUM_QTY,
       SUM([Unit Price]) AS SUM_BASE_PRICE,
       COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key] <= DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION (RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

Fitur umpan balik pemrosesan kueri

Fitur umpan balik pemrosesan kueri adalah bagian dari keluarga fitur pemrosesan kueri Cerdas.

Umpan balik pemrosesan kueri adalah prosesor kueri di SQL Server, Azure SQL Database, dan Azure SQL Managed Instance menggunakan data historis tentang eksekusi kueri untuk memutuskan apakah kueri mungkin menerima bantuan dari satu atau beberapa perubahan ke cara dikompilasi dan dijalankan. Data kinerja dikumpulkan dalam Query Store, beserta berbagai saran untuk meningkatkan eksekusi kueri. Jika berhasil, kami menyimpan perubahan ini secara permanen di disk, dalam memori, dan/atau di Query Store untuk penggunaan di masa mendatang. Jika saran tidak menghasilkan peningkatan yang memadai, saran akan dibuang, dan kueri terus dijalankan tanpa umpan balik tersebut.

Untuk informasi tentang fitur umpan balik pemrosesan kueri mana yang tersedia dalam rilis SQL Server yang berbeda, atau di Azure SQL Database atau Azure SQL Managed Instance, lihat Pemrosesan kueri cerdas di database SQL atau artikel berikut untuk setiap fitur umpan balik.

Umpan balik pemberian memori

Umpan balik pemberian memori telah diperkenalkan dalam gelombang selama rilis utama SQL Server sebelumnya.

Umpan balik pemberian memori mode batch

Untuk informasi tentang umpan balik alokasi memori mode Batch, kunjungi Umpan balik alokasi memori mode Batch.

Umpan balik pemberian memori mode baris

Untuk informasi tentang umpan balik pemberian memori mode baris, kunjungi Umpan balik pemberian memori mode baris.

Persentil dan persistensi mode memori memberikan umpan balik

Untuk informasi tentang umpan balik pemberian memori mode persentil dan persistensi, kunjungi Umpan balik pemberian memori mode persentil dan persistensi.

Tingkat paralelisme (DOP) umpan balik

Untuk informasi tentang umpan balik DOP, kunjungi Umpan balik derajat paralelisme (DOP).

Umpan balik estimasi kardinalitas (CE)

Untuk informasi tentang umpan balik CE, kunjungi umpan balik estimasi kardinalitas (CE).

Pemaksaan rencana yang dioptimalkan dengan Query Store

Untuk informasi tentang pemaksaan paket yang dioptimalkan dengan Query Store, kunjungi Pemaksaan paket yang dioptimalkan dengan Query Store.

Konten terkait

  • Last updated on