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

  • Makale

SQL Server 2012'den veya önceki bir sürümden 2014'e 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ümden daha yavaş çalışır. Birçok olası neden ve katkıda bulunan faktör olsa da, göreli olarak 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 ortaya çıkan 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. Yürütme planı Özellikler penceresi CE modeli sürümünü bulun.
  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ştiricisi'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, ce değişiklikleri bunun nedeni değildir.

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

Sorgunuz için CE ile ilgili çeşitli sorgu ipuçlarını test edin. SQL Server 2014 için ilgili izleme bayraklarını 4137, 9472 ve 4139 kullanarak sorguyu test edin. 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 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 sağlam seçenek) Sorgu Deposu (QDS) gerektirmez.

  • İyi bir plan yapmaya zorlayın.

    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ı genelinde 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 risk azaltma olarak kullanılmalıdır.

Eski CE'yi etkinleştirme seçenekleri

Sorgu düzeyi: Sorgu İpucu veya QUERYTRACEON seçeneğini kullanın

  • SQL Server 2016 SP1 ve sonraki sürümler 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. İşte bir örnek:

    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ümler 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'den daha fazla etkilendiğ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 gerileyebilir.

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 gerileyebilir.

Sık sorulan sorular

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 makalesinde 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ümler 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çerken 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ımları ve daha derin test kapsamını 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 bu 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 abonelik düzeyinde etkinleştirilip sunucu düzeyinde etkinleştirilmemesi için bir destek bileti oluşturabilirsiniz.

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

LEGACY_CARDINALITY_ESTIMATION etkinleştirildiğinde 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 etkin bir veritabanı, uyarlamalı sorgu işleme özellik ailesinden yararlanmaya devam edebilir.

S6: Eski CE ne zaman destekten çıkarılacak?

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ümlerine göre 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 olduğundan ve özel izinler gerektirmediğinden bunu kullanmayı USE HINT düşünmelisiniz.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION , veritabanının uyumluluk düzeyine bakılmaksızın 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 kullanın.

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 .

CE karmaşık bir sorundur ve algoritmalar, tablolar ve dizinler için istatistikler gibi tahminler için sağlanan mükemmelden daha az veriye dayanır. Tablo değerli işlevler (TVF'ler) ve birçok varsayıma dayalı modeller (koşul ve sütunların bağıntı veya bağımsızlığı, tekdüzen veri dağıtımı, kapsama vb.) gibi bazı model dışı yapılarla ilgili bilgi yoktur.

Müşteri şeması, veri ve iş yüklerinin sınırsız birleşimi 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ımlarda olduğu gibi) ve düzeltilebilirken, 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 varsayalım. 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 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 bilgi edinmek için kaynak var mı?

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