SQL Server'da yavaş çalışan sorgularda sorun giderme

  • Makale

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

Giriş

Bu makalede, veritabanı uygulamalarının SQL Server kullanırken karşılaşabileceği bir performans sorununun nasıl ele alınabileceği açıklanmaktadı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 ayarladığınız 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şturmış olabilirsiniz ve bu eşiği kullanabilirsiniz. Ardından, her bir sorguya ve önceden oluşturulmuş performans temeli 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ırmayı daraltmaya yardımcı olmak için CPU süresi ve mantıksal okumalar gibi diğer ölçümler toplanır.

  • Şu anda yürütülen deyimler için sys.dm_exec_requests total_elapsed_time ve cpu_time sütunlarını denetleyin. 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 sys.dm_exec_query_stats last_elapsed_time ve last_worker_time sütunlarını 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 , paralel bir sorgu olur.

  • sorguyu SQL Server Management Studio (SSMS) veya Azure Data Studio'da isteğe bağlı olarak yürütebiliyorsanız, BU sorguyu SET STATISTICS TIMEON ve SET STATISTICS GÇON 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 Fiili Yürütme Planını Ekle açık olarak çalıştırın.

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

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

    4. ElapsedTime ve CpuTime değerlerini denetleyin.

      QueryTimeStats özelliği genişletilmiş SQL Server yürütme planı özellikleri penceresinin ekran görüntüsü.

Ç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ışan veya bekleyen iki kategoride gruplandırılabilir:

  • BEKLENİYOR: Sorgular uzun süredir bir performans sorunuyla karşıldıkları için yavaş olabilir. Bekleme türlerindeki performans sorunlarının ayrıntılı listesine bakın.

  • ÇALıŞıYOR: Uzun süre çalıştıkları (yürütüldikleri) için sorgular yavaş olabilir. Başka bir deyişle, bu sorgular etkin olarak CPU kaynaklarını kullanır.

Sorgu bir süre çalıştırılabilir ve yaşam süresi (süre) içinde bir süre beklenebilir. Ancak, uzun süren süresine katkıda bulunan baskın kategorinin hangisi olduğunu belirlemektir. Bu nedenle, ilk görev sorguların hangi kategoride olduğunu oluşturmaktır. Çok basit: Sorgu çalışmıyorsa, bekliyor olur. İdeal olan, sorgunun geçen zamanının çoğunu çalışır durumda ve kaynakları beklemek için çok az zaman harcar. Ayrıca, en iyi senaryo senaryosunda bir sorgu önceden belirlenmiş bir temel içinde veya altında çalışır. Sorun türünü belirlemek için sorgunun geçen saatini ve CPU saatini karşılaştırın.

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

CPU süresi geçen süreye yakın, buna eşit veya daha yüksekse, 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ış demektir. 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 veri/dizin sayfalarını okuma - en sık SQL Server CPU kullanımının sürücüleridir. CPU kullanımının diğer kaynaklardan geldiği senaryolar olabilir: bir süre döngüsü (T-SQL'de veya XProcs veya SQL CRL nesneleri gibi diğer kodlarda). Tablodaki ikinci örnek, CPU'nun çoğunluğunun okumalardan olmadığı böyle bir senaryoyu gösterir.

Not

CPU süresi sürenin üzerindeyse, bu paralel sorgunun yürütülür olduğunu gösterir; birden çok iş parçacığı cpu'sunu aynı anda kullanıyor. Daha fazla bilgi için bkz. Paralel sorgular - çalıştırıcı veya garson.

Tür 2: Performans sorunu bekleniyor (garson)

Geçen süre CPU süresinden çok daha uzunsa sorgu bir performans sorununu bekliyor. Geçen süre, sorgunun CPU'da yürütülmesini (CPU süresi) ve 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 - çalıştırıcı veya garson

Paralel sorgular genel süreye göre daha fazla CPU süresi kullanabilir. Paralelliğin amacı, birden çok iş parçacığının sorgunun bölümlerini aynı anda çalıştırmasına izin vermektir. Saat süresinin bir saniyesinde, bir sorgu sekiz paralel iş parçacığı yürüterek sekiz saniye CPU süresi kullanabilir. Bu nedenle, geçen süre ve CPU süresi farkı temelinde CPU'ya 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 çalıştırıcı olarak düşünün.

Paralel sorgu örnekleri:

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

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

Ekran görüntüsü, yavaş sorgu sorunlarını giderme metodolojisinin üst düzey görsel gösterimini gösterir.

Bekleyen sorguları tanılama ve çözme

İlgilendiğiniz sorguların garsonlar olduğunu tespit ettiyseniz, sonraki adımınız performans sorunu sorunlarını çözmeye odaklanmaktır. Aksi takdirde, 4. adım: Çalışan sorguları tanılama ve çözme bölümüne gidin.

Performans sorunlarını bekleyen bir sorguyu iyileştirmek için, beklemenin ne kadar sürdüğünü ve performans sorununun nerede olduğunu (bekleme türü) belirleyin. Bekleme türü onaylandıktan sonra bekleme süresini azaltın veya beklemeyi tamamen ortadan kaldırın.

Yaklaşık bekleme süresini hesaplamak için, sorgunun geçen zamanından CPU süresini (çalışan saati) çıkarın. Genellikle CPU süresi gerçek yürütme süresidir ve sorgunun ömrünün kalan bölümü 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

Performans sorununu belirleme veya bekleme

  • Geçmiş uzun süre bekleyen sorguları (örneğin, >geçen sürenin %20'sini bekleme süresi) tanımlamak için aşağıdaki sorguyu çalıştırın. Bu sorgu, SQL Server 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ı özelliklerindenWaitStats'ı denetleyin:

    1. Sorguyu Fiili Yürütme Planını Ekle 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ı olacak başvurular

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:

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ılama ve çözme

CPU (çalışan) süresi geçen genel 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 yol açtığında, yüksek CPU kullanımı çok 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ı çözümlemek için yaygın yöntemler