Pengoptimalan Rencana Eksekusi Sensitif terhadap Parameter

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru Azure SQL Database Azure SQL Managed Instance basis data SQL di Microsoft Fabric

Pengoptimalan Rencana Sensitif Parameter (PSP) adalah bagian dari keluarga 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 yang tidak seragam. Untuk informasi selengkapnya, lihat Sensitivitas Parameter dan Parameter dan Penggunaan Ulang Rencana Eksekusi.

Untuk informasi selengkapnya tentang solusi yang ada untuk skenario masalah ini, lihat:

Pengoptimalan PSP secara otomatis memungkinkan beberapa paket cache aktif untuk satu pernyataan berparameter. Rencana eksekusi cache mengakomodasi ukuran data yang berbeda berdasarkan nilai parameter runtime yang disediakan pelanggan.

Memahami parameterisasi

Dalam Mesin Database SQL Server, menggunakan parameter atau penanda parameter dalam pernyataan Transact-SQL (T-SQL) meningkatkan kemampuan mesin relasional untuk mencocokkan pernyataan T-SQL baru dengan rencana eksekusi yang ada dan sebelumnya dikompilasi dan mempromosikan penggunaan kembali rencana. Untuk informasi selengkapnya, lihat Parameterisasi Sederhana.

Anda juga dapat menggantikan parameterisasi sederhana bawaan SQL Server dengan menetapkan bahwa semua pernyataan SELECT, INSERT, UPDATE, dan DELETE dalam database diparameterisasi, dengan batasan tertentu. Untuk informasi selengkapnya, lihat Parameterisasi secara Paksa.

Implementasi pengoptimalan PSP

Selama kompilasi awal, histogram statistik kolom mengidentifikasi distribusi non-seragam dan mengevaluasi predikat parameter yang paling berisiko , hingga tiga dari semua predikat yang tersedia. Dengan kata lain, jika beberapa predikat dalam kueri yang sama memenuhi kriteria, pengoptimalan PSP memilih tiga teratas. Fitur PSP membatasi jumlah predikat yang dievaluasi, untuk menghindari membengkaknya cache rencana dan Penyimpanan Kueri (Query Store) jika terlalu banyak rencana.

Untuk rencana yang memenuhi syarat, kompilasi awal menghasilkan rencana dispatcher yang berisi logika pengoptimalan PSP yang disebut sebagai ekspresi dispatcher. Rencana dispatcher memetakan ke varian kueri berdasarkan predikat nilai batas rentang kardinalitas.

Terminology

Ekspresi pengendali

Mengevaluasi kardinalitas dari predikat berdasarkan nilai parameter runtime dan mengarahkan eksekusi ke varian kueri yang berbeda.

Rencana dispatcher

Rencana yang mengandung ekspresi penjadwal di-cache untuk kueri asli. Rencana dispatcher pada dasarnya adalah kumpulan predikat yang dipilih oleh fitur, dengan beberapa detail tambahan. Untuk setiap predikat yang dipilih, beberapa detail yang disertakan dalam rencana dispatcher adalah nilai batas tinggi dan batas rendah. Nilai-nilai ini digunakan untuk membagi nilai parameter menjadi kategori atau rentang yang berbeda. Rencana dispatcher juga berisi statistik yang digunakan untuk menghitung nilai batas.

Varian kueri

Saat dispatcher plan mengevaluasi kardinalitas predikat berdasarkan nilai parameter runtime, ia mengelompokkan nilai-nilai tersebut dan menghasilkan kueri turunan terpisah untuk dikompilasi dan dijalankan. Kueri-kueri anak ini disebut sebagai varian-varian kueri. Varian kueri memiliki rencana mereka sendiri di cache rencana dan Query Store.

Rentang kardinalitas predikat

Pada runtime, kardinalitas setiap predikat dievaluasi berdasarkan nilai parameter runtime. Dispatcher mengelompokkan nilai kardinalitas ke dalam tiga rentang kardinalitas predikat pada waktu kompilasi. Misalnya, fitur pengoptimalan PSP dapat membuat tiga rentang yang akan mewakili rentang kardinalitas rendah, sedang, dan tinggi, seperti yang ditunjukkan pada diagram berikut.

