Bagikan melalui

Pengoptimalan rencana parameter opsional (OPPO)

Berlaku untuk: SQL Server 2025 (17.x) Database SQL Azure SQL databasedi Microsoft Fabric

Pengoptimalan rencana parameter opsional (OPPO) meningkatkan kualitas rencana kueri untuk kueri yang menyertakan parameter opsional. Dalam kueri ini, rencana eksekusi optimal bergantung pada apakah nilai parameter berada NULL pada waktu eksekusi. Istilah parameter opsional mengacu pada variasi tertentu dari masalah rencana sensitif parameter (PSP), di mana nilai parameter pada waktu eksekusi menentukan apakah kueri memerlukan pencarian atau pemindaian.

Gambaran Umum

Kueri yang menggunakan parameter opsional sering menyertakan predikat yang menerapkan filter secara kondisional berdasarkan apakah nilai parameter disediakan. Pola umumnya adalah sebagai berikut:

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

Ketika @p IS NOT NULL, pencarian indeks pada col1 sering kali merupakan rencana eksekusi yang paling efisien. Ketika @p IS NULL, predikat dievaluasi menjadi TRUE, dan proses pemindaian mungkin lebih tepat. Tanpa OPPO, Mesin Database SQL Server harus mengkompilasi dan menyimpan satu rencana eksekusi yang berlaku untuk kedua kasus. Karena rencana berbasis pencarian tidak valid ketika @p IS NULL, pengoptimal sering memilih rencana berbasis pemindaian konservatif untuk semua eksekusi. Pilihan ini dapat mengakibatkan pilihan rencana yang tidak efisien dan penggunaan sumber daya yang berlebihan untuk eksekusi selektif.

Teknik petunjuk tradisional seperti OPTIMIZE FOR tidak efektif dalam skenario ini, karena rencana harus tetap benar untuk kedua status parameter.

OPPO menggunakan infrastruktur pengoptimalan rencana adaptif (Multiplan) yang diperkenalkan dengan pengoptimalan Parameter Sensitive Plan (PSP). Infrastruktur ini menghasilkan dan menyimpan beberapa rencana eksekusi untuk satu pernyataan, yang memungkinkan OPPO membuat asumsi yang berbeda berdasarkan nilai parameter yang digunakan dalam kueri.

Terminologi dan cara kerjanya

OPPO dibangun pada kerangka kerja pengoptimalan rencana adaptif (Multiplan), yang juga digunakan oleh Pengoptimalan Rencana Sensitif Parameter. Dengan menggunakan Multiplan, Mesin Database dapat menghasilkan dan menyimpan beberapa rencana eksekusi untuk satu kueri.

Saat Mesin Database mendeteksi pola parameter opsional yang memenuhi syarat, mesin tersebut membuat:

  • Rencana dispatcher
  • Satu atau beberapa varian kueri, masing-masing dioptimalkan untuk status nilai parameter tertentu

Pada waktu eksekusi:

  • Mesin Database mengevaluasi nilai parameter.
  • Dispatcher Multiplan memilih varian kueri yang sesuai.
  • Varian kueri yang dipilih dijalankan.

Setelah Mesin Database memilih varian kueri, Mesin Database menyederhanakan predikat berdasarkan nilai parameter aktual. Pertimbangkan ekspresi berikut:

@p1 IS NULL

Dalam contoh ini, ekspresi disederhanakan ke hasil konstanta untuk varian yang dipilih. Penggabungan hasil konstan ini memungkinkan pengoptimal untuk menghasilkan rencana eksekusi yang tidak valid dalam rencana yang dapat digunakan kembali secara tunggal.

Dengan memilih rencana dengan cara ini, OPPO memungkinkan eksekusi yang efisien untuk status parameter yang berbeda tanpa memerlukan penulisan ulang kueri atau petunjuk kueri manual.

Pengoptimalan OPPO dan PSP mengatasi berbagai variasi masalah rencana terkait parameter:

  • Pengoptimalan PSP memilih rencana berdasarkan perkiraan perbedaan kardinalitas untuk predikat kesetaraan atau rentang.

  • OPPO memilih paket berdasarkan apakah nilai parameter adalah NULL.

Satu kueri mungkin mendapat manfaat dari kedua atau salah satu fitur tergantung pada predikat yang terlibat.

Pola kueri yang didukung

