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

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

Özgün KB numarası: 224453

Summary

Engelleme, kilit tabanlı ilişkisel veritabanlarında normaldir, ancak kalıcı veya uzun süre çalışan engelleme aktarım hızını düşürür ve uygulama zaman aşımlarına neden olur. Bu makalede, SQL Server'de hangi engellemenin olduğu, dinamik yönetim görünümlerinden (DMV) ve Genişletilmiş Olaylar'dan engelleme verilerinin nasıl yakalandığı ve baş engelleyicinin nasıl tanımlandığı açıklanır. Ardından en yaygın engelleme senaryolarında ve bunların çözümlerinde yol gösterilir.

Bu makalede bağlantı terimi, veritabanının tek bir oturum açma oturumuna başvurur. Her bağlantı oturum kimliği (SPID) olarak veya session_id birçok DMV'de görünür. Her SPID genellikle "işlem" olarak adlandırılır, ancak ayrı bir işletim sistemi işlemi değildir. Bir istemci bağlantısına hizmet veren sunucu tarafı kaynakları ve veri yapılarıdır. Tek bir istemci uygulamasının bir veya daha fazla bağlantısı olabilir. SQL Server perspektifinden bakıldığında, birçok istemciden gelen bağlantılar ve bir istemciden gelen birçok bağlantı aynı şekilde ele alınıyor. Bir bağlantı, kaynak istemciden bağımsız olarak başka bir bağlantıyı engelleyebilir.

Not

Bu makalede, Azure SQL Yönetilen Örneği dahil olmak üzere SQL Server örneklere odaklanılır. Azure SQL Veritabanında engelleme sorunlarını gidermeye özgü bilgiler için bkz. Azure SQL Veritabanı engelleme sorunlarını anlama ve çözme.

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 bir oturum belirli bir kaynakta kilit tuttuğunda ve ikinci bir SPID aynı kaynakta çakışan bir kilit türü elde etmeye çalıştığında engelleme gerçekleşir. İlk SPID'nin kaynağı kilitlediği zaman dilimi genellikle kısadı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 sistem performansı üzerinde fark edilebilir bir etkisi olmaksızın gün boyunca birçok kez gerçekleşebilir.

Sorgunun süresi ve işlem bağlamı, kilitlerinin ne kadar süre tutulduğunu 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, yalnızca okunduğu sırada kaynakta tutulur. INSERT, UPDATE ve DELETE deyimlerinde, hem veri tutarlılığı hem de gerekirse sorgunun geri alınmasına izin vermek için kilitler sorgu sırasında tutulur.

Bir işlem içinde yürütülen sorgular için sorgu türü, işlem yalıtım düzeyi ve sorguda kilit ipuçlarının kullanılıp kullanılmadığı, kilitlerin ne kadar süre tutuldığını belirler. 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 etkiye neden oldukları noktada kalırken, aşağıdaki nedenlerden birinden kaynaklanır:

  • SPID, bir kaynak kümesini bırakmadan önce uzun bir süre kilitli tutar. Bu engelleme türü zaman içinde kendi kendine çözülür, ancak performans düşüşlerine neden olabilir.

  • SPID, bir kaynak kümesini kilitli tutar ve hiçbir zaman bırakmaz. Bu engelleme türü kendi kendine çözülmez 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. Sorunu tek tek sorgulara daraltmak için SQL Server Management Studio kullanarak bu durumlarla ilgili sorunları gidermek zordur. Buna karşılık, ikinci durum tanılanması daha kolay olabilecek tutarlı bir durumla sonuçlanır.

Uygulamalar ve engelleme

