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

  • Makale

Şunlar için geçerlidir: SQL Server

Bu makalede İyileştirici Zaman Aşımı, sorgu performansını nasıl etkileyebileceği ve performansı nasıl iyileştirebileceğiniz anlatılarak anlatı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ı Bir Sorgu İyileştiricisi, birden çok sorgu planı oluşturup değerlendirdikten sonra en düşük maliyetli sorgu yürütme planını 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ı, Deyim İyileştirmesinin Erken Sonlandırılmasına Neden Olan Neden 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, zaman aşımına ulaşılana kadar yarım milyondan fazla görev dikkate alınır.

İyileştirici Zaman Aşımı SQL Server olarak 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ına neden olabilir. 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 iyileştirme olanakları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, 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 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ştirilmiş).

  • 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ğerinZaman Aşımı olduğunu görebilirsiniz.

      SSMS'de sorgu planında iyileştirici zaman aşımını gösteren ekran görüntüsü.

İyileştirici Zaman Aşımına neden olan nedir?

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

  • Tablolar hangi sırayla birleştirilmelidir?

    Üç tablolu birleştirmelerin yürütme seçeneklerine (Table1, Table2, Table3) bir örnek aşağıda verilmiştir:

    • ve sonucu ile Table2 birleştirin Table1Table3
    • ve sonucu ile Table3 birleştirin Table1Table2
    • ve sonucu ile Table3 birleştirin Table2Table1

    Not: Tablo sayısı ne kadar büyük olursa olasılıklar da o kadar büyük olur.

  • Tablodaki satırları almak için hangi yığın veya ikili ağaç (HoBT) erişim yapısı kullanılacak?

    • Kümelenmiş
    • 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 kombinasyonları

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ütmesi görebilirsiniz.

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

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

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

  1. NJ - NJ
  2. NJ - HJ
  3. NJ - MJ
  4. HJ - NJ
  5. HJ - HJ
  6. HJ - MJ
  7. MJ - NJ
  8. MJ - HJ
  9. 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 t2 ve ardından ile katılma t1t3
  • ile t3 ve ardından ile katılma t1t2
  • ile t3 ve ardından ile 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 22 = 4 seçenek olduğu anlamına gelir. Üç tablomuz olduğundan 43 = 64 seçenek olabilir.

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

Şimdi sorguda birleştirilmiş n tablo olduğunu ve her tablonun kümelenmiş bir dizine ve kümelenmemiş dizine sahip olduğunu varsayalım. Aşağıdaki faktörleri göz önünde bulundurun:

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

Yukarıdaki tüm bunları çarpın ve olası plan sayısını elde edebiliriz: 2*n!*12n-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 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ğini unutmayın. Belirttiğimiz gibi, filtre koşullarını, istatistikleri 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üçümsenen 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, önceki bölümde açıklanan karmaşık bir sorgu çalıştırma senaryosuna kıyasla daha nadir bir durumdur, ancak bu mümkündür.

Çözümler

Sorgu planında görüntülenen İyileştirici Zaman Aşımı, sorgu performansının düşük olmasına neden olduğu anlamına gelmez. Çoğu durumda, bu durumla ilgili hiçbir şey yapmanıza gerek olmayabilir. SQL Server ile biten 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

Farklı bir CE yapılandırması kullanarak veya farklı bir sistemde (donanım belirtimleri) farklı bir SQL Server derlemesinde aynı sorguyu aynı veri kümesiyle yürütip yürütemediğiniz kontrol edin. Performans ayarlamada yol gösterici bir ilke", "temel olmadan performans sorunu yoktur." Bu nedenle, aynı sorgu için bir temel oluşturmak önemlidir.

2. Adım: İyileştirici Zaman Aşımına yol açan "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 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ğinden 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 daraltma .

Alt sorgular veya türetilmiş tablolar

Aşağıdaki sorgu, her birinde 4-5 birleşim bulunan iki ayrı sorgu kümesini (türetilmiş tablolar) birleştiren bir örnektir. Ancak, SQL Server tarafından ayrıştırıldıktan sonra, sekiz tablo birleştirilmiş 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 (CTE' ler)

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 CTE'leri kullanmak kötü amaçlıdır. CTE'ler sorguyu mantıksal olarak kesmeye benzer, ancak 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'ye katılmış 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 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 gizlenebilir. Aşağıdaki örnek, iki TFV arasında birleştirme olarak görünen öğeleri 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 ...

Birliği

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. Ardından 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çimlerini 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 olmalısınız.

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 gereklidir ve bu birleştirme bir seçenek değilse sorgu bir 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ştirmesini ö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 yaşansa bile alternatif CE yapılandırmasını kullanarak 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ştirici düzeltmelerini etkinleştirmediyseniz, aşağıdaki iki yöntemden birini kullanarak bunları etkinleştirmeyi göz önünde bulundurun:

  • Sunucu düzeyi: İzleme bayrağı T4199 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 almasına neden olabilir. Bu nedenle daha uygun bir sorgu planı seçebilir. Daha fazla bilgi için bkz. SQL Server sorgu iyileştirici 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 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 ...