Pengoptimalan parameter opsional berlaku untuk kueri di mana pemeriksaan pada parameter memengaruhi validitas perencanaan eksekusi. Sebagai contoh, pertimbangkan formulir web aplikasi untuk perusahaan realty yang memungkinkan pemfilteran opsional pada jumlah kamar tidur untuk daftar tertentu. OPPO berlaku untuk predikat parameter opsional yang bersifat disjungtif seperti:

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

Bahkan jika penanda parameter dapat mengendus @bedrooms = 10 parameter, dan Anda 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 Anda dapat menulis ulang kueri ini sebagai dua pernyataan terpisah. Bergantung pada nilai runtime parameter, Anda dapat mengevaluasi contoh berikut:

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

Fitur ini dapat mencapai hal ini dengan menggunakan infrastruktur Multiplan, yang memungkinkan pembentukan rencana dispatcher untuk mengirimkan varian kueri.

OPPO menyematkan petunjuk kueri yang dihasilkan PLAN PER VALUE sistem (optional_predicate) dalam metadata rencana untuk menghubungkan setiap varian kueri dengan kondisi parameternya. Petunjuk ini dihasilkan oleh sistem dan disematkan dalam teks kueri rencana. Petunjuk ini tidak valid untuk digunakan oleh aplikasi atau diterapkan secara manual.

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 predikat berdasarkan nilai parameter, memungkinkan rencana berbasis pemindaian dibuat.

    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, sehingga database yang menggunakan tingkat kompatibilitas 170 (default di SQL Server 2025 (17.x)) menggunakan OPPO secara default.

Anda dapat memastikan bahwa database menggunakan OPPO di SQL Server 2025 (17.x) dengan menjalankan pernyataan berikut:

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Untuk menonaktifkan OPPO pada sebuah database, nonaktifkan konfigurasi dengan cakupan database OPTIONAL_PARAMETER_OPTIMIZATION.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Menggunakan pengoptimalan paket parameter opsional melalui petunjuk kueri

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Gunakan petunjuk kueri untuk menonaktifkan OPPO untuk kueri tertentu. Tentukan petunjuk melalui USE HINT klausa. Untuk informasi selengkapnya, lihat petunjuk Kueri .

Petunjuk ini berfungsi di bawah tingkat kompatibilitas apa pun, dan menimpa OPTIONAL_PARAMETER_OPTIMIZATION konfigurasi yang ditetapkan untuk cakupan database.

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Tentukan petunjuk kueri langsung dalam kueri atau melalui Query Store hints.

"Event yang Diperpanjang"

Gunakan peristiwa yang diperluas berikut untuk pemecahan masalah dan diagnostik. Kejadian-kejadian ini tidak diperlukan untuk menggunakan fitur.

  • optional_parameter_optimization_skipped_reason: Terjadi ketika OPPO memutuskan bahwa kueri tidak memenuhi syarat untuk pengoptimalan. Kejadian yang diperluas ini mengikuti pola yang sama dengan parameter_sensitive_plan_optimization_skipped_reason event yang digunakan oleh pengoptimalan PSP. Karena sebuah kueri dapat menghasilkan optimalisasi PSP dan varian kueri OPPO secara bersamaan, periksa kedua kejadian tersebut untuk memahami mengapa salah satu atau tidak satu pun dari kedua fitur itu digunakan.

    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 ini mengikuti pola yang sama dengan peristiwa query_with_parameter_sensitivity yang digunakan pengoptimalan PSP. Ini termasuk bidang tambahan yang tersedia dalam peningkatan untuk pengoptimalan PSP.

    Bidang-bidang ini ditampilkan:

    • jumlah predikat yang dianggap menarik oleh fitur,
    • rincian lebih lanjut dalam format JSON mengenai predikat yang menarik, dan
    • apakah OPPO didukung untuk predikat atau predikat.

Remarks

  • XML ShowPlan untuk varian kueri terlihat mirip dengan contoh berikut. Fitur memilih predikat dengan informasi masing-masing ditambahkan ke petunjuk PLAN PER VALUE (optional_predicate).
<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

Kelayakan dan batasan kueri

OPPO hanya berlaku untuk kueri yang memenuhi syarat untuk pengoptimalan Multiplan. Fitur ini tidak diterapkan dalam skenario yang mencakup:

  • Kueri yang menggunakan variabel lokal alih-alih parameter
  • Kueri dikompilasi dengan OPTION (RECOMPILE)
  • Kueri dijalankan dengan SET ANSI_NULLS OFF
  • Pernyataan dengan parameterisasi otomatis

Konten terkait

  • Last updated on