SQL Server'da yavaş çalışan sorgularla ilgili sorunları giderin

Özgün ürün sürümü: SQL Server
Özgün KB numarası: 243589

Giriş

Bu makalede SQL Server kullanılırken veritabanı uygulamalarının karşılaşabileceği performans sorunlarının nasıl ele alınabileceği açıklanır: belirli bir sorgunun veya sorgu grubunun yavaş performansı. Aşağıdaki metodoloji yavaş sorgu sorununun nedenini daraltmanıza ve sizi çözüme yönlendirmenize yardımcı olur.

Yavaş sorguları bulma

SQL Server örneğinizde sorgu performansı sorunlarınız olduğunu tespit etmek için, sorguları yürütme sürelerine (geçen süre) göre inceleyerek başlayın. Belirlenen performans temeli temelinde, sürenin belirlediğiniz eşiği (milisaniye cinsinden) aşıp aşmayacağını denetleyin. Örneğin, stres testi ortamında iş yükünüzün 300 ms'den uzun olmaması için bir eşik oluşturup bu eşiği kullanabilirsiniz. Ardından, her bir sorguya ve önceden oluşturulmuş performans temel süresine odaklanarak bu eşiği aşan tüm sorguları tanımlayabilirsiniz. Sonuç olarak, iş kullanıcıları veritabanı sorgularının genel süresini önemser; bu nedenle asıl odak yürütme süresidir. Araştırmanın daraltılmasında yardımcı olması için CPU süresi ve mantıksal okuma gibi diğer ölçümler toplanır.

