Aracılığıyla paylaş

Engelleme sorunlarını anlama ve çözme

Şunlar için geçerlidir:Azure SQL VeritabanıFabric'teki SQL veritabanı

Makalede Azure SQL Veritabanı ve Doku SQL veritabanında engelleme açıklanır ve engelleme sorunlarını giderme ve çözme adımları gösterilmektedir.

Hedefleme

Bu makalede bağlantı terimi, veritabanının tek bir oturum açılmış oturumunu ifade eder. 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. Daha doğrusu, her SPID, belirli bir istemciden gelen 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. Azure SQL Veritabanı 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; bunlar atomiktir. Bir bağlantı, kaynak istemciden bağımsız olarak başka bir bağlantıyı engelleyebilir.

Kilitlenme sorunlarını giderme hakkında bilgi için bkz. Azure SQL Veritabanı ve Fabric SQL veritabanıkilitlenmeleri analiz etme ve önleme.

Not

Bu içerik Azure SQL Veritabanı odaklanmıştır. Azure SQL Veritabanı, Microsoft SQL Server veritabanı altyapısının en son kararlı sürümünü temel alır, bu nedenle içeriğin çoğu benzerdir, ancak sorun giderme seçenekleri ve araçları farklılık gösterebilir. SQL Server'da engelleme hakkında daha fazla bilgi için bkz . SQL Server engelleme sorunlarını anlama ve çözme. Doku SQL veritabanı, Azure SQL Veritabanı ile birçok özelliği paylaşır. Performans izleme hakkında daha fazla bilgi için bkz. Microsoft Fabric'te SQL veritabanını izleme.

Engellemeyi anlama

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. Azure SQL Veritabanı veritabanında 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. İ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 alabilir ve işlemeye devam eder. Bu davranış normaldir ve bir gün boyunca sistem performansı üzerinde belirgin bir etkisi olmadan birçok kez gerçekleşebilir.

Azure SQL Veritabanı'ndaki her yeni veritabanında, varsayılan olarak read committed snapshot (RCSI) veritabanı ayarı etkinleştirilmiştir. Eşzamanlılığı artırmak için satır sürümleme kullanan RCSI ile verileri okuyan ve yazan oturumlar arasındaki engelleme en aza indirilir. Ancak engelleme ve kilitlenmeler Azure SQL Veritabanı veritabanlarında yine de oluşabilir çünkü:

  • Verileri değiştiren sorgular birbirinizi engelleyebilir.
  • Sorgular, engellemeyi artırabilecek yalıtım düzeyleri altında çalıştırılabilir. Yalıtım düzeyleri Transact-SQL'deki uygulama bağlantı dizesi, sorgu ipuçları veya SET deyimlerinde belirtilebilir.
  • RCSI devre dışı bırakılabilir, bu da veritabanının okunabilir duruma getirilmiş yalıtım seviyesi altında çalışırken SELECT deyimlerini korumak için paylaşılan (S) kilitleri kullanmasına neden olur ve. Bu, engelleme ve kilitlenmeleri artırabilir.

Anlık görüntü yalıtım düzeyi, Azure SQL Veritabanı'daki yeni veritabanları için de varsayılan olarak etkinleştirilir. Anlık görüntü yalıtımı, veriler için işlem düzeyi tutarlılığı sağlayan ve güncelleştirilecek satırları seçmek için satır sürümlerini kullanan ek bir satır tabanlı yalıtım düzeyidir. Anlık görüntü yalıtımını kullanmak için, sorgular veya bağlantılar işlem yalıtım düzeyini açıkça SNAPSHOT olarak ayarlamalıdır. Bu yalnızca veritabanı için anlık görüntü yalıtımı etkinleştirildiğinde yapılabilir.

RCSI ve/veya anlık görüntü yalıtımının Transact-SQL ile etkinleştirilip etkinleştirilmediğini belirleyebilirsiniz. Azure SQL Veritabanı'da veritabanınıza bağlanın ve aşağıdaki sorguyu çalıştırın:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

RCSI etkinse, is_read_committed_snapshot_on sütun 1 değerini döndürür. Anlık görüntü yalıtımı etkinleştirilirse, snapshot_isolation_state_desc sütun ON değerini döndürür.