Engelleme sorunuyla karşılaştığınızda sunucu tarafı ayarlama ve platform sorunlarına odaklanabilirsiniz. Ancak, yalnızca veritabanına dikkat etmek bir çözüme yol açmayabilir. İstemci uygulamasını ve gönderdiği sorguları inceleme konusunda daha iyi yönlendirebileceğiniz zamanı ve enerjiyi emebilir. Uygulamanın yaptığı veritabanı çağrılarıyla ilgili olarak hangi düzeyde görünürlük sağladığı fark etmez, engelleme sorunu hem uygulamanın gönderdiği 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ını çözemeyebilirsiniz. Özellikle performansa duyarlı OLTP ortamları için SQL Server için bir uygulama geliştirme aracı seçmeden önce bu potansiyeli yakından inceleyin.

Veritabanı ve uygulamanın tasarım ve oluşturma aşamasında veritabanı performansına dikkat edin. Özellikle her sorgu için kaynak tüketimini, yalıtım düzeyini ve işlem yolu uzunluğunu değerlendirin. Her sorgu ve işlem mümkün olduğunca basit olmalıdır. İyi bağlantı yönetimi disiplini alıştırması yapın. Uygulama olmadan, düşük sayıda kullanıcı için 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.

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

Engelleme sorunlarını giderme

Hangi engelleme durumunda olduğunuzdan 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 eder. Konsept, ana engelleyiciyi bulmak ve sorgunun ne yaptığını ve neden engellendiğini belirlemektir. Sorunlu sorguyu (yani uzun süre kilit tutan şeyi) tanımladıktan sonra, sonraki adım engellemenin neden gerçekleştiğini analiz etmek ve belirlemektir. Nedenini anladıktan sonra sorguyu ve işlemi yeniden tasarlayarak değişiklik yapabilirsiniz.

Sorun giderme adımları:

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

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

  3. Uzun süreli engellemenin neden gerçekleştiğini analiz edin ve anlayın.

  4. Sorguyu ve işlemi yeniden tasarlayarak engelleme sorununu çözün.

Şimdi de uygun bir veri yakalama ile ana engelleme oturumunu nasıl tespit edeceğimizi tartışalım.

Engelleme bilgilerini toplama

Engelleme sorunlarını giderme zorluğunu gidermek için bir 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.

İlk yöntem, 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).

İkinci yöntem, 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 odaklanır.

DMV'lerden bilgi toplama

Engelleme sorunlarını gidermek için DMV'leri kullanarak engelleme zincirinin başındaki SPID'yi (oturum kimliği) ve SQL deyimini belirleyin. Engellenen kurban SPID'lerini arayın. 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 engellendi mi? Baş engelleyiciyi bulmak için zinciri takip edebilir ve ardından kilidini neden koruyabildiğini araştırabilirsiniz.

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

  • SQL Server Management Studio (SSMS) Nesne Gezgini'nde ü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 güncel işlemleri gösterir. İşlemi genişletirseniz rapor, baş işlem tarafından engellenen işlemleri gösterir. Bu rapor, Engellenen SQL Deyimini ve Engellenmiş SQL Deyimini de gösterir.

  • SSMS'de Etkinlik İzleyicisi'ni açın ve Engellenenler sütununa bakın. Daha fazla bilgi için bkz . Etkinlik İzleyicisi.

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

  • 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 çekirdeğinde sys.dm_exec_sessions bulacaksınız.

  • Belirli bir oturumu zaten tanımladıysanız, oturumun gönderdiği son deyimi bulmak için kullanın DBCC INPUTBUFFER(<session_id>) . Benzer sonuçlar dinamik yönetim işlevi (DMF) tarafından sys.dm_exec_input_buffer sorgulanması ve filtrelenmesi daha kolay bir sonuç kümesinde döndürülür ve session_id ve request_id sağlanır. Ö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);

  • sys.dm_exec_requests sütununa bakın ve blocking_session_id sütununa başvurun. = 0 olduğunda blocking_session_id oturum engellenmez. Yalnızca yürütülmekte olan istekleri listelerken sys.dm_exec_requests , sys.dm_exec_sessions tüm bağlantıları (etkin veya değil) listeler. Sonraki sorguda sys.dm_exec_requests ile sys.dm_exec_sessions arasındaki bu ortak birleştirme üzerine oluşturun. tarafından sys.dm_exec_requestsdöndürülecek 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 geçerli SQL toplu metin veya giriş arabellek metnini bulmak için bu örnek sorguyu çalıştırın. text sütununun sys.dm_exec_sql_text öğesi tarafından döndürülen veriler NULL ise sorgu o anda yürütülmüyordur. Bu durumda, event_info sütununun sys.dm_exec_input_buffer sütunu SQL motoruna geçirilen son komut dizesini içerecektir. Bu sorgu, session_id başına engellenen session_id'ler 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;

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 iş parçacığı/görev katmanındaki başvuru sys.dm_os_waiting_tasks. Bu DMV, isteğin şu anda karşılaştığı SQL wait_type hakkında bilgi döndürür. sys.dm_exec_requests gibi, sys.dm_os_waiting_tasks tarafından yalnızca etkin istekler dö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.

  • Sorgular tarafından hangi kilitlerin yerleştirildiği hakkında daha ayrıntılı bilgi için sys.dm_tran_locks DMV'sini kullanın. Bu DMV, üretim SQL Server örneğinde büyük miktarlarda veri döndürebilir ve şu anda hangi kilitlerin tutulacağını tanılamak için kullanışlıdır.

