Aracılığıyla paylaş

SQL Server ve Azure SQL Yönetilen Örneği sorgu performansı sorunlarının algılanabilir türleri

  • Makale

Şunlar için geçerlidir:SQL Server Azure SQL Yönetilen Örneği

Performans sorunlarını çözmek için ilgili sorunun sorgu çalışır durumdayken mi yoksa bekleme durumundayken mi ortaya çıktığını tespit edin. Bu tespite göre farklı çözümler uygulamanız gerekecektir. Her sorun türüyle ilgili sorunlar ve çözümler bu makalede ele alınıyor.

Bu tür performans sorunlarını algılamak için SQL Server DMV'lerini kullanabilirsiniz.

Çalışanla ilgili sorunlar: Çalışanla ilgili sorunlar genellikle yetersiz veya fazla kullanılan kaynaklarla ilgili yetersiz sorgu planı veya yürütme sorunlarıyla sonuçlanan derleme sorunlarıyla ilgilidir. Beklemeyle ilgili sorunlar: Beklemeyle ilgili sorunlar genellikle şunlarla ilgilidir:

  • Kilitler (engelleme)
  • G/Ç
  • Kullanımla ilgili çekişme tempdb
  • Bellek verme beklemeleri

Bu makale SQL Server ve Azure SQL Yönetilen Örneği hakkındadır. Ayrıca bkz. Azure SQL Veritabanı'de sorgu performansı sorunlarını algılanabilir türler.

En iyi olmayan sorgu planına neden olan derleme sorunları

SQL Sorgu İyileştiricisi tarafından oluşturulan bir alt çalışma planı, yavaş sorgu performansının nedeni olabilir. EKSIK dizin, eski istatistikler, işlenecek satır sayısıyla ilgili yanlış bir tahmin veya gerekli belleğin yanlış tahmini nedeniyle SQL Sorgu İyileştiricisi bir alt çalışma planı üretebilir. Sorgunun geçmişte veya başka bir örnekte daha hızlı yürütüldüklerini biliyorsanız, farklı olup olmadığını görmek için gerçek yürütme planlarını karşılaştırın.

  • DMV'lerde ve sorgu yürütme planlarında eksik dizinleri kullanarak eksik dizinleri belirleyin. Bu makalede, eksik dizin isteklerini kullanarak kümelenmemiş dizinleri algılama ve ayarlama işlemleri gösterilmektedir.
  • daha iyi bir plan elde etmek için istatistikleri güncelleştirmeyi veya dizinleri yeniden oluşturmayı deneyin. Bu sorunları otomatik olarak azaltmak için veritabanının otomatik plan düzeltmesini etkinleştirin.
  • Gelişmiş bir sorun giderme adımı olarak, kod değişikliği yapmadan Sorgu Deposu'nu kullanarak sorgu ipuçlarını uygulamak için Sorgu Deposu ipuçlarını kullanın.
  • Veritabanı uyumluluk düzeyini değiştirmeyi ve akıllı sorgu işlemeyi uygulamayı deneyin. SQL Sorgu İyileştiricisi, veritabanınızın uyumluluk düzeyine bağlı olarak farklı bir sorgu planı oluşturabilir. Daha yüksek uyumluluk düzeyleri daha akıllı sorgu işleme özellikleri sağlar.

Performans için uygulamaları ve veritabanlarını ayarlama makalesindeki sorgu ayarlama ve ipucu örneği, parametreli sorgu nedeniyle bir altoptimal sorgu planının etkisini, bu koşulu algılamayı ve çözümlemek için sorgu ipucunun nasıl kullanılacağını gösterir.

En iyi durumda olmayan sorgu yürütme planlarına sahip sorguları düzeltme

Aşağıdaki bölümlerde, en iyi olmayan sorgu yürütme planıyla sorguların nasıl çözümleneceğini açıklanmıştır.

Parametreye duyarlı plan (PSP) sorunları olan sorgular

Parametreye duyarlı plan (PSP) sorunu, sorgu iyileştiricisi yalnızca belirli bir parametre değeri (veya değer kümesi) için en uygun sorgu yürütme planını oluşturduğunda ve önbelleğe alınan plan ardışık yürütmelerde kullanılan parametre değerleri için en uygun durumda olmadığında oluşur. Daha sonra en uygun olmayan planlar sorgu performansı sorunlarına neden olabilir ve genel iş yükü aktarım hızını düşürebilir.

Parametre algılama ve sorgu işleme hakkında daha fazla bilgi için bkz . Sorgu işleme mimarisi kılavuzu.