Sorgunun süresi ve işlem bağlamı, kilitlerinin ne kadar süreyle tutuldığını ve bunların diğer sorgular üzerindeki etkisini belirler. RCSI altında çalıştırılan SELECT deyimleri okunan veriler üzerinde paylaşılan (S) kilitleri almaz ve bu nedenle verileri değiştiren işlemleri engellemez. 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.

Açık bir işlem içinde yürütülen sorgular için kilitlerin türü ve kilitlerin tutıldığı süre 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 olumsuz bir etkiye neden olan bir noktaya kadar devam ettiğinde, bunun nedeni aşağıdakilerden biridir:

  • SPID, bir kaynak kümesindeki kilitleri uzun bir süre boyunca tutar ve sonrasında serbest bırakır. 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.

İyileştirilmiş kilitleme

İyileştirilmiş kilitleme, yeni bir Veritabanı Altyapısı özelliği kilit belleğini ve yazma işlemleri için gereken kilit sayısını önemli ölçüde azaltır. İyileştirilmiş kilitleme iki birincil bileşen kullanır: İşlem Kimliği (TID) kilitleme (diğer satır sürüm oluşturma özelliklerinde de kullanılır) ve nitelemeden sonra kilitleme (LAQ). Ek yapılandırma gerektirmez.

Bu makale şu anda optimize edilmemiş kilitleme ile Veritabanı Motoru'nun davranışına uygulanmaktadır.

Daha fazla bilgi edinmek ve iyileştirilmiş kilitlemenin nerede kullanılabilir olduğunu öğrenmek için bkz . İyileştirilmiş kilitleme.

Uygulamalar ve engelleme

Engelleme sorunuyla karşı karşıyayken 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 yönelik zamanı ve enerjiyi daha iyi emebilir. Uygulamanın yapılan veritabanı çağrılarıyla ilgili ne düzeyde görünürlük sunduğu fark etmez, engelleme sorunu hem uygulama tarafından gönderilen tam SQL deyimlerinin denetlenmesini 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 Azure SQL Veritabanı 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 bir bağlantı yönetimi disiplini uygulanmalıdır. Uygulama bu olmadan, 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.

Düzgün uygulama ve sorgu tasarımı sayesinde Azure SQL Veritabanı, çok az engellemeyle aynı anda binlerce kullanıcıyı tek bir sunucuda destekleyemktedir.

Not

Daha fazla uygulama geliştirme kılavuzu için bkz. Bağlantı sorunlarını ve diğer hataları giderme ve Geçici Hata İşleme .

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 geri kalan yapısını belirler. Konsept, ana 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 olduğunu analiz etmek ve belirlemektir. Bunun nedenlerini 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 kilitli 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 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 veritabanı yöneticisi, Azure SQL Veritabanı veritabanındaki kilitleme ve engelleme durumunu sürekli izleyen SQL betiklerini kullanabilir. Bu verileri toplamak için temelde iki yöntem vardır.

İlki, dinamik yönetim nesnelerini (DMO'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, neyin yürütüldüğünü yakalamak için XEvents kullanmaktı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. Engellenmiş kurban SPID'lerini arayın. Herhangi bir SPID başka bir SPID tarafından engelleniyorsa kaynağa sahip olan SPID'yi (engelleyen SPID) araştırın. Bu sahip SPID de engelleniyor mu? Zinciri takip ederek ana engelleyiciyi bulabilir ve ardından neden kilidini koruduğunu araştırabilirsiniz.

Bu betiklerin her birini Azure SQL Veritabanı hedef veritabanında çalıştırmayı unutmayın.

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

  • Zaten belirli bir oturum tanımladıysanız, bir oturum tarafından gönderilen son deyimi bulmak için DBCC INPUTBUFFER(<session_id>) kullanabilirsiniz. Benzer sonuçlar, sys.dm_exec_input_buffer dinamik yönetim işlevi (DMF) ile sorgulanması ve filtrelenmesi daha kolay bir sonuç kümesinde session_id ve request_id sağlanarak 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);

  • sys.dm_exec_requests içindeki blocking_session_id sütununa bakın. blocking_session_id = 0 olduğunda, bir oturum engellenmiyordur. 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 sys.dm_exec_requests ile sys.dm_exec_sessions arasındaki bu ortak birleştirme üzerine oluşturun.

  • 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. sys.dm_exec_sql_text text alanı tarafından döndürülen veriler NULL ise sorgu şu anda yürütülmüyordur. Bu durumda, event_info alanı sys.dm_exec_input_buffer SQL altyapısına geçirilen son komut dizesini içerir. 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'in iş parçacığı/görev katmanında bulunan sys.dm_os_waiting_tasks'a başvurun. Bu, isteğin şu anda karşılaştığı SQL bekleme türü 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çinde toplanan bekleme istatistikleri de dahil olmak üzere bekleme türleri hakkında daha fazla bilgi için bkz. DMV sys.dm_db_wait_stats. Bu DMV yalnızca geçerli veritabanı için toplam bekleme istatistiklerini döndürür.

  • Hangi kilitlerin sorgular tarafından konulduğu hakkında daha ayrıntılı bilgi için sys.dm_tran_locks DMV'sini kullanın. Bu DMV, üretim veritabanında büyük miktarda 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çirmenize olanak sağlayan veri noktaları sağlar.