Dengan kata lain, ketika kueri dengan parameter awalnya dikompilasi, fitur pengoptimalan PSP menghasilkan rencana kerangka yang dikenal sebagai rencana dispatcher. Ekspresi dispatcher memiliki logika yang mem-bucketisasi kueri ke dalam varian kueri berdasarkan nilai runtime parameter. Ketika eksekusi aktual dimulai, dispatcher melakukan dua langkah:

dispatcher mengevaluasi ekspresi dispatcher-nya untuk set parameter yang diberikan untuk menghitung rentang kardinalitas.
dispatcher memetakan rentang ini ke varian kueri tertentu dan mengkompilasi dan menjalankan varian. Dengan memiliki beberapa varian kueri, fitur pengoptimalan PSP memungkinkan adanya beberapa rencana eksekusi untuk satu kueri.

Batas rentang kardinalitas dapat dilihat dalam XML ShowPlan dari rencana penjadwalan:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Petunjuk pengoptimalan PSP yang dihasilkan ditambahkan pada pernyataan SQL dalam ShowPlan XML dari suatu varian kueri. Petunjuk tidak dapat digunakan secara langsung dan tidak diurai jika ditambahkan secara manual. Petunjuk berisi elemen-elemen berikut:

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

ObjectID berasal dari modul (yaitu, prosedur tersimpan, fungsi, pemicu) yang merupakan bagian dari pernyataan saat ini; dengan asumsi bahwa pernyataan telah dihasilkan dari modul. Jika pernyataan adalah hasil dari SQL dinamis atau ad hoc (yaitu, sp_executesql) elemen ObjectID sama dengan 0.
QueryVariantID kira-kira setara dengan kombinasi rentang untuk semua predikat yang dipilih pengoptimalan PSP. Sebagai contoh, jika kueri memiliki dua predikat yang memenuhi syarat untuk PSP dan setiap predikat memiliki tiga rentang, akan ada sembilan rentang varian kueri bernomor 1-9.
rentang predikat adalah informasi rentang kardinalitas berdasarkan predikat yang dihasilkan dari ekspresi pengirim.

Dan, dalam XML ShowPlan dari varian kueri (di dalam elemen Dispatcher):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

Dimulai dengan SQL Server 2025 (17.x) dan kompatibilitas database 170, pengoptimalan PSP mencakup empat peningkatan berikut:
- Dukungan untuk pernyataan Bahasa Manipulasi Data (DML) seperti DELETE, INSERT, MERGE, dan UPDATE.
- Dukungan yang diperluas untuk tempdb.
- Pertimbangan tambahan yang diberikan dalam skenario di mana beberapa predikat yang memenuhi syarat ada pada tabel yang sama.
- Perubahan pada ekstensi peristiwa query_with_parameter_sensitivity yang mencakup bidang interesting_predicate_count, max_skewness, psp_optimization_supported, dan query_type sebelum ada perubahan pada SQL Server 2025 (17.x) dan kompatibilitas database 170. Sekarang mencakup bidang interesting_predicate_count, interesting_predicate_details, psp_optimization_supported, dan query_type. Untuk informasi selengkapnya, lihat bagian Kejadian yang Diperluas .
Fitur pengoptimalan PSP saat ini hanya berfungsi dengan predikat kesetaraan.
Paket dispatcher secara otomatis dibangun kembali jika ada perubahan distribusi data yang signifikan. Paket varian kueri kompilasi ulang secara independen sesuai kebutuhan, seperti jenis paket kueri lainnya, tunduk pada peristiwa kompilasi ulang default. Untuk informasi selengkapnya tentang kompilasi ulang, lihat Kompilasi Ulang Rencana Eksekusi.
Tampilan katalog sistem sys.query_store_plan penyimpanan kueri telah diubah untuk membedakan antara rencana yang dikompilasi normal, rencana dispatcher, dan rencana varian kueri. Tampilan katalog sistem Penyimpanan Kueri baru, sys.query_store_query_variant, berisi informasi tentang hubungan induk-anak antara kueri berparameter asli (juga dikenal sebagai kueri induk), paket dispatcher, dan varian kueri anak mereka.
Ketika ada beberapa predikat yang merupakan bagian dari tabel yang sama, pengoptimalan PSP memilih predikat yang memiliki ke condong data terbanyak berdasarkan histogram statistik yang mendasarinya. Misalnya, dengan SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, karena baik column1 = @predicate1 maupun column2 = @predicate2 berasal dari tabel yang sama, table1, hanya predikat yang paling miring yang akan dievaluasi oleh fitur. Namun, jika kueri contoh melibatkan operator seperti UNION, PSP mengevaluasi lebih dari satu predikat. Sebagai contoh, jika sebuah kueri memiliki karakteristik yang mirip dengan SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, PSP dalam kasus ini akan memilih paling banyak dua predikat, karena sistem memperlakukan skenario ini seolah-olah terdapat dua tabel yang berbeda. Perilaku yang sama dapat diamati dari kueri yang bergabung sendiri melalui alias tabel.