• Şu anda yürütülen ifadeler için sys.dm_exec_requests içindeki total_elapsed_time ve cpu_time sütunlarını kontrol edin. Verileri almak için aşağıdaki sorguyu çalıştırın:

  SELECT 
      req.session_id
      , req.total_elapsed_time AS duration_ms
      , req.cpu_time AS cpu_time_ms
      , req.total_elapsed_time - req.cpu_time AS wait_time
      , req.logical_reads
      , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
         ((CASE statement_end_offset
             WHEN -1
             THEN DATALENGTH(ST.text)  
             ELSE req.statement_end_offset
           END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
        1, 512)  AS statement_text  
  FROM sys.dm_exec_requests AS req
      CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
  ORDER BY total_elapsed_time DESC;
  

• Sorgunun geçmiş yürütmeleri için last_elapsed_time ve last_worker_time sütunlarını sys.dm_exec_query_stats içinde denetleyin. Verileri almak için aşağıdaki sorguyu çalıştırın:

  SELECT t.text,
       (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
       (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
       ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.total_logical_writes / qs.execution_count AS avg_writes,
       (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
  FROM sys.dm_exec_query_stats qs
       CROSS apply sys.Dm_exec_sql_text (sql_handle) t
  WHERE t.text like '<Your Query>%'
  -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
  ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
  

  Not

  Negatif bir değer gösteriyorsa avg_wait_time , bu paralel bir sorgu olur.

• Sorguyu SQL Server Management Studio'da (SSMS), sqlcmd veya Visual Studio Code için MSSQL uzantısında talep üzerine yürütebiliyorsanız, SET STATISTICS TIMEON ve SET STATISTICS IOON ile çalıştırın.

  SET STATISTICS TIME ON
  SET STATISTICS IO ON
  <YourQuery>
  SET STATISTICS IO OFF
  SET STATISTICS TIME OFF
  

  Ardından İletiler'den CPU süresini, geçen süreyi ve aşağıdaki gibi mantıksal okumaları görürsünüz:

    Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
  
    SQL Server Execution Times:
      CPU time = 460 ms,  elapsed time = 470 ms.
  

• Sorgu planı toplayabilirseniz Yürütme planı özelliklerindeki verileri denetleyin.

  1. Sorguyu Gerçek Yürütme Planını Dahil Et ile çalıştırın.

  2. Yürütme planından en soldaki işleci seçin.

  3. Özellikler'den QueryTimeStats özelliğini genişletin.

  4. ElapsedTime ve CpuTime değerlerini denetleyin.

     

Çalıştırma ve Bekleme: Sorgular neden yavaş?

Önceden tanımlanmış eşiğinizi aşan sorgular bulursanız bunların neden yavaş olabileceğini inceleyin. Performans sorunlarının nedeni, çalışma veya bekleme olarak iki kategoride gruplandırılabilir.

• BEKLENİYOR: Sorgular, uzun süreli bir darboğazda bekledikleri için yavaş olabilir. Bekleme türlerindeki performans sorunlarının ayrıntılı listesine bakın.

• ÇALıŞıYOR: Sorgular uzun süre çalıştığından (yürütülüyor) yavaş olabilir. Başka bir deyişle, bu sorgular etkin olarak CPU kaynaklarını kullanır.

Sorgu kullanım ömründe (süre) bir süre çalışabilir ve bir süre bekleyebilir. Uzun geçen süresine katkıda bulunan baskın kategorinin hangisi olduğunu belirlemek sizin asıl odak noktanızdır. Bu nedenle ilk görev sorguların hangi kategoride olduğunu belirlemektir. Bu basit bir işlemdir: Sorgu çalışmıyorsa, bekliyor olur. İdeal olan, bir sorgunun toplam geçen süresinin çoğunu çalışır durumda ve çok azını kaynak bekleme durumunda geçirmesidir. Ayrıca, en iyi durumda, sorgu önceden belirlenmiş bir temel içinde veya altında çalışır. Sorun türünü belirlemek için sorgunun geçen süresini ve CPU süresini karşılaştırın.

Tür 1: CPU bağlı (çalıştırıcı)

CPU süresi geçen süreye yakın, buna eşit veya daha uzunsa, CPU'ya bağlı bir sorgu olarak kabul edebilirsiniz. Örneğin, geçen süre 3000 milisaniye (ms) ise ve CPU süresi 2900 ms ise, geçen sürenin çoğu CPU üzerinde harcanmıştır. Ardından bunun CPU'ya bağlı bir sorgu olduğunu söyleyebiliriz.

Çalıştırma (CPU'ya bağlı) sorgu örnekleri:

Geçen Süre (ms)	CPU Süresi (ms)	Okumalar (mantıksal)
3200	3000	300000
1080	1000	20

Mantıksal okumalar - önbellekteki verileri/dizin sayfalarını okuma - genellikle SQL Server'da CPU kullanımının sürücüleridir. CPU kullanımının diğer kaynaklardan geldiği senaryolar olabilir: bir while döngüsü (T-SQL'de veya XProcs veya SQL CRL nesneleri gibi diğer kodlarda). Tablodaki ikinci örnek, CPU'nun kullanımının çoğunlukla okuma işlemlerinden kaynaklanmadığı böyle bir senaryoyu gösterir.

Not

CPU süresi sürenin üzerindeyse, bu, paralel bir sorgunun yürütüldüğü anlamına gelir; birden fazla iş parçacığı aynı anda CPU'yu kullanıyor. Daha fazla bilgi için Paralel sorgular - çalıştırıcı veya garson bölümüne bakın.

Tür 2: Tıkanıklıkta bekleme

Geçen süre CPU süresinden önemli ölçüde fazlaysa sorgu bir darboğazda bekliyor. Geçen süre, CPU'da sorguyu yürütme süresini (CPU süresi) ve bir kaynağın yayımlanmasını bekleme süresini (bekleme süresi) içerir. Örneğin, geçen süre 2000 ms ve CPU süresi 300 ms ise, bekleme süresi 1700 ms 'dir (2000 - 300 = 1700). Daha fazla bilgi için bkz Bekleme Türleri.

Bekleyen sorgu örnekleri:

Geçen Süre (ms)	CPU Süresi (ms)	Okumalar (mantıksal)
2000	300	28000
10080	700	80000

Paralel sorgular - yürütücü veya bekleyici

Paralel sorgular genel süreye göre daha fazla CPU süresi kullanabilir. Paralelliğin amacı, birden çok iş parçacığının bir sorgunun parçalarını aynı anda çalıştırmasına izin vermektir. Bir sorgu, saat süresinin bir saniyesinde sekiz paralel iş parçacığı yürüterek sekiz saniye CPU süresi kullanabilir. Bu nedenle, geçen süre ve CPU zaman farkı temelinde CPU bağlı veya bekleyen bir sorgu belirlemek zorlaşır. Ancak, genel bir kural olarak, yukarıdaki iki bölümde listelenen ilkeleri izleyin. Özet:

• Geçen süre CPU süresinden çok daha uzunsa, bunu bir garson olarak düşünün.
• CPU süresi geçen süreden çok daha uzunsa, bunu bir işlem olarak düşünün.

Paralel sorgu örnekleri:

Geçen Süre (ms)	CPU Süresi (ms)	Mantıksal okumalar
1200	8100	850000
3080	12300	1500000

Metodolojinin üst düzey görsel gösterimi

Bekleyen sorguları teşhis et ve çöz

İlgilendiğiniz sorguların bekleme durumunda olduğunu tespit ettiyseniz, bir sonraki adım darboğaz sorunlarını çözmeye odaklanmaktır. Aksi takdirde, 4. adıma gidin: Çalışan sorguları tanılama ve çözme.

Beklemeye neden olan darboğazlardaki bir sorguyu iyileştirmek için, beklemenin ne kadar sürdüğünü ve darboğazın nerede olduğunu (bekleme türü) belirleyin. Bekleme türü onaylandıktan sonra bekleme süresini kısaltın veya beklemeyi tamamen ortadan kaldırın.

Yaklaşık bekleme süresini hesaplamak için CPU süresini (çalışan zamanı) sorgunun geçen zamanından çıkarın. Genellikle, CPU süresi gerçek yürütme süresidir ve sorgunun yaşam süresinin kalan kısmı bekler.

Yaklaşık bekleme süresini hesaplama örnekleri:

Geçen Süre (ms)	CPU Süresi (ms)	Bekleme süresi (ms)
3200	3000	200
7080	1000	6080

Darboğazı belirleyin veya bekleyin

• Geçmişte uzun süre bekleyen sorguları tanımlamak için (örneğin, geçen sürenin %20'si bekleme süresidir) aşağıdaki sorguyu çalıştırın. Bu sorgu, SQL Server'ın başlangıcından bu yana önbelleğe alınmış sorgu planları için performans istatistiklerini kullanır.

  SELECT t.text,
           qs.total_elapsed_time / qs.execution_count
           AS avg_elapsed_time,
           qs.total_worker_time / qs.execution_count
           AS avg_cpu_time,
           (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
           AS avg_wait_time,
           qs.total_logical_reads / qs.execution_count
           AS avg_logical_reads,
           qs.total_logical_writes / qs.execution_count
           AS avg_writes,
           qs.total_elapsed_time
           AS cumulative_elapsed_time
  FROM sys.dm_exec_query_stats qs
           CROSS apply sys.Dm_exec_sql_text (sql_handle) t
  WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
           > 0.2
  ORDER BY qs.total_elapsed_time / qs.execution_count DESC
  

• Şu anda 500 ms'den uzun bekleme süreleriyle yürütülen sorguları belirlemek için aşağıdaki sorguyu çalıştırın:

  SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
  FROM sys.dm_exec_requests r 
     JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
  WHERE wait_time > 500
  AND is_user_process = 1
  

• Sorgu planı toplayabilirseniz, SSMS'deki yürütme planı özelliklerinden WaitStats'ıdenetleyin:

  1. Sorguyu Gerçek Yürütme Planını Dahil Et açık olarak çalıştırın.
  2. Yürütme planı sekmesinde en soldaki işleci sağ tıklatın
  3. Özellikler'i ve ardından WaitStats özelliğini seçin.
  4. WaitTimeMs ve WaitType değerlerini denetleyin.

• PSSDiag/SQLdiag veya SQL LogScout LightPerf/GeneralPerf senaryolarını biliyorsanız, performans istatistiklerini toplamak ve SQL Server örneğinizde bekleyen sorguları belirlemek için bunlardan birini kullanmayı göz önünde bulundurun. Toplanan veri dosyalarını içeri aktarabilir ve SQL Nexus ile performans verilerini analiz edebilirsiniz.

Beklemeleri ortadan kaldırmaya veya azaltmaya yardımcı olan kaynaklar

Her bekleme türünün nedenleri ve çözümleri farklılık gösterir. Tüm bekleme türlerini çözümlemek için tek bir genel yöntem yoktur. Sık karşılaşılan bekleme türü sorunlarını gidermeye ve çözmeye yönelik makaleler şunlardır:

• Engelleme sorunlarını anlama ve çözme (LCK_M_*)
• Azure SQL Veritabanı engelleme sorunlarını anlama ve çözme
• G/Ç sorunlarının neden olduğu yavaş SQL Server performansını çözün (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• SQL Server'da son sayfadaki PAGELATCH_EX ekleme çekişmesini çözme
• Bellek açıklamaları ve çözümleri verir (RESOURCE_SEMAPHORE)
• ASYNC_NETWORK_IO bekleme türünden kaynaklanan yavaş sorgularda sorun giderme
• Always On Kullanılabilirlik Grupları ile HADR_SYNC_COMMIT yüksek bekleme türü sorunlarını giderme
• Nasıl Çalışır: CMEMTHREAD ve Hata Ayıklama
• Paralellik beklemelerini eyleme dönüştürülebilir hale getirme (CXPACKET ve CXCONSUMER)
• THREADPOOL bekleme

Birçok Bekleme türünün açıklamaları ve bunların ne gösterdiği için Bekleme Türleri'ndeki tabloya bakın.

Çalışan sorguları tanımlama ve çözme

CPU (çalışan) süresi genel olarak geçen süreye çok yakınsa, sorgu yaşam süresinin çoğunu yürütmeye harcar. Genellikle, SQL Server altyapısı yüksek CPU kullanımına neden olduğunda, yüksek CPU kullanımı çok fazla sayıda mantıksal okuma (en yaygın neden) kullanan sorgulardan gelir.

Şu anda yüksek CPU etkinliğinden sorumlu sorguları tanımlamak için aşağıdaki deyimi çalıştırın:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Şu anda sorgular CPU'yu yönlendirmiyorsa CPU'ya bağlı geçmiş sorguları aramak için aşağıdaki deyimi çalıştırabilirsiniz:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Uzun süre çalışan, CPU'ya bağlı sorguları düzeltmek için yaygın yöntemler

• Sorgunun sorgu planını inceleme
• Güncelleştirme İstatistikleri
• Eksik Dizinleri belirleyin ve uygulayın. Eksik dizinleri tanımlama hakkında daha fazla adım için bkz . Eksik dizin önerileriyle kümelenmemiş dizinleri ayarlama
• Sorguları yeniden tasarlama veya yeniden yazma
• Parametreye duyarlı planları belirleme ve düzeltme
• SARG yeteneği sorunlarını tanımlama ve çözme
• Uzun süre çalışan iç içe döngülerin TOP, EXISTS, IN, FAST, SET ROWCOUNT, OPTION (FAST N) kaynaklı olabileceği Satır hedefi sorunlarını belirleyin ve çözün. Daha fazla bilgi için Row Goals Gone Rogue ve Showplan geliştirmeleri - Satır Hedefi Tahmini: RowsWithoutRowGoal bölümüne bakın.
• Kardinalite tahmini sorunlarını değerlendirin ve çözün. Daha fazla bilgi için bkz . SQL Server 2012 veya önceki sürümlerden 2014 veya sonraki bir sürüme yükseltildikten sonra sorgu performansını düşürme
• Hiç bitmeyecekmiş gibi görünen sorguları belirleyin ve çözün, bkz SQL Server'da hiç bitmeyecekmiş gibi görünen sorgularda sorun giderme
• İyileştirici zaman aşımından etkilenen yavaş sorguları belirleme ve çözme
• Yüksek CPU performansı sorunlarını belirleyin. Daha fazla bilgi için bkz . SQL Server'da yüksek CPU kullanımı sorunlarını giderme
• İki sunucu arasında performansında önemli bir fark olan sorgunun sorunlarını giderme
• Sistemdeki bilgi işlem kaynaklarını artırma (CPU)
• Dar ve geniş planlar ile UPDATE performans sorunlarını giderme

Önerilen kaynaklar

• SQL Server ve Azure SQL Yönetilen Örneği'nde algılanabilir sorgu performansı darboğazları türleri
• Performans İzleme ve Ayarlama Araçları
• SQL Server'da Otomatik Ayarlama Seçenekleri
• Dizin mimarisi ve Tasarım Yönergeleri
• Sorgu zaman aşımı hatalarını giderin
• SQL Server'daki yüksek CPU kullanımı sorunlarını giderme
• SQL Server 2012 veya önceki bir sürümden 2014 veya sonraki bir sürüme yükseltme sonrasında sorgu performansında düşüş