Genişletilmiş Olaylar'dan bilgi toplama

Önceki bilgilere ek olarak, Azure SQL Veritabanı'nda bir engelleme sorununu kapsamlı bir şekilde araştırmak için genellikle sunucudaki etkinliklerin izinin alınması 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şlem içindeki 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, Azure SQL Veritabanı için desteklenmeyen kullanım dışı izleme teknolojisidir. Genişletilmiş Olaylar, gözlemlenen sistem üzerinde daha çok yönlülük ve daha az etki sağlayan yeni izleme teknolojisidir ve arabirimi SQL Server Management Studio (SSMS) ile tümleşiktir.

SSMS'de Genişletilmiş Olaylar Yeni Oturum Sihirbazı'nın nasıl kullanılacağını açıklayan belgeye bakın. Ancak Azure SQL veritabanları için SSMS, Nesne Gezgini'deki her veritabanının altında bir Genişletilmiş Olaylar alt klasörü sağlar. Bu yararlı olayları yakalamak için Genişletilmiş Olaylar oturum sihirbazını kullanın:

  • Kategori Hataları:

    • Dikkat
    • Hata_bildirildi
    • Çalıştırma uyarısı

  • Kategori Uyarıları:

    • Eksik_birleştirme_predikati

  • Kategori Yürütme:

    • Rpc_tamamlandı
    • Rpc_starting
    • SQL_işlem_tamamlandı
    • Sql_toplu_işlem_başlangıcı

  • Kategori deadlock_monitor

    • veritabanı_xml_kilitlenme_raporu

  • Kategori Oturumu

    • Mevcut bağlantı
    • Oturum aç
    • Oturumu kapatma

Not

Kilitlenmeler hakkında ayrıntılı bilgi için bkz. Azure SQL Veritabanı ve Fabric SQL veritabanında kilitlenmeleri analiz etme ve önleme.

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

Önceki bilgileri inceleyerek engelleme sorunlarının çoğunun nedenini belirleyebilirsiniz. Bu makalenin geri kalanında, bazı yaygın engelleme senaryolarını belirlemek ve çözmek için bu bilgilerin nasıl kullanılacağı tartışılır. Bu tartışmada, engelleyici SPID'lerle ilgili bilgileri yakalamak için engelleme betiklerini (daha önce bahsedilmiş) kullandığınız ve XEvent oturumu kullanarak uygulama etkinliğini yakaladığınız varsayılır.