XML ShowPlan untuk varian kueri akan terlihat serupa dengan contoh berikut, di mana kedua predikat yang dipilih memiliki informasi masing-masing ditambahkan ke petunjuk terkait PLAN PER VALUE PSP.

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>
      <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">

Anda dapat memengaruhi ambang batas kecondongan saat ini yang digunakan oleh fitur pengoptimalan PSP, dengan menggunakan satu atau beberapa metode berikut:
- Bendera pelacakan kardinalitas (CE), seperti bendera pelacakan 9481 (tingkat global, sesi, atau kueri)
- Opsi konfigurasi yang lingkupnya pada database yang mencoba mengurangi penggunaan model CE, atau memengaruhi asumsi yang diasumsikan oleh model CE terkait independensi beberapa predikat. Ini sangat berguna dalam kasus di mana statistik multikolom tidak ada, yang mempengaruhi kemampuan optimisasi PSP untuk menilai relevansi predikat tersebut.
- Untuk informasi selengkapnya, lihat bagian Peningkatan Asumsi Korelasi untuk Beberapa Predikat dari dokumen teknis Mengoptimalkan rencana kueri Anda dengan Estimator Kardinalitas SQL Server 2014. Model CE yang lebih baru mencoba mengasumsikan beberapa korelasi dan ketergantungan lebih sedikit untuk konjungsi dan disjunksi predikat. Menggunakan model CE warisan dapat memengaruhi bagaimana pemilihan predikat dalam skenario gabungan multikolom dapat dihitung. Tindakan ini hanya boleh dipertimbangkan untuk skenario tertentu, dan tidak disarankan untuk menggunakan model CE warisan untuk sebagian besar beban kerja.
Pengoptimalan PSP saat ini mengkompilasi dan menjalankan setiap varian kueri sebagai pernyataan baru yang disiapkan, yang merupakan salah satu alasan bahwa varian kueri kehilangan keterkaitan dengan modul-modul induk jika rencana dispaker didasarkan pada modul (yaitu, prosedur tersimpan, pemicu, fungsi, tampilan, dan sebagainya). Sebagai pernyataan yang sudah disiapkan, object_id bukanlah sesuatu yang bisa dipetakan secara langsung menjadi objek dalam sys.objects, melainkan pada dasarnya adalah nilai yang dihitung berdasarkan hash internal dari teks batch. Untuk informasi selengkapnya, lihat bagian Tabel yang Dikembalikan dari sys.dm_exec_plan_attributes dokumentasi DMV.

Paket varian kueri ditempatkan di penyimpanan objek cache paket (CACHESTORE_OBJCP) sementara paket dispatcher ditempatkan di penyimpanan cache Paket SQL (CACHESTORE_SQLCP). Namun, fitur PSP akan menyimpan object_id induk dari varian kueri dalam atribut ObjectID yang merupakan bagian dari petunjuk PLAN PER VALUE yang ditambahkan oleh PSP ke ShowPlan XML jika kueri induk adalah bagian dari modul dan bukan T-SQL dinamis atau ad hoc. Statistik performa agregat untuk prosedur, fungsi, dan pemicu yang di-cache dapat terus digunakan untuk tujuan masing-masing. Statistik terkait eksekusi yang lebih terperinci, seperti yang ditemukan dalam tampilan yang serupa dengan sys.dm_exec_query_stats DMV, masih berisi data untuk varian kueri. Namun, hubungan antara object_id varian kueri dan objek dalam sys.objects tabel tidak selaras saat ini, tanpa pemrosesan tambahan dari XML ShowPlan untuk setiap varian kueri di mana statistik runtime yang lebih terperinci diperlukan. Informasi statistik runtime dan waktu tunggu untuk varian kueri dapat diperoleh dari Query Store tanpa menggunakan teknik penguraian ShowPlan XML tambahan jika Query Store diaktifkan.
Karena varian kueri PSP dijalankan sebagai pernyataan baru yang disiapkan, object_id tidak secara otomatis diekspos dalam berbagai DMV terkait cache paket sys.dm_exec_* tanpa memecah XML ShowPlan dan menerapkan teknik pencocokan pola teks (yaitu, pemrosesan XQuery tambahan). Hanya rancangan pengoptimalan PSP dispatcher yang saat ini menghasilkan ID objek induk yang sesuai. object_id diekspos dalam Penyimpanan Kueri, karena Penyimpanan Kueri memungkinkan model yang lebih relasional dibandingkan dengan hierarki cache rencana yang tersedia. Untuk informasi selengkapnya, lihat tampilan katalog sistem Penyimpanan Kueri sys.query_store_query_variant.

