SQL Server hiç bitmemiş gibi görünen sorgularda sorun giderme

Makale
01/19/2024

Bu makalede, hiçbir zaman tamamlanmamış gibi görünen bir sorgunuz olduğu veya tamamlanmasının birkaç saat veya gün sürebileceği sorun için sorun giderme adımları açıklanmaktadır.

Hiç bitmeyen sorgu nedir?

Bu belge, yürütmeye veya derlemeye devam eden sorgulara, yani CPU'larının artmaya devam etmesine odaklanır. Engellenen veya hiçbir zaman yayımlanmayan bir kaynağı bekleyen sorgular için geçerli değildir (CPU sabit kalır veya çok az değişir).

Önemli

Yürütmeyi tamamlamak için bir sorgu bırakılırsa, sonunda tamamlanır. Yalnızca birkaç saniye veya birkaç gün sürebilir.

Hiçbir zaman bitmeyen terim, sorgu sonunda tamamlandığında sorgunun tamamlanmadığı algısını açıklamak için kullanılır.

Hiç bitmeyen bir sorguyu tanımlama

Sorguların sürekli olarak yürütülüyor mu yoksa bir performans sorununa mı takılı kaldığını belirlemek için şu adımları izleyin:

Aşağıdaki sorguyu çalıştırın:

DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    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,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        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
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

Örnek çıkışı denetleyin.
- Bu makaledeki sorun giderme adımları, cpu'nun geçen süreyle orantılı olarak önemli bekleme süreleri olmadan arttığı aşağıdakine benzer bir çıkış fark ettiğinizde özellikle geçerlidir. Bazı CPU'ya bağlı T-SQL istekleri hiç mantıksal okuma yapmayabileceği için (örneğin hesaplamalar veya WHILE döngü gerçekleştirme) içindeki değişikliklerin logical_reads bu durumda uygun olmadığını unutmayın.
  
  Session_id Durum cpu_time logical_reads wait_time wait_type
  
  56 Çalışan 7038 101000 0 NULL
  
  56 çalıştırılabilir 12040 301000 0 NULL
  
  56 Çalışan 17020 523000 0 NULL
- Cpu'nun çok az değişmediği veya çok az değişmediği ve oturumun bir kaynakta beklediği aşağıdakine benzer bir bekleme senaryosu gözlemlerseniz bu makale geçerli değildir.
  
  Session_id Durum cpu_time logical_reads wait_time wait_type
  
  56 Askıya 0 3 8312 LCK_M_U
  
  56 Askıya 0 3 13318 LCK_M_U
  
  56 Askıya 0 5 18331 LCK_M_U
Daha fazla bilgi için bkz. Beklemeleri veya performans sorunlarını tanılama.

Session_id	Durum	cpu_time	logical_reads	wait_type
56	Çalışan	7038	101000	NULL
56	çalıştırılabilir	12040	301000	NULL
56	Çalışan	17020	523000	NULL

Session_id	Durum	logical_reads	wait_time	wait_type
56	Askıya	3	8312	LCK_M_U
56	Askıya	3	13318	LCK_M_U
56	Askıya	5	18331	LCK_M_U

Uzun derleme süresi

Nadir durumlarda CPU'nun zaman içinde sürekli arttığını ancak bunun sorgu yürütme tarafından yönlendirilmediğini gözlemleyebilirsiniz. Bunun yerine, aşırı uzun bir derleme (sorguyu ayrıştırma ve derleme) tarafından yönlendirilebilir. Bu gibi durumlarda , transaction_name çıkış sütununu denetleyin ve değerini sqlsource_transformarayın. Bu işlem adı bir derlemeyi gösterir.

Tanılama verilerini toplama

SQL Server Management Studio (SSMS) kullanarak tanılama verilerini toplamak için şu adımları izleyin:

Tahmini sorgu yürütme planı XML'sini yakalayın.
Yavaşlığın nereden gelebileceğine dair belirgin göstergeler olup olmadığını görmek için sorgu planını gözden geçirin. Tipik örnekler şunlardır:
- Tablo veya dizin taramaları (tahmini satırlara bakın).
- Büyük bir dış tablo veri kümesi tarafından yönlendirilen iç içe döngüler.
- Döngünün iç tarafında büyük bir dal bulunan iç içe döngüler.
- Tablo biriktiricileri.
- SELECT Listedeki her satırın işlenmesi uzun süren işlevler.
Sorgu herhangi bir zamanda hızlı çalışıyorsa, karşılaştırmak için "hızlı" yürütmeleri Gerçek XML Yürütme Planı'nı yakalayabilirsiniz.

