Azure SQL Veritabanı engelleme sorunlarını anlama ve çözme
Şunlar için geçerlidir: Azure SQL Veritabanı
Amaç
Makale, Azure SQL veritabanlarında engellemeyi açıklar ve engelleme sorunlarını gidermeyi ve çözmeyi gösterir.
Bu makalede bağlantı terimi, veritabanının tek bir oturum açma 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 süreç olarak adlandırılır, ancak her zamanki anlamda ayrı bir süreç bağlamı değildir. Bunun yerine 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ında kilitlenmeleri analiz etme ve önleme.
Not
Bu içerik Azure SQL Veritabanına 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 engelleme hakkında daha fazla bilgi için bkz. Engelleme sorunlarını anlama ve çözme SQL Server.
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ındaki bir 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. Genellikle, ilk SPID'nin kaynağı kilitlediği zaman dilimi küçük olur. Sahip olan oturum kilidi serbest bıraktığında, ikinci bağlantı kaynak üzerinde kendi kilidini almak ve işlemeye devam etmek için serbesttir. Bu normal bir davranıştır 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 kaydedilmiş okuma anlık görüntüsü (RCSI) veritabanı ayarı etkindir. Eşzamanlılığı artırmak için satır sürümü oluşturma kullanan RCSI altında, verileri okuyan oturumlar ve veri yazan oturumlar arasında engelleme en aza indirilir. Ancak, Azure SQL Veritabanındaki veritabanlarında engelleme ve kilitlenmeler oluşmaya devam edebilir çünkü:
- Verileri değiştiren sorgular birbirlerini engelleyebilir.
- Sorgular, engellemeyi artıran yalıtım düzeyleri altında çalıştırılabilir. Yalıtım düzeyleri Transact-SQL'deki uygulama bağlantı dizelerinde, sorgu ipuçlarında veya SET deyimlerinde belirtilebilir.
- RCSI devre dışı bırakılabilir ve bu da veritabanının SELECT deyimlerini korumak için paylaşılan (S) kilitleri kullanmasına ve okuma işlemi yapılan yalıtım düzeyi altında çalışmasına neden olabilir. Bu, engelleme ve kilitlenmeleri artırabilir.
Anlık görüntü yalıtım düzeyi, Azure SQL Veritabanındaki yeni veritabanları için de varsayılan olarak etkindir. 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ın veya bağlantıların işlem yalıtım düzeyini açıkça olarak
SNAPSHOTolarak ayarlaması gerekir. 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ı'nda 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 etkinleştirilirse sütun
is_read_committed_snapshot_on1 değerini döndürür. Anlık görüntü yalıtımı etkinleştirilirse sütun
snapshot_isolation_state_descON değerini döndürür.
Sorgunun süresi ve işlem bağlamı, kilitlerinin ne kadar süreyle tutuldığını ve böylece 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 deyimleri için, 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 tutulduğu 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:
- Veritabanı Altyapısında Kilitleme
- Kilitleme ve Satır Sürüm Oluşturmayı Özelleştirme
- Kilit Modları
- Kilit Uyumluluğu
- İşlemler
Kilitleme ve engelleme, sistem performansı üzerinde zarara neden olan bir etkinin olduğu noktaya kadar devam ettiğinde, 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 kaynakta kilit tutar ve bunları hiçbir zaman serbest bırakmaz. Bu engelleme türü kendi kendine çözümlenmez 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 gibi durumlarda sorunu tek tek sorgulara daraltmak için SQL Server Management Studio kullanarak sorunları gidermek zordur. Buna karşılık, ikinci durum tanılanması daha kolay olabilecek tutarlı bir durumla sonuçlanabilir.
Uygulamalar ve engelleme
Engelleme sorunuyla karşılaşıldığında 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 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 her sorgu için kaynak tüketimi, yalıtım düzeyi ve işlem yolu uzunluğu değerlendirilmelidir. Her sorgu ve işlem olabildiğince basit olmalıdır. İyi bir 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ı arttıkça performans önemli ölçüde düşebilir.
Düzgün uygulama ve sorgu tasarımı ile Azure SQL Veritabanı, aynı anda binlerce kullanıcıyı tek bir sunucuda çok az engellemeyle destekleyebilecek özelliktedir.
Not
Daha fazla uygulama geliştirme kılavuzu için bkz. Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği ile Geçici Hata İşleme ile ilgili bağlantı sorunlarını ve diğer hataları giderme.
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 edecek olanlardır. Burada kavram, baş engelleyiciyi bulmak ve sorgunun ne yaptığını ve neden engellendiğini belirlemektir. Sorunlu sorgu tanımlandıktan sonra (yani uzun süre kilit tutan sorgu), bir 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şiklikler yapabilirsiniz.
Sorun giderme adımları:
Ana engelleme oturumunu belirleme (baş engelleyici)
Engellemeye neden olan sorguyu ve işlemi bulun (uzun bir süre kilit tutan şey)
Uzun süren engellemenin neden gerçekleştiğini analiz etme/anlama
Sorguyu ve işlemi yeniden tasarlayarak engelleme sorununu çözme
Şimdi, uygun bir veri yakalama ile ana engelleme oturumunu nasıl tespit etmek istediğinize bakalım.
Engelleme bilgilerini toplama
Engelleme sorunlarını giderme zorluğunu gidermek için veritabanı yöneticisi, Azure SQL Veritabanındaki veritabanındaki kilitleme ve engelleme durumunu sürekli izleyen SQL betiklerini kullanabilir. Bu verileri toplamak için temelde iki 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). İkinci yöntem, yürütülenleri 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ı hedefler. 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 yürüyebilir ve ardından kilidini neden koruyabildiğini araştırabilirsiniz.
Bu betiklerin her birini Azure SQL Veritabanındaki 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, sorgulayıp filtrelemesi daha kolay olan bir sonuç kümesinde daha fazla veri döndürür. Diğer sorguların çekirdeğinde 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 dinamik yönetim işlevi (DMF) ile
sys.dm_exec_input_buffersorgulanması ve filtrelenmesi daha kolay bir sonuç kümesinde döndürülerek session_id ve request_id sağlanabilir. Ö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);
içindeki
sys.dm_exec_requestssütununa
blocking_session_idbakın. = 0 olduğunda
blocking_session_id, bir oturum engellenmiyor. 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 ve
sys.dm_exec_sessionsarasında
sys.dm_exec_requestsbu ortak birleştirmeyi oluşturun.
sys.dm_exec_sql_text veyasys.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. alanı
sys.dm_exec_sql_texttarafından
textdöndürülen veriler NULL ise, sorgu şu anda yürütülmüyordur. Bu durumda,
event_infoalanı
sys.dm_exec_input_bufferSQL 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;
- Bir engelleme zincirinde yer alan oturumların sorgu metni de dahil olmak üzere birden çok oturum engelleme zincirinin başını belirlemek için Microsoft Desteği tarafından sağlanan bu daha ayrıntılı örnek sorguyu çalıştırı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 geçerli açık işlemleri görüntülemek için sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections ve sys.dm_exec_sql_text gibi başka bir DMV kümesi kullanın. İşlemleri izlemeyle ilişkili birkaç DMV vardır, burada işlemlerle ilgili 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'in iş parçacığı/görev katmanındaki başvuru sys.dm_os_waiting_tasks. Bu, isteğin şu anda karşılaştığı SQL bekleme türü 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ç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.
- 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 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.
ü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'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.
Genişletilmiş Olaylar'dan bilgi toplama
Önceki bilgilere ek olarak, Azure SQL Veritabanındaki engelleme sorununu ayrıntılı bir şekilde araştırmak için 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ş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 sisteme daha çok yönlülük ve daha az etki sağlayan daha yeni izleme teknolojisidir ve arabirimi SQL Server Management Studio (SSMS) ile tümleştirilir.
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ı 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
- Error_reported
- Execution_warning
Kategori Uyarıları:
- Missing_join_predicate
Kategori Yürütme:
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
Kategori deadlock_monitor
- database_xml_deadlock_report
Kategori oturumu
- Existing_connection
- Oturum aç
- Oturumu Kapat
Not
Kilitlenmeler hakkında ayrıntılı bilgi için bkz. Azure 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ğı anlatılır. Bu tartışmada engelleyici SPID'lerle ilgili bilgileri yakalamak için engelleme betiklerini kullandığınız (daha önce başvuruluyor) ve XEvent oturumu kullanarak uygulama etkinliğini yakaladığınız varsayılır.
Engelleme verilerini analiz etme
ve
session_idkullanarak
blocking_theseDMV'lerin
sys.dm_exec_requestsçıkışını inceleyin ve
sys.dm_exec_sessionsengelleme 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 tabloyu paylaşırlar ve engelleme zincirinde yer alan oturumlardan biri veya daha fazlası bir yazma işlemi gerçekleştirmektedir.
DMV'lerin
sys.dm_exec_requestsçıkışını inceleyin ve
sys.dm_exec_sessionsengelleme zincirinin başındaki SPID'ler hakkında bilgi için. 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 tamamlandığını ve uygulamanın başka bir sorgu veya toplu iş göndermesini beklediğini gösterir. Çalıştırılabilir veya çalışır durumda olması, 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ı Arka Plan 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ülüyor değil. Bu genellikle SPID'nin uygulamadan bir komut beklediğini gösterir. Çalışma SPID şu anda bir zamanlayıcı üzerinde çalışıyor. Runnable SPID, bir zamanlayıcının çalıştırılabilir kuyruğundadır ve zamanlayıcı süresini almak için bekler. 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 deyim tarafından alınan kilitleri tutuyor olabilir.
sys.dm_exec_requests.open_transaction_count
Benzer şekilde, bu alan size bu istekteki açık hareket 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.
sys.dm_exec_requests.wait_type,
wait_timeve
last_wait_type
sys.dm_exec_requests.wait_typeNULL ise, istek şu anda hiçbir şey beklemez ve
last_wait_typedeğer isteğin en son
wait_typekarşılaştığını gösterir. En yaygın bekleme türleri hakkında
sys.dm_os_wait_statsdaha fazla bilgi ve açıklama için bkz. sys.dm_os_wait_stats.
wait_timedeğeri, isteğin ilerleme kaydedip ilerlemediğini belirlemek için kullanılabilir. Tabloya
sys.dm_exec_requestsyönelik bir sorgu, sütununda önceki bir sorgusundan
sys.dm_exec_requestsalınan değerden daha
wait_timeküçük bir değer döndürdüğünde
wait_time, bu, ö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, isteğin
wait_resourcebeklediği kaynağı görüntüleyen between
sys.dm_exec_requestsçıkışı karşılaştırılarak doğrulanabilir.
sys.dm_exec_requests.wait_resourceBu alan, engellenen bir isteğin beklediği kaynağı gösterir. Aşağıdaki tabloda yaygın
wait_resourcebiçimler ve anlamları listelenmiştir:
Kaynak Biçimlendir Örnek Açıklama Tablo DatabaseID:ObjectID:IndexID SEKME: 5:261575970:1 Bu durumda, veritabanı kimliği 5 pubs örnek veritabanıdır ve nesne kimliği 261575970 başlıklar 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 içinden DatabaseID, FileId, PageId
wait_resourcedeğerini geçirin.
Anahtar DatabaseID:Hobt_id (Dizin anahtarı için karma değer) ANAHTAR: 5:72057594044284928 (3300a4f361aa) Bu durumda, veritabanı kimliği 5 Pubs'dır Hobt_ID 72057594044284928 object_id 261575970 için index_id 2'ye karşılık gelir (başlık tablosu).
sys.partitionshobt_id belirli
index_idbir ve
object_idile ilişkilendirmek için katalog görünümünü kullanın. Dizin anahtarı karmasını belirli bir anahtar değeriyle silmenin 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ı, sayfa 104 başlıklar tablosuna ait bir sayfa ve yuva 3 satır sayfadaki konumunu gösterir. Derlemek 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ı, sayfa 104 başlıklar tablosuna ait bir sayfa ve yuva 3 satır 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 birleştirilebilen 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 durum verilerini 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 , 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 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 (ana bilgisayar adı), belirli ağ kitaplıklarında (net_library), BIR SPID
last_request_start_timetarafından gönderilen son toplu iş içinde
sys.dm_exec_sessionsolduğunda , içinde kullanarak 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ı nedenleri ile eşlenmiştir.
Waittype, Open_Tran ve 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 veya oturumun komutuyla
KILL kesilmesi gerekip gerekmediğini gösterir. Daha fazla bilgi için bkz . KILL (Transact-SQL).
|Senaryo||Waittype||Open_Tran||Durum||Giderir?||Diğer Belirtiler|
|1||NOT NULL||>= 0||Runnable||Evet, sorgu tamamlandığında.||,


