Sorgu iyileştirici zaman aşımından etkilenen yavaş sorgu sorunlarını giderme

2024-12-14

Şunlar için geçerlidir: SQL Server

Bu makalede İyileştirici Zaman Aşımı, sorgu performansını nasıl etkileyebileceği ve performansın nasıl iyileştirebileceği tanıtılabilir.

İyileştirici Zaman Aşımı nedir?

SQL Server maliyet tabanlı sorgu iyileştiricisi (QO) kullanır. QO hakkında bilgi için bkz . Sorgu işleme mimarisi kılavuzu. Maliyet tabanlı Sorgu İyileştiricisi, birden çok sorgu planı oluşturup değerlendirdikten sonra en düşük maliyetle bir sorgu yürütme planı seçer. SQL Server Sorgu İyileştiricisi'nin amaçlarından biri, sorgu yürütmeye kıyasla sorgu iyileştirmede makul bir zaman harcamaktır. Sorguyu en iyi duruma getirmek, sorguyu yürütmekten çok daha hızlı olmalıdır. Bu hedefi gerçekleştirmek için QO,iyileştirme işlemini durdurmadan önce dikkate alınması gereken yerleşik bir görev eşiğine sahiptir. QO tüm olası planları dikkate almadan önce eşiğe ulaşıldığında, İyileştirici Zaman Aşımı sınırına ulaşır. Sorgu planında Bir İyileştirici Zaman Aşımı olayı, Deyimi İyileştirmenin Erken Sonlandırma Nedeni altında Zaman Aşımı olarak bildirilir. Bu eşiğin saat saatine değil iyileştirici tarafından dikkate alınabilecek olasılık sayısına bağlı olduğunu anlamak önemlidir. Geçerli SQL Server QO sürümlerinde, bir zaman aşımına ulaşılana kadar yarım milyondan fazla görev dikkate alınır.

İyileştirici Zaman Aşımı SQL Server'da tasarlanmıştır ve çoğu durumda sorgu performansını etkileyen bir faktör değildir. Ancak bazı durumlarda SQL sorgu planı seçimi İyileştirici Zaman Aşımından olumsuz etkilenebilir ve daha yavaş sorgu performansı oluşabilir. Bu tür sorunlarla karşılaştığınızda, İyileştirici Zaman Aşımı mekanizmasını ve karmaşık sorguların nasıl etkilenebileceğini anlamak, sorun gidermenize ve sorgu hızınızı artırmanıza yardımcı olabilir.

İyileştirici Zaman Aşımı eşiğine ulaşmanın sonucu, SQL Server'ın iyileştirme olasılıklarının tamamını dikkate almamasıdır. Başka bir ifadeyle, daha kısa yürütme süreleri üretebilecek planları kaçırmış olabilir. QO eşikte durur ve daha iyi ve keşfedilmemiş seçenekler olsa bile bu noktada en düşük maliyetli sorgu planını dikkate alır. İyileştirici Zaman Aşımına ulaşıldıktan sonra seçilen planın sorgu için makul bir yürütme süresi oluşturabileceğini unutmayın. Ancak bazı durumlarda, seçilen plan en iyi durumda olmayan bir sorgu yürütmesine neden olabilir.

İyileştirici Zaman Aşımını algılama

İyileştirici Zaman Aşımını gösteren belirtiler şunlardır:

Karmaşık sorgu

Çok sayıda birleştirilmiş tablo içeren karmaşık bir sorgunuz var (örneğin, sekiz veya daha fazla tablo birleştirilir).
Yavaş sorgu

Sorgu, başka bir SQL Server sürümünde veya sisteminde çalıştığından yavaş veya yavaş çalışabilir.

Sorgu planı StatementOptmEarlyAbortReason=Timeout değerini gösterir

Sorgu planı XML sorgu planında gösterilir StatementOptmEarlyAbortReason="TimeOut" .

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

Microsoft SQL Server Management Studio'da en soldaki plan işlecinin özelliklerini denetleyin. Deyim İyileştirmesinin Erken Sonlandırılmasına neden olan değerin Zaman Aşımı olduğunu görebilirsiniz.

İyileştirici Zaman Aşımına ne neden olur?

İyileştirici eşiğine ulaşılması veya aşılması için hangi koşulların neden olacağını belirlemenin basit bir yolu yoktur. Aşağıdaki bölümler, en iyi planı ararken QO tarafından keşfedilen plan sayısını etkileyen bazı faktörlerdir.

Tablolar hangi sırada birleştirilmelidir?

Aşağıda üç tablolu birleştirmelerin (Table1, Table2, Table3) yürütme seçeneklerine bir örnek verilmiştir:
- ile Table2 birleştirin Table1 ve sonucu ileTable3
- ile Table3 birleştirin Table1 ve sonucu ileTable2
- ile Table3 birleştirin Table2 ve sonucu ileTable1
Not: Tablo sayısı ne kadar büyükse, olasılıklar o kadar büyük olur.
Tablodan satırları almak için hangi yığın veya ikili ağaç (HoBT) erişim yapısı kullanılacak?
- Kümelenmiş dizin
- Kümelenmemiş Dizin1
- Kümelenmemiş Dizin2
- Tablo yığını
Hangi fiziksel erişim yöntemi kullanılacak?
- Dizin arama
- Dizin taraması
- Tablo taraması
Hangi fiziksel birleştirme işleci kullanılacak?
- İç İçe Döngüler birleştirme (NJ)
- Karma birleştirme (HJ)
- Birleştirme birleştirme (MJ)
- Uyarlamalı birleştirme (SQL Server 2017 (14.x) ile başlayarak)
Daha fazla bilgi için bkz . Birleştirmeler.
Sorgunun bölümlerini paralel veya seri olarak yürütelim mi?

Daha fazla bilgi için bkz . Paralel sorgu işleme.

Aşağıdaki faktörler göz önünde bulundurulan erişim yöntemlerinin sayısını ve dolayısıyla göz önünde bulundurulan olasılıkları azaltacaktır:

Sorgu önkoşulları (yan tümcesindeki WHERE filtreler)
Kısıtlamaların varlığı
İyi tasarlanmış ve güncel istatistiklerin birleşimleri

Not: QO'nin eşiğe ulaşması, daha yavaş bir sorguyla sonuçlanacağı anlamına gelmez. Çoğu durumda sorgu iyi performans gösterir, ancak bazı durumlarda daha yavaş bir sorgu yürütme görebilirsiniz.

Faktörlerin nasıl değerlendirildiğinden örnek

Bunu göstermek için üç tablo (t1, t2ve t3) arasındaki birleştirmenin bir örneğini alalım ve her tablonun kümelenmiş dizini ve kümelenmemiş dizini vardır.

İlk olarak, fiziksel birleştirme türlerini göz önünde bulundurun. Burada iki birleşim söz konusu. Ayrıca üç fiziksel birleştirme olasılığı (NJ, HJ ve MJ) olduğundan, sorgu 3² = 9 yolla gerçekleştirilebilir.

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

Ardından, Permütasyonlar kullanılarak hesaplanan birleştirme sırasını göz önünde bulundurun: P (n, r). İlk iki tablonun sırası önemli olmadığından P(3,1) = 3 olasılık olabilir:

ile ve ardından ile t2 katılma t1t3
ile ve ardından ile t3 katılma t1t2
ile ve ardından ile t3 katılma t2t1

Ardından, veri alma için kullanılabilecek kümelenmiş ve kümelenmemiş dizinleri göz önünde bulundurun. Ayrıca her dizin için arama veya tarama olmak üzere iki erişim yöntemimiz vardır. Bu, her tablo için 2 2 = 4 seçenek olduğu anlamına gelir. Üç tablomuz olduğundan 4³ = 64 seçenek olabilir.

Son olarak, tüm bu koşullar göz önünde bulundurularak 9*3*64 = 1728 olası plan olabilir.

Şimdi sorguya katılan n tablo olduğunu ve her tablonun kümelenmiş bir dizini ve kümelenmemiş dizini olduğunu varsayalım. Aşağıdaki etmenleri inceleyin:

Birleştirme siparişleri: P(n,n-2) = n!/2
Birleştirme türleri: 3^n-1
Arama ve tarama yöntemleriyle farklı dizin türleri: 4ⁿ

Yukarıdaki tüm bunları çarpın ve olası plan sayısını elde edebiliriz: 2*n!*12^n-1. n = 4 olduğunda, sayı 82.944'dür. n = 6 olduğunda, sayı 358.318.080'dir. Bu nedenle, sorguda yer alan tablo sayısındaki artışla, olası planların sayısı geometrik olarak artar. Ayrıca, paralellik olasılığını ve diğer faktörleri de eklerseniz, kaç olası planın dikkate alınacağını hayal edebilirsiniz. Bu nedenle, çok sayıda birleştirme içeren bir sorgunun, daha az birleştirme içeren bir sorgudan daha iyileştirici zaman aşımı eşiğine ulaşma olasılığı daha yüksektir.

Yukarıdaki hesaplamalarda en kötü durum senaryosunun gösterildiğine dikkat edin. Belirttiğimiz gibi, filtre önkoşulları, istatistikler ve kısıtlamalar gibi olasılık sayısını azaltacak faktörler vardır. Örneğin, bir filtre koşulu ve güncelleştirilmiş istatistikler, bir dizin aramasını kullanmak taramadan daha verimli olabileceğinden fiziksel erişim yöntemlerinin sayısını azaltır. Bu aynı zamanda daha küçük bir birleşim seçimine yol açar ve bu şekilde devam eder.

Neden basit bir sorguyla İyileştirici Zaman Aşımı görüyorum?

Sorgu İyileştirici ile hiçbir şey basit değildir. Birçok olası senaryo vardır ve karmaşıklık derecesi o kadar yüksektir ki tüm olasılıkları kavramak zordur. Sorgu İyileştiricisi, belirli bir aşamada bulunan planın maliyetine göre zaman aşımı eşiğini dinamik olarak ayarlayabilir. Örneğin, görece verimli görünen bir plan bulunursa, daha iyi bir plan aramak için görev sınırı azaltılabilir. Bu nedenle, küçümsenmiş kardinalite tahmini (CE), İyileştirici Zaman Aşımına erken basmak için bir senaryo olabilir. Bu durumda araştırmanın odak noktası CE'dir. Bu durum, önceki bölümde açıklanan karmaşık bir sorguyu çalıştırma senaryosuyla karşılaştırıldığında daha nadir görülen bir durumdur, ancak bu mümkündür.

Çözümlemeler

Sorgu planında görüntülenen İyileştirici Zaman Aşımı, düşük sorgu performansının nedeni olduğu anlamına gelmez. Çoğu durumda, bu durumla ilgili hiçbir şey yapmanız gerekmeyebilir. SQL Server'ın sonundaki sorgu planı makul olabilir ve çalıştırdığınız sorgu iyi çalışıyor olabilir. İyileştirici Zaman Aşımı ile karşılaştığınızı asla bilemeyebilirsiniz.

Ayarlama ve iyileştirme gereksinimini bulursanız aşağıdaki adımları deneyin.

1. Adım: Temel oluşturma

Aynı sorguyu farklı bir SQL Server derlemesinde, farklı bir CE yapılandırmasında veya farklı bir sistemde (donanım belirtimleri) aynı veri kümesiyle yürütebilir olup olmadığını denetleyin. Performans ayarlamada yol gösteren bir ilke "temel olmadan performans sorunu yoktur." şeklindedir. Bu nedenle, aynı sorgu için bir temel oluşturmak önemlidir.

2. Adım: İyileştirici Zaman Aşımına neden olan "gizli" koşulları arayın

Karmaşıklığını belirlemek için sorgunuzu ayrıntılı olarak inceleyin. İlk incelemeden sonra, sorgunun karmaşık olduğu ve birçok birleştirme içerdiği açıkça belli olmayabilir. Burada yaygın bir senaryo, görünümlerin veya tablo değerli işlevlerin dahil olmasıdır. Örneğin, yüzeyde sorgu iki görünümü birleştirdiği için basit görünebilir. Ancak görünümlerin içindeki sorguları incelediğinizde, her görünümün yedi tabloyu birleştirdiğini görebilirsiniz. Sonuç olarak, iki görünüm birleştirildiğinde 14 tablo birleştirmesi elde edilir. Sorgunuz aşağıdaki nesneleri kullanıyorsa, içindeki temel sorguların nasıl göründüğünü görmek için her nesnenin detayına gidin:

Bu senaryoların tümü için en yaygın çözüm sorguyu yeniden yazmak ve birden çok sorguya bölmektir. Daha fazla ayrıntı için bkz . 7. Adım: Sorguyu iyileştirme.

Alt sorgular veya türetilmiş tablolar

Aşağıdaki sorgu, her birinde 4-5 birleştirme ile iki ayrı sorgu kümesini (türetilmiş tablolar) birleştiren bir örnektir. Ancak, SQL Server tarafından ayrıştırıldıktan sonra, sekiz tablonun katıldığı tek bir sorguda derlenir.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Ortak tablo ifadeleri (CTEs)

Birden çok ortak tablo ifadesi (CTE) kullanmak, sorguyu basitleştirmek ve İyileştirici Zaman Aşımı'nı önlemek için uygun bir çözüm değildir. Birden çok CTE yalnızca sorgunun karmaşıklığını artırır. Bu nedenle, iyileştirici zaman aşımlarını çözerken CTA'ları kullanmak kötü amaçlıdır. CTE'ler sorguyu mantıksal olarak bölmek gibi görünür, ancak bunlar tek bir sorguda birleştirilir ve tabloların tek bir büyük birleşimi olarak iyileştirilir.

Burada, birçok birleştirme içeren tek bir sorgu olarak derlenecek bir CTE örneği verilmiştir. my_cte karşı sorgunun iki nesneli basit bir birleşim olduğu görünebilir, ancak aslında CTE'de birleştirilen yedi tablo daha vardır.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Görünümler

Görünüm tanımlarını denetlediğinizden ve tüm tabloları dahil ettiğinizden emin olun. CTE'lere ve türetilmiş tablolara benzer şekilde birleşimler de görünümlerin içinde gizlenebilir. Örneğin, iki görünüm arasındaki birleştirme sonunda sekiz tablo içeren tek bir sorgu olabilir:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Tablo değerli işlevler (TVF'ler)

Bazı birleşimler, TFV'lerin içinde gizlenmiş olabilir. Aşağıdaki örnek, iki TFV arasında birleştirme olarak görünenleri gösterir ve tablo dokuz tablolu birleştirme olabilir.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Birleşim

Birleşim işleçleri birden çok sorgunun sonuçlarını tek bir sonuç kümesinde birleştirir. Ayrıca birden çok sorguyu tek bir sorguda birleştirir. Daha sonra tek ve karmaşık bir sorgu alabilirsiniz. Aşağıdaki örnek, 12 tablo içeren tek bir sorgu planıyla sonuçlanır.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

3. Adım: Daha hızlı çalışan bir temel sorgunuz varsa sorgu planını kullanın

1. Adım'dan elde ettiğiniz belirli bir temel planın test yoluyla sorgunuz için daha iyi olduğunu belirlerseniz, QO'yi bu planı seçmeye zorlamak için aşağıdaki seçeneklerden birini kullanın:

4. Adım: Plan seçeneklerini azaltma

İyileştirici Zaman Aşımı olasılığını azaltmak için QO'nin plan seçerken göz önünde bulundurması gereken olasılıkları azaltmaya çalışın. Bu işlem, sorguyu farklı ipucu seçenekleriyle test etmeyi içerir. QO ile ilgili çoğu kararda olduğu gibi, dikkate alınması gereken çok çeşitli faktörler olduğundan, seçenekler her zaman yüzey üzerinde belirleyici değildir. Bu nedenle, tek bir garantili başarılı strateji yoktur ve seçilen plan seçilen sorgunun performansını artırabilir veya azaltabilir.

JOIN siparişini zorlama

Sipariş permütasyonlarını ortadan kaldırmak için kullanın OPTION (FORCE ORDER) :

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

JOIN olasılıklarını azaltma

Diğer alternatifler yardımcı olmadıysa, birleştirme ipuçlarıyla fiziksel birleşim işleçlerinin seçeneklerini sınırlayarak sorgu planı birleşimlerini azaltmayı deneyin. Örneğin: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) veya OPTION (MERGE JOIN).

Not: Bu ipuçlarını kullanırken dikkatli olmanız gerekir.

Bazı durumlarda, iyileştiriciyi daha az birleştirme seçeneğiyle sınırlamak, en iyi birleştirme seçeneğinin kullanılamamasına neden olabilir ve sorguyu yavaşlatabilir. Ayrıca bazı durumlarda, iyileştirici (örneğin, satır hedefi) için belirli bir birleştirme gerekir ve bu birleştirme bir seçenek değilse sorgu plan oluşturamayabilir. Bu nedenle, belirli bir sorgu için birleştirme ipuçlarını hedefledikten sonra, daha iyi performans sunan ve İyileştirici Zaman Aşımını ortadan kaldıran bir birleşim bulup bulmadığınızdan emin olun.

Bu tür ipuçlarının nasıl kullanılacağına dair iki örnek aşağıda verilmiştir:

Sorguda yalnızca karma ve döngü birleşimlerine izin vermek ve birleştirme birleştirmeyi önlemek için kullanın OPTION (HASH JOIN, LOOP JOIN) :
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

İki tablo arasında belirli bir birleştirmeyi zorunlu kılma:

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

5. Adım: CE yapılandırmasını değiştirme

Eski CE ile Yeni CE arasında geçiş yaparak CE yapılandırmasını değiştirmeyi deneyin. CE yapılandırmasını değiştirmek, SQL Server sorgu planlarını değerlendirip oluşturduğunda QO'nin farklı bir yol seçmesine neden olabilir. Bu nedenle, bir İyileştirici Zaman Aşımı sorunu ortaya çıksa bile alternatif CE yapılandırması kullanılarak seçilenden daha iyi performans gösteren bir planla sonuçlanmanız mümkündür. Daha fazla bilgi için bkz . En iyi sorgu planını etkinleştirme (Kardinalite Tahmini).

6. Adım: İyileştirici düzeltmelerini etkinleştirme

Sorgu İyileştiricisi düzeltmelerini etkinleştirmediyseniz, aşağıdaki iki yöntemden birini kullanarak bunları etkinleştirmeyi göz önünde bulundurun:

Sunucu düzeyi: T4199 izleme bayrağını kullanın.
Veritabanı düzeyi: SQL Server 2016 ve sonraki sürümleri için veritabanı uyumluluk düzeylerini kullanın ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON veya değiştirin.

QO düzeltmeleri, iyileştiricinin plan araştırmasında farklı bir yol izlemesine neden olabilir. Bu nedenle daha uygun bir sorgu planı seçebilir. Daha fazla bilgi için bkz . SQL Server sorgu iyileştiricisi düzeltme izleme bayrağı 4199 bakım modeli.

7. Adım: Sorguyu geliştirme

Geçici tablolar kullanarak tek bir çok tablolu sorguyu birden çok ayrı sorguya ayırmayı göz önünde bulundurun. Sorguyu bölmek, iyileştirici için görevi basitleştirmenin yollarından yalnızca biridir. Aşağıdaki örneğe bakın:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Sorguyu iyileştirmek için birleştirme sonuçlarının bir bölümünü geçici bir tabloya ekleyerek tek sorguyu iki sorguya ayırmayı deneyin:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Aracılığıyla paylaş