Sorgu Deposu ile plan zorlama optimizasyonu

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

Sorgu iyileştirme, "yeterince iyi" bir sorgu yürütme planı oluşturmaya yönelik çok aşamalı bir işlemdir. Bazı durumlarda, sorgu iyileştirmenin bir parçası olan sorgu derlemesi, genel sorgu yürütme süresinin büyük bir yüzdesini temsil edebilir ve önemli sistem kaynaklarını kullanabilir. İyileştirilmiş plan dayatma, akıllı sorgu işleme özellikleri grubunun bir parçasıdır. İyileştirilmiş plan zorlaması, zorlamalı sorguların yinelenmesi için derleme ek yükünü azaltır ve Sorgu Deposu'nu etkinleştirip "okuma yazma" modunda olmasını gerektirir. Sorgu yürütme planı oluşturulduktan sonra, iyileştirme yeniden yürütme betiği olarak yeniden kullanmak üzere belirli derleme adımları depolanır. İyileştirme yeniden yürütme betiği, gizli bir özniteliğinde OptimizationReplaysıkıştırılmış showplan XML'in bir parçası olarak depolanır.

İyileştirilmiş plan zorlama uygulaması

Bir sorgu derleme işleminden ilk kez geçtiğinde, sorgu iyileştirici giriş ağacına göre, optimizasyonda harcanan sürenin tahminine dayanan bir eşik değer, optimizasyon yeniden yürütme betiğinin oluşturulup oluşturulmayacağını belirler.

Derleme tamamlandıktan sonra, önceki tahminin doğru olup olmadığını değerlendirmek için birkaç çalışma zamanı ölçümü kullanılabilir hale gelir. Veritabanı Altyapısı eşiğin aşıldığını onaylarsa, iyileştirme yeniden yürütme betiği kalıcılık için uygundur. Bu çalışma zamanı ölçümleri erişilen nesne sayısını, birleştirme sayısını, iyileştirme sırasında yürütülen iyileştirme görevlerinin sayısını ve gerçek iyileştirme süresini içerir.

İyileştirme yeniden yürütme betiğini kullanmanın olası avantajı, iyileştirme yeniden yürütme betiğini depolama yüküyle de karşılaştırılır. İyileştirme yeniden yürütme betiğini yeniden yürütmeye ilişkin göreli süre tahmini, normal iyileştirme işlemini yürütmek için harcanan süreyle karşılaştırılır. Bu tahmin, iyileştirme yeniden yürütme betiğinde depolanan iyileştirme görevleri sayısına ve normal derleme sırasında yürütülen iyileştirme görevlerinin sayısına bağlıdır. İyileştirme yeniden yürütme betiğinin yeniden oynatılması derleme süresini azaltmada önemli bir avantaj gösteriyorsa, iyileştirme yeniden yürütme betiği kalıcı hale gelir.

Considerations

İyileştirilmiş plan zorlama özelliği etkinleştirildiğinde, iyileştirilmiş plan zorlama için uygunluk ölçütleri şöyledir:

1. Yalnızca tam iyileştirmeden geçen sorgu planları uygundur ve bu, özelliğin StatementOptmLevel="FULL" varlığıyla doğrulanabilir.

2. RECOMPILE ipucuna sahip deyimler ve dağıtılmış sorgular uygun değil.

Ancak, Sorgu Deposu optimize edilmiş plan zorlaması ile kapsamı belirlenmiş bir sorgu planını bağımsız olarak yakalarsa, varsayılan yeniden derleme olaylarına tabi olacak aynı sorgunun ikinci bir yeniden derlemesi için optimizasyon yeniden yürütme kodu oluşturulur. Yürütme Planlarını Yeniden Derleme bölümünde yeniden derleme hakkında daha fazla bilgi edinin.

İyileştirme yeniden yürütme betiği oluşturulsa bile, Sorgu Deposu tarafından yapılandırılan yakalama ilkeleri ölçütleri karşılanmadıysa( özellikle bu deyimin yürütme sayısı ve birleştirilmiş derleme ve yürütme süreleri) Sorgu Deposu'nda kalıcı olmayabilir. Bu durumda geçersiz optimizasyon yeniden oynatma betiği bellekten eş zamansız olarak kaldırılır.

İyileştirilmiş plan zorlamanın etkinleştirilmesi ve devre dışı bırakılması

Veritabanı için iyileştirilmiş plan zorlamayı etkinleştirebilir veya devre dışı bırakabilirsiniz. Veritabanı için optimize edilmiş plan zorlama etkinleştirildiğinde, DISABLE_OPTIMIZED_PLAN_FORCING sorgu ipucunu kullanarak belirli sorgular için bunu devre dışı bırakabilirsiniz. Query Store'da zorlanarak uygulanan bir sorgu planı için iyileştirilmiş plan zorlamayı da devre dışı bırakabilirsiniz.

İyileştirilmiş plan zorlamasını bir veritabanı için etkinleştirin veya devre dışı bırakın

İyileştirilmiş plan zorlama, SQL Server 2022 (16.x) ve üzeri sürümlerde oluşturulan yeni veritabanları için varsayılan olarak etkindir. İyileştirilmiş plan zorlamanın kullanıldığı her veritabanı için Sorgu Deposu etkinleştirilmelidir. Mevcut veritabanları veya SQL Server'ın daha düşük bir sürümünden geri yüklenen veritabanlarıyla yükseltilen örnekler, optimize edilmiş plan zorlama özelliğini varsayılan olarak etkinleştirdi.