Engelleme verilerini analiz et

  • 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 durum, hangi isteklerin engellendiğini ve hangilerinin engelleyen olduğunu en net şekilde tanımlar. Engellenen ve engellenmekte olan oturumları daha derinlemesine inceleyin. Engelleme zincirinin ortak bir noktası 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 alanları 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 tamamladığını ve uygulamanın yeni 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.
    Koşma 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üreyi almak için bekliyor.
    Askıya alındı SPID, kilit veya mandal gibi bir kaynak bekliyor.

    • sys.dm_exec_sessions.open_transaction_count
      Bu alan, bu oturumdaki açık hareketlerin 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 komut tarafından alınan kilitleri tutuyor olabilir.

    • sys.dm_exec_requests.open_transaction_count
      Benzer şekilde, bu alan size bu istekteki açık hareketlerin sayısını bildirir. Bu değerin büyüklüğü 0'dan büyükse, SPID açık bir işlem içindedir ve işlemdeki herhangi bir ifade tarafından edinilen kilitleri tutuyor olabilir.

    • sys.dm_exec_requests.wait_type, wait_time ve last_wait_type
      sys.dm_exec_requests.wait_type öğesi NULL ise istek şu anda hiçbir şey beklemiyordur ve last_wait_type değeri, isteğin karşılaştığı en son wait_type değerini 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. Önceki sys.dm_exec_requests sorgusundan elde edilen wait_time değerinden daha küçük bir değeri wait_time sütununda döndüren bir sys.dm_exec_requests tablosuna yapılan sorgu, bu, önceki kilidin alınıp serbest bırakıldığını ve şimdi yeni bir kilit beklediğini gösterir (sıfır olmayan wait_time varsayılarak). Bu, isteğin beklediği kaynağı görüntüleyen wait_resource çıkışı arasındaki sys.dm_exec_requests karşılaştırılarak doğrulanabilir.

    • sys.dm_exec_requests.wait_resource Bu alan, engellenen bir 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 VeritabanıID:NesneID:DizinID TAB: 5:261575970:1 Bu durumda, veritabanı kimliği 5 pubs örnek veritabanıdır ve nesne kimliği 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 is pubs, dosya kimliği 1 birincil veri dosyasıdır ve 104. sayfa başlıklar tablosuna ait bir sayfadır. Sayfanın ait olduğu object_id tanımlamak için, wait_resourceDatabaseID, FileId, PageId değerini geçirerek sys.dm_db_page_infodinamik yönetim işlevini kullanın.
    Anahtar Veritabanı Kimliği:Hobt_id (Dizin anahtarı için karma değeri) ANAHTAR: 5:72057594044284928 (3300a4f361aa) Bu durumda, veritabanı kimliği 5 pubsve Hobt_ID 72057594044284928 object_id 261575970 için index_id 2'ye (başlık tablosu) karşılık gelir. hobt_id'i belirli bir index_id ve object_idile ilişkilendirmek için sys.partitions katalog görünümünü kullanın. Dizin anahtarı karmasını belirli bir anahtar değerine geri dönüştürmenin 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 3. yuva satırın sayfadaki konumunu gösterir.
    Derleme 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 3. yuva 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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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 (ana bilgisayar adı), belirli ağ kitaplıklarında (net_library) olup olmadığını, bir SPID tarafından gönderilen son toplu işlemin last_request_start_time tarihinde sys.dm_exec_sessions gerçekleştiğini, bir isteğin ne kadar süreyle çalıştığını start_timesys.dm_exec_requests kullanarak belirleyebilirsiniz.

Yaygın engelleme senaryoları

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

Waittype, Open_Tranve Status sütunları, sys.dm_exec_requesttarafından döndürülen bilgilere başvurur. Diğer sütunlar sys.dm_exec_sessionstarafından döndürülebilir. "Çözümlenecek mi?" sütunu, engellemenin kendi kendine çözümlenip çözümlenmeyeceğini veya oturumun KILL komutuyla sonlandırılması gerekip gerekmediğini gösterir. Daha fazla bilgi için bkz. KILL.

Senaryo Bekleme Türü 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_sessionsiçinde, reads, cpu_timeve/veya memory_usage sütunları zaman içinde artar. Sorgu tamamlandığında süre yüksektir.
2 NULL >0 uyku Hayır, ama SPID sonlandırılabilir. 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 çalıştırılabilir Hayır İstemci tüm satırları getirene veya bağlantıyı kapatana kadar çözümlenmiyor. 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ümlenmiyor. SPID'ler öldürülebilir, ancak 30 saniye kadar sürebilir. Engelleme zincirinin başındaki SPID için sys.dm_exec_sessionshostname sütunu, engellediği SPID'lerden biriyle aynıdır.
5 NULL >0 Geri alma Evet. Bu SPID için Genişletilmiş Olaylar oturumunda, bir sorgu zaman aşımı veya iptalin meydana geldiğini ya da yalnızca bir geri alma işleminin yapıldığını belirten bir dikkat sinyali görülebilir.
6 NULL >0 uyku Sonunda. Windows oturumun artık etkin olmadığını belirlediğinde Azure SQL Veritabanı bağlantısı kesilir. last_request_start_time içindeki sys.dm_exec_sessions değeri, geçerli saatten çok daha erkendir.