Considerations

Untuk mengaktifkan pengoptimalan PSP, aktifkan tingkat kompatibilitas database 160 untuk database yang anda sambungkan saat menjalankan kueri.
Untuk wawasan tambahan tentang fitur pengoptimalan PSP, disarankan agar integrasi Query Store diaktifkan dengan menyalakan Query Store. Contoh berikut mengaktifkan Penyimpanan Kueri untuk sebuah database yang telah ada sebelumnya yang disebut MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

Note

Dimulai dengan SQL Server 2022 (16.x), Penyimpanan Kueri sekarang diaktifkan secara default untuk semua database yang baru dibuat.

Untuk menonaktifkan pengoptimalan PSP di tingkat database, gunakan konfigurasi berlingkup database ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
Untuk menonaktifkan pengoptimalan PSP di tingkat kueri, gunakan petunjuk DISABLE_PARAMETER_SENSITIVE_PLAN.
Jika pengambilan parameter dinonaktifkan oleh trace flag 4136, PARAMETER_SNIFFING konfigurasi yang dicakup dalam ruang lingkup database, atau USE HINT('DISABLE_PARAMETER_SNIFFING') hint kueri, pengoptimalan PSP dinonaktifkan untuk beban kerja terkait dan konteks eksekusi. Untuk informasi selengkapnya, lihat Petunjuk kueri dan MENGUBAH KONFIGURASI CAKUPAN DATABASE.
Jumlah varian rencana unik per dispatcher yang disimpan dalam plan cache dibatasi untuk menghindari cache yang berlebihan. Ambang internal tidak didokumenkan. Karena setiap batch SQL memiliki potensi untuk membuat beberapa rencana, dan setiap rencana varian kueri memiliki entri independen dalam cache rencana, dimungkinkan untuk mencapai jumlah maksimum default entri rencana yang diizinkan. Jika tingkat pengeluaran cache rencana terpantau tinggi, atau ukuran CACHESTORE_OBJCPCACHESTORE_SQLCP dan berlebihan, Anda harus mempertimbangkan untuk menerapkan bendera pelacakan 174.
Jumlah varian rencana unik yang disimpan untuk kueri di Query Store dibatasi oleh opsi konfigurasi max_plans_per_query. Karena varian kueri dapat memiliki lebih dari satu rencana, total 200 rencana dapat ada per kueri dalam Query Store. Angka ini mencakup semua rencana varian permintaan untuk semua dispatcher yang termasuk dalam permintaan induk. Pertimbangkan max_plans_per_query memperbesar opsi konfigurasi Penyimpanan Kueri.
- Contoh bagaimana jumlah rencana unik dapat melebihi batas default Penyimpanan Kueri max_plans_per_query adalah skenario di mana terdapat perilaku berikut. Mari kita asumsikan bahwa Anda memiliki kueri dengan ID Kueri 10, yang memiliki dua paket dispatcher dan setiap paket dispatcher memiliki masing-masing 20 varian kueri (total 40 varian kueri). Jumlah rencana untuk ID Kueri 10 adalah 40 rencana untuk varian kueri dan dua rencana dispatcher. Ada kemungkinan juga bahwa kueri induk itu sendiri (ID Kueri 10) dapat memiliki 5 paket reguler (non-dispatcher). Ini membuat 47 rencana (40 dari varian kueri, 2 dispatcher, dan 5 rencana terkait non-PSP). Selanjutnya, jika setiap varian kueri juga memiliki rata-rata lima rencana, dalam skenario ini memungkinkan adanya lebih dari 200 rencana di Penyimpanan Kueri untuk kueri utama. Ini juga akan bergantung pada ketidakseimbangan data yang signifikan dalam himpunan data yang dirujuk oleh contoh kueri induk ini.
Untuk setiap pemetaan varian kueri ke dispatcher tertentu:
- Itu query_plan_hash unik. Kolom ini tersedia dalam sys.dm_exec_query_stats, dan tampilan manajemen dinamis dan tabel katalog lainnya.
- plan_handle adalah unik. Kolom ini tersedia dalam sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans, dan di Tampilan dan Fungsi Manajemen Dinamis lainnya, dan tabel katalog.
- query_hash adalah umum untuk pemetaan varian lain ke dispatcher yang sama, sehingga dimungkinkan untuk menentukan penggunaan sumber daya agregat untuk kueri yang hanya berbeda dengan nilai parameter input. Kolom ini tersedia dalam sys.dm_exec_query_stats, sys.query_store_query, dan tabel Tampilan Manajemen Dinamis dan katalog lainnya.
- sql_handle ini menjadi unik karena selama proses kompilasi teks kueri ditambahkan pengidentifikasi pengoptimalan PSP yang khusus. Kolom ini tersedia dalam sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans, dan di Tampilan dan Fungsi Manajemen Dinamis lainnya, dan tabel katalog. Informasi handle yang sama tersedia di Penyimpanan Kueri sebagai kolom last_compile_batch_sql_handle dalam tabel katalog sys.query_store_query.
- query_id unik di Penyimpanan Kueri. Kolom ini tersedia dalam sys.query_store_query, dan tabel-tabel katalog Query Store lainnya.