Veritabanı düzeyinde iyileştirilmiş plan zorlamayı etkinleştirmek için ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON veritabanı kapsamında yapılandırmayı kullanın. Henüz etkinleştirilmemişse Sorgu Deposu'nun etkinleştirilmesi gerekir. Örnek A'da örnek kod bulun veya Sorgu Deposu'nu kullanarak Performansı izleme bölümünde Sorgu Deposu hakkında daha fazla bilgi edinin.

Veritabanı düzeyinde iyileştirilmiş plan zorlamayı devre dışı bırakmak için veritabanına özel yapılandırmayı kullanın ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Sorgu ipucuyla iyileştirilmiş plan zorlamayı devre dışı bırakma

Bir veritabanında iyileştirilmiş plan zorlama özelliği etkinleştirildiğinde, DISABLE_OPTIMIZED_PLAN_FORCING kullanarak tek bir sorgu için iyileştirilmiş plan zorlamayı devre dışı bırakabilirsiniz.

Bu sorgu ipucunu uygulama örneğini Örnek E'de bulabilirsiniz.

Sorgu Deposu ile bir planı zorlama, ancak optimize edilmiş plan zorlamayı devre dışı bırakma

sp_query_store_force_plan yordamı bir disable_optimized_plan_forcing parametre içerir. Bu parametrenin çalışması için sp_query_store_force_plan saklı yordamına ek bir parametre gerekir. Ek parametre olarak adlandırılır @replica_group_id. Varsayılan olarak, @replica_group_id birincil, yapılandırılmış ikincil çoğaltmaların olmadığı durumlarda bile bir (1) değerine sahiptir.

sp_query_store_force_plan saklı yordama uygun parametreleri uygulama örneğini bulun.

Katalog sys.query_store_plan görünümü, planın ilişkili bir iyileştirme yeniden yürütme betiğine sahip olup olmadığını gösteren sütunlar içerir ve ilişkili iyileştirme yeniden yürütme betiğine özgü mevcut hata nedeni sütununa yeni bir durum ekler. sys.query_store_plan'da daha fazla bilgi edinin.

Examples

Bu makaledeki kod örnekleri, AdventureWorks2025 giriş sayfasından indirebileceğiniz AdventureWorksDW2025 veya örnek veritabanını kullanır.

A. Sorgu Deposu'yu etkinleştirme ve veritabanı için iyileştirilmiş plan zorlama

Aşağıdaki kod bir veritabanında Sorgu Deposu'nu etkinleştirir ve ardından veritabanı üzerinde iyileştirilmiş plan zorlamasını etkinleştirir. ALTER DATABASE SET seçeneklerinde Sorgu Deposu'yu etkinleştirme seçenekleri hakkında daha fazla bilgi edinin.

Kodu çalıştırmadan önce uygun kullanıcı veritabanına bağlanın.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Optimizasyon yeniden yürütme betiği olan tüm sorguları seçin.

Aşağıdaki örnek kod, Sorgu Deposu'nda iyileştirme yeniden yürütme betiği olan tüm query_ids seçer. Örnek kodu çalıştırmadan önce uygun kullanıcı veritabanına bağlanın.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Sorgu Deposu'nda bir planı zorlama ve iyileştirilmiş plan zorlamayı devre dışı bırakma

Aşağıdaki kod, Sorgu Deposu'nda bir planı uygular, ancak optimize edilmiş plan zorlamayı devre dışı bırakır. Aşağıdaki kodu çalıştırmadan önce @query_id ve @plan_id öğelerini örneğinize uygun bir kombinasyonla değiştirin. Saklı yordam, Sorgu Deposu'nda optimize edilmiş plan zorlamasını devre dışı bırakmaya çalışırken sp_query_store_force_plan parametresinin üçüncü parametre olarak verilmesini bekler @replica_group_id. Bu, belirli bir çoğaltmada zorlanmış bir plan için optimize edilmiş plan dayatmasını devre dışı bırakmak amacıyla kullanılabilir. Birincil çoğaltmadaki özelliği devre dışı bırakmak için @replica_group_id = 1 değeri kullanılır.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

sp_query_store_force_plan'da daha fazla bilgi edinin.

D. Sorgu Deposu'nun iyileştirilmiş plan zorlamayı devre dışı bıraktığı tüm sorguları seçin

Aşağıdaki örnek Sorgu Deposu'nda is_optimized_plan_forcing_disabled zorlanan ve olarak ayarlandığı 1tüm planları sorgular. Kodu çalıştırmadan önce uygun kullanıcı veritabanına bağlanın.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Sorgu için iyileştirilmiş plan zorlamayı devre dışı bırakma

Aşağıdaki örnek, DISABLE_OPTIMIZED_PLAN_FORCINGsorgu ipucunu kullanarak bir sorgu için iyileştirilmiş plan zorlamayı devre dışı bırakır.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

İlgili içerik

• sys.query_store_plan (Transact-SQL)
• VERİTABANI ALANLI KONFİGÜRASYONU DEĞİŞTİR (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• SQL veritabanlarında akıllı sorgu işleme