Ayrıntılı engelleme senaryoları

  1. Normal çalışan ve yürütme süresi uzun olan bir sorgudan kaynaklanan engelleme

    Çözüm: Bu tür bir engelleme sorununun çözümü, sorguyu iyileştirmenin yollarını aramaktır. Aslında, bu tür engelleme sorunları yalnızca bir performans sorunu olabilir ve bu şekilde ele almanız gerekebilir. 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 İçin İzleme ve Ayarlama.

    SSMS'deki Sorgu Deposu'ndan gelen raporlar da en yüksek maliyetli sorguları, en iyi durumdaki yürütme planlarını belirlemek için önerilen ve değerli bir araçtır. Ayrıca Sorgu Performansı İçgörüsügözden geçirin.

    Sorgu yalnızca SELECT işlemleri gerçekleştiriyorsa, özellikle RCSI devre dışı bırakılmışsa, veritabanınızda etkinleştirildiyse deyimini anlık görüntü yalıtımı altında çalıştırmayı göz önünde bulundurun. RCSI etkinleştirildiğinde olduğu gibi, verileri okuyan sorgular anlık görüntü yalıtım düzeyi altında paylaşılan (S) kilitleri gerektirmez. Ayrıca anlık görüntü yalıtımı, açık bir çok deyimli işlemdeki tüm deyimler için işlem düzeyi tutarlılığı sağlar. Anlık görüntü yalıtımı veritabanınızda zaten etkinleştirilmiş olabilir. Anlık görüntü yalıtımı, değişiklik yapan sorgularla da kullanılabilir, ancak güncelleştirme çakışmalarını işlemeniz gerekir.

    Diğer kullanıcıları engelleyen ve optimize edilemeyen uzun süre çalışan bir sorgunuz varsa, bunu OLTP ortamından veritabanının eşzamanlı salt okunur çoğaltması olan ayrılmış bir raporlama sistemine taşımayı göz önünde bulundurun.

  2. İşlenmemiş bir işlem içeren uyuyan bir SPID'nin neden olduğu engelleme

    Bu tür bir engelleme, genellikle uyku modunda olan veya bir komut bekleyen, ancak işlem iç içe geçme düzeyi (@@TRANCOUNT'den itibaren open_transaction_count, sys.dm_exec_requests) sıfırdan büyük olan bir SPID ile tanımlanabilir. Bu durum, uygulama bir sorgu zaman aşımı yaşarsa veya gerekli sayıda ROLLBACK ve/veya COMMIT ifadesini gerçekleştirmeden iptal ederse oluşabilir. Bir SPID sorgu zaman aşımı veya iptal isteği aldığında, geçerli sorguyu ve toplu işlem grubunu sonlandırır, ancak işlemi otomatik olarak geri almaz veya işlemez. Uygulama bundan sorumludur çünkü Azure SQL Veritabanı tek bir sorgu iptal edildiğinde işlemin tamamının geri alınması gerektiğini düşünemez. 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;
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 onaylanana veya geri alınana kadar tutulur. Uygulamalar işlemleri açık bir şekilde açar ve taahhüt ederse, bir iletişim hatası ya da başka bir hata oturumu ve işlemini açık durumda bırakabilir.

    Bu makaledeki sys.dm_tran_active_transactions temelli betiği, örnek üzerinde şu anda onaylanmamış işlemleri belirlemek için kullanın.

    Çözümler:

    • Ayrıca, bu engelleme sorunları sınıfı bir performans sorunu da olabilir ve bu nedenle üzerinde çalışmanızı gerektirir. Sorgu yürütme süresi azaltılabilirse, sorgu zaman aşımı veya iptal gerçekleşmez. Uygulamanın ortaya çıkması durumunda zaman aşımı veya iptal senaryolarını işleyebilmesi ö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. Düşünü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. Açık işlemlerin kontrol edilmesi gerekir, çünkü toplu işlem sırasında çağrılan bir saklı yordam, istemci uygulamanın bilgisi olmadan bir işlem başlatmış olabilir. Belirli koşullar, örneğin sorgunun iptali, yordamın mevcut deyimden sonrasının yürütülmesini engeller. Bu nedenle, yordama IF @@ERROR <> 0'ı kontrol etme ve işlemi sona erdirme mantığı dahil edilmiş olsa bile, bu geri alma kodu bu tür durumlarda çalıştırılamaz.
      • Bağlantı havuzu, bağlantıyı açan bir uygulamada kullanılıyorsa ve web tabanlı bir uygulama gibi yeniden 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ıldığında, bağlantının serbest bırakılması Azure SQL Veritabanı bağlantısının fiziksel olarak kesilmesine neden olur ve bu da sunucunun açık işlemleri geri almasını sağlar.
      • SET XACT_ABORT ON'ü, bağlantı için veya işlemleri başlatan ve bir hatadan sonra temizlemeyen saklı yordamlarda 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_ABORTgö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 iptal edilir. 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 ONsonra, hataya neden olan bir deyimi izleyen T-SQL deyimleri yürütülemez. Bu, mevcut kodun hedeflenen akışını etkileyebilir.

  3. İlgili istemci uygulaması tüm sonuç satırlarını tamamlanmaya getirmemiş 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 ş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. Sorundan kaçınmak için hataya neden olan uygulamaları ana OLTP veritabanından ayrı bir raporlama veya karar destek veritabanıyla kısıtlayabilirsiniz.

    Bu senaryonun etkisi, Azure SQL Veritabanı'ndaki varsayılan yapılandırma olan yürütülen anlık görüntüyü oku özelliği veritabanında etkinleştirildiğinde azalır. Bu makalenin Engellemeyi anlama bölümünde daha fazla bilgi edinin.

    Not

    Azure SQL Veritabanı bağlanan uygulamalar için yeniden deneme mantığı kılavuzuna bakın.

    Çözüm: Sonucun tüm satırlarını tamamlanmaya getirmek için uygulamanın yeniden yazılması gerekir. Bu, sunucu tarafı sayfalamasını gerçekleştirmek için sorgunun ORDER BY yan tümcesinde OFFSET ve FETCH kullanımını dışlamaz.

  4. Geri alma durumundaki bir oturumun neden olduğu engelleme

    KILLed olan veya kullanıcı tanımlı bir işlemin dışında iptal edilen 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 bir istek kilitlenme kurbanı olarak seçildiğinde de oluşabilir. Bu genellikle sys.dm_exec_requests çıktısını gözlemleyerek tanımlanabilir. Bu, ROLLBACK komutunu gösterebilir ve percent_complete sütununda ilerleme durumu belirtilebilir.

    2019'da kullanıma sunulan Hızlandırılmış veritabanı kurtarma sayesinde, uzun geri alma işlemleri nadir olmalıdır.

    Çözüm: SPID'nin yapılan değişiklikleri geri döndürmeyi bitirmesini bekleyin.

    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.

  5. Yalnız bırakılmış bağlantının neden olduğu engelleme

    İstemci uygulaması hataları yakalarsa veya istemci iş istasyonu yeniden başlatılırsa, sunucuya yönelik ağ oturumu bazı koşullar altında hemen iptal edilmeyebilir. Azure SQL Veritabanı perspektifinden bakıldığında, istemci hala mevcut görünüyor ve alınan tüm kilitler hala korunabilir. Daha fazla bilgi için bkz: SQL Server'da yetim bağlantı sorunlarını giderme.

    Çözüm: İstemci uygulamasının kaynakları düzgün bir şekilde temizlenmeden bağlantısı kesildiyse komutunu kullanarak SPID'yi KILL sonlandırabilirsiniz. KILL komutu, SPID değerini giriş olarak alır. Örneğin, SPID 99'u öldürmek için aşağıdaki komutu çalıştırın:

    KILL 99

İlgili içerik