Basit Sorgu Profili Oluşturma Altyapısı bu SQL Server sürümlerinde kullanıma sunulmuştur. Yavaş bir sorgunun yürütülmesi sırasında gerçek istatistikleri yakalamanızı sağlar. Bu sorun giderme özelliği, çalışma zamanında sorgu planındaki sorgu işleçlerini incelemenize ve zamanın çoğunun sorguda nereye harcandığı konusunda bilgi edinmenize olanak tanır.

Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v1 kullanarak sorgudaki yavaş adımları belirlemek için şu adımları izleyin:

XEvent'i etkinleştirmek query_thread_profile için aşağıdaki komutları çalıştırın:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

Etkilenen hiç bitmeyen sorguyu uygulamadan başlatın.

Sorgu planı işleçlerinin çalışma zamanı yürütme istatistiklerini denetlemek için aşağıdaki komutu dakikada birkaç kez veya birkaç kez çalıştırın:

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

Analiz için yeterli veri sağlamak için birbirinden bir dakika arayla üç veya dört anlık görüntü yakalayın. Özellikle, zaman içindeki her işlecin sayılarını row_count karşılaştırabilir ve satır sayısında (milyon veya daha fazla) önemli bir artış göstereni görebilirsiniz.
SSMS'deki yeni bir sorgu penceresinde, aşağıdaki komutları çalıştırarak sorunlu sorgu için tahmini bir sorgu planı yakalayın:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
3. adımda sorgu tarafından tanımlanan en yüksek satır sayısıyla düğüm kimliğini kullanarak, tahmini sorgu planında aynı düğümü bulun. Bu adım, uzun yürütme süresinin ana nedeninin plandaki hangi işleç olduğunu anlamanıza yardımcı olur.

Aşağıdaki komutu çalıştırarak XEvent'i durdurun:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

Canlı sorgu planlarını satır sayısı için gerçek değerlerle yakalamak için Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v2'yi kullanabilirsiniz. Bu profil oluşturma altyapısı, çalışma zamanında sorgu planındaki sorgu işleçlerini incelemenize ve zamanın çoğunun sorguda nerede harcandığı anlamanızı sağlar.

Sorgudaki yavaş adımları belirlemek için şu adımları izleyin:

bu SQL Server sürümlerinde basit altyapıyı etkinleştirmek için aşağıdaki yöntemlerden birini kullanın:

Aşağıdaki komutu çalıştırarak izleme bayrağı 7412'yi etkinleştirin:
```
DBCC TRACEON (7412, -1)
```

Alternatif olarak, aşağıdaki komutları çalıştırarak XEvent'i etkinleştirin query_thread_profile :

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

Etkilenen hiç bitmeyen sorguyu uygulamadan başlatın.
Çalışan hiç bitmeyen sorgunuzun kimliğini belirlemek Session_id için aşağıdakine benzer bir komut kullanın:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Sorgu planını ve plandaki gerçek istatistikleri incelemek için aşağıdaki komutu bir dakika arayla üç veya dört kez çalıştırın. Sorgu planını her seferinde kaydettiğinizden emin olun; böylece bunları karşılaştırabilir ve CPU süresinin çoğunu hangi sorgu işlecinin tüketebileceğini belirlersiniz. Özellikle, zaman içinde her işlecin satır sayısını (Gerçek Satır Sayısı) karşılaştırabilir ve satır sayısında (milyon veya daha fazla) önemli bir artış gösteren işleçleri görebilirsiniz. değerini önceki 3. adımda bulduğunuz tamsayı değeriyle değiştirin <session_id> .
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Bir tane başlattıysanız XEvent'i durdurun veya izleme bayrağını devre dışı bırakın:

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

Canlı sorgu planlarını satır sayısı için gerçek değerlerle yakalamak için Basit sorgu yürütme istatistikleri profil oluşturma altyapısı v3'lerini kullanabilirsiniz. Bu profil oluşturma altyapısı, çalışma zamanında sorgu planındaki sorgu işleçlerini incelemenize ve zamanın çoğunun sorguda nerede harcandığı anlamanızı sağlar. Basit profil oluşturma, SQL Server 2019'da varsayılan olarak etkindir.

Sorgudaki yavaş adımları belirlemek için şu adımları izleyin:

Etkilenen hiç bitmeyen sorguyu uygulamanızdan başlatın.
Çalışan hiç bitmeyen sorgunuzun kimliğini belirlemek Session_id için aşağıdakine benzer bir komut kullanın:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Sorgu planını ve plandaki gerçek istatistikleri incelemek için aşağıdaki komutu üç veya dört kez çalıştırın. Sorgu planını her seferinde kaydettiğinizden emin olun; böylece bunları karşılaştırabilir ve CPU süresinin çoğunu hangi sorgu işlecinin tüketebileceğini belirlersiniz. değerini önceki 3. adımda bulduğunuz tamsayı değeriyle değiştirin <session_id> .
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
Özellikle , query_plan sütununun altındaki XML bağlantısını seçin. Grafik sorgu planı yeni bir pencerede açıldığında, buna sağ tıklayın ve Yürütme Planını Farklı Kaydet...'i seçin. Analiz için yeterli veri sağlamak üzere birbirinden bir dakika arayla üç veya dört anlık görüntü yakalamak için adımları yineleyin. Özellikle, zaman içinde her işlecin satır sayısını (gerçek satır sayısı) karşılaştırabilir ve satır sayısında (milyon veya daha fazla) hangi işleçlerin önemli bir artış gösterdiğini görebilirsiniz.
Not

'den sys.dm_exec_query_statistics_xmlherhangi bir çıkış almıyorsanız, aşağıdaki komutu çalıştırarak veritabanı seçeneğinin LAST_QUERY_PLAN_STATS devre dışı bırakılıp bırakılmadığını de kontrol edebilirsiniz:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
komutunu çalıştırarak ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ONveritabanı düzeyinde son sorgu planı istatistiklerini etkinleştirebilirsiniz.

Toplanan planları gözden geçirme yöntemi

Bu bölümde toplanan verilerin nasıl gözden geçirileceği gösterilmektedir. SQL Server 2016 SP1 ve sonraki derlemelerde ve sürümlerde toplanan birden çok XML sorgu planını (*.sqlplan uzantısını kullanarak) kullanır.

Yürütme planlarını karşılaştırmak için şu adımları izleyin:

Daha önce kaydedilmiş bir sorgu yürütme planı dosyasını (.sqlplan) açın.
Yürütme planının boş bir alanına sağ tıklayın ve Showplan'ı Karşılaştır'ı seçin.
Karşılaştırmak istediğiniz ikinci sorgu planı dosyasını seçin.
İşleçler arasında akan çok sayıda satırı gösteren kalın okları arayın. Ardından, ok öncesinde veya sonrasında işleci seçin ve iki plandaki gerçek satır sayısını karşılaştırın.
En büyük satır akışının aynı işleçlerde olup olmadığını görmek için ikinci ve üçüncü planları karşılaştırın.

İşte bir örnek:

Çözüm

Sorguda kullanılan tablolar için istatistiklerin güncelleştirildiğinden emin olun.
Sorgu planında eksik bir dizin önerisi arayın ve herhangi birini uygulayın.
Sorguyu basitleştirmek amacıyla yeniden yazın:
- Önceden işlenen verileri azaltmak için daha fazla seçmeli WHERE koşul kullanın.
- Parçala.
- Geçici tablolarda bazı bölümleri seçin ve daha sonra birleştirin.
- İyileştirici satır hedefi nedeniyle çok uzun süre çalışan sorgularda , EXISTSve FAST (T-SQL) öğelerini kaldırınTOP. Alternatif olarak ipucunuDISABLE_OPTIMIZER_ROWGOAL kullanabilirsiniz. Daha fazla bilgi için bkz. Satır Goals Gone Rogue.
- Deyimleri tek bir büyük sorguda birleştirdikleri durumlarda Ortak Tablo İfadeleri (CTE) kullanmaktan kaçının.
Daha iyi bir plan oluşturmak için sorgu ipuçlarını kullanmayı deneyin:
- HASH JOIN veya MERGE JOIN ipucu
- FORCE ORDER Ipucu
- FORCESEEK Ipucu
- RECOMPILE
- Zorlayabileceğiniz hızlı bir sorgu planınız varsa KULLAN PLAN N'<xml_plan>'
Böyle bir plan varsa ve SQL Server sürümünüz Sorgu Deposu'yu destekliyorsa, iyi bilinen bir planı zorlamak için Sorgu Deposu'yu (QDS) kullanın.

Beklemeleri veya performans sorunlarını tanılama

Bu bölüm, sorununuzun uzun süre çalışan bir CPU sürüş sorgusu olmadığı durumlarda başvuru olarak buraya eklenmiştir. Bekleme süresi uzun olan sorgularla ilgili sorunları gidermek için bunu kullanabilirsiniz.

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.

Aracılığıyla paylaş