SQL Server 2012 veya önceki bir sürümden 2014 veya sonraki bir sürüme yükseltme sonrasında sorgu performansında düşüş

SQL Server'ı 2012'den veya önceki bir sürümden 2014 veya sonraki bir sürüme yükselttikten sonra şu sorunla karşılaşabilirsiniz: özgün sorguların çoğu iyi çalışır, ancak sorgularınızın birkaçı önceki sürüme göre daha yavaş çalışır. Birçok olası neden ve katkıda bulunan faktör olsa da, nispeten yaygın nedenlerden biri, yükseltmeden sonra Kardinalite Tahmini (CE) modelindeki değişikliklerdir. SQL Server 2014'te başlayan CE modellerinde önemli değişiklikler yapılmıştır.

Bu makalede, varsayılan CE kullanılırken oluşan ancak eski CE kullanılırken gerçekleşmeyen sorgu performansı sorunları için sorun giderme adımları ve çözümleri sağlanır.

Not

Yükseltmeden sonra tüm sorgular daha yavaş çalışıyorsa, bu makalede sunulan sorun giderme adımları büyük olasılıkla sizin durumunuz için geçerli değildir.

Sorun giderme: Sorunun CE değişiklikleri olup olmadığını belirleyin ve nedenini öğrenin

1. Adım: Varsayılan CE'nin kullanılıp kullanılmadiğini belirleme

1. Yükseltmeden sonra daha yavaş çalışan bir sorgu seçin.
2. Sorguyu çalıştırın ve yürütme planını toplayın.
3. Yürütme planı Özellikler penceresi CardinalityEstimationModelVersion öğesini denetleyin.
4. 70 değeri eski CE değerini, 120 veya üzeri bir değer ise varsayılan CE'nin kullanımını gösterir.

Eski CE kullanılıyorsa, performans sorununun nedeni CE değişiklikleri değildir. Varsayılan CE kullanılıyorsa sonraki adıma geçin.

2. Adım: Sorgu İyileştirici'nin eski CE kullanarak daha iyi bir plan oluşturabileceğini belirleme

Sorguyu eski CE ile çalıştırın. Varsayılan CE'yi kullanmaktan daha iyi performans gösterirse sonraki adıma geçin. Performans iyiye gitmiyorsa bunun nedeni CE değişiklikleri değildir.

3. Adım: Sorgunun eski CE ile neden daha iyi performans sergileyebini öğrenin

Sorgunuz için CE ile ilgili çeşitli sorgu ipuçlarını test edin. SQL Server 2014 için, sorguyu test etmek için karşılık gelen 4137, 9472 ve 4139 izleme bayraklarını kullanın. Bu testlere göre performansı olumlu yönde etkileyen ipuçlarını veya izleme bayraklarını belirleyin.

Çözüm

Sorunu çözmek için aşağıdaki yöntemlerden birini deneyin:

• Sorguyu iyileştirin.

  Anlaşılır bir şekilde, sorguları yeniden yazmak her zaman mümkün değildir, ancak özellikle de yeniden yazılabilir yalnızca birkaç sorgu olduğunda, bu yaklaşım ilk seçenek olmalıdır. Ce sürümlerinden bağımsız olarak en iyi şekilde yazılmış sorgular daha iyi performans gösterir.

• 3. Adımda tanımlanan sorgu ipuçlarını kullanın.

  Bu hedeflenen yaklaşım, diğer iş yüklerinin varsayılan CE varsayımlarından ve geliştirmelerinden yararlanmasını sağlar. Ayrıca, plan kılavuzu oluşturmaktan daha sağlam bir seçenektir. Bir planı zorlamanın aksine (en güçlü seçenek) Sorgu Deposu (QDS) gerektirmez.

• İyi bir plan yapmaya zorla.

  Bu uygun bir seçenektir ve belirli sorguları hedeflemek için kullanılabilir. Planı zorlamak bir plan kılavuzu veya QDS kullanılarak yapılabilir. QDS'nin kullanımı genellikle daha kolaydır.

• Eski CE'yi zorlamak için veritabanı kapsamlı yapılandırmayı kullanın.

  Bu, veritabanı genelindeki bir ayar olduğundan ve bu veritabanındaki tüm sorgular için geçerli olduğundan daha az tercih edilen bir yaklaşımdır. Yine de, hedeflenen bir yaklaşım uygun olmadığında bazen gereklidir. Bu kesinlikle uygulanması en kolay seçenektir.

• Eski CE'yi genel olarak zorlamak için izleme bayrağı 9841'i kullanın. Bunu yapmak için DBCC TRACEON kullanın veya izleme bayrağını başlangıç parametresi olarak ayarlayın.

  Bu en az hedeflenen yaklaşımdır ve yalnızca diğer seçeneklerden herhangi birini uygulayamadığınızda geçici bir azaltma olarak kullanılmalıdır.

Eski CE'yi etkinleştirme seçenekleri

Sorgu düzeyi: Sorgu İpucu veya QUERYTRACEON seçeneğini kullanma