|2||NULL||>0||Uyku||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 ise (READ COMMMITTED), 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 sonlandırılabilir, ancak 30 saniye kadar sürebilir.||


|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||Uyku||Son -unda. Windows NT oturumun artık etkin olmadığını belirlediğinde Azure SQL Veritabanı bağlantısı kesilir.||


Ayrıntılı engelleme senaryoları
Normalde ç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 engelleme sorunu sınıfı yalnızca bir performans sorunu olabilir ve bunu bu şekilde izlemenizi gerektirir. Belirli bir yavaş çalışan sorguyla ilgili sorunları giderme hakkında bilgi için bkz. SQL Server yavaş çalışan sorgularda sorun giderme. Daha fazla bilgi için bkz. Performans İzleme ve Ayarlama.
SSMS'de Sorgu Deposu'ndan gelen raporlar, en yüksek maliyetli sorguları, en iyi durumda olmayan yürütme planlarını belirlemek için önerilen ve değerli bir araçtır. Sorgu Performansı İçgörüleri de dahil olmak üzere Azure SQL veritabanının Azure portal Akıllı Performans bölümünü de 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 iyileştirilemeyen uzun süre çalışan bir sorgunuz varsa, bunu OLTP ortamından veritabanının zaman uyumlu salt okunur bir çoğaltması olan ayrılmış bir raporlama sistemine taşımayı göz önünde bulundurun.
Kaydedilmemiş bir işlemi olan uyuyan BIR SPID'nin neden olduğu engelleme
Bu engelleme türü genellikle, işlem iç içe yerleştirme düzeyi (
@@TRANCOUNT
open_transaction_countkimden
sys.dm_exec_requests) sıfırdan büyük olan, uykuda olan veya 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 işlemi verirse ortaya çıkabilir. 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. Azure SQL Veritabanı, tek bir sorgu iptal edildiği için işlemin tamamının geri alınması gerektiğini varsayamadığından, 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; 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 çıkışı, işlem iç içe yerleştirme düzeyinin bir olduğunu gösterir. İşlemde alınan tüm kilitler, işlem yürütülene veya geri alınana kadar tutulur. Uygulamalar işlemleri açıkça açar ve işlerse, bir iletişim veya başka bir hata oturumu ve işlemini açık durumda bırakabilir.
Örnek genelinde şu anda kaydedilmemiş işlemleri belirlemek için bu makalenin
sys.dm_tran_active_transactionsönceki bölümlerindeki betiği kullanın.
Çözünürlükler:
Ayrıca, bu engelleme sorunu sınıfı da bir performans sorunu olabilir ve bu nedenle bunu takip etmenizi 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ı incelemenizden de yararlanabilirsiniz.
Uygulamalar işlem iç içe yerleştirme düzeylerini düzgün bir şekilde yönetmelidir veya sorgunun bu şekilde iptal edilmesinden sonra engelleme sorununa neden olabilir. Aşağıdaki topluluklara bir göz atın:
- İstemci uygulamanın hata işleyicisinde, istemci uygulama 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 saklı yordam istemci uygulamasının bilgisi olmadan bir işlem başlatmış olabileceğinden açık işlemlerin denetlenerek yapılması gerekir. Sorguyu iptal etme gibi bazı koşullar, yordamın geçerli deyimden sonra yürütülmesini engeller, bu nedenle yordamın işlemi denetleme
IF @@ERROR <> 0ve durdurma mantığı olsa bile, bu geri alma kodu bu gibi durumlarda yürütülmeyecektir.
- Bağlantı havuzu, bağlantıyı açan ve web tabanlı bir uygulama gibi havuza bağlantıyı bırakmadan önce az sayıda sorgu çalıştıran bir uygulamada kullanılıyorsa, bağlantı havuzunu geçici olarak devre dışı bırakmak, istemci uygulama hataları uygun şekilde işlemek üzere değiştirilene kadar sorunu hafifletmeye yardımcı olabilir. Bağlantı havuzu devre dışı bırakılarak, bağlantı serbest bırakılarak Azure SQL Veritabanı bağlantısının fiziksel olarak kesilmesine neden olur ve bu da sunucunun tüm açık işlemleri geri almasına neden olur.
- Bağlantı için veya işlemleri başlatan ve bir hatanın ardından temizlemeyen saklı yordamlarda kullanın
SET XACT_ABORT ON. Ç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.
- İstemci uygulamanın hata işleyicisinde, istemci uygulama bir işlemin açık olduğuna inanmasa bile herhangi bir hatayı izleyerek yürütür
Not
Bağlantı, bağlantı havuzundan yeniden kullanılana kadar 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 işlem 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 ONen 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.
-
karşılık gelen 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, kilitler tablolarda bırakılabilir ve diğer kullanıcılar engellenebilir. 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 yapmak için 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.
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ına bağlanan uygulamalar için yeniden deneme mantığı kılavuzuna bakın.
Çözüm: Sonucun tüm satırlarını tamamlanmak üzere getirmek için uygulamanın yeniden yazılması gerekir. Bu, sunucu tarafı disk belleği gerçekleştirmek için sorgunun ORDER BY yan tümcesinde OFFSET ve FETCH kullanımını elemez.
Geri alma durumundaki bir oturumun neden olduğu engelleme
KILLed olan veya kullanıcı tanımlı bir işlemin dışında iptal edilen bir veri değiştirme sorgusu geri alınır. Bu, 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 çıktısı
sys.dm_exec_requestsgözlemlenerek tanımlanabilir. Bu, ROLLBACK komutunu gösterebilir ve sütunda
percent_completeilerleme gösterilebilir.
2019'da sunulan Hızlandırılmış Veritabanı Kurtarma özelliği sayesinde, uzun geri alma işlemleri nadir olmalıdır.
Çözünürlük: 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ştirmayın. Mümkünse, düşük etkinlik dönemlerinde bu tür işlemleri gerçekleştirin.
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ı tarafından bakıldığında istemci hâlâ mevcut gibi görünür ve alınan kilitler korunmaya devam edilebilir. Daha fazla bilgi için bkz. SQL Server'da yalnız bırakılmış 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
KILLsonlandırabilirsiniz.
KILLkomutu SPID değerini giriş olarak alır. Örneğin, SPID 99'u sonlandırmak için aşağıdaki komutu çalıştırın:
KILL 99
Ayrıca bkz.
- Azure SQL Veritabanında kilitlenmeleri analiz etme ve önleme
- Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği'nde izleme ve performansı ayarlama
- Sorgu Deposu kullanarak performans izleme
- İşlem Kilitleme ve Satır Sürümü Oluşturma Kılavuzu
- İŞLEM YALıTıM DÜZEYINI AYARLAMA
- Hızlı Başlangıç: SQL Server’da genişletilmiş olaylar
- Veritabanı performansını izlemek ve sorunlarını gidermek için yapay zeka kullanan Akıllı İçgörüler
Sonraki adımlar
- Azure SQL Veritabanı: Otomatik Ayarlama ile Performans Ayarlamayı Geliştirme
- Azure SQL ile tutarlı performans sunma
- Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği ile ilgili bağlantı sorunlarını ve diğer hataları giderme
- Geçici Hata İşleme
- Azure SQL Veritabanı’nda en yüksek paralellik derecesini (MAXDOP) yapılandırma
- Azure SQL Veritabanı’nda yüksek CPU sorunlarını tanılama ve giderme