sys.dm_os_waiting_tasks üzerindeki INNER JOIN nedeniyle aşağıdaki sorgu, sys.dm_tran_locks çıkışını 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'lerle, 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 başvurulacak aracı, PSSDiag veri toplayıcısını kullanmaktır. Bu araç, zaman içinde yukarıda bahsedilen DMV'lerden sonuç kümelerini toplamak için "SQL Server Performans İstatistikleri"ni kullanır. Bu araç sürekli geliştiğinden, GitHub'da DiagManager'ın en son genel sürümünü gözden geçirin.

Genişletilmiş olaylardan bilgi toplama

Önceki bilgilere ek olarak, SQL Server engelleme sorununu kapsamlı bir şekilde araştırmak için genellikle sunucudaki etkinliklerin bir izlemesini yakalamanız gerekir. Örneğin, bir oturum bir işlem içinde birden çok deyim yürütürse, yalnızca oturumun gönderdiği son deyim temsil edilir. Ancak, kilitlerin hala tutulmasının nedeni önceki ifadelerden biri olabilir. İzleme, oturumun geçerli işlem içinde yürüttüğü tüm komutları görmenizi sağlar.

İki yöntem kullanarak SQL Server'de izlemeleri yakalayabilirsiniz: 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. Arabirimi SSMS ile tümleşiktir.

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

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

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

  • 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ğı açıklanır. Bu tartışmada, engelleyici SPID'ler hakkındaki bilgileri yakalamak ve XEvent oturumu kullanarak uygulama etkinliğini yakalamak için engelleme betiklerini (daha önce başvurulmak üzere) kullandığınız varsayılır.