• SQL Server 2016 SP1 ve sonraki sürümleri için sorgunuz için ipucunu FORCE_LEGACY_CARDINALITY_ESTIMATION kullanın, örneğin:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Eski bir CE planını zorlamak için izleme bayrağı 9481'i etkinleştirin. Bir örnek aşağıda verilmiştir:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Veritabanı düzeyi: Kapsamlı yapılandırma veya uyumluluk düzeyini ayarlama

• SQL Server 2016 ve sonraki sürümleri için veritabanı kapsamlı yapılandırmayı değiştirin:

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Veritabanı için uyumluluk düzeyini değiştirin. SQL Server 2014 için kullanılabilen tek veritabanı düzeyi seçeneğidir. Bu değişikliğin yalnızca CE'yi etkilemediğini unutmayın. Uyumluluk düzeyi değişikliklerinin etkisini belirlemek için ALTER DATABASE uyumluluk düzeyine (Transact-SQL) gidin ve içindeki "Farklar" tablolarını inceleyin.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Not

Geçersiz kılma izleme bayrağı veya sorgu ipucu kullanılmadığı sürece bu değişiklik, yapılandırmanın değiştirildiği veritabanı bağlamında yürütülen tüm sorguları etkiler. Varsayılan CE nedeniyle daha iyi performans gösteren sorgular gerilenebilir.

Sunucu düzeyi: İzleme bayrağını kullanma

Sunucu genelinde eski CE'yi zorlamak için izleme bayrağı 9481'i kullanın:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Not

Geçersiz kılma izleme bayrağı veya sorgu ipucu kullanılmadığı sürece bu değişiklik SQL Server örneği bağlamında yürütülen tüm sorguları etkiler. Varsayılan CE nedeniyle daha iyi performans gösteren sorgular gerilenebilir.

Sık sorulan sorular

S1: SQL Server'ın daha yeni bir sürümüne yükseltmeyle ilgileniyorum ve kardinalite tahmin aracı performans regresyonları konusunda endişeliyim. Sorunları en aza indirmek için hangi yükseltme planlaması önerilir?

Daha düşük uyumluluk düzeylerinde çalışan önceden var olan veritabanları için, sorgu işlemcisini daha yüksek bir uyumluluk düzeyine yükseltmek için önerilen iş akışı Veritabanı Uyumluluk Modunu Değiştirme ve Sorgu Deposu ve Sorgu Deposu Kullanım Senaryolarını Kullanma bölümünde ayrıntılı olarak açıklanmaktadır. Makalede sunulan metodoloji, SQL Server ve Azure SQL Veritabanı için 130 veya üzeri sürümlere taşımalar için geçerlidir.

S2: CE değişikliklerini test etmek için zamanım yok. Bu durumda ne yapabilirim?

Önceden var olan uygulamalar ve iş yükleri için, yeterli regresyon testi gerçekleştirilinceye kadar varsayılan CE'ye geçmenizi önermeyiz. Hala şüpheleriniz varsa SQL Server'ı yükseltmenizi ve en son kullanılabilir uyumluluk düzeyine geçmenizi öneririz. Önlem olarak, SQL Server 2014 için izleme bayrağı 9481'i etkinleştirin veya test etme fırsatı bulana kadar SQL Server 2016 ve sonraki sürümleri için LEGACY_CARDINALITY_ESTIMATION veritabanı kapsamlı yapılandırmasını ON yapılandırın.

S3: Eski CE'yi kalıcı olarak kullanmanın herhangi bir dezavantajı var mı?

Gelecekteki kardinalite tahmin aracıyla ilgili iyileştirmeler ve düzeltmeler, daha yeni sürümler etrafında ortalanır. Sürüm 70 kabul edilebilir bir ara durumdur. Ancak, dikkatli bir testten sonra en son CE düzeltmelerinden yararlanmak için daha yeni bir CE sürümüne geçmenizi öneririz. Eski CE'den geçiş yaparken sorgu planı değişiklikleri olasılığı yüksektir, bu nedenle üretim sistemlerinde değişiklik yapmadan önce test edin. Değişiklikler birçok durumda sorgu performansını geliştirebilir, ancak bazı durumlarda sorgu performansı düşebilir.

Önemli

Varsayılan CE, uzun vadede gelecekteki yatırım ve daha derin test kapsamı alacak ana kod yoludur, bu nedenle eski CE'yi süresiz olarak kullanmayı planlamayın.

S4: Binlerce veritabanım var ve her biri için LEGACY_CARDINALITY_ESTIMATION el ile açmak istemiyorum. Alternatif bir yöntem var mı?

SQL Server 2014 için, uyumluluk düzeyinden bağımsız olarak tüm veritabanları için eski CE'yi kullanmak üzere izleme bayrağı 9481'i etkinleştirin. SQL Server 2016 ve sonraki sürümlerde veritabanlarında yineleme yapmak için aşağıdaki sorguyu yürütür. Veritabanı geri yüklendiğinde veya başka bir sunucuya eklendiğinde bile ayar etkinleştirilir.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Azure SQL Veritabanı için, bu izleme bayrağının sunucu düzeyinde değil abonelik düzeyinde etkinleştirilmesi için bir destek bileti oluşturabilirsiniz.