Çeşitli geçici çözümler PSP sorunlarını hafifletebilir. Her geçici çözümün ilişkili dezavantajları ve dezavantajları vardır:

Sorgu ipuçlarını uygulamak için sorguyu değiştirin veya kod değişikliği yapmadan ipucunu uygulamak için Sorgu Deposu ipuçlarını kullanın. SQL Server'ın SQL Server 2022 öncesi sürümlerinde plan kılavuzlarını kullanın.

PSP sorunlarını çözme hakkında daha fazla bilgi için şu blog gönderilerine bakın:

Yanlış parametreleştirmenin neden olduğu derleme etkinliği

Sorgunun değişmez değerleri olduğunda, veritabanı altyapısı deyimini otomatik olarak parametrelendirir veya kullanıcı derleme sayısını azaltmak için deyimi açıkça parametreleştirir. Aynı kalıbı farklı değişmez değerlerle kullanan bir sorgu için çok sayıda derleme olması yüksek CPU kullanımına neden olabilir. Benzer şekilde, değişmez değerlerini koruyan bir sorguyu yalnızca kısmen parametreleştirirseniz veritabanı altyapısı sorguya ek parametreleştirme uygulamaz.

Aşağıda kısmen parametreli bir sorgu örneği verilmiştir:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Bu örnekte alır, t1.c1 @p1ancak t2.c2 GUID değerini değişmez değer olarak almaya devam eder. Bu durumda değerini değiştirirseniz c2, sorgu farklı bir sorgu olarak değerlendirilir ve yeni bir derleme gerçekleşir. Bu örnekteki derlemeleri azaltmak için GUID'yi de parametreleştirebilirsiniz.

Aşağıdaki sorgu, sorgunun düzgün parametrelendirilip parametrelendirilmediğini belirlemek için sorgu karması tarafından sorgu sayısını gösterir:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Sorgu planı değişikliklerini etkileyen faktörler

Sorgu yürütme planı yeniden derlemesi, özgün önbelleğe alınmış plandan farklı bir sorgu planı oluşturulmasına neden olabilir. Mevcut özgün plan çeşitli nedenlerle otomatik olarak yeniden derlenebilir:

  • Şemadaki değişikliklere sorgu tarafından başvurulur
  • Tablolardaki veri değişikliklerine sorgu tarafından başvurulur
  • Sorgu bağlamı seçenekleri değiştirildi

Derlenmiş bir plan aşağıdakiler gibi çeşitli nedenlerle önbellekten çıkarılabilir:

  • Örnek yeniden başlatmaları
  • Veritabanı kapsamlı yapılandırma değişiklikleri
  • Bellek baskısı
  • Önbelleği temizlemek için açık istekler

YENIDEN DERLE ipucu kullanırsanız, plan önbelleğe alınmaz.

Yeniden derleme (veya önbellek çıkarma işleminden sonra yeni derleme), yine de özgün ile aynı sorgu yürütme planının oluşturulmasına neden olabilir. Plan önceki veya özgün plandan değiştiğinde, şu açıklamalar olasıdır:

  • Fiziksel tasarım değiştirildi: Örneğin, yeni oluşturulan dizinler bir sorgunun gereksinimlerini daha etkili bir şekilde kapsar. Sorgu iyileştiricisi bu yeni dizini kullanmanın ilk sorgu yürütme sürümü için seçilen veri yapısını kullanmaktan daha uygun olduğuna karar verirse, yeni dizinler yeni bir derlemede kullanılabilir. Başvuruda bulunan nesnelerde yapılan tüm fiziksel değişiklikler, derleme zamanında yeni bir plan seçimine neden olabilir.

  • Sunucu kaynağı farklılıkları: Bir sistemdeki plan başka bir sistemdeki plandan farklı olduğunda, kullanılabilir işlemci sayısı gibi kaynak kullanılabilirliği, hangi planın oluşturulduğunu etkileyebilir. Örneğin, bir sistemde daha fazla işlemci varsa paralel bir plan seçilebilir. Paralellik hakkında daha fazla bilgi için bkz . En yüksek paralellik derecesini yapılandırma Sunucu Yapılandırma Seçeneği.

  • Farklı istatistikler: Başvuruda bulunılan nesnelerle ilişkili istatistikler değişmiş olabilir veya özgün sistemin istatistiklerinden önemli ölçüde farklı olabilir. İstatistikler değişirse ve yeniden derleme gerçekleşirse, sorgu iyileştiricisi değiştirildiğinden itibaren istatistikleri kullanır. Düzeltilen istatistiklerin veri dağılımları ve sıklıkları, özgün derlemenin veri dağılımlarından farklı olabilir. Bu değişiklikler kardinalite tahminleri oluşturmak için kullanılır. (Kardinalite tahminleri , mantıksal sorgu ağacından akması beklenen satır sayısıdır.) Kardinalite tahminlerinde yapılan değişiklikler farklı fiziksel işleçler ve ilişkili işlem siparişleri seçmenize neden olabilir. İstatistiklerde yapılan küçük değişiklikler bile sorgu yürütme planının değiştirilmesine neden olabilir.

  • Veritabanı uyumluluk düzeyi veya kardinalite tahmin aracı sürümü değiştirildi: Veritabanı uyumluluk düzeyinde yapılan değişiklikler, farklı bir sorgu yürütme planına neden olabilecek yeni stratejileri ve özellikleri etkinleştirebilir. Veritabanı uyumluluk düzeyinin ötesinde, devre dışı bırakılmış veya etkin izleme bayrağı 4199 veya veritabanı kapsamlı yapılandırma QUERY_OPTIMIZER_HOTFIXES değiştirilmiş durumu, derleme zamanında sorgu yürütme planı seçimlerini de etkileyebilir. İzleme bayrakları 9481 (eski CE'yi zorla) ve 2312 (varsayılan CE'yi zorla) planı da etkiler.

Azure SQL Yönetilen Örneği kaynak sınırları sorunları

Yetersiz sorgu planlarına ve eksik dizinlere ilişkin yavaş sorgu performansı genellikle yetersiz veya fazla kullanılan kaynakla ilgilidir. Sorgu planı en uygun durumdaysa, sorgu (ve veritabanı) yönetilen örneğin kaynak sınırlarına ulaşıyor olabilir. Hizmet düzeyi için günlük yazma aktarım hızının fazla olması bir örnek olabilir.

Sorunu yetersiz kaynak olarak tanımlarsanız, CPU gereksinimlerini almak için veritabanınızın kapasitesini artırmak için kaynakları yükseltebilirsiniz. Yönetilen örneği ölçeklendirme hakkında bilgi için bkz. Hizmet katmanı kaynak sınırları

Artam iş yükü hacminin neden olduğu performans sorunları

Uygulama trafiğinde ve iş yükü hacminde artış CPU kullanımının artmasına neden olabilir. Ancak bu sorunu düzgün bir şekilde tanılamak için dikkatli olmanız gerekir. Yüksek CPU ile ilgili bir sorun gördüğünüzde, artışın iş yükü birimindeki değişikliklerden kaynaklanıp kaynak edilmediğini belirlemek için şu soruları yanıtlayın:

  • Yüksek CPU sorununun nedeni uygulamadaki sorgular mı?

  • Belirleyebileceğiniz en çok CPU tüketen sorgular için:

    • Aynı sorguyla birden çok yürütme planı ilişkilendirildi mi? Öyleyse, neden?
    • Aynı yürütme planına sahip sorgular için yürütme süreleri tutarlı mıydı? Yürütme sayısı arttı mı? Öyleyse, iş yükü artışı performans sorunlarına neden olabilir.

Özetle, sorgu yürütme planı farklı şekilde yürütülmezse ancak yürütme sayısıyla birlikte CPU kullanımı artmışsa, performans sorunu büyük olasılıkla bir iş yükü artışıyla ilgilidir.

CPU sorununa neden olan bir iş yükü hacmi değişikliğini tanımlamak her zaman kolay değildir. Şu faktörleri göz önünde bulundurun:

  • Değiştirilen kaynak kullanımı: Örneğin, CPU kullanımının uzun bir süre için yüzde 80'e yükseldiği bir senaryo düşünün. Yalnızca CPU kullanımı, iş yükü hacminin değiştiği anlamına gelmez. Sorgu yürütme planındaki regresyonlar ve veri dağıtımındaki değişiklikler, uygulama aynı iş yükünü yürütse bile daha fazla kaynak kullanımına katkıda bulunabilir.

  • Yeni sorgunun görünümü: Bir uygulama farklı zamanlarda yeni bir sorgu kümesi yönlendirebilir.

  • İstek sayısındaki artış veya azalma: Bu senaryo, bir iş yükünün en belirgin ölçüsüdür. Sorgu sayısı her zaman daha fazla kaynak kullanımına karşılık gelmez. Ancak, diğer faktörlerin değişmemiş olduğu varsayıldığında bu ölçüm hala önemli bir sinyaldir.

  • Paralellik: Aşırı paralellik, cpu ve çalışan iş parçacığı kaynaklarının diğer sorgularını aç bırakarak diğer eşzamanlı iş yükü performansını kötüleştirebilir. Paralellik hakkında daha fazla bilgi için bkz . En yüksek paralellik derecesini yapılandırma Sunucu Yapılandırma Seçeneği.

Yürütme sorunlarıyla ilgili bir yetersiz planı ve Beklemeyle ilgili sorunları ortadan kaldırdıktan sonra, performans sorunu genellikle sorguların büyük olasılıkla bazı kaynakları beklemesidir. Beklemeyle ilgili sorunların kaynağı şunlar olabilir:

  • Engelleme:

    Sorgulardan biri veritabanındaki nesnelerin kilidini tutarken diğerleri de aynı nesnelere erişmeye çalışıyor olabilir. DMV’leri kullanarak engelleyici sorguları tanımlayabilirsiniz. Daha fazla bilgi için bkz. Engelleme sorunlarını anlama ve çözme.

  • GÇ sorunları

    Sorgular, sayfaların verilere veya günlük dosyalarına yazılması için bekliyor olabilir. Bu durumda, DMV’deki INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG veya PAGEIOLATCH_* bekleme istatistiklerini denetleyin. Bkz. GÇ performansı sorunlarını belirlemek için DMV’leri kullanma.

  • Tempdb sorunları

    İş yükü geçici tablolar kullanıyorsa veya planlarda tempdb taşmaları varsa, sorguların tempdb aktarım hızıyla ilgili bir sorunu olabilir. Daha fazla araştırma yapmak için tempdb sorunlarını belirleme bölümünü gözden geçirin.

  • Bellekle ilgili sorunlar

    İş yükünde yeterli bellek yoksa, sayfa ömrü beklentisi düşebilir veya sorgular gerekenden daha az bellek alabilir. Bazı durumlarda, Sorgu İyileştiricisi’ndeki yerleşik zeka bellekle ilgili sorunları çözer. Bkz. Bellek verme sorunlarını belirlemek için DMV’leri kullanma. Yetersiz bellek hatalarıyla karşılaşırsanız sys.dm_os_out_of_memory_events içeriğini gözden geçirin. Ayrıca daha yüksek sanal çekirdek/bellek oranlarına sahip Azure SQL Yönetilen Örneği donanımın Bellek için iyileştirilmiş premium serisi katmanını da göz önünde bulundurun.

En iyi bekleme kategorilerini gösterme yöntemleri

Bu yöntemler genellikle en fazla bekleme türü kategorilerini göstermek için kullanılır:

  • Zaman içindeki her sorgu için bekleme istatistiklerini bulmak için Sorgu Deposu'na bakın. Sorgu Deposu'nda bekleme türleri bekleme kategorileri halinde birleştirilir. Bekleme kategorilerinin eşlemesini sys.query_store_wait_stats'de bekleme türleri bulabilirsiniz.
  • Sorgu işlemi sırasında yürütülen iş parçacıkları tarafından karşılaşılan tüm beklemeler hakkında bilgi döndürmek için sys.dm_os_wait_stats kullanın. Azure SQL Yönetilen Örneği veya SQL Server örneğiyle ilgili performans sorunlarını tanılamak için bu toplu görünümü kullanabilirsiniz. Sorgular kaynaklarda, kuyruk beklemelerinde veya dış beklemelerde bekliyor olabilir.
  • Bazı kaynaklarda bekleyen görev kuyruğu hakkında bilgi döndürmek için sys.dm_os_waiting_tasks kullanın.

Yüksek CPU senaryolarında Sorgu Deposu ve bekleme istatistikleri aşağıdaki durumlarda CPU kullanımını yansıtmayabilir:

  • Yüksek CPU kullanan sorgular hala yürütülüyor.
  • Yük devretme gerçekleştiğinde yüksek CPU kullanan sorgular çalıştırılıyordu.

Sorgu Deposu ve bekleme istatistiklerini izleyen DMV'ler yalnızca başarıyla tamamlanan ve zaman aşımına uğradı sorguların sonuçlarını gösterir. Deyimler bitene kadar şu anda yürütülen deyimler için veri göstermezler. Şu anda yürütülen sorguları ve ilişkili çalışan süresini izlemek için dinamik yönetim görünümü sys.dm_exec_requests kullanın.

İpucu

Ek araçlar:

Sonraki adımlar