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

Şunlar için geçerlidir:Microsoft SQL Server 2025 (17.x) Azure SQL VeritabanıMicrosoft Fabric'te SQL veritabanı

İsteğe bağlı parametreler terimi, sorgu yürütme sırasında var olan hassas parametre değerinin tablo üzerinde arama yapmamız veya taramamız gerekip gerekmediğini denetlediği parametreye duyarlı plan (PSP) sorununun belirli bir varyasyonunu ifade eder. Basit bir örnek şöyle olabilir:

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

Bu örnekte, Table1 üzerinde bir dizin olsa bile SQL Server her zaman Table1(col1) tablosunu tarayan bir plan seçer. ARAMA planı, NULL'lerle mümkün olmayabilir. Yürütme sırasında dizin aramasını taramaya dinamik olarak değiştiren bir işleç olmadığından, OPTIMIZE FOR gibi sorgu ipucu teknikleri bu tür PSP sorunları için yararlı olmayabilir. Çalışma zamanında bu tür bir arama tarama> bileşimi de etkili olmayabilir, çünkü bu işlecin üzerindeki kardinalite tahminleri büyük olasılıkla yanlış olacaktır. Sonuç, verimsiz plan seçimleri ve benzer sorgu desenlerine sahip daha karmaşık sorgular için aşırı bellek izinleridir.

İsteğe Bağlı Parametre Planı iyileştirmesi (OPPO) özelliği, tek bir deyimden birden çok plan oluşturan Parametre Duyarlı Plan iyileştirmesi ile sunulan uyarlamalı plan iyileştirme (Multiplan) altyapısını kullanır. Bu, özelliğin sorguda kullanılan parametre değerlerine bağlı olarak farklı varsayımlarda bulunmasını sağlar. Sorgu yürütme süresi boyunca OPPO uygun planı seçer:

• burada parametre değeri IS NOT NULL, bir arama planı veya tam tarama planından daha uygun bir şey kullanır.
• Parametre değeri NULL olduğunda bir tarama planı kullanılır.

Parametre Duyarlı Plan iyileştirmesini içeren uyarlamalı plan iyileştirme özellik ailesinin bir parçası olarak OPPO, dinamik arama özelliklerini kapsayan Multiplan özellik kümesinin ikinci bileşenine bir çözüm sağlar.

• Eşitlik önkoşulları

  WHERE column1 = @p
  

• Dinamik arama

  WHERE (column1 = @p1 OR @p1 IS NULL)
    AND (column2 = @p2 OR @p2 IS NOT NULL)
  

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

Term	Description
Dispatcher ifadesi	Bu ifade, çalışma zamanı parametre değerlerine göre koşul kardinalitesini değerlendirir ve yürütmeyi farklı sorgu değişkenlerine yönlendirir.
Dağıtıcı planı	Dağıtıcı ifadesini içeren bir plan özgün sorgu için önbelleğe alınır. Dağıtıcı planı temelde özellik tarafından seçilen ve birkaç ek ayrıntı içeren bir koşul koleksiyonudur. Seçilen her koşul için dağıtıcı planına dahil edilen ayrıntılardan bazıları yüksek ve düşük sınır değerleridir. Bu değerler parametre değerlerini farklı demetlere veya aralıklara bölmek için kullanılır. Dağıtıcı planı, sınır değerlerini hesaplamak için kullanılan istatistikleri de içerir.
Sorgu değişkeni	Dağıtıcı planı, çalışma zamanı parametre değerlerine göre önermelerin kardinalitesini değerlendirirken bunları gruplandırır ve çalıştırmak için ayrı alt sorgular oluşturur. Bu çocuk sorgulara sorgu varyantları adı verilir. Sorgu değişkenlerinin plan önbelleğinde ve Sorgu Deposu'nda kendi planları vardır. Başka bir deyişle, farklı sorgu değişkenlerini kullanarak tek bir sorgu için birden çok plan hedefine ulaşırız.

