Aracılığıyla paylaş

İsteğe bağlı parametre planı iyileştirme (OPPO)

Şunlar için geçerlidir: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

İsteğe bağlı parametre planı iyileştirmesi (OPPO), isteğe bağlı parametreler içeren sorgular için sorgu planı kalitesini artırır. Bu sorgularda, en uygun yürütme planı bir parametre değerinin NULL yürütme zamanında olup olmamasına bağlıdır. İsteğe bağlı parametreler terimi, parametreye duyarlı plan (PSP) sorununun belirli bir varyasyonunu ifade eder ve bu durumda yürütme zamanındaki parametre değeri sorgunun bir arama mı yoksa tarama mı gerektirdiğini belirler.

Genel Bakış

İsteğe bağlı parametreler kullanan sorgular genellikle parametre değerinin sağlanıp sağlanmadığına göre koşullu olarak filtre uygulayan koşullar içerir. Yaygın bir desen aşağıdaki gibidir:

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

Ne zaman @p IS NOT NULL uygulanırsa, col1 üzerindeki dizin araması genellikle en verimli yürütme planıdır. olduğunda @p IS NULLkoşul olarak değerlendirilir TRUEve tarama daha uygun olabilir. OPPO olmadan, SQL Server Veritabanı Altyapısı her iki durumda da geçerli olan tek bir yürütme planını derlemeli ve önbelleğe almalıdır. @p IS NULL geçerli olmadığından, arama tabanlı bir plan mümkün olmadığında, optimizatör genellikle her bir yürütme için temkinli bir tarama tabanlı plan seçer. Bu seçim, verimsiz plan seçeneklerine ve seçmeli yürütmeler için aşırı kaynak kullanımına neden olabilir.

Geleneksel OPTIMIZE FOR gibi ipucu teknikleri, planın her iki parametre durumu için de doğru kalması gerektiğinden bu senaryoda etkili değildir.

OPPO, Parametreye Duyarlı Plan (PSP) iyileştirmesi ile sunulan Multiplan altyapısını kullanan uyarlamalı plan optimizasyonunu kullanır. Bu altyapı, OPPO'nun sorguda kullanılan parametre değerlerine göre farklı varsayımlarda bulunabilmesini sağlayan tek bir deyim için birden çok yürütme planı oluşturur ve önbelleğe alır.

Terminoloji ve nasıl çalıştığı

OPPO, Parametreye Duyarlı Plan iyileştirmesi tarafından da kullanılan uyarlamalı plan iyileştirme (Multiplan) çerçevesini temel alır. Veritabanı Altyapısı, Multiplan kullanarak tek bir sorgu için birden çok yürütme planı oluşturabilir ve önbelleğe alabilir.

Veritabanı Altyapısı uygun bir isteğe bağlı parametre deseni algıladığında şunları oluşturur:

  • Dağıtıcı planı
  • Her biri belirli bir parametre değeri durumu için iyileştirilmiş bir veya daha fazla sorgu değişkeni

Yürütme zamanında:

  • Veritabanı Altyapısı parametre değerini değerlendirir.
  • Multiplan dağıtıcısı uygun sorgu değişkenini seçer.
  • Seçili sorgu değişkeni yürütülür.

Veritabanı Altyapısı bir sorgu değişkenini seçtikten sonra, gerçek parametre değerine göre koşulları basitleştirir. Aşağıdaki ifadeyi göz önünde bulundurun:

@p1 IS NULL

Bu örnekte, ifade seçilen değişken için sabit bir sonuda basitleştirilmiştir. Bu sabit sonuç katlama, iyileştiricinin tek bir yeniden kullanılabilir planda geçerli olmayan yürütme planları oluşturmasına olanak tanır.

OPPO, planları bu şekilde seçerek, sorgu yeniden yazma veya el ile sorgu ipuçları gerektirmeden farklı parametre durumları için verimli yürütmeye olanak tanır.

OPPO ve PSP iyileştirmesi, parametreyle ilgili plan sorunlarının farklı çeşitlemelerini giderir:

  • PSP optimizasyonu, eşitlik veya aralık koşulları için tahmini kardinalite farklılıklarına göre planları seçer.

  • OPPO, bir parametre değerinin NULLolup olmadığına göre planları seçer.

Tek bir sorgu, ilgili koşula bağlı olarak her iki özellikten de yararlanabilir.

Desteklenen sorgu desenleri

İsteğe bağlı parametre planı iyileştirmesi, parametreler üzerindeki denetimlerin yürütme planının NULL geçerliliğini etkilediği sorgular için geçerlidir. Örneğin, belirli bir ilan için yatak odası sayısı üzerinde isteğe bağlı filtrelemeye izin veren bir emlak şirketine ait uygulama web formunu düşünün. OPPO, aşağıdakiler gibi ayrık isteğe bağlı parametre koşullarında geçerlidir:

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

Parametre işaretçileri parametreyi @bedrooms = 10 koklayabilse bile ve yatak odası sayısının kardinalitesinin çok düşük olacağını biliyor olsanız da, optimizatör yatak odası sütununda mevcut olan bir indeks üzerinde arama yapmak için bir plan üretmez, çünkü @bedroomsNULL olduğunda bu geçerli bir plan değildir. Oluşturulan plan, dizin taraması içermez.

Bu sorguyu iki ayrı deyim olarak yeniden yazabileceğinizi düşünün. parametresinin çalışma zamanı değerine bağlı olarak aşağıdaki örneği değerlendirebilirsiniz:

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

Bu özellik, sorgu değişkenini dağıtan bir dağıtıcı planının oluşturulmasına olanak tanıyan Multiplan altyapısını kullanarak bunu gerçekleştirebilir.

OPPO, her sorgu değişkenini parametre durumuyla ilişkilendirmek için plan meta verilerine sistem tarafından oluşturulan PLAN PER VALUE bir sorgu ipucu (optional_predicate) ekler. Bu ipucu sistem tarafından oluşturulur ve planın sorgu metnine eklenir. Bu ipucu, bir uygulama tarafından kullanılmak veya el ile uygulamak için geçerli değildir.

Önceki örnekle devam edersek,

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

OPPO, Showplan XML içinde bunlara aşağıdaki özniteliklerin eklendiği iki sorgu değişkeni oluşturabilir:

  • @bedrooms, NULL'e eşittir. Sorgu değişkeni, parametreleri parametre değerine göre katlayarak tarama tabanlı bir plan oluşturulmasını sağlar.

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

  • @bedrooms IS NOT NULL

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

İsteğe bağlı parametre planı iyileştirmeyi kullanma

Bir veritabanı için OPPO'yı etkinleştirmek için aşağıdaki önkoşullar gereklidir:

  • Veritabanı uyumluluk düzeyi 170 kullanmalıdır.
  • Veritabanı kapsamlı OPTIONAL_PARAMETER_OPTIMIZATION yapılandırma etkinleştirilmelidir.

OPTIONAL_PARAMETER_OPTIMIZATION Veritabanı kapsamlı yapılandırma varsayılan olarak etkindir, bu nedenle uyumluluk düzeyi 170 olan bir veritabanı (SQL Server 2025'te (17.x)) varsayılan olarak OPPO kullanır.

Aşağıdaki deyimleri yürüterek veritabanının SQL Server 2025'te (17.x) OPPO kullandığından emin olabilirsiniz:

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

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Bir veritabanında OPPO'yı devre dışı bırakmak için veritabanı kapsamlı yapılandırmayı OPTIONAL_PARAMETER_OPTIMIZATION devre dışı bırakın:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Sorgu ipucu aracılığıyla isteğe bağlı parametre planı iyileştirmesini kullanma

Belirli bir sorgu için DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION OPPO'yu devre dışı bırakmak için sorgu ipucunu kullanın. ipucunu USE HINT yan tümce aracılığıyla belirtiniz. Daha fazla bilgi için bkz.sorgu ipuçları .

Bu ipucu herhangi bir uyumluluk düzeyi altında çalışır ve veritabanı kapsamlı yapılandırmayı OPTIONAL_PARAMETER_OPTIMIZATION geçersiz kılar.

Sorgu ipucunu DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION doğrudan sorguda veya Sorgu Deposu ipuçları aracılığıyla belirtin.

Genişletilmiş Olaylar

Sorun giderme ve tanılama için aşağıdaki genişletilmiş olayları kullanın. Bu olayların özelliği kullanmak için gerekli değildir.

  • optional_parameter_optimization_skipped_reason: OPPO, bir sorguyu iyileştirme için uygun olmadığına karar verince gerçekleşir. Bu genişletilmiş olay, PSP iyileştirmesinin kullandığı olayla parameter_sensitive_plan_optimization_skipped_reason aynı deseni izler. Bir sorgu hem PSP optimizasyonu hem de OPPO sorgu varyantları oluşturabildiğinden, bu özelliklerin neden hiçbiri veya yalnızca birinin devreye girdiğini anlamak için her iki olayı da kontrol edin.

    Aşağıdaki sorgu, PSP'nin atlanma nedenlerinin tümünü gösterir:

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

  • query_with_optional_parameter_predicate: Bu genişletilmiş olay, PSP iyileştirmesinin query_with_parameter_sensitivity kullandığı olayla aynı deseni izler. PSP iyileştirmesi geliştirmelerinde kullanılabilen ek alanları içerir.

    Bu alanlar şunları görüntüler:

    • özelliğin ilginç bulduğu koşul sayısı,
    • ilgi çekici önermelerle ilgili JSON biçimindeki daha fazla ayrıntı ve
    • OPPO'nun koşul veya koşullar için desteklenip desteklenmediği.

Remarks

  • Sorgu değişkeninin ShowPlan XML'i aşağıdaki örneğe benzer. Özelliğin seçtiği her koşulun bilgileri, kendi PLAN PER VALUE (optional_predicate) ipucuna eklenir.
<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

  • query_with_optional_parameter_predicate ile genişletilmiş olaydan örnek çıktı:

    Field Value
    optional_parameter_optimization_supported True
    optional_parameter_predicate_count 3
    predicate_details {"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
    query_type 193

Sorgu uygunluğu ve sınırlamaları

OPPO yalnızca Multiplan iyileştirmesi için uygun sorgular için geçerlidir. Bu özellik aşağıdaki senaryolarda uygulanmaz:

  • Parametreler yerine yerel değişkenleri kullanan sorgular
  • ile derlenen sorgular OPTION (RECOMPILE)
  • ile yürütülen sorgular SET ANSI_NULLS OFF
  • Otomatik parametrelendirilmiş ifadeler

İlgili içerik

  • Last updated on