S5: Eski CE ile çalışmak yeni özelliklere erişmemi engelleyecek mi?

LEGACY_CARDINALITY_ESTIMATION etkin olsa bile SQL Server sürümüne ve ilişkili veritabanı uyumluluk düzeyine dahil olan en son işlevlere erişmeye devam edersiniz. Örneğin, SQL Server 2017'de veritabanı uyumluluk düzeyi 140'ta çalıştırılan LEGACY_CARDINALITY_ESTIMATION etkinleştirilmiş bir veritabanı, uyarlamalı sorgu işleme özelliği ailesinden yararlanmaya devam edebilir.

S6: Eski CE ne zaman destekten çıkacak?

Bu noktada eski CE'yi desteklemeyi durdurma planlarımız yok. Ancak, gelecekteki kardinalite tahmin aracıyla ilgili iyileştirmeler ve düzeltmeler CE'nin daha yeni sürümleri etrafında ortalanır.

S7: Varsayılan CE ile gerileyen yalnızca birkaç sorgum var, ancak çoğu sorgu performansı aynı, hatta geliştirilmiş. Ne yapmalıyım?

Sunucu kapsamlı izleme bayrağı 9481 veya LEGACY_CARDINALITY_ESTIMATION veritabanı kapsamlı yapılandırmasına daha ayrıntılı bir alternatif, sorgu kapsamlı USE HINT yapısının kullanılmasıdır. Daha fazla bilgi için bkz . SQL Server 2016'da USE HINT sorgu ipucu bağımsız değişkeni ve USE HINT.

Not

İzleme bayrağı 9481 olan bir QUERYTRACEON seçenek de vardır, ancak bunun yerine daha temiz ve özel izinler gerektirmediğinden bunu kullanmayı USE HINT düşünmelisiniz.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION , veritabanının uyumluluk düzeyinden bağımsız olarak sorgu iyileştirici CE modelini sürüm 70 olarak ayarlamanıza olanak tanır. Bkz . Sorgu düzeyi: Sorgu İpucu veya QUERYTRACEON seçeneğini kullanma.

Alternatif olarak, varsayılan CE ile sorunlu olan tek bir sorgu varsa, Sorgu Deposu'nda depolanan eski bir CE planını zorlayabilir veya bir plan kılavuzuyla birlikte kullanabilirsiniz FORCE_LEGACY_CARDINALITY_ESTIMATION .

S8: Varsayılan CE kullanılırken önemli ölçüde fazla veya düşük tahminlerle ilgili bir plan değişikliği nedeniyle sorgu performansı gerilediyse, sorun üründe düzeltilecek mi?

CE karmaşık bir sorundur ve algoritmalar, tablolar ve dizinler için istatistikler gibi tahminler için kullanılabilir olan mükemmel olmayan verileri kullanır. Tablo değerli işlevler (TVF'ler) ve birçok varsayıma (koşul ve sütunların bağıntı veya bağımsızlığı, tekdüzen veri dağıtımı, kapsama vb.) dayalı modeller gibi bazı model dışı yapılar için bilgi yoktur.

Müşteri şeması, veri ve iş yüklerinin sınırsız birleşimleri göz önünde bulundurulduğunda, tüm servis talepleri için uygun modelleri seçmek neredeyse imkansızdır. Varsayılan CE'deki bazı değişiklikler hatalar içerebilir (diğer yazılımlar gibi) ve düzeltilebilir, ancak diğer sorunlar model değişikliğinden kaynaklanabilir.

CE sürümlerinde, özellikle 70'ten 120'ye kadar olan değişiklikler, kullanılan modeller için birçok farklı seçenek içerir. Örneğin, filtreleri tahmin ederken, bu tür bağıntıların sıklıkla mevcut olması ve CE modeli 70'in bu gibi durumlarda sonuçları hafife alması nedeniyle, koşul arasında bir bağıntı düzeyi olduğunu varsayın. Bu değişiklikler birçok iş yükü için test edilmiş ve birçok sorgu geliştirilmiş olsa da, diğer bazı sorgular için eski CE daha iyi bir eşleşmeydi ve bu nedenle varsayılan CE ile performans regresyonları gözlemlenebilir.

Ne yazık ki bu bir hata olarak kabul edilmez. Bu gibi durumlarda, sorgu performansı kabul edilebilir değilse eski CE ile yapmanız gereken gibi sorguyu ayarlama veya önceki bir CE modelini veya belirli bir yürütme planını zorlama gibi bir geçici çözüm kullanın.

S9: Varsayılan CE'deki kardinalite değişiklikleri ve sorgu performansı etkisi hakkında ayrıntılı bilgi edinmek için kaynak var mı?

Ayrıntılar için bkz . SQL Server 2014 Kardinalite Tahmin Aracı ile Sorgu Planlarınızı İyi hale getirme ve "SQL Server 2014'te Ne Değişti?" bölümünü okuyun.