Pengoptimalan Rencana Sensitif Parameter

Artikel
01/08/2024

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

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.

Terminologi

Ekspresi dispatcher

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

Paket 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.

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>

Keterangan

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 Penyimpanan Kueri sys.query_store_plan (Transact-SQL) telah diubah untuk membedakan antara paket yang dikompilasi normal, paket dispatcher, dan paket varian kueri. Tampilan katalog sistem Penyimpanan Kueri baru, sys.query_store_query_variant (Transact-SQL), 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 (Transact-SQL).

Pertimbangan

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 database yang sudah ada sebelumnya yang disebut MyNewDatabase:

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

Catatan

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_OPTIMIZATION petunjuk kueri.
Jika sniffing parameter dinonaktifkan oleh Bendera Pelacakan 4136, PARAMETER_SNIFFING konfigurasi tercakup database, atau USE HINT('DISABLE_PARAMETER_SNIFFING') petunjuk kueri, pengoptimalan PSP dinonaktifkan untuk beban kerja terkait dan konteks eksekusi. Untuk informasi selengkapnya, lihat Petunjuk (Transact-SQL) - Konfigurasi CAKUPAN Kueri dan UBAH DATABASE (Transact-SQL).
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 paket, dan setiap paket varian kueri memiliki entri independen dalam cache paket, dimungkinkan untuk mencapai jumlah maksimum default entri paket yang diizinkan. Jika tingkat pengeluaran cache paket terpantau tinggi, atau ukuran CACHESTORE_OBJCP penyimpanan dan CACHESTORE_SQLCP cache 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 paket, dimungkinkan dalam skenario ini untuk memiliki lebih dari 200 paket di Penyimpanan Kueri untuk kueri induk. 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 ¹
Petunjuk melalui pengguna	Petunjuk varian kueri	Petunjuk varian kueri	Petunjuk varian kueri	T/A
Petunjuk melalui umpan balik	Petunjuk varian kueri	Petunjuk varian kueri	Petunjuk varian kueri	T/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 paket otomatis dari fitur penyetelan otomatis

Aktivitas yang Diperluas

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).

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	nama_objek	statement	informasi_tambahan
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 umum

Masalah	Tanggal ditemukan	Status	Tanggal diselesaikan
Pengecualian Pelanggaran Akses terjadi di Penyimpanan Kueri di SQL Server 2022 (16.x) dalam kondisi tertentu. Anda mungkin mengalami pengecualian pelanggaran Akses saat integrasi Query Store pengoptimalan PSP diaktifkan. Untuk informasi selengkapnya, lihat pembaruan di Pengoptimalan Paket Sensitif Parameter, Mengapa?.	Maret 2023	Diselesaikan	Agustus 2023 (CU 7)

Diselesaikan

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

Catatan

Dimulai dengan SQL Server 2022 (16.x) Pembaruan Kumulatif 7, beberapa perbaikan untuk kondisi balapan yang dapat menyebabkan pelanggaran akses telah dirilis. Jika pelanggaran akses yang terkait dengan pengoptimalan PSP dengan integrasi Penyimpanan Kueri terjadi setelah menerapkan Pembaruan Kumulatif 7 untuk SQL Server 2022 (16.x), pertimbangkan bagian solusi berikut.

Masalah ini terjadi karena kondisi balapan yang dapat disebabkan ketika statistik runtime untuk kueri yang dijalankan sedang dipertahankan dari representasi dalam memori Penyimpanan Kueri (ditemukan di MEMORYCLERK_QUERYDISKSTORE_HASHMAP petugas memori) ke versi disk pada 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 utas latar belakang Penyimpanan Kueri tambahan yang terkait dengan pembersihan rencana kueri Penyimpanan Kueri (yaitu, STALE_QUERY_THRESHOLD_DAYS dan/atau MAX_STORAGE_SIZE_MB opsi Penyimpanan Kueri), kueri dari Penyimpanan Kueri, ada skenario di mana varian kueri dan/atau pernyataan dispatcher terkaitnya dapat didereferensikan 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.

Solusi sementara: Varian kueri yang ada di Penyimpanan Kueri dapat dihapus, atau fitur PSP dapat dinonaktifkan sementara pada tingkat kueri atau database hingga perbaikan tambahan tersedia jika sistem Anda masih mengalami pelanggaran akses di Penyimpanan Kueri dengan integrasi PSP diaktifkan setelah menerapkan Pembaruan Kumulatif 7 untuk SQL Server 2022 (16.x).

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_OPTIMIZATION petunjuk kueri.

Untuk menghapus semua varian kueri dari Penyimpanan Kueri, bukan hanya varian kueri yang muncul dalam tampilan katalog sys.query_store_query_variant (Transact-SQL), kueri yang mirip dengan yang berikut ini dapat digunakan. Ganti [<database>] dengan database yang sesuai yang mengalami masalah:

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the Query Store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

Jika Penyimpanan Kueri Anda besar, atau jika sistem Anda memiliki beban kerja yang substansial dan/atau jumlah kueri non-parameter ad hoc yang memenuhi syarat untuk diambil oleh Penyimpanan Kueri, menonaktifkan Penyimpanan Kueri mungkin memakan waktu. Untuk menonaktifkan Penyimpanan Kueri secara paksa dalam skenario ini, gunakan perintah sebagai gantinya ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) , dalam sampel T-SQL sebelumnya. Untuk menemukan kueri yang tidak berparameter, lihat Menemukan kueri non-parameter di Penyimpanan Kueri.

Bagikan melalui

Pengoptimalan Rencana Sensitif Parameter

Memahami parameterisasi

Implementasi pengoptimalan PSP

Terminologi

Ekspresi dispatcher

Paket dispatcher

Varian kueri

Rentang kardinalitas predikat

Keterangan

Pertimbangan

Rencana memaksa di Penyimpanan Kueri

Perilaku petunjuk kueri Penyimpanan Kueri

Aktivitas yang Diperluas

Perilaku Audit SQL Server

Masalah umum

Diselesaikan

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

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Pengoptimalan Rencana Sensitif Parameter

Memahami parameterisasi

Implementasi pengoptimalan PSP

Terminologi

Ekspresi dispatcher

Paket dispatcher

Varian kueri

Rentang kardinalitas predikat

Keterangan

Pertimbangan

Rencana memaksa di Penyimpanan Kueri

Perilaku petunjuk kueri Penyimpanan Kueri

Aktivitas yang Diperluas

Perilaku Audit SQL Server

Masalah umum

Diselesaikan

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

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: