Aracılığıyla paylaş

SQL Server engelleme sorunlarını anlama ve çözme

  • Makale

Şunlar için geçerlidir: SQL Server (desteklenen tüm sürümler), Azure SQL Yönetilen Örneği

Özgün KB numarası: 224453

Hedefleme

Makalede SQL Server'da engelleme açıklanır ve engelleme sorunlarını giderme ve çözme adımları gösterilmektedir.

Bu makalede bağlantı terimi, veritabanının tek bir oturum açma oturumuna başvurur. Her bağlantı, birçok DMV'de oturum kimliği (SPID) veya session_id olarak görünür. Bu SPID'lerin her biri genellikle işlem olarak adlandırılır, ancak her zamanki anlamda ayrı bir işlem bağlamı değildir. Bunun yerine her SPID, belirli bir istemciden tek bir bağlantının isteklerine hizmet vermek için gereken sunucu kaynaklarından ve veri yapılarından oluşur. Tek bir istemci uygulamasının bir veya daha fazla bağlantısı olabilir. SQL Server açısından bakıldığında, tek bir istemci bilgisayardaki tek bir istemci uygulamasından birden çok bağlantı ile birden çok istemci uygulamasından veya birden çok istemci bilgisayardan birden çok bağlantı arasında fark yoktur; Atomik. Bir bağlantı, kaynak istemciden bağımsız olarak başka bir bağlantıyı engelleyebilir.

Not

Bu makale, Azure SQL Yönetilen Örneği'ler de dahil olmak üzere SQL Server örneklerine odaklanmıştır. Azure SQL Veritabanı'da engelleme sorunlarını gidermeye özgü bilgiler için bkz. Engelleme sorunlarını anlama ve çözme Azure SQL Veritabanı.

Engelleme nedir?

Engelleme, kilit tabanlı eşzamanlılığa olan her ilişkisel veritabanı yönetim sisteminin (RDBMS) tasarımından kaynaklanan ve kaçınılamaz ve bir özelliğidir. Daha önce belirtildiği gibi, SQL Server'da engelleme, bir oturum belirli bir kaynakta kilit tuttuğunda ve ikinci bir SPID aynı kaynakta çakışan bir kilit türü almaya çalıştığında oluşur. Genellikle, ilk SPID'nin kaynağı kilitlediği zaman dilimi küçüktür. Sahip olan oturum kilidi serbest bıraktığında, ikinci bağlantı kaynakta kendi kilidini tutabilir ve işlemeye devam eder. Burada açıklandığı gibi engelleme normal bir davranıştır ve bir gün boyunca sistem performansı üzerinde fark edilebilir bir etkisi olmadan birçok kez gerçekleşebilir.

Sorgunun süresi ve işlem bağlamı, kilitlerinin ne kadar süreyle tutuldığını ve böylece diğer sorgular üzerindeki etkisini belirler. Sorgu bir işlem içinde yürütülmezse (ve hiçbir kilit ipucu kullanılmazsa), SELECT deyimleri için kilitler sorgu sırasında değil, aslında okunan kaynakta tutulur. INSERT, UPDATE ve DELETE deyimleri için, hem veri tutarlılığı hem de gerekirse sorgunun geri alınmasına izin vermek için sorgu sırasında kilitler tutulur.

Bir işlem içinde yürütülen sorgular için kilitlerin tutulma süresi sorgu türüne, işlem yalıtım düzeyine ve sorguda kilit ipuçlarının kullanılıp kullanılmadığına göre belirlenir. Kilitleme, kilit ipuçları ve işlem yalıtım düzeylerinin açıklaması için aşağıdaki makalelere bakın:

Kilitleme ve engelleme, sistem performansı üzerinde zarar verici bir etkinin olduğu noktada kalırken, bunun nedeni aşağıdakilerden biridir:

  • SPID, bir dizi kaynağı serbest bırakmadan önce uzun bir süre boyunca kilitler. Bu engelleme türü zaman içinde kendini çözer, ancak performans düşüşlerine neden olabilir.

  • SPID, bir dizi kaynak üzerinde kilit tutar ve bunları hiçbir zaman serbest bırakmaz. Bu engelleme türü kendi kendine çözüm bulmaz ve etkilenen kaynaklara erişimi süresiz olarak engeller.

İlk senaryoda, farklı SPID'ler zaman içinde farklı kaynaklarda engellemeye neden olduğundan ve hareketli bir hedef oluşturduğundan durum çok akıcı olabilir. Bu durumların, sorunu tek tek sorgularla daraltmak için SQL Server Management Studio'yu kullanarak sorunlarını gidermesi zordur. Buna karşılık, ikinci durum tanılanması daha kolay olabilecek tutarlı bir duruma neden olur.

Uygulamalar ve engelleme

Engelleme sorunuyla karşılaşılırken sunucu tarafı ayarlama ve platform sorunlarına odaklanma eğilimi olabilir. Ancak, yalnızca veritabanına dikkat etmek bir çözüme yol açmayabilir ve istemci uygulamasını ve gönderdiği sorguları incelemeye daha iyi yönlendirilen zamanı ve enerjiyi emebilir. Uygulamanın yapılan veritabanı çağrılarıyla ilgili olarak hangi görünürlük düzeyini ortaya çıkardığının önemi yoktur, yine de engelleyici bir sorun, hem uygulama tarafından gönderilen tam SQL deyimlerinin incelenmesini hem de uygulamanın sorgu iptali, bağlantı yönetimi, tüm sonuç satırlarını getirme vb. ile ilgili tam davranışını gerektirir. Geliştirme aracı bağlantı yönetimi, sorgu iptali, sorgu zaman aşımı, sonuç getirme vb. üzerinde açık denetime izin vermiyorsa engelleme sorunları çözülemeyebilir. Bu potansiyel, özellikle performansa duyarlı OLTP ortamları için SQL Server için bir uygulama geliştirme aracı seçmeden önce yakından incelenmelidir.

Veritabanı ve uygulamanın tasarım ve oluşturma aşamasında veritabanı performansına dikkat edin. Özellikle kaynak tüketimi, yalıtım düzeyi ve işlem yolu uzunluğu her sorgu için değerlendirilmelidir. Her sorgu ve işlem mümkün olduğunca basit olmalıdır. İyi bağlantı yönetimi uzmanlık alanı uygulanmalıdır, bu olmadan uygulama düşük sayıda kullanıcıda kabul edilebilir bir performansa sahip gibi görünebilir, ancak kullanıcı sayısı yukarı doğru ölçeklendirildikçe performans önemli ölçüde düşebilir.

Uygun uygulama ve sorgu tasarımıyla SQL Server, çok az engellemeyle tek bir sunucuda binlerce eşzamanlı kullanıcıyı destekleyebilecektir.

Engelleme sorunlarını giderme

Hangi engelleme durumunda olduğumuzdan bağımsız olarak, kilitleme sorunlarını giderme metodolojisi aynıdır. Bu mantıksal ayrımlar, bu makalenin bileşiminin geri kalanını dikte edecektir. Kavram, baş engelleyiciyi bulmak ve sorgunun ne yaptığını ve neden engellendiğini belirlemektir. Sorunlu sorgu belirlendikten sonra (yani uzun süre kilit tutan şey), bir sonraki adım engellemenin neden gerçekleştiğini analiz etmek ve belirlemektir. Bunun nedenini anladıktan sonra sorguyu ve işlemi yeniden tasarlayarak değişiklikler yapabilirsiniz.

Sorun giderme adımları:

  1. Ana engelleme oturumunu tanımlama (baş engelleyici)

  2. Engellemeye neden olan sorguyu ve işlemi bulun (uzun bir süre kilit tutan şey)

  3. Uzun süreli engellemenin neden gerçekleştiğini analiz etme/anlama

  4. Sorguyu ve işlemi yeniden tasarlayarak engelleme sorununu çözme

Şimdi uygun bir veri yakalama ile ana engelleme oturumunu nasıl belirleyeceklerini tartışalım.

Engelleme bilgilerini toplama

Engelleme sorunlarını gidermenin zorluğunu gidermek için, veritabanı yöneticisi SQL Server'da kilitleme ve engelleme durumunu sürekli izleyen SQL betiklerini kullanabilir. Bu verileri toplamak için iki ücretsiz yöntem vardır.

Birincisi dinamik yönetim nesnelerini (DPO' lar) sorgulamak ve sonuçları zaman içinde karşılaştırmak üzere depolamaktır. Bu makalede başvuruda bulunan bazı nesneler dinamik yönetim görünümleri (DMV) ve bazıları dinamik yönetim işlevleridir (DDF).

İkincisi, yürütülenleri yakalamak için Genişletilmiş Olaylar(XEvents) veya SQL Profil Oluşturucu İzlemeleri kullanmaktır. SQL İzleme ve SQL Server Profiler kullanım dışı bırakıldığından, bu sorun giderme kılavuzu XEvents'e odaklanacaktır.

DMV'lerden bilgi toplama

Engelleme sorunlarını gidermek için DMV'lere başvurmak, engelleme zincirinin başındaki SPID'yi (oturum kimliği) ve SQL Deyimini tanımlamayı hedeflemektedir. Engellenen kurban SPID'leri arayın. Herhangi bir SPID başka bir SPID tarafından engelleniyorsa, kaynağın sahibi olan SPID'yi (engelleyen SPID) araştırın. Bu sahip SPID de engelleniyor mu? Baş engelleyiciyi bulmak için zincirde gezinebilir ve kilidini neden koruyabildiğini araştırabilirsiniz.

Bunu yapmak için aşağıdaki yöntemlerden birini kullanabilirsiniz:

  • SQL Server Management Studio'da (SSMS) Nesne Gezgini üst düzey sunucu nesnesine sağ tıklayın, Raporlar'ı genişletin, Standart Raporlar'ı genişletin ve ardından Etkinlik - Tüm Engelleme İşlemleri'ni seçin. Bu rapor, bir engelleme zincirinin başındaki geçerli işlemleri gösterir. İşlemi genişletirseniz, rapor baş hareket tarafından engellenen hareketleri gösterir. Bu rapor, Engellenen SQL Deyimini ve Engellenen SQL Deyimini de gösterir.

  • SSMS'de Etkinlik İzleyicisi'ni açın ve Engellenenler sütununa bakın. Etkinlik İzleyicisi hakkında daha fazla bilgiyi burada bulabilirsiniz.

DMV'ler kullanılarak daha ayrıntılı sorgu tabanlı yöntemler de kullanılabilir:

  • sp_who ve sp_who2 komutları, tüm geçerli oturumları gösteren eski komutlardır. DMV sys.dm_exec_sessions , sorgulayıp filtrelemesi daha kolay olan bir sonuç kümesinde daha fazla veri döndürür. Diğer sorguların merkezinde bulabilirsiniz sys.dm_exec_sessions .

  • Zaten tanımlanmış belirli bir oturum varsa, bir oturum tarafından gönderilen son deyimi bulmak için kullanabilirsiniz DBCC INPUTBUFFER(<session_id>) . Benzer sonuçlar, session_id ve request_id sağlayarak sorgulanması ve filtrelenmesi daha kolay bir sonuç kümesinde dinamik yönetim işlevi (DMF) ile sys.dm_exec_input_buffer döndürülebilir. Örneğin, session_id 66 ve request_id 0 tarafından gönderilen en son sorguyu döndürmek için:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • sütununa sys.dm_exec_requests bakın ve sütununa blocking_session_id başvurun. = 0 olduğunda blocking_session_id bir oturum engellenmez. Yalnızca yürütülmekte olan istekleri listelerken sys.dm_exec_requests , tüm bağlantılar (etkin veya değil) içinde sys.dm_exec_sessionslistelenir. Sonraki sorguda ile sys.dm_exec_sessions arasında sys.dm_exec_requests bu ortak birleştirmeyi oluşturun. tarafından sys.dm_exec_requestsdöndürülmek için sorgunun SQL Server ile etkin bir şekilde yürütülmesi gerektiğini unutmayın.

  • Sys.dm_exec_sql_text veya sys.dm_exec_input_buffer DMV'lerini kullanarak etkin olarak yürütülen sorguları ve bunların geçerli SQL toplu iş metnini veya giriş arabelleği metnini bulmak için bu örnek sorguyu çalıştırın. sütunu sys.dm_exec_sql_text tarafından text döndürülen veriler NULL ise sorgu şu anda yürütülmüyordur. Bu durumda sütunu SQL event_info sys.dm_exec_input_buffer altyapısına geçirilen son komut dizesini içerir. Bu sorgu, session_id başına engellenen session_ids listesi de dahil olmak üzere diğer oturumları engelleyen oturumları belirlemek için de kullanılabilir.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Microsoft Desteği tarafından sağlanan bu daha ayrıntılı örnek sorguyu çalıştırarak bir engelleme zincirinde yer alan oturumların sorgu metni de dahil olmak üzere birden çok oturum engelleme zincirinin başını tanımlayın.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;
  • Uzun süre çalışan veya kaydedilmemiş işlemleri yakalamak için sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections ve sys.dm_exec_sql_textgibi geçerli açık işlemleri görüntülemek için başka bir DMV kümesi kullanın. İşlemleri izlemeyle ilişkili çeşitli DMV'ler vardır, burada işlemler hakkında daha fazla DMV'ye bakın.
SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • SQL Server'ın iş parçacığı/görev katmanındaki başvuru sys.dm_os_waiting_tasks . Bu, isteğin şu anda karşılaştığı SQL wait_type hakkında bilgi döndürür. gibi sys.dm_exec_requests, yalnızca etkin istekler tarafından sys.dm_os_waiting_tasksdöndürülür.

Not

Zaman içindeki toplam bekleme istatistikleri dahil olmak üzere bekleme türleri hakkında daha fazla bilgi için bkz. DMV sys.dm_db_wait_stats.

üzerindeki sys.dm_os_waiting_tasksINNER JOIN nedeniyle, aşağıdaki sorgu çıkışı sys.dm_tran_locks yalnızca şu anda engellenen isteklerle, bekleme durumlarıyla ve kilitleriyle kısıtlar:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

DMV'lerde sorgu sonuçlarının zaman içinde depolanması, kalıcı engellemeyi veya eğilimleri belirlemek için belirli bir zaman aralığında engellemeyi gözden geçirmenizi sağlayacak veri noktaları sağlar. CSS'nin bu tür sorunları gidermeye yönelik go-to aracı PSSDiag veri toplayıcısını kullanmaktır. Bu araç, zaman içinde yukarıda başvuruda bulunulmuş DMV'lerden sonuç kümelerini toplamak için "SQL Server Performans İstatistikleri" kullanır. Bu araç sürekli geliştikçe GitHub'da DiagManager'ın en son genel sürümünü gözden geçirin.

Genişletilmiş olaylardan bilgi toplama

Yukarıdaki bilgilere ek olarak, SQL Server'daki engelleme sorununu kapsamlı bir şekilde araştırmak için genellikle sunucudaki etkinliklerin bir izlemesinin yakalanması gerekir. Örneğin, bir oturum bir işlem içinde birden çok deyim yürütürse, yalnızca gönderilen son deyim temsil edilir. Ancak, kilitlerin hala tutulmasının nedeni önceki ifadelerden biri olabilir. İzleme, geçerli işlemdeki bir oturum tarafından yürütülen tüm komutları görmenizi sağlar.

SQL Server'da izlemeleri yakalamanın iki yolu vardır; Genişletilmiş Olaylar (XEvents) ve Profil Oluşturucu İzlemeleri. Ancak, SQL Server Profiler kullanan SQL izlemeleri kullanım dışıdır. XEvents, gözlemlenen sisteme daha çok yönlülük ve daha az etki sağlayan yeni, üstün izleme platformudur ve arabirimi SSMS ile tümleşiktir.

SSMS'de başlamaya hazır, XEvent Profil Oluşturucu menüsünün altındaki Nesne Gezgini listelenen önceden hazırlanmış Genişletilmiş Olay oturumları vardır. Daha fazla bilgi için bkz . XEvent Profiler. SSMS'de kendi özel Genişletilmiş Olay oturumlarınızı da oluşturabilirsiniz. Bkz . Genişletilmiş Olaylar Yeni Oturum Sihirbazı. Engelleme sorunlarını gidermek için genellikle şunları yakalarız:

  • Kategori Hataları:
    • Dikkat
    • Blocked_process_report**
    • Error_reported (Kanal Yöneticisi)
    • Exchange_spill
    • Execution_warning

**Engellenen işlem raporlarının oluşturulduğu eşiği ve sıklığı yapılandırmak için sp_configure komutunu kullanarak saniyeler içinde ayarlanabilen engellenen işlem eşiği seçeneğini yapılandırın. Varsayılan olarak engellenen işlem raporları üretilmemiş durumdadır.

  • Kategori Uyarıları:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Kategori Yürütme:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategori Kilidi

    • Lock_deadlock

  • Kategori Oturumu

    • Existing_connection
    • Oturum aç
    • Oturumu kapatma

Yaygın engelleme senaryolarını belirleme ve çözme

Yukarıdaki bilgileri inceleyerek engelleme sorunlarının çoğunun nedenini belirleyebilirsiniz. Bu makalenin geri kalanında, bazı yaygın engelleme senaryolarını tanımlamak ve çözmek için bu bilgilerin nasıl kullanılacağı anlatılır. Bu tartışma, engelleyici SPID'ler hakkındaki bilgileri yakalamak için engelleme betiklerini (daha önce başvurulmuş) kullandığınızı ve bir XEvent oturumu kullanarak uygulama etkinliğini yakaladığınızı varsayar.

Engelleme verilerini analiz etme

  • ve kullanarak session_idblocking_these DMV'lerin sys.dm_exec_requests çıkışını inceleyin ve sys.dm_exec_sessions engelleme zincirlerinin başlarını belirleyin. Bu, hangi isteklerin engellendiğini ve hangilerinin engellendiğini en net şekilde belirler. Engellenen ve engellenen oturumlara daha fazla göz atın. Engelleme zincirinde ortak veya kök var mı? Büyük olasılıkla ortak bir tablo paylaşırlar ve engelleme zincirinde yer alan oturumlardan biri veya daha fazlası bir yazma işlemi gerçekleştiriyor.

  • DMV'lerin sys.dm_exec_requests çıkışını inceleyin ve sys.dm_exec_sessions engelleme zincirinin başındaki SPID'ler hakkında bilgi alın. Aşağıdaki sütunları arayın:

    • sys.dm_exec_requests.status

      Bu sütun belirli bir isteğin durumunu gösterir. Genellikle, uyku durumu SPID'nin yürütmeyi tamamlandığını ve uygulamanın başka bir sorgu veya toplu iş göndermesini beklediğini gösterir. Çalıştırılabilir veya çalışıyor durumu, SPID'nin şu anda bir sorguyu işlediğini gösterir. Aşağıdaki tabloda, çeşitli durum değerlerinin kısa açıklamaları yer alır.

      Durum Anlamı
      Background SPID; kilitlenme algılama, günlük yazıcısı veya denetim noktası gibi bir arka plan görevi çalıştırıyor.
      Uyku SPID şu anda yürütülmüyor. Bu durum genellikle SPID'nin uygulamadan bir komut beklediği anlamına gelir.
      Çalışıyor SPID şu anda bir zamanlayıcı üzerinde çalışıyor.
      Çalıştırılabilir SPID, bir zamanlayıcının çalıştırılabilir kuyruğunda ve zamanlayıcı süresini almayı bekliyor.
      Askıya alındı SPID, kilit veya mandal gibi bir kaynak bekliyor.

    • sys.dm_exec_sessions.open_transaction_count

      Bu sütun, bu oturumdaki açık işlemlerin sayısını bildirir. Bu değer 0'dan büyükse, SPID açık bir işlem içindedir ve işlem içindeki herhangi bir deyim tarafından alınan kilitleri tutuyor olabilir. Açık işlem, şu anda etkin olan bir deyim veya geçmişte çalıştırılan ve artık etkin olmayan bir deyim isteği tarafından oluşturulmuş olabilir.

    • sys.dm_exec_requests.open_transaction_count

      Benzer şekilde, bu sütun size bu istekteki açık işlemlerin sayısını bildirir. Bu değer 0'dan büyükse, SPID açık bir işlem içindedir ve işlem içindeki herhangi bir etkin deyim tarafından alınan kilitleri tutuyor olabilir. 'nin aksine sys.dm_exec_sessions.open_transaction_countetkin bir istek yoksa bu sütunda 0 gösterilir.

    • sys.dm_exec_requests.wait_type, wait_timeve last_wait_type

      sys.dm_exec_requests.wait_type NULL ise, istek şu anda hiçbir şey beklemiyordur ve last_wait_type değer isteğin en son wait_type karşılaştığını gösterir. Hakkında daha fazla bilgi sys.dm_os_wait_stats ve en yaygın bekleme türlerinin açıklaması için bkz . sys.dm_os_wait_stats. wait_time değeri, isteğin ilerleme kaydedip ilerlemediğini belirlemek için kullanılabilir. Tabloya sys.dm_exec_requests yönelik bir sorgu, sütununda önceki bir sorgusundan sys.dm_exec_requestsalınan değerden daha wait_time küçük bir değer döndürdüğündewait_time, bu, önceki kilidin alınıp serbest bırakıldığını ve artık yeni bir kilitte (sıfır wait_timeolmadığı varsayılarak) beklediğini gösterir. Bu, isteğin wait_resource beklediği kaynağı görüntüleyen çıkış arasındaki sys.dm_exec_requests karşılaştırılarak doğrulanabilir.

    • sys.dm_exec_requests.wait_resource

      Bu sütun, engellenen isteğin beklediği kaynağı gösterir. Aşağıdaki tabloda yaygın wait_resource biçimler ve anlamları listelenmiştir:

      Kaynak Biçim Örnek Açıklama
      Tablo DatabaseID:ObjectID:IndexID SEKME: 5:261575970:1 Bu durumda, veritabanı kimliği 5 pubs örnek veritabanıdır ve object_id 261575970 başlık tablosudur ve 1 kümelenmiş dizindir.
      Sayfa DatabaseID:FileID:PageID SAYFA: 5:1:104 Bu durumda, veritabanı kimliği 5 pubs, dosya kimliği 1 birincil veri dosyası ve sayfa 104 başlıklar tablosuna ait bir sayfadır. Sayfanın ait olduğu object_id tanımlamak için, sys.dm_db_page_info dinamik yönetim işlevini kullanın ve veritabanından DatabaseID, FileId, PageId değerini wait_resourcegeçirin.
      Anahtar DatabaseID:Hobt_id (Dizin anahtarı için karma değer) ANAHTAR: 5:72057594044284928 (3300a4f361aaa) Bu durumda, veritabanı kimliği 5 Pubs'tır Hobt_ID 72057594044284928 object_id 261575970 için index_id 2'ye karşılık gelir (başlık tablosu). sys.partitions katalog görünümünü kullanarak öğesini belirli index_id bir ve object_idile ilişkilendirinhobt_id. Dizin anahtarı karmasını belirli bir anahtar değeriyle silmenin hiçbir yolu yoktur.
      Satır DatabaseID:FileID:PageID:Slot(satır) RID: 5:1:104:3 Bu durumda, veritabanı kimliği 5 pubs, dosya kimliği 1 birincil veri dosyasıdır, sayfa 104 başlıklar tablosuna ait bir sayfadır ve 3. yuva satırın sayfadaki konumunu gösterir.
      Derle DatabaseID:FileID:PageID:Slot(satır) RID: 5:1:104:3 Bu durumda, veritabanı kimliği 5 pubs, dosya kimliği 1 birincil veri dosyasıdır, sayfa 104 başlıklar tablosuna ait bir sayfadır ve 3. yuva satırın sayfadaki konumunu gösterir.

    • sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV, işleme veya geri alma bekleyen işlemlerin tam bir resmi için diğer DMV'lere katılabilen açık işlemler hakkındaki verileri içerir. Sys.dm_tran_session_transactions dahil olmak üzere diğer DMV'lere katılmış açık işlemler hakkında bilgi döndürmek için aşağıdaki sorguyu kullanın. Bir işlemin geçerli durumunu, transaction_begin_timeve diğer durumsal verileri göz önünde bulundurarak engelleme kaynağı olup olmadığını değerlendirin.

      SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Diğer sütunlar

      sys.dm_exec_sessions ve sys.dm_exec_request'daki kalan sütunlar da sorunun kökü hakkında içgörü sağlayabilir. Yararlılıkları, sorunun koşullarına bağlı olarak değişir. Örneğin, sorunun yalnızca belirli istemcilerden (), belirli ağ kitaplıklarında (hostnameclient_interface_name ), bir SPID last_request_start_time tarafından gönderilen son toplu iş içinde sys.dm_exec_sessionsolduğunda , içinde bir isteğin ne kadar süreyle çalıştığını start_time sys.dm_exec_requestsvb. belirleyebilirsiniz.

Yaygın engelleme senaryoları

Aşağıdaki tabloda yaygın belirtiler olası nedenlerle eşlenmiştir.

wait_type, open_transaction_countve status sütunları sys.dm_exec_request tarafından döndürülen bilgilere başvurur; diğer sütunlar sys.dm_exec_sessions tarafından döndürülebilir. "Çözümlenir mi?" sütunu, engellemenin kendi kendine çözümlenip çözümlenmeyeceğini ya da oturumun komut aracılığıyla KILL çözümlenip çözümlenmeyeceğini gösterir. Daha fazla bilgi için bkz . KILL (Transact-SQL).

Senaryo Wait_type Open_Tran Durum Giderir? Diğer Belirtiler
1 NOT NULL >= 0 Runnable Evet, sorgu tamamlandığında. , reads, cpu_timeve/veya memory_usage sütunlarında sys.dm_exec_sessionszaman içinde artar. Tamamlandığında sorgunun süresi yüksek olur.
2 NULL >0 uyuyan Hayır, ama SPID öldürülebilir. Bu SPID için Genişletilmiş Olay oturumunda bir sorgu zaman aşımı veya iptal oluştuğunu belirten bir dikkat sinyali görülebilir.
3 NULL >= 0 Runnable Hayır İstemci tüm satırları getirene veya bağlantıyı kapatana kadar çözümlenmez. SPID öldürülebilir, ancak 30 saniye kadar sürebilir. open_transaction_count = 0 ise ve SPID kilitleri tutarken işlem yalıtım düzeyi varsayılan (READ COMMITTED) ise, bu olası bir nedendir.
4 Değişir >= 0 Runnable Hayır İstemci sorguları iptal edene veya bağlantıları kapatana kadar çözümlenmez. SPID'ler öldürülebilir, ancak 30 saniyeye kadar sürebilir. hostname Bir engelleme zincirinin başındaki SPID için içindeki sütunsys.dm_exec_sessions, engellediği SPID ile aynı olacaktır.
5 NULL >0 Geri alma Evet. Bu SPID için Genişletilmiş Olaylar oturumunda bir sorgu zaman aşımı veya iptal oluştuğunu veya yalnızca bir geri alma deyimi verildiğini belirten bir dikkat sinyali görülebilir.
6 NULL >0 uyuyan Sonunda. Windows NT oturumun artık etkin olmadığını belirlediğinde, bağlantı kesilir. last_request_start_time içindeki sys.dm_exec_sessions değeri geçerli saatten çok daha eskidir.

Ayrıntılı engelleme senaryoları

Senaryo 1: Uzun yürütme süresine sahip normalde çalışan bir sorgudan kaynaklanan engelleme

Bu senaryoda, etkin olarak çalışan bir sorgu kilitleri aldı ve kilitler serbest bırakılmaz (işlem yalıtım düzeyinden etkilenir). Bu nedenle diğer oturumlar, serbest bırakılana kadar kilitlerde bekler.

Çözüm:

Bu engelleme sorununun çözümü, sorguyu iyileştirmenin yollarını aramaktır. Bu engelleme sorunu sınıfı bir performans sorunu olabilir ve bunu bu şekilde işlemden kaldırmanızı gerektirir. Belirli bir yavaş çalışan sorguyla ilgili sorunları giderme hakkında bilgi için bkz . SQL Server'da yavaş çalışan sorgularda sorun giderme. Daha fazla bilgi için bkz . Performans İzleme ve Ayarlama.

Sorgu Deposu'ndan (SQL Server 2016'da kullanıma sunulan) SSMS'de yerleşik raporlar, en yüksek maliyetli sorguları ve en iyi olmayan yürütme planlarını tanımlamak için de kesinlikle önerilen ve değerli bir araçtır.

Uzun süre çalışan ve diğer kullanıcıları engelleyen bir sorgunuz varsa ve bu sorgu iyileştirilemiyorsa, bir OLTP ortamından ayrılmış bir raporlama sistemine taşımayı göz önünde bulundurun. Veritabanının salt okunur çoğaltmasını eşitlemek için Always On kullanılabilirlik gruplarını da kullanabilirsiniz.

Not

Sorgu yürütme sırasında engellemeye, satır veya sayfa kilitlerinin tablo kilitlerine ilerletilmesi senaryosu olan sorgu yükseltmesi neden olabilir. Microsoft SQL Server, kilit yükseltme işleminin ne zaman gerçekleştirileceğini dinamik olarak belirler. Kilit yükseltmesini önlemenin en basit ve en güvenli yolu, işlemleri kısa tutmak ve kilit yükseltme eşiklerinin aşılmaması için pahalı sorguların kilit ayak izini azaltmaktır. Aşırı kilit yükseltmesini algılama ve önleme hakkında daha fazla bilgi için bkz . Kilit yükseltmenin neden olduğu engelleme sorununu çözme.

Senaryo 2: Kaydedilmemiş bir işlem içeren uyuyan bir SPID'nin neden olduğu engelleme

Bu engelleme türü genellikle uykuda olan veya işlem iç içe yerleştirme düzeyine (@@TRANCOUNTopen_transaction_countsıfırdansys.dm_exec_requests) sahip bir komut bekleyen bir SPID tarafından tanımlanabilir. Bu durum, uygulama bir sorgu zaman aşımıyla karşılaşırsa veya gerekli sayıda ROLLBACK ve/veya COMMIT deyimi vermeden iptal verirse oluşabilir. BIR SPID sorgu zaman aşımı veya iptal aldığında geçerli sorguyu ve toplu işlemi sonlandırır, ancak işlemi otomatik olarak geri almaz veya işlemez. Sql Server tek bir sorgu iptal edildiği için işlemin tamamının geri alınması gerektiğini varsayamadiğinden, uygulama bundan sorumludur. Sorgu zaman aşımı veya iptal, Genişletilmiş Olay oturumunda SPID için bir DİkKAT sinyal olayı olarak görünür.

Kaydedilmemiş bir açık işlemi göstermek için aşağıdaki sorguyu çalıştırın:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Ardından bu sorguyu aynı pencerede yürütebilirsiniz:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

İkinci sorgunun çıktısı, işlem sayısının bir olduğunu gösterir. İşlemde alınan tüm kilitler, işlem işlenene veya geri alınana kadar tutulur. Uygulamalar açıkça açıksa ve işlemleri işliyorsa, bir iletişim veya başka bir hata oturumu ve işlemini açık durumda bırakabilir.

Örnek genelinde şu anda kaydedilmemiş işlemleri tanımlamak için bu makalenin sys.dm_tran_active_transactions önceki bölümlerindeki betiği kullanın.

Çözümler:

  • Bu engelleme sorunu sınıfı bir performans sorunu da olabilir ve bunu bu şekilde işlemden kaldırmanızı gerektirir. Sorgu yürütme süresi azaltılabilirse, sorgu zaman aşımı veya iptal gerçekleşmeyebilir. Uygulamanın ortaya çıkması durumunda zaman aşımını işleyebileceği veya senaryoları iptal etmesi önemlidir, ancak sorgunun performansını inceleme avantajından da yararlanabilirsiniz.

  • Uygulamaların işlem iç içe yerleştirme düzeylerini düzgün bir şekilde yönetmesi gerekir veya sorgunun bu şekilde iptal edilmesinden sonra engelleme sorununa neden olabilirler. Aşağıdaki topluluklara bir göz atın:

    • İstemci uygulamasının hata işleyicisinde, istemci uygulaması bir işlemin açık olduğuna inanmasa bile herhangi bir hatayı izleyerek yürütür IF @@TRANCOUNT > 0 ROLLBACK TRAN . Toplu işlem sırasında çağrılan bir saklı yordam istemci uygulamasının bilgisi olmadan bir işlem başlatmış olabileceğinden, açık işlemleri denetlemek gerekir. Sorguyu iptal etme gibi bazı koşullar, yordamın geçerli deyimin ardından yürütülmesini engeller, bu nedenle yordamın işlemi denetleme IF @@ERROR <> 0 ve durdurma mantığı olsa bile bu geri alma kodu bu gibi durumlarda yürütülmeyecektir.

    • Bağlantı havuzu, bağlantıyı açan bir uygulamada kullanılıyorsa ve web tabanlı bir uygulama gibi havuza bağlantıyı bırakmadan önce birkaç sorgu çalıştırıyorsa, bağlantı havuzunu geçici olarak devre dışı bırakmak, istemci uygulaması hataları uygun şekilde işlemek için değiştirilene kadar sorunu hafifletmeye yardımcı olabilir. Bağlantı havuzu devre dışı bırakılarak, bağlantının serbest bırakılması SQL Server bağlantısının fiziksel olarak kesilmesine neden olur ve sunucunun tüm açık işlemleri geri almasına neden olur.

    • Bağlantı için veya işlemleri başlatan ve bir hatadan sonra temizlemeyen saklı yordamlarda kullanın SET XACT_ABORT ON . Bir çalışma zamanı hatası durumunda, bu ayar tüm açık işlemleri durdurur ve denetimi istemciye döndürür. Daha fazla bilgi için SET XACT_ABORT (Transact-SQL) gözden geçirin.

Not

Bağlantı havuzundan yeniden kullanılana kadar bağlantı sıfırlanmaz, bu nedenle kullanıcının bir işlemi açıp bağlantı havuzuna bağlantıyı serbest bırakması mümkündür, ancak birkaç saniye boyunca yeniden kullanılamayabilir ve bu süre boyunca işlem açık kalır. Bağlantı yeniden kullanılmazsa, bağlantı zaman aşımına uğradıklarında ve bağlantı havuzundan kaldırıldığında işlem durdurulacaktır. Bu nedenle, istemci uygulamasının hata işleyicisindeki işlemleri durdurması veya bu olası gecikmeyi önlemek için kullanması SET XACT_ABORT ON en uygunudur.

Dikkat

aşağıdaki SET XACT_ABORT ON, hataya neden olan bir deyimi izleyen T-SQL deyimleri yürütülmeyecektir. Bu, mevcut kodun hedeflenen akışını etkileyebilir.

Senaryo 3: karşılık gelen istemci uygulaması tüm sonuç satırlarını tamamlanmaya getirmemiş olan bir SPID'nin neden olduğu engelleme

Tüm uygulamalar sunucuya bir sorgu gönderdikten hemen sonra tüm sonuç satırlarını tamamlamalıdır. Bir uygulama tüm sonuç satırlarını getirmezse tablolar kilitli kalabilir ve diğer kullanıcıların işlem yapmasını engelleyebilir. SQL deyimlerini sunucuya saydam bir şekilde gönderen bir uygulama kullanıyorsanız, uygulamanın tüm sonuç satırlarını getirmesi gerekir. Yapılandırılmazsa (ve bunu yapacak şekilde yapılandırılamazsa), engelleme sorununu çözemeyebilirsiniz. Sorundan kaçınmak için hataya neden olan uygulamaları ana OLTP veritabanından ayrı bir raporlama veya karar destek veritabanıyla kısıtlayabilirsiniz.

Çözüm:

Sonucun tüm satırlarını tamamlamaya getirmek için uygulamanın yeniden yazılması gerekir. Bu, sunucu tarafı disk belleği gerçekleştirmek için bir sorgunun ORDER BY yan tümcesinde OFFSET ve FETCH kullanımını dışlamaz.

Senaryo 4: Dağıtılmış istemci/sunucu kilitlenmesi nedeniyle engelleme

Geleneksel bir kilitlenmeden farklı olarak, dağıtılmış kilitlenme RDBMS kilit yöneticisi kullanılarak algılanamaz. Bunun nedeni kilitlenmeye dahil olan kaynaklardan yalnızca birinin SQL Server kilidi olmasıdır. Kilitlenmenin diğer tarafı, SQL Server'ın denetimi olmayan istemci uygulama düzeyindedir. Aşağıdaki iki bölümde, bunun nasıl gerçekleşebileceğine ve uygulamanın bunu önlemenin olası yöntemlerine ilişkin örnekler gösterilmektedir.

Örnek A: İstemci/sunucu tek bir istemci iş parçacığı ile dağıtılmış kilitlenme

İstemcinin birden çok açık bağlantısı ve tek bir yürütme iş parçacığı varsa, aşağıdaki dağıtılmış kilitlenme oluşabilir. Burada kullanılan terimin dbproc istemci bağlantı yapısına başvurduğuna dikkat edin.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

Yukarıda gösterilen durumda, tek bir istemci uygulama iş parçacığının iki açık bağlantısı vardır. Dbproc1 üzerinde zaman uyumsuz olarak bir SQL işlemi gönderir. Bu, devam etmeden önce aramanın döndürülmesini beklemediği anlamına gelir. Uygulama daha sonra dbproc2 üzerinde başka bir SQL işlemi gönderir ve döndürülen verileri işlemeye başlamak için sonuçları bekler. Veriler geri gelmeye başladığında (dbproc ilk yanıt veren dbproc1 olduğunu varsayalım), bu dbproc üzerinde döndürülen tüm verileri tamamlayarak işler. SPID1, SPID2 tarafından tutulan bir kilitte engellenene kadar (iki sorgu sunucuda zaman uyumsuz olarak çalıştığından) dbproc1'den sonuçları getirir. Bu noktada dbproc1, daha fazla veri için süresiz olarak bekler. SPID2 kilitte engellenmez, ancak istemci olan dbproc2'ye veri göndermeye çalışır. Ancak, dbproc2, uygulamanın tek yürütme iş parçacığı dbproc1 tarafından kullanımda olduğundan, uygulama katmanında dbproc1 üzerinde etkili bir şekilde engellenir. Bu, SQL Server'ın yalnızca bir SQL Server kaynağı olduğundan, sql server'ın algılayamadığı veya çözümleyemediği bir kilitlenmeyle sonuçlanır.

Örnek B: Bağlantı başına bir iş parçacığı ile istemci/sunucu dağıtılmış kilitlenmesi

İstemcideki her bağlantı için ayrı bir iş parçacığı olsa bile, aşağıdaki gibi bu dağıtılmış kilitlenmenin bir varyasyonu yine de oluşabilir.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Bu durum Örnek A'ya benzer, ancak dbproc2 ve SPID2 aynı tablodaki bir SELECT , UPDATEveya DELETE deyimi için bir kerede satır işleme gerçekleştirmek ve her satırı bir arabellek aracılığıyla dbproc1'e teslim etmek amacıyla bir INSERTdeyim çalıştırır. Sonunda, SPID1 (, veya gerçekleştirerekINSERT) SPID2 tarafından tutulan bir kilitte engellenir (öğesini gerçekleştirirSELECT).DELETEUPDATE SPID2, istemci dbproc2'ye bir sonuç satırı yazar. Dbproc2 daha sonra bir arabellekteki satırı dbproc1'e geçirmeye çalışır, ancak dbproc1'in meşgul olduğunu bulur (SPID2'de engellenen geçerli INSERTöğesini tamamlamak için SPID1'de beklerken engellenir). Bu noktada, dbproc2 uygulama katmanında SPID (SPID1) SPID2 tarafından veritabanı düzeyinde engellenen dbproc1 tarafından engellenir. Bu da SQL Server'ın yalnızca bir SQL Server kaynağı olduğu için algılayamadığı veya çözümleyemediği bir kilitlenmeyle sonuçlanır.

Hem A hem de B örnekleri, uygulama geliştiricilerinin bilmesi gereken temel sorunlardır. Bu durumları uygun şekilde işlemek için uygulamaları kodlamalıdır.

Çözüm:

Bir sorgu zaman aşımı sağlandığında, dağıtılmış kilitlenme oluşursa zaman aşımı oluştuğunda bozulur. Sorgu zaman aşımı kullanma hakkında daha fazla bilgi için bağlantı sağlayıcısı belgelerinize başvurun.

Senaryo 5: Geri alma durumundaki bir oturumun neden olduğu engelleme

Kullanıcı tanımlı bir işlemin dışında öldürülen veya iptal edilen bir veri değiştirme sorgusu geri alınır. Bu durum istemci ağ oturumunun bağlantısının kesilmesinin bir yan etkisi olarak veya kilitlenme kurbanı olarak bir istek seçildiğinde de oluşabilir. Bu genellikle çıkışını gözlemleyerek sys.dm_exec_requeststanımlanabilir. Bu, GERI ALMA commandişlemini gösterebilir ve sütunda percent_complete ilerleme gösterilebilir.

Kullanıcı tanımlı bir işlemin dışında öldürülen veya iptal edilen bir veri değiştirme sorgusu geri alınır. Bu durum, istemci bilgisayarın yeniden başlatılmasının ve ağ oturumunun bağlantısının kesilmesinin bir yan etkisi olarak da oluşabilir. Benzer şekilde, kilitlenme kurbanı olarak seçilen bir sorgu geri alınır. Veri değiştirme sorgusu genellikle değişikliklerin ilk uygulandığından daha hızlı geri alınamaz. Örneğin, bir DELETE, INSERTveya UPDATE deyimi bir saattir çalışıyorsa geri alınması en az bir saat sürebilir. Bu beklenen bir davranıştır, çünkü yapılan değişikliklerin geri alınması gerekir, aksi halde veritabanındaki işlem ve fiziksel bütünlük tehlikeye girer. Bunun olması gerektiğinden, SQL Server SPID'yi altın veya geri alma durumunda işaretler (bu, kilitlenme kurbanı olarak seçilemediği veya öldürülemediği anlamına gelir). Bu genellikle, ROLLBACK komutunu gösteren çıktısı sp_whogözlemlenerek tanımlanabilir. status sütunu sys.dm_exec_sessions BIR ROLLBACK durumunu gösterir.

Not

Hızlandırılmış Veritabanı Kurtarma özelliği etkinleştirildiğinde uzun geri alma işlemleri nadir görülür. Bu özellik SQL Server 2019'da kullanıma sunulmuştur.

Çözüm:

Oturumda yapılan değişikliklerin geri döndürülmesini beklemeniz gerekir.

Örnek bu işlemin ortasında kapatılırsa, veritabanı yeniden başlatıldıktan sonra kurtarma modunda olur ve tüm açık işlemler işlenene kadar erişilemez hale gelir. Başlangıç kurtarma işlemi temelde işlem başına çalışma zamanı kurtarma ile aynı süreyi alır ve bu süre boyunca veritabanına erişilemez. Bu nedenle, sunucuyu geri alma durumundaki bir SPID'yi düzeltmeye zorlamak genellikle ters üretime neden olur. Hızlandırılmış Veritabanı Kurtarma'nın etkinleştirildiği SQL Server 2019'da bu durum oluşmamalıdır.

Bu durumu önlemek için OLTP sistemlerinde yoğun saatlerde büyük toplu yazma işlemleri veya dizin oluşturma veya bakım işlemleri gerçekleştirmayın. Mümkünse, düşük etkinlik dönemlerinde bu tür işlemleri gerçekleştirin.

Senaryo 6: Yalnız bırakılmış bir işlemin neden olduğu engelleme

Bu yaygın bir sorun senaryosudur ve kısmen Senaryo 2 ile çakışıyor. İstemci uygulaması durursa, istemci iş istasyonu yeniden başlatılırsa veya toplu iptal hatası oluşursa, bunların tümü işlemi açık bırakabilir. Uygulama, uygulamanın veya bloklarındaki CATCH işlemi geri almazsa veya FINALLY başka bir şekilde bu durumu işlemezse bu durum oluşabilir.

Bu senaryoda, bir SQL toplu işleminin yürütülmesi iptal edilirken uygulama SQL işlemini açık bırakır. SQL Server örneğinin perspektifinden bakıldığında, istemci hala mevcut gibi görünür ve alınan tüm kilitler korunur.

Yalnız bırakılmış bir işlemi göstermek için, var olmayan bir tabloya veri ekleyerek toplu durdurma hatası benzetimini yapan aşağıdaki sorguyu yürütür:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Ardından bu sorguyu aynı pencerede yürütebilirsiniz:

SELECT @@TRANCOUNT;

İkinci sorgunun çıktısı, işlem sayısının bir olduğunu gösterir. İşlemde alınan tüm kilitler, işlem işlenene veya geri alınana kadar tutulur. Toplu iş sorgu tarafından zaten durdurulmuş olduğundan, yürüten uygulama hala açık olan işlemi temizlemeden aynı oturumda diğer sorguları çalıştırmaya devam edebilir. Oturum kapatılana veya SQL Server örneği yeniden başlatılana kadar kilit tutulur.

Çözümler:

  • Bu durumu önlemenin en iyi yolu, özellikle beklenmeyen sonlandırmalar için uygulama hatasını/özel durum işlemesini geliştirmektir. Uygulama kodunda bir Try-Catch-Finally blok kullandığınızdan emin olun ve özel durum söz konusu olduğunda işlemi geri alın.
  • Oturum için veya işlemleri başlatan ve bir hatadan sonra temizlemeyen saklı yordamlarda kullanmayı SET XACT_ABORT ON göz önünde bulundurun. Toplu işlemi durduran bir çalışma zamanı hatası durumunda, bu ayar tüm açık işlemleri otomatik olarak geri alır ve denetimi istemciye döndürür. Daha fazla bilgi için SET XACT_ABORT (Transact-SQL) gözden geçirin.
  • Kaynaklarını düzgün bir şekilde temizlemeden bağlantısı kesilmiş bir istemci uygulamasının yalnız bırakılmış bağlantısını çözmek için komutunu kullanarak SPID'yi KILL sonlandırabilirsiniz. Başvuru için bkz . KILL (Transact-SQL).

KILL komutu SPID değerini giriş olarak alır. Örneğin, SPID 9'u öldürmek için aşağıdaki komutu çalıştırın:

KILL 99

Not

Komutun KILL tamamlanması, komutun denetimleri KILL arasındaki aralık nedeniyle 30 saniye kadar sürebilir.

Ayrıca bkz.