Pemaksaan rencana pada Query Store

Menggunakan prosedur tersimpan sp_query_store_force_plan dan sp_query_store_unforce_plan yang sama untuk beroperasi pada paket dispatcher atau varian.

Jika suatu varian dipaksakan, dispatcher induk tidak ikut dipaksakan. Jika dispatcher diwajibkan, hanya varian dari dispatcher tersebut yang dianggap memenuhi syarat untuk digunakan.

Varian yang sebelumnya dipaksakan oleh dispatcher lain menjadi tidak aktif tetapi mempertahankan status paksa hingga dispatcher mereka dipaksa lagi
Varian yang sebelumnya dipaksa dalam dispatcher yang sama yang telah menjadi tidak aktif dipaksa lagi

Perilaku petunjuk kueri Penyimpanan Kueri

Saat petunjuk Query Store ditambahkan ke varian kueri (kueri turunan), petunjuk diterapkan dengan cara yang sama seperti kueri non-PSP. Petunjuk variasi kueri memiliki prioritas yang lebih tinggi jika petunjuk juga telah diterapkan ke kueri induk di Query Store.
Saat petunjuk Penyimpanan Kueri ditambahkan ke kueri induk dan kueri turunan (varian kueri) tidak memiliki petunjuk Penyimpanan Kueri yang ada, kueri turunan (varian kueri) akan mewarisi petunjuk tersebut dari kueri induk.
Jika petunjuk kueri Penyimpanan Kueri dihapus dari kueri induk, kueri anak (varian kueri) juga memiliki petunjuk yang dihapus.
Jika RECOMPILE petunjuk ditambahkan ke kueri induk, sistem akan menghasilkan rencana non-PSP setelah rencana varian kueri yang ada dihapus dari cache rencana, karena fitur PSP tidak beroperasi pada kueri yang memiliki RECOMPILE petunjuk.
Hasil hint Penyimpanan Kueri dapat diamati menggunakan Peristiwa Diperluas query_store_hints_application_success dan query_store_hints_application_failed. Untuk tabel sys.query_store_query_hints, tabel berisi informasi mengenai petunjuk kueri yang telah diterapkan. Jika petunjuk hanya diterapkan pada kueri induk, katalog sistem berisi informasi petunjuk untuk kueri induk, tetapi tidak untuk kueri anaknya meskipun kueri anak mewarisi petunjuk kueri induk.

PSP dengan petunjuk kueri dan perilaku pemaksaan rencana dapat diringkas dalam tabel berikut:

Petunjuk varian kueri atau rencana	Elemen Induk memiliki petunjuk yang diaplikasikan pengguna	Elemen induk memiliki petunjuk yang telah diimplementasikan dengan umpan balik	Induk memiliki rencana paksa secara manual	Induk memiliki rencana paksa APC ¹
Petunjuk melalui pengguna	Petunjuk varian kueri	Petunjuk varian kueri	Petunjuk varian kueri	N/A
Petunjuk melalui umpan balik	Petunjuk varian kueri	Petunjuk varian kueri	Petunjuk varian kueri	N/A
Rencana dipaksa oleh pengguna	Varian kueri rencana paksa	Varian kueri rencana paksa	Varian kueri rencana paksa	Varian kueri rencana paksa
Rencana yang dipaksa oleh APC	Varian kueri rencana paksa	Varian kueri rencana paksa	Varian kueri rencana paksa	Varian kueri rencana paksa
Tidak ada petunjuk atau rencana paksa	Petunjuk pengguna induk	Tidak ada petunjuk	Tidak ada tindakan	Tidak ada tindakan

¹ Komponen koreksi rencana otomatis dari fitur penyetelan otomatis

"Event yang Diperpanjang"

parameter_sensitive_plan_optimization_skipped_reason: Terjadi ketika fitur "rencana sensitif terhadap parameter" dilewati. Gunakan peristiwa ini untuk mengamati alasan pengoptimalan PSP diabaikan.

Kueri berikut menunjukkan semua kemungkinan alasan mengapa PSP dilewati:
```
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'psp_skipped_reason_enum'
ORDER BY map_key;
```
parameter_sensitive_plan_optimization: Terjadi saat kueri menggunakan fitur pengoptimalan PSP. Hanya saluran debug. Beberapa bidang yang menarik mungkin:
- is_query_variant: menjelaskan apakah ini adalah paket dispatcher (induk) atau paket varian kueri (anak)
- predicate_count: jumlah predikat yang dipilih oleh PSP
- query_variant_id: menampilkan ID varian kueri. Nilai 0 berarti objek adalah rencana dispatcher (induk).
query_with_parameter_sensitivity: Kejadian ini ketika dipicu akan menampilkan jumlah predikat yang dianggap menarik oleh fitur, rincian lebih lanjut dalam format json mengenai predikat yang menarik tersebut, serta apakah PSPO didukung untuk predikat tersebut.
Contoh output dari peristiwa yang query_with_parameter_sensitivity diperluas

Field	Value
interesting_predicate_count	3
interesting_predicate_details	{"Predicates":[{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75}]}
psp_optimization_supported	True
query_type	195

Perilaku Audit SQL Server

Pengoptimalan PSP menyediakan data audit untuk pernyataan rencana dispatcher, dan varian kueri apa pun yang terkait dengan dispatcher. Kolom additional_information dalam Audit SQL Server juga menyediakan informasi tumpukan T-SQL yang sesuai untuk varian kueri. Menggunakan database MyNewDatabase sebagai contoh, jika database ini memiliki tabel yang disebut T2 dan prosedur tersimpan dengan nama usp_test, setelah eksekusi prosedur tersimpan usp_test, log audit mungkin berisi entri-entri berikut:

action_id	object_name	pernyataan	additional_information
AUSC			`<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>`
EX	usp_test	exec usp_test 300
SL	T2	pilih * dari dbo.t2 di mana ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	pilih * dari dbo.t2 di mana ID=@id opsi (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	`tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
EX	usp_test	exec usp_test 60000
SL	T2	pilih * dari dbo.t2 di mana ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	pilih * dari dbo.t2 di mana ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`

Masalah yang diketahui

Issue	Tanggal ditemukan	Status	Tanggal penyelesaian
Pengecualian Pelanggaran Akses terjadi di Query Store di SQL Server 2022 (16.x) pada kondisi tertentu. Anda mungkin mengalami pengecualian pelanggaran akses saat pengoptimalan PSP Query Store diaktifkan. Untuk informasi selengkapnya, lihat pembaruan di Pengoptimalan Paket Sensitif Parameter, Mengapa?	Maret 2023	Resolved	Agustus 2023 (SQL Server 2022 CU 7)
Pengecualian pelanggaran akses dapat terjadi pada replika sekunder yang dapat dibaca dalam kondisi tertentu di SQL Server 2025 (17.x). Anda mungkin mengalami pengecualian pelanggaran akses ketika pengoptimalan PSP diaktifkan pada database sekunder yang dapat dibaca dan telah dikonfigurasi untuk menggunakan fitur Penyimpanan Kueri untuk replika sekunder yang dapat dibaca.	September 2025	Resolved	Januari 2026 (SQL Server 2025 CU 1)

Resolved

Pengecualian pelanggaran akses dapat terjadi pada replika sekunder yang dapat dibaca dalam kondisi tertentu

(Diselesaikan pada Januari 2026)

Kueri yang memenuhi kondisi berikut dapat mengalami pelanggaran akses saat rencana sensitif parameter (PSP) varian kueri tidak dapat mengevaluasi keadaan tetap dari pernyataan dispatcher induknya:

Dijalankan pada replika sekunder
Sensitif terhadap pengendus parameter
Memenuhi syarat untuk pengoptimalan rencana sensitif parameter (PSP)

Pengecualian pelanggaran akses terjadi di Penyimpanan Kueri di SQL Server 2022 dalam kondisi tertentu

(Diselesaikan pada Maret 2023)

Note

SQL Server 2022 (16.x) Pembaruan Kumulatif 7 merilis beberapa perbaikan untuk kondisi race yang dapat menyebabkan pelanggaran akses.

Masalah ini terjadi karena kondisi balapan yang dapat menyebabkan ketika statistik runtime untuk kueri yang dijalankan sedang dipertahankan dari representasi memori penyimpanan kueri (ditemukan di petugas memori MEMORYCLERK_QUERYDISKSTORE_HASHMAP) ke versi disk penyimpanan kueri. Statistik runtime, yang ditampilkan sebagai Statistik Runtime, disimpan dalam memori untuk jangka waktu tertentu, yang ditentukan oleh DATA_FLUSH_INTERVAL_SECONDS opsi SET QUERY_STORE pernyataan (nilai defaultnya adalah 15 menit). Anda dapat menggunakan kotak dialog Query Store di Management Studio untuk memasukkan nilai untuk Interval Flush Data (Menit), yang dikonversi secara internal menjadi detik. Jika sistem berada di bawah tekanan memori, statistik runtime dapat ditulis ke dalam disk lebih awal daripada yang ditentukan oleh opsi DATA_FLUSH_INTERVAL_SECONDS. Saat terdapat utas latar belakang tambahan yang terkait dengan pembersihan rencana kueri dari Penyimpanan Kueri (yaitu, opsi Penyimpanan Kueri STALE_QUERY_THRESHOLD_DAYS dan/atau MAX_STORAGE_SIZE_MB), dan ketika kueri diambil dari Penyimpanan Kueri, ada skenario di mana varian kueri dan/atau pernyataan dispatcher terkait bisa menjadi tereferensi sebelum waktunya. Ini dapat mengakibatkan pelanggaran akses pada operasi menyisipkan atau menghapus varian kueri ke dalam Penyimpanan Kueri.

Lihat bagian Keterangan dari artikel "Cara Kerja Query Store Mengumpulkan Data" untuk informasi lebih lanjut tentang operasi Query Store.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-02-13

Bagikan melalui

Pengoptimalan Rencana Eksekusi Sensitif terhadap Parameter

Memahami parameterisasi

Implementasi pengoptimalan PSP

Terminology

Ekspresi pengendali

Rencana dispatcher

Varian kueri

Rentang kardinalitas predikat

Remarks

Considerations

Pemaksaan rencana pada Query Store

Perilaku petunjuk kueri Penyimpanan Kueri

"Event yang Diperpanjang"

Perilaku Audit SQL Server

Masalah yang diketahui

Resolved

Pengecualian pelanggaran akses dapat terjadi pada replika sekunder yang dapat dibaca dalam kondisi tertentu

Pengecualian pelanggaran akses terjadi di Penyimpanan Kueri di SQL Server 2022 dalam kondisi tertentu

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: