Bagikan melalui

Pengoptimalan Rencana Sensitif Parameter

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

Pengoptimalan Parameter Sensitif Plan (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 Kembali 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 mengambil alih perilaku parameterisasi sederhana default SQL Server dengan menentukan bahwa semua SELECTpernyataan , , INSERTUPDATE, dan DELETE dalam database diparameterkan, tunduk pada batasan tertentu. Untuk informasi selengkapnya, lihat Parameterisasi 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 kembungnya cache paket dan Penyimpanan Kueri (jika Penyimpanan Kueri diaktifkan) dengan terlalu banyak paket.

Untuk rencana yang memenuhi syarat, kompilasi awal menghasilkan rencana dispatcher yang berisi logika pengoptimalan PSP yang disebut ekspresi dispatcher. Paket dispatcher memetakan untuk mengkueri varian berdasarkan predikat nilai batas rentang kardinalitas.

Terminology

Ekspresi pengendali

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

Rencana dispatcher

Paket yang berisi ekspresi dispatcher di-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 detail yang disertakan dalam paket dispatcher adalah nilai batas tinggi dan rendah . Nilai-nilai ini digunakan untuk membagi nilai parameter menjadi wadah atau rentang yang berbeda. Paket dispatcher juga berisi statistik yang digunakan untuk menghitung nilai batas.

Varian kueri

Sebagai paket dispatcher mengevaluasi kardinalitas predikat berdasarkan nilai parameter runtime, ia mem-bucketisasi nilai-nilai tersebut, dan menghasilkan kueri anak terpisah untuk dikompilasi dan dijalankan. Kueri anak ini disebut varian kueri. Varian kueri memiliki paket mereka sendiri di cache paket dan Penyimpanan Kueri.

Rentang kardinalitas predikat

Pada runtime, kardinalitas setiap predikat dievaluasi berdasarkan nilai parameter runtime. Dispatcher merumuskan 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.

Diagram memperlihatkan batas Parameter Rencana Sensitif.

Dengan kata lain, ketika kueri berparameter awalnya dikompilasi, fitur pengoptimalan PSP menghasilkan rencana shell yang dikenal sebagai paket 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. Berdasarkan memiliki beberapa varian kueri, fitur pengoptimalan PSP mencapai memiliki beberapa rencana untuk satu kueri.

Batas rentang kardinalitas dapat dilihat dalam XML ShowPlan dari paket pengiriman:

<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 ke pernyataan SQL di XML ShowPlan dari 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.
  • predikat rentang adalah predikat informasi rentang kardinalitas yang dihasilkan dari ekspresi dispatcher.

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 Menyusun 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 keduanya column1 = @predicate1 dan column2 = @predicate2 berasal dari tabel yang sama, table1, hanya predikat yang paling condong yang akan dievaluasi oleh fitur . Namun, jika kueri contoh melibatkan operator seperti UNION, PSP mengevaluasi lebih dari satu predikat. Sebagai contoh, jika kueri memiliki karakteristik yang mirip SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicatedengan , PSP memilih paling banyak dua predikat dalam hal ini, karena sistem memperlakukan skenario ini seolah-olah mereka adalah dua tabel yang berbeda. Perilaku yang sama dapat diamati dari kueri yang bergabung sendiri melalui alias tabel.

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

    <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 ke condong saat ini yang digunakan oleh fitur pengoptimalan PSP, dengan satu atau beberapa metode berikut:

    • Bendera pelacakan kardinalitas (CE), seperti bendera pelacakan 9481 (tingkat global, sesi, atau kueri)

    • Opsi konfigurasi tercakup database yang mencoba menurunkan model CE yang digunakan, atau memengaruhi asumsi yang dilakukan model CE sehubungan dengan independensi beberapa predikat. Ini sangat berguna dalam kasus di mana statistik multi-kolom tidak ada, yang memengaruhi kemampuan pengoptimalan PSP untuk mengevaluasi pencalonan predikat tersebut.

    • Untuk informasi selengkapnya, lihat bagian Peningkatan Asumsi Korelasi untuk Beberapa Predikat dari bagian Mengoptimalkan rencana kueri Anda dengan laporan resmi Estimator Kardinalitas SQL Server 2014. Model CE yang lebih baru mencoba untuk mengasumsikan beberapa korelasi dan lebih sedikit kemerdekaan 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 asosiasinya dengan modul induk' object_id jika rencana dispatcher didasarkan pada modul (yaitu, prosedur tersimpan, pemicu, fungsi, tampilan, dan sebagainya). Sebagai pernyataan yang disiapkan, object_id bukan apa pun yang dapat dipetakan sys.objects ke objek secara langsung tetapi pada dasarnya adalah nilai terhitung berdasarkan hash internal 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 varian kueri dalam atribut ObjectID yang merupakan bagian dari petunjuk PLAN PER VALUE yang ditambahkan PSP ke XML ShowPlan 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 mirip sys.dm_exec_query_stats dengan DMV masih berisi data untuk varian kueri, namun, hubungan antara object_id varian kueri dan objek dalam sys.objects tabel saat ini tidak selaras, tanpa pemrosesan tambahan XML ShowPlan untuk setiap varian kueri di mana statistik runtime yang lebih terperinci diperlukan. Informasi statistik runtime dan tunggu untuk varian kueri dapat diperoleh dari Penyimpanan Kueri tanpa teknik penguraian XML ShowPlan tambahan jika Penyimpanan Kueri diaktifkan.

  • Karena varian kueri PSP dijalankan sebagai pernyataan baru yang disiapkan, object_id tidak secara otomatis diekspos dalam berbagai DMV terkait sys.dm_exec_* cache paket tanpa menghancurkan XML ShowPlan dan menerapkan teknik pencocokan pola teks (yaitu, pemrosesan XQuery tambahan). Hanya paket pengoptimalan PSP dispatcher yang saat ini memancarkan ID objek induk yang sesuai. object_id diekspos dalam Penyimpanan Kueri, karena Penyimpanan Kueri memungkinkan model yang lebih relasional daripada hierarki cache paket yang disediakan. 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, sebaiknya integrasi Query Store diaktifkan, dengan mengaktifkan Penyimpanan Kueri. 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 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF konfigurasi cakupan database.

  • Untuk menonaktifkan pengoptimalan PSP di tingkat kueri, gunakan DISABLE_PARAMETER_SENSITIVE_PLAN petunjuk kueri.

  • 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 paket unik per dispatcher yang disimpan dalam cache paket terbatas untuk menghindari kembung cache. 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 paket unik yang disimpan untuk kueri di penyimpanan Penyimpanan Kueri dibatasi oleh max_plans_per_query opsi konfigurasi. Karena varian kueri dapat memiliki lebih dari satu paket, total 200 paket dapat ada per kueri dalam Penyimpanan Kueri. Angka ini mencakup semua paket varian kueri untuk semua dispatcher yang termasuk dalam kueri induk. Pertimbangkan untuk max_plans_per_query meningkatkan opsi konfigurasi Penyimpanan Kueri.

    • Contoh bagaimana jumlah paket unik dapat melebihi batas Penyimpanan max_plans_per_query Kueri default akan menjadi skenario di mana Anda memiliki 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 total paket untuk ID Kueri 10 adalah 40 paket untuk varian kueri dan dua paket dispatcher. Ada kemungkinan juga bahwa kueri induk itu sendiri (ID Kueri 10) dapat memiliki 5 paket reguler (non-dispatcher). Ini membuat 47 paket (40 dari varian kueri, 2 dispatcher, dan 5 paket 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 kecondongan data berat dalam himpunan data yang mungkin dirujuk oleh kueri induk contoh 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.
    • Itu plan_handle 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 unik karena pengidentifikasi pengoptimalan PSP khusus ditambahkan ke teks kueri selama kompilasi. 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 penanganan yang sama tersedia di Penyimpanan Kueri sebagai last_compile_batch_sql_handle kolom dalam sys.query_store_query tabel katalog.
    • query_id unik di Penyimpanan Kueri. Kolom ini tersedia dalam sys.query_store_query, dan tabel katalog Penyimpanan Kueri lainnya.

Rencana memaksa di Penyimpanan Kueri

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

Jika varian dipaksa, dispatcher induk tidak dipaksa. Jika dispatcher dipaksakan, hanya varian dari dispatcher tersebut yang dianggap memenuhi syarat untuk digunakan:

  • Varian yang sebelumnya dipaksakan dari dispatcher lain menjadi tidak aktif tetapi mempertahankan status paksa sampai 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 Penyimpanan Kueri ditambahkan ke varian kueri (kueri turunan), petunjuk diterapkan dengan cara yang sama seperti kueri non-PSP. Petunjuk varian kueri memang memiliki prioritas yang lebih tinggi jika petunjuk juga telah diterapkan ke kueri induk di Penyimpanan Kueri.

  • Saat petunjuk Penyimpanan Kueri ditambahkan ke kueri induk dan kueri turunan (varian kueri) tidak memiliki petunjuk Penyimpanan Kueri yang sudah ada, kueri turunan (varian kueri) mewarisi petunjuk dari kueri induk.

  • Jika petunjuk kueri Penyimpanan Kueri dihapus dari kueri induk, kueri anak (varian kueri) juga memiliki petunjuk yang dihapus.

  • RECOMPILE Jika petunjuk ditambahkan ke kueri induk, sistem akan menghasilkan paket non-PSP setelah paket varian kueri yang ada dihapus dari cache paket, karena fitur PSP tidak beroperasi pada kueri yang memiliki RECOMPILE petunjuk.

  • Hasil petunjuk Penyimpanan Kueri dapat diamati menggunakan Peristiwa query_store_hints_application_success dan query_store_hints_application_failed peristiwa yang Diperluas. 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 pemakaian rencana dapat diringkas dalam tabel berikut:

Petunjuk atau paket varian kueri Induk memiliki petunjuk yang diterapkan pengguna Induk memiliki petunjuk yang diterapkan umpan balik Induk memiliki rencana paksa secara manual Induk memiliki paket paksa APC 1
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

1 Komponen koreksi paket otomatis dari fitur penyetelan otomatis

"Event yang Diperpanjang"

  • parameter_sensitive_plan_optimization_skipped_reason: Terjadi ketika fitur rencana sensitif parameter dilewati. Gunakan kejadian ini untuk memantau alasan pengoptimalan PSP dilewati.

    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 paket 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 paket 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. MyNewDatabase Menggunakan database sebagai contoh, jika database ini memiliki tabel yang disebut T2 dan prosedur tersimpan usp_testdengan nama , setelah eksekusi prosedur tersimpan usp_test, log audit mungkin berisi 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=opsi @id (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=opsi @id (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 Penyimpanan Kueri di SQL Server 2022 (16.x) dalam 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 (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 saat pengoptimalan PSP diaktifkan pada database sekunder yang dapat dibaca yang telah dikonfigurasi untuk menggunakan Penyimpanan Kueri untuk fitur sekunder yang dapat dibaca . September 2025 Memiliki solusi

Memiliki solusi

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

Kueri yang memenuhi kondisi berikut dapat mengalami pelanggaran akses saat varian kueri PSP tidak dapat menentukan status yang tersimpan dari pernyataan dispatcher induk:

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

Solusi sementara: Nonaktifkan PSP pada sekunder untuk setiap database yang di-onboarding untuk menggunakan Penyimpanan Kueri untuk fitur sekunder yang dapat dibaca. Dari dalam konteks database tertentu, terbitkan pernyataan Transact-SQL berikut:

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Resolved

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

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 Penyimpanan Kueri 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 disiram ke disk lebih awal dari yang DATA_FLUSH_INTERVAL_SECONDS ditentukan dengan opsi . 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 selama operasi menyisipkan atau menghapus varian kueri ke Dalam Penyimpanan Kueri.

Lihat bagian Keterangan dari artikel Cara Penyimpanan Kueri Mengumpulkan Data untuk informasi selengkapnya tentang operasi Penyimpanan Kueri.

Konten terkait

  • Last updated on