Bagikan melalui

Pengoptimalan rencana parameter opsional (OPPO)

Berlaku untuk:Berlaku untuk: SQL Server 2025 (17.x) Azure SQL DatabaseDatabase SQL di Microsoft Fabric

Istilah parameter opsional mengacu pada variasi tertentu dari masalah rencana sensitif parameter (PSP) di mana nilai parameter sensitif yang ada selama eksekusi kueri, mengontrol apakah kita perlu melakukan pencarian atau memindai tabel. Contoh sederhananya adalah sesuatu seperti:

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
      OR @p IS NULL);

Dalam contoh ini, SQL Server selalu memilih paket yang memindai tabel Table1, bahkan jika ada indeks pada Table1(col1). Rencana pencarian mungkin tidak dapat dilakukan dengan NULL. Teknik petunjuk kueri, seperti OPTIMIZE FOR, mungkin tidak berguna untuk jenis masalah PSP ini karena saat ini tidak ada operator yang secara dinamis mengubah pencarian indeks menjadi pemindaian selama eksekusi. Kombinasi seek-scan> semacam ini pada runtime mungkin juga tidak efektif, karena perkiraan kardinalitas di atas operator tersebut kemungkinan tidak akurat. Hasilnya adalah pilihan rencana yang tidak efisien dan pemberian memori yang berlebihan untuk kueri yang lebih kompleks dengan pola kueri yang sama.

Fitur Optimasi Rencana Parameter Opsional (OPPO) menggunakan infrastruktur optimasi rencana adaptif (Multiplan) yang diperkenalkan dengan peningkatan Optimasi Rencana Sensitif Parameter, yang memungkinkan pembangkitan beberapa rencana dari satu pernyataan. Ini memungkinkan fitur untuk membuat asumsi yang berbeda tergantung pada nilai parameter yang digunakan dalam kueri. Selama waktu eksekusi kueri, OPPO memilih paket yang sesuai:

  • di mana nilai parameter IS NOT NULL, sistem menggunakan strategi pencarian atau sesuatu yang lebih optimal daripada strategi pemindaian penuh.
  • di mana nilai parameter adalah NULL, ia menggunakan rencana pemindaian.

Sebagai bagian dari keluarga fitur pengoptimalan rencana adaptif yang mencakup pengoptimalan Rencana Sensitif Parameter, OPPO memberikan solusi untuk komponen kedua dari set fitur Multiplan, yang mencakup kemampuan pencarian dinamis.

  • Predikat kesetaraan

    WHERE column1 = @p

  • Pencarian dinamis

    WHERE (column1 = @p1 OR @p1 IS NULL)
  AND (column2 = @p2 OR @p2 IS NOT NULL)

Terminologi dan cara kerjanya

Term Description
Ekspresi pengirim Ekspresi ini mengevaluasi kardinalitas predikat berdasarkan nilai parameter runtime, dan merutekan eksekusi ke varian kueri yang berbeda.
Rencana dispatcher Rencana yang mengandung ekspresi dispatcher disimpan dalam cache untuk kueri asli. Paket dispatcher pada dasarnya adalah kumpulan predikat yang dipilih oleh fitur, dengan beberapa detail tambahan. Untuk setiap predikat yang dipilih, beberapa rincian yang disertakan dalam rencana dispatcher, termasuk nilai batas tinggi dan rendah. Nilai-nilai ini digunakan untuk membagi nilai parameter menjadi wadah atau rentang yang berbeda. Rencana dispatcher juga berisi statistik yang digunakan untuk menghitung nilai batas.
Varian kueri Karena paket dispatcher mengevaluasi kardinalitas predikat berdasarkan nilai parameter runtime, paket tersebut merumuskannya dan menghasilkan kueri anak terpisah untuk dijalankan. Kueri anak ini disebut varian kueri. Varian dari kueri memiliki rencana mereka sendiri dalam cache rencana dan Penyimpan Kueri. Dengan kata lain, dengan menggunakan varian kueri yang berbeda, kami mencapai tujuan dari beberapa rencana untuk satu kueri.

Sebagai contoh, pertimbangkan formulir web aplikasi untuk perusahaan realty yang memungkinkan pemfilteran opsional pada jumlah kamar tidur untuk daftar tertentu. Antipattern umum dapat mengekspresikan filter opsional sebagai:

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

Bahkan jika parameter @bedrooms = 10 diendus oleh penggunaan penanda parameter, dan kita tahu bahwa kardinalitas untuk jumlah kamar tidur kemungkinan sangat rendah, pengoptimal tidak menghasilkan rencana yang mencari indeks yang ada di kolom kamar tidur karena itu bukan rencana yang valid untuk kasus di mana @bedrooms adalah NULL. Rencana yang dihasilkan tidak menyertakan pemindaian indeks.

Bayangkan jika ini bisa ditulis ulang sebagai dua pernyataan terpisah. Tergantung pada nilai runtime parameter, kita dapat mengevaluasi sesuatu seperti ini:

IF @bedrooms IS NULL
    SELECT * FROM Properties;
ELSE
    SELECT * FROM Properties
    WHERE bedrooms = @bedrooms;

Kita dapat mencapai hal ini dengan menggunakan infrastruktur optimisasi rencana adaptif, yang memungkinkan dibuatnya rencana pemrosesan yang mendistribusikan dua varian kueri.

Mirip dengan rentang kardinalitas predikat yang digunakan pengoptimalan PSP, OPPO menyematkan petunjuk kueri yang dapat digunakan sistem dengan teks kueri dari rencana tersebut. Petunjuk ini tidak valid untuk digunakan oleh aplikasi atau jika Anda mencoba menggunakannya sendiri.

Melanjutkan dengan contoh sebelumnya,

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO dapat menghasilkan dua varian kueri yang mungkin memiliki atribut berikut yang ditambahkan ke dalam XML Showplan:

  • @bedrooms adalah NULL. Varian kueri melipat kueri asli untuk mencapai rencana pemindaian.

    SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms is NULL))

  • @bedrooms IS NOT NULL

    SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms is NULL))

Optimalkan rencana dengan menggunakan parameter opsional

Untuk mengaktifkan OPPO untuk database, prasyarat berikut diperlukan:

  • Database harus menggunakan tingkat kompatibilitas 170.
  • Konfigurasi OPTIONAL_PARAMETER_OPTIMIZATION cakupan database harus diaktifkan.

Konfigurasi OPTIONAL_PARAMETER_OPTIMIZATION cakupan database diaktifkan secara default. Ini berarti bahwa database yang menggunakan tingkat kompatibilitas 170 (default di SQL Server 2025) menggunakan OPPO secara default.

Anda dapat memastikan bahwa database menggunakan OPPO di SQL Server 2025 dengan menjalankan pernyataan berikut:

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Untuk menonaktifkan pengoptimalan rencana parameter opsional untuk database, nonaktifkan OPTIONAL_PARAMETER_OPTIMIZATION konfigurasi yang mencakup database.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Menggunakan pengoptimalan rencana parameter opsional melalui petunjuk kueri

Anda bisa menggunakan DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION petunjuk kueri untuk menonaktifkan pengoptimalan rencana parameter opsional untuk kueri tertentu. Petunjuk harus ditentukan melalui USE HINT klausul . Untuk informasi selengkapnya, lihat petunjuk Kueri .

Petunjuk berfungsi di bawah tingkat kompatibilitas apa pun, dan menggantikan pengaturan konfigurasi cakupan database OPTIONAL_PARAMETER_OPTIMIZATION.

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Petunjuk kueri dapat ditentukan langsung dalam kueri, atau melalui petunjuk Penyimpanan Kueri.

"Event yang Diperpanjang"

  • optional_parameter_optimization_skipped_reason: Terjadi ketika OPPO memutuskan bahwa kueri tidak memenuhi syarat untuk pengoptimalan. Peristiwa yang diperluas ini mengikuti pola yang sama dengan peristiwa parameter_sensitive_plan_optimization_skipped_reason yang digunakan oleh pengoptimalan PSP. Karena kueri dapat menghasilkan pengoptimalan PSP dan varian kueri OPPO, Anda harus memeriksa kedua kejadian untuk memahami mengapa salah satu atau tidak satupun dari fitur-fitur tersebut diaktifkan.

    Kueri berikut menunjukkan semua kemungkinan alasan mengapa PSP dilewati:

    SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

  • query_with_optional_parameter_predicate: Peristiwa yang diperluas mengikuti pola yang sama dengan peristiwa query_with_parameter_sensitivity yang digunakan oleh pengoptimalan PSP. Ini termasuk bidang tambahan yang tersedia dalam peningkatan untuk pengoptimalan PSP yang terdiri dari menampilkan jumlah predikat yang dianggap menarik oleh fitur, serta format json yang memberikan lebih banyak detail mengenai predikat yang menarik, dan dukungan OPPO untuk predikat tersebut jika ada.

Remarks

  • XML ShowPlan untuk varian kueri akan terlihat mirip dengan contoh berikut, di mana predikat yang dipilih memiliki informasi masing-masing ditambahkan ke PLAN PER VALUE, optional_predicate hint.
<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">
  • Contoh output dari peristiwa yang query_with_optional_parameter_predicate diperluas
Field Value
optional_parameter_optimization_supported True
optional_parameter_predicate_count 3
predicate_details {"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
query_type 193

Konten terkait

  • Last updated on