Engelleme verilerini analiz etme

  • sys.dm_exec_requests ve sys.dm_exec_sessions kullanarak engelleme zincirlerinin başlarını belirlemek için DMV'lerin blocking_these ve session_id çıkışını inceleyin. Bu çıkış hangi isteklerin engellendiğini ve hangilerinin engellendiğini açıkça tanımlar. Engellenen ve engellenmekte olan oturumlara daha fazla göz atın. Engelleme zincirinde ortak veya kök var mı? Büyük olasılıkla ortak bir tabloyu paylaşırlar ve engelleme zincirinde yer alan oturumlardan biri veya daha fazlası bir yazma işlemi gerçekleştiriyordur.

  • Engelleme zincirinin başındaki SPID'ler hakkında bilgi almak için DMV'lerin sys.dm_exec_requests ve sys.dm_exec_sessions çıkışını inceleyin. 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 tamamlayıp 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ı gösterir. 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şturulabilir.

    • sys.dm_exec_requests.open_transaction_count

      Benzer şekilde, bu sütun bu istekteki açık işlemlerin sayısını gösterir. 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_time ve last_wait_type

      sys.dm_exec_requests.wait_type NULL ise, istek şu anda hiçbir şey beklemiyordur. değeri, last_wait_type isteğin en son wait_type karşılaştığını gösterir. sys.dm_os_wait_stats Hakkında daha fazla bilgi 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 wait_timedaha küçük wait_time bir değer döndürdüğündesys.dm_exec_requests, bu koşul önceki kilidin alınıp serbest bırakıldığını ve şimdi yeni bir kilitte (sıfır wait_timeolmadığı varsayılarak) beklediğini gösterir. Bu koşulu doğrulamak için, isteğin wait_resource beklediği kaynağı görüntüleyen çıkış arasındaki sys.dm_exec_requests karşılaştırmayı yapabilirsiniz.

    • 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çimleri 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 Veritabanı Kimliği:Dosya Kimliği:Sayfa Kimliği SAYFA: 5:1:104 Bu durumda, veritabanı kimliği 5 pub, 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'yi tanımlamak için sys.dm_db_page_info dinamik yönetim işlevini kullanarak wait_resource'den DatabaseID, FileId, PageId değerini geçirin.
      Anahtar Veritabanı Kimliği:Hobt_id (Dizin anahtarı için karma değeri) ANAHTAR: 5:72057594044284928 (3300a4f361aa) 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ıklar tablosu). sys.partitions'yi kullanarak hobt_id'i belirli bir index_id ve object_id ile ilişkilendirin. Dizin anahtarı karmasını belirli bir anahtar değeriyle değiştirmenin hiçbir yolu yoktur.
      Satır Veritabanı Kimliği:Dosya Kimliği:Sayfa Kimliği:Yuva (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ık tablosuna ait bir sayfadır ve yuva 3 satırın sayfadaki konumunu gösterir.

    • sys.dm_tran_active_transactions sys.dm_tran_active_transactions DMV, işleme veya geri alma bekleyen işlemlerin tam 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_time ve diğer durum verilerini göz önünde bulundurarak bir 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'te kalan sütunlar da sorunun kaynağı hakkında içgörü sağlayabilir. Bunların yararlılıkları, sorunun koşullarına bağlı olarak değişir. Örneğin, sorunun yalnızca belirli istemcilerden (hostname), belirli ağ kitaplıklarında (client_interface_name) gerçekleşip gerçekleşmediğini, bir SPID tarafından gönderilen son toplu işin ne zaman last_request_start_time içinde sys.dm_exec_sessions olduğunu, start_time içinde sys.dm_exec_requests ile bir isteğin ne kadar süredir çalışmakta olduğunu vb. belirleyebilirsiniz.

Yaygın engelleme senaryoları

Aşağıdaki tabloda yaygın belirtiler olası nedenleri ile 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. SONLANDIRMA (Transact-SQL).

Senaryo Wait_type Open_Tran Durum Çözülür? Diğer Belirtiler
1 Null Değil >= 0 çalıştırılabilir Evet, sorgu tamamlandığında. sys.dm_exec_sessions,reads, cpu_time ve/veya memory_usage sütunları zaman içinde artacaktır. Sorgu tamamlandığında süresi yüksek olacaktır.
2 NULL >0 uyku Hayır, ama SPID sonlandırılabilir. Bu SPID için Genişletilmiş Olay oturumunda bir dikkat sinyali görülebilir, bu da bir sorgu zaman aşımının veya iptalinin oluştuğunu belirtir.
3 NULL >= 0 çalıştırılabilir Hayır İstemci tüm satırları getirene veya bağlantıyı kapatana kadar çözümlenmeyecektir. 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 ise (READ COMMITTED), bu olası bir nedendir.
4 Değişir >= 0 çalıştırılabilir Hayır İstemci sorguları iptal edene veya bağlantıları kapatana kadar çözümlenmeyecektir. SPID'ler öldürülebilir ancak 30 saniye kadar sürebilir. Bir engelleme zincirinin başındaki SPID için hostname içindeki sys.dm_exec_sessions sütunu, engellediği SPID'lerden biriyle 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 deyiminin verildiğini belirten bir uyarı sinyali görülebilir.
6 NULL >0 uyku 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 erkendir.

Ayrıntılı engelleme senaryoları

Senaryo 1: Normal çalışan ve uzun yürütme süresine sahip bir sorgudan kaynaklanan engelleme

Bu senaryoda, etkin bir şekilde çalışan bir sorgu kilit alır 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 sorununu çözmek için sorguyu iyileştirin. Bu tür bir engelleme sorunu bir performans sorunu olabilir. Böyle davranın. 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 için Görüntüleme Ayar.

Query Store (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ı belirlemek için önerilen ve değerli bir araçtır.

Uzun süre çalışan ve diğer kullanıcıları engelleyen bir sorgunuz varsa ve bunu iyileştiremiyorsanız, 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 ilerletilmesine neden 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şiklerini aşmamak 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şleme sahip uyuyan bir SPID'nin neden olduğu engelleme

Bu tür bir engellemeyi genellikle uykuda olan veya işlem iç içe yerleştirme düzeyi (@@TRANCOUNTopen_transaction_countkimden ) sıfırdan sys.dm_exec_requestsbüyük bir komut bekleyen bir SPID tarafından tanımlayabilirsiniz. Bu durum, uygulama bir sorgu zaman aşımıyla karşılaşırsa veya gerekli sayıda ROLLBACK ve/veya COMMIT ekstresi vermeden bir 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. Uygulama bu eylemden sorumludur çünkü SQL Server tek bir sorgu iptal edildiği için işlemin tamamının geri alınması gerektiğini varsayamaz. Sorgu zaman aşımı veya iptal, Genişletilmiş Olay oturumunda SPID için bir DİkKAT sinyal olayı olarak görünür.

İşlenmemiş açık bir 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ütün:

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

İkinci sorgunun çıkışı, işlem iç içe geçirme düzeyinin bir olduğunu gösterir. İşlemde alınan tüm kilitler, işlem işlenene veya geri alınana kadar tutulur. Uygulamalar işlemleri açıkça açar ve işlerse bir iletişim hatası veya başka bir hata oturumu ve işlemini açık durumda bırakabilir.

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

Çözümler:

  • Bu engelleme sorunu sınıfı bir performans sorunu da olabilir. Sorgu yürütme süresini kısaltabiliyorsanız, 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ı incelemeniz de yararlı olabilir.

  • 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ı bir işlemin açık olduğuna inanmasa bile istemci uygulamasının hata işleyicisinde herhangi bir hatanın sonrasında IF @@TRANCOUNT > 0 ROLLBACK TRAN komutunu yürütün. Toplu işlem sırasında bir saklı yordam çağırdığından, istemci uygulamanın bilgisi olmadan bir işlem başlatmış olabileceğinden, açık işlemlerin denetlenmesi gerekir. Sorguyu iptal etme gibi belirli koşullar, yordamın geçerli deyimi geçmişe atmasını önler bu nedenle yordamın, IF @@ERROR <> 0 işlemini denetleme ve işlemi durdurma mantığı olsa bile, bu geri alma kodu bu gibi durumlarda yürütülmeyecektir.

    • Bağlantıyı açan bir uygulamada bağlantı havuzu kullanıyorsanız ve web tabanlı bir uygulama gibi yeniden havuza bağlantıyı bırakmadan önce birkaç sorgu çalıştırıyorsanız, 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ı serbest bırakılarak SQL Server bağlantısının fiziksel olarak kesilmesine neden olur ve bu da sunucunun açık işlemleri geri döndürmesine neden olur.

    • Bağlantı için veya işlemleri başlatan ve bir hatadan sonra temizlemeyen saklı yordamlarda SET XACT_ABORT ON kullanın. 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) bölümünü gözden geçirin.

Not

Bağlantı havuzundan yeniden kullanılana kadar bağlantı sıfırlanmaz, bu nedenle bir kullanıcının bir işlemi açıp bağlantı havuzuna bağlantıyı serbest bırakması mümkündür ancak işlem açık kalırken birkaç saniye boyunca yeniden kullanılamayabilir. Bağlantı yeniden kullanılmazsa bağlantı zaman aşımına uğradığı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 SET XACT_ABORT ON kullanması idealdir.

Dikkat

SET XACT_ABORT ON işlemini takiben, 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, diğer kullanıcıları engelleyen tablolarda kilitler bırakabilir. SQL deyimlerini sunucuya şeffaf bir şekilde gönderen bir uygulama kullanıyorsanız uygulamanın tüm sonuç satırlarını getirmesi gerekir. Eğer yapılandırılmamışsa (ve yapılandırılamıyorsa), engelleme sorununu çözemezsiniz. Sorunu önlemek için kötü davranan uygulamaları ana OLTP veritabanından ayrı bir raporlama veya karar destek veritabanıyla kısıtlayın.

Çözüm:

Sonucun tüm satırlarını tamamlanmaya getirmek için uygulamayı yeniden yazın. Bu, sunucu tarafı sayfalamasını gerçekleştirmek için 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ış bir 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ı istemci uygulama düzeyindedir ve üzerinde SQL Server denetimi yoktur. Bunun nasıl gerçekleşebileceğini ve uygulamanın bunu önlemesinin olası yollarını gösteren iki örnek aşağıda verilmiştir.

Örnek A: Tek bir istemci iş parçacığı ile istemci/sunucu dağıtılmış kilitlenmesi

İ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. Kısa olması için burada kullanılan dbproc teriminin, istemci bağlantı yapısını ifade ettiğine 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)

Önceki örnekte, tek bir istemci uygulama iş parçacığının iki açık bağlantısı vardır. Dbproc1 üzerinde asenkron bir SQL işlemi gönderir. Bu, devam etmeden önce geri dönmek için aramayı 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 olarak 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 dbproc1'den sonuçları getirir (çünkü iki sorgu sunucuda asenkron olarak çalışır). Bu noktada dbproc1, daha fazla veri için süresiz olarak bekler. SPID2 bir kilit üzerinde engellenmez ancak istemcisi olan dbproc2'ye veri göndermeye çalışır. Ancak, uygulama için tek yürütme iş parçacığı dbproc1 tarafından kullanıldığı için dbproc2, uygulama katmanında dbproc1 üzerinde etkin bir şekilde engellenir. Bu durum, SQL Server ilgili kaynaklardan yalnızca biri SQL Server bir kaynak olduğundan 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 örnekte gösterildiği 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, INSERTveya UPDATE deyimi için her defasında bir satır işleme gerçekleştirmek ve her satırı bir arabellek aracılığıyla dbproc1'e teslim etmek amacıyla bir DELETE deyimini çalıştırır. Sonunda, (INSERT, UPDATE veya DELETE'ü gerçekleştiren) SPID1, (SELECT'i gerçekleştiren) SPID2 tarafından tutulan bir kilit üzerinde engellenir. SPID2, istemci dbproc2'ye bir sonuç satırı yazar. Daha sonra, dbproc2 bir arabellekteki satırı dbproc1'e geçirmeye çalışır, ancak dbproc1'in meşgul olduğunu tespit eder (SPID2'de engellenen geçerli INSERT öğesini bitirmek için SPID1'de beklerken engellenir). Bu noktada dbproc2, SPID'si (SPID1) SPID2 tarafından veritabanı düzeyinde engellenen dbproc1 tarafından uygulama katmanında engellenir. Bu da ilgili kaynaklardan yalnızca biri SQL Server kaynağı olduğundan yine SQL Server'ın algılayamadığı veya çözemediği bir kilitlenmeyle sonuçlanır.

Hem A hem de B örneği, uygulama geliştiricilerinin farkında olması gereken temel sorunlardır. Uygulamaları bu durumları uygun bir şekilde ele alacak şekilde kodlamaları gerekir.

Çözüm:

Bir sorgu zaman aşımı sağladığınızda, dağıtılmış kilitlenme oluşursa, zaman aşımı kilitlenmeyi bozar. Sorgu zaman aşımı kullanma hakkında daha fazla bilgi için bağlantı sağlayıcı belgelerinize bakın.

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

Kullanıcı tanımlı bir işlemin dışındaki bir veri değiştirme sorgusunu sonlandırmak veya iptal etmek istediğinizde, sorgu geri alınır. Bu geri alma, istemci ağ oturumunun bağlantısının kesilmesinin, istemci bilgisayarın yeniden başlatılmasının veya isteğin kilitlenme kurbanı olarak seçilmesinin bir yan etkisi olarak da gerçekleşebilir. Veri değiştirme sorgusunu genellikle başlangıçtaki değişikliklerden daha hızlı geri alamayabilirsiniz. Örneğin, bir DELETE, INSERTveya UPDATE deyimi bir saat boyunca çalıştırdıysa geri alınması en az bir saat sürebilir. Bu davranış beklenir, çünkü değişiklikler geri alınmazsa veritabanındaki işlem ve fiziksel bütünlük tehlikeye girer. Bu geri alma işleminin tamamlanması gerektiğinden SQL Server oturumu KILLED/ROLLBACK durumunda işaretler ve oturumu yeniden öldüremez veya kilitlenme kurbanı olarak seçemezsiniz. Bu durumu genellikle sütun raporları sys.dm_exec_requests ve command sütunun ilerleme durumunu gösterdiği çıkışını KILLED/ROLLBACKpercent_complete gözlemleyerek tanımlayabilirsiniz.

Not

Hızlandırılmış Veritabanı Kurtarma özelliği etkinleştirildiğinde uzun süren geri alma işlemleri nadirdir. Bu özellik, SQL Server 2019'da kullanılmaya başlanmıştır.

Çözüm:

Oturumun değişiklikleri geri döndürmeyi bitirmesini bekleyin.

Bu işlemin ortasında örneği kapatırsanız, veritabanı yeniden başlatıldıktan sonra kurtarma modundadır ve tüm açık işlemler işlenene kadar erişilemez. Başlangıç kurtarma işlemi, her işlem için çalışma zamanı kurtarma işlemiyle 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 üretimdir. 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ştirmeyin. Mümkünse, bu tür işlemleri düşük etkinlikli dönemlerde gerçekleştirin.

Senaryo 6: Yalnız bırakılmış bağlantının neden olduğu engelleme

Bu yaygın bir sorun senaryosudur ve kısmen Senaryo 2 ile çakışır. İ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. Bu durum, uygulamanın CATCH veya FINALLY bloklarındaki işlemi geri almazsa veya bu durumu başka bir şekilde işlemezse ortaya çıkabilir.

Bu senaryoda, bir SQL toplu işleminin yürütülmesi iptal edilirken, uygulama SQL bağlantısını ve 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 hala tutulur.

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

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ütün:

SELECT @@TRANCOUNT;

İkinci sorgunun çıkışı, işlem iç içe geçirme düzeyinin 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ı ve özel durum işlemeyi geliştirmektir. Uygulama kodunda bir Try-Catch-Finally bloğu kullandığınızdan emin olun ve bir ö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 SET XACT_ABORT ON kullanın. 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 bkz. SET XACT_ABORT (Transact-SQL).
  • 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ı çözümlemek için KILL komutunu kullanarak SPID'yi sonlandırabilirsiniz. Başvuru için bkz. KILL (Transact-SQL).

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

KILL 99

Not

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

İlgili içerik

  • Last updated on