Ö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. İsteğe bağlı filtreyi şu şekilde ifade etmek sıkça görülen bir hatalı örüntü olabilir:

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

Parametre @bedrooms = 10, parametre işaretçilerinin kullanımıyla izlenirse ve yatak odası sayısının kartalitesinin büyük olasılıkla çok düşük olacağını biliyor olsak bile, optimizatör, @bedroomsNULL olduğu durum için geçerli bir plan olmadığı için yatak odası sütununda bulunan bir dizine başvuran bir plan üretmez. Oluşturulan plan, dizin taraması içermez.

Bunun iki ayrı deyim olarak yeniden yazılabileceğini düşünün. parametresinin çalışma zamanı değerine bağlı olarak aşağıdakine benzer bir değer değerlendirebiliriz:

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

Bunu, iki sorgu değişkeni gönderen bir dağıtıcı planının oluşturulmasına olanak tanıyan uyarlamalı plan iyileştirme altyapısını kullanarak elde edebiliriz.

PSP iyileştirmesinin kullandığı koşul kardinalite aralığına benzer şekilde OPPO, planın sorgu metniyle birlikte sistem tarafından kullanılabilir bir sorgu ipucu ekler. Bu ipucu, uygulama tarafından veya kendiniz kullanmaya çalıştığınızda 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 varyantı, tarama planı elde etmek için özgün sorguyu katladı.

  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, uyumluluk düzeyi 170 (SQL Server 2025'te varsayılan) kullanan bir veritabanının varsayılan olarak OPPO kullandığı anlamına gelir.

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

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Veritabanı için isteğe bağlı parametre planı iyileştirmesini 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 ipuçları aracılığıyla isteğe bağlı parametre planı iyileştirmeyi kullanma

Belirli bir sorgu için isteğe bağlı parametre planı iyileştirmesini devre dışı bırakmak için sorgu ipucunu kullanabilirsiniz DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION . İpuçları USE HINT yan tümcesi kullanılarak belirtilmelidir. Daha fazla bilgi için bkz.sorgu ipuçları .

İpuçları, herhangi bir uyumluluk düzeyinde çalışır ve OPTIONAL_PARAMETER_OPTIMIZATION veritabanı kapsamlı yapılandırmayı geçersiz kılar.

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

Genişletilmiş Olaylar

• 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 optimizasyonu tarafından kullanılan parameter_sensitive_plan_optimization_skipped_reason olayıyla aynı modeli izler. Bir sorgu hem PSP optimizasyonu hem de OPPO sorgu çeşitleri oluşturabildiğinden, bir veya her iki özelliğin neden devreye girmediğini anlamak için her iki olayı da kontrol etmelisiniz.

  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: Genişletilmiş olay, PSP iyileştirmesi tarafından kullanılan query_with_parameter_sensitivity olayıyla aynı deseni izler. PSP optimizasyonuna yönelik iyileştirmeler kapsamında, özellik tarafından ilgi çekici bulunan koşul sayısını görüntüleyen, ilgi çekici koşullarla ilgili json formatında daha fazla ayrıntı sağlayan ve koşul veya koşullar için OPPO desteğinin mevcut olup olmadığını belirten ek alanları içerir.

Remarks

• Sorgu varyantı için ShowPlan XML, aşağıdaki örnek gibi görünebilir; burada seçilen predikatların ilgili bilgileri DEĞER BAŞINA PLAN isteğe bağlı predikat 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">

• Genişletilmiş olaydan query_with_optional_parameter_predicate ö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

İlgili içerik

• Sorgu işleme mimarisi kılavuzu
• Yürütme planlarını yeniden derleme
• Parametreler ve yürütme planı yeniden kullanımı
• Basit parametreleştirme
• Zorlamalı parametreleştirme
• Sorgu ipuçları (Transact-SQL)
• SQL veritabanlarında akıllı sorgu işleme
• Parametre Duyarlılığı
• VERİTABANI ALANLI KONFİGÜRASYONU DEĞİŞTİR (Transact-SQL)