Udostępnij za pośrednictwem

Omówienie i rozwiązywanie problemów z blokowaniem

  • Artykuł

Dotyczy:Baza danych SQL Usługi Azure SQL Databasew sieci szkieletowej

W tym artykule opisano blokowanie w usłudze Azure SQL Database i bazie danych SQL Fabric oraz pokazano, jak rozwiązywać problemy z blokowaniem i rozwiązywać problemy z blokowaniem.

Cel cząstkowy

W tym artykule termin połączenie odnosi się do pojedynczej sesji logowania bazy danych. Każde połączenie jest wyświetlane jako identyfikator sesji (SPID) lub session_id w wielu widokach dynamicznego zarządzania (DMV). Każdy z tych identyfikatorów SPID jest często określany jako proces, chociaż nie jest to oddzielny kontekst procesu w zwykłym sensie. Zamiast tego każdy identyfikator SPID składa się z zasobów serwera i struktur danych niezbędnych do obsługi żądań pojedynczego połączenia od danego klienta. Jedna aplikacja kliencka może mieć co najmniej jedno połączenie. Z perspektywy usługi Azure SQL Database nie ma różnicy między wieloma połączeniami z jednej aplikacji klienckiej na jednym komputerze klienckim i wieloma połączeniami z wielu aplikacji klienckich lub wielu komputerów klienckich; są niepodzielne. Jedno połączenie może zablokować inne połączenie, niezależnie od klienta źródłowego.

Aby uzyskać informacje na temat rozwiązywania problemów z zakleszczeniami, zobacz Analizowanie i zapobieganie zakleszczeniom w usłudze Azure SQL Database i bazie danych SQL Fabric.

Uwaga

Ta zawartość koncentruje się na usłudze Azure SQL Database. Usługa Azure SQL Database jest oparta na najnowszej stabilnej wersji aparatu bazy danych programu Microsoft SQL Server, więc większość zawartości jest podobna, chociaż opcje rozwiązywania problemów i narzędzia mogą się różnić. Aby uzyskać więcej informacji na temat blokowania w programie SQL Server, zobacz Omówienie i rozwiązywanie problemów z blokowaniem programu SQL Server. Usługa Fabric SQL Database udostępnia wiele funkcji w usłudze Azure SQL Database. Aby uzyskać więcej informacji na temat monitorowania wydajności, zobacz Monitor SQL Database in Microsoft Fabric.

Informacje o blokowaniu

Blokowanie jest niemożliwą do uniknięcia i celową cechą każdego systemu zarządzania relacyjnymi bazami danych (RDBMS) ze współbieżnością opartą na blokadach. Blokowanie w bazie danych w usłudze Azure SQL Database występuje, gdy jedna sesja przechowuje blokadę określonego zasobu, a drugi identyfikator SPID próbuje uzyskać typ blokady powodującej konflikt w tym samym zasobie. Zazwyczaj przedział czasu, dla którego pierwszy identyfikator SPID blokuje zasób, jest mały. Gdy sesja będąca właścicielem zwalnia blokadę, drugie połączenie jest następnie zwalniane, aby uzyskać własną blokadę na zasobie i może kontynuować przetwarzanie. To zachowanie jest normalne i może wystąpić wiele razy w ciągu dnia bez zauważalnego wpływu na wydajność systemu.

Każda nowa baza danych w usłudze Azure SQL Database ma domyślnie włączone ustawienie bazy danych migawki zatwierdzonej do odczytu (RCSI). Blokowanie między sesjami odczytu danych i sesji zapisywania danych jest zminimalizowane w obszarze RCSI, co używa przechowywania wersji wierszy w celu zwiększenia współbieżności. Jednak blokowanie i zakleszczenia mogą nadal występować w bazach danych w usłudze Azure SQL Database, ponieważ:

  • Zapytania modyfikujące dane mogą blokować się nawzajem.
  • Zapytania mogą być uruchamiane na poziomach izolacji, które zwiększają blokowanie. Poziomy izolacji można określić w parametry połączenia aplikacji, wskazówki dotyczące zapytań lub instrukcje SET w języku Transact-SQL.
  • Funkcja RCSI może być wyłączona, co powoduje, że baza danych może używać blokad udostępnionych (S) w celu ochrony instrukcji SELECT uruchamianych na poziomie izolacji zatwierdzonej do odczytu. Może to zwiększyć blokowanie i zakleszczenia.

Poziom izolacji migawek jest również domyślnie włączony dla nowych baz danych w usłudze Azure SQL Database. Izolacja migawki to dodatkowy poziom izolacji oparty na wierszach, który zapewnia spójność na poziomie transakcji dla danych i który używa wersji wierszy do wybierania wierszy do aktualizacji. Aby użyć izolacji migawki, zapytania lub połączenia muszą jawnie ustawić poziom izolacji transakcji na SNAPSHOTwartość . Można to zrobić tylko wtedy, gdy dla bazy danych jest włączona izolacja migawki.

Możesz określić, czy izolacja RCSI i/lub migawki jest włączona w języku Transact-SQL. Połącz się z bazą danych w usłudze Azure SQL Database i uruchom następujące zapytanie:

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

Jeśli funkcja RCSI jest włączona, kolumna is_read_committed_snapshot_on zwraca wartość 1. Jeśli izolacja migawki jest włączona, kolumna snapshot_isolation_state_desc zwraca wartość WŁĄCZONE.

Czas trwania zapytania oraz jego kontekst transakcji określają, jak długo utrzymywane są blokady, oraz jaki mają wpływ na inne zapytania. Instrukcje SELECT uruchamiane w obszarze RCSI nie uzyskują blokad udostępnionych (S) na odczytywanych danych i w związku z tym nie blokują transakcji modyfikujących dane. W przypadku instrukcji INSERT, UPDATE i DELETE blokady są przechowywane podczas zapytania, zarówno w celu zapewnienia spójności danych, jak i umożliwienia wycofania zapytania w razie potrzeby.

W przypadku zapytań wykonywanych w ramach transakcji jawnej typ blokad i czas trwania, dla których blokady są przechowywane, są określane przez typ zapytania, poziom izolacji transakcji i określa, czy wskazówki dotyczące blokady są używane w zapytaniu. Opis blokowania, wskazówek dotyczących blokowania i poziomów izolacji transakcji można znaleźć w następujących artykułach:

Gdy blokowanie utrzymuje się do punktu, w którym występuje szkodliwy wpływ na wydajność systemu, jest to spowodowane jedną z następujących przyczyn:

  • Identyfikator SPID przechowuje blokady zestawu zasobów przez dłuższy czas przed ich wydaniem. Ten typ blokowania rozwiązuje się w czasie, ale może powodować obniżenie wydajności.

  • Identyfikator SPID przechowuje blokady zestawu zasobów i nigdy ich nie zwalnia. Ten typ blokowania nie rozwiązuje się samodzielnie i uniemożliwia dostęp do zasobów, których dotyczy problem, na czas nieokreślony.

W pierwszym scenariuszu sytuacja może być bardzo płynna, ponieważ różne identyfikatory SPID powodują blokowanie różnych zasobów w czasie, tworząc ruchomy cel. Te sytuacje są trudne do rozwiązania przy użyciu SQL Server Management Studio, aby zawęzić problem do poszczególnych zapytań. Natomiast druga sytuacja powoduje spójny stan, który może być łatwiejszy do zdiagnozowania.

Zoptymalizowane blokowanie

Zoptymalizowane blokowanie to nowa funkcja aparatu bazy danych znacząco zmniejsza pamięć blokady i liczbę blokad jednocześnie wymaganych do zapisu. Zoptymalizowane blokowanie używa dwóch podstawowych składników: blokowanie identyfikatora transakcji (TID) (używane również w innych funkcjach przechowywania wersji wierszy) i blokowanie po kwalifikacjach (LAQ). Nie wymaga żadnej dodatkowej konfiguracji.

Ten artykuł dotyczy obecnie zachowania aparatu bazy danych bez zoptymalizowanego blokowania.

Aby uzyskać więcej informacji i dowiedzieć się, gdzie jest dostępna zoptymalizowana blokada, zobacz Zoptymalizowane blokowanie.

Aplikacje i blokowanie

Istnieje tendencja do skupienia się na dostrajaniu po stronie serwera i problemach z platformą w przypadku wystąpienia problemu blokującego. Jednak zwracanie uwagi tylko na bazę danych może nie prowadzić do rozwiązania i może lepiej absorbować czas i energię skierowaną do badania aplikacji klienckiej i przesyłanych zapytań. Niezależnie od poziomu widoczności, jaki aplikacja oferuje w kontekście wywołań bazy danych, problem blokujący często wymaga zarówno inspekcji dokładnej składni SQL przesłanej przez aplikację, jak i ścisłego zachowania aplikacji w zakresie anulowania zapytań, zarządzania połączeniami, pobierania wszystkich wierszy wyników itd. Jeśli narzędzie programistyczne nie zezwala na jawną kontrolę nad zarządzaniem połączeniami, anulowaniem zapytań, limitem czasu zapytania, pobieraniem wyników itd., blokowanie problemów może nie być możliwe do rozwiązania. Ten potencjał należy dokładnie zbadać przed wybraniem narzędzia do tworzenia aplikacji dla usługi Azure SQL Database, szczególnie w przypadku środowisk OLTP z uwzględnieniem wydajności.

Zwróć uwagę na wydajność bazy danych na etapie projektowania i budowy bazy danych i aplikacji. W szczególności powinno się oceniać zużycie zasobów, poziom izolacji i długość ścieżki transakcji dla każdego zapytania. Każde zapytanie i transakcja powinny być jak najbardziej uproszczone. Należy zachować dyscyplinę w zarządzaniu połączeniami. Bez niego aplikacja może wydawać się mieć akceptowalną wydajność przy niskiej liczbie użytkowników, ale wydajność może znacznie obniżyć się w miarę skalowania liczby użytkowników w górę.

Dzięki właściwemu projektowi aplikacji i zapytań usługa Azure SQL Database może obsługiwać wiele tysięcy równoczesnych użytkowników na jednym serwerze z niewielkim blokowaniem.

Uwaga

Aby uzyskać więcej wskazówek dotyczących tworzenia aplikacji, zobacz Rozwiązywanie problemów z łącznością oraz inne błędy i obsługa błędów przejściowych.

Rozwiązywanie problemów z blokowaniem

Niezależnie od sytuacji blokowania, w której się znajdujemy, metodyka rozwiązywania problemów z blokowaniem jest taka sama. To logiczne separacje dyktują resztę kompozycji tego artykułu. Koncepcja polega na znalezieniu blokady głównej i określeniu, co to zapytanie robi i dlaczego blokuje. Po zidentyfikowaniu problematycznego zapytania (czyli przechowywania blokad przez dłuższy czas), następnym krokiem jest przeanalizowanie i określenie przyczyny blokowania. Po zrozumieniu przyczyn możemy wprowadzić zmiany, przeprojektując zapytanie i transakcję.

Kroki do podjęcia w celu rozwiązywania problemów:

  1. Identyfikowanie głównej sesji blokowania (bloker główny)

  2. Znalezienie zapytania i transakcji powodującej blokowanie (co powoduje blokowanie przez dłuższy czas)

  3. Analizowanie/zrozumienie, dlaczego występuje blokowanie przez dłuższy czas

  4. Rozwiązanie problemu z blokowaniem przez przeprojektowanie zapytania i transakcji

Teraz przyjrzyjmy się omówieniu sposobu określenia głównej sesji blokującej przy użyciu odpowiedniego przechwytywania danych.

Zbieranie informacji o blokowaniu

Aby przeciwdziałać trudnościom z rozwiązywaniem problemów z blokowaniem, administrator bazy danych może używać skryptów SQL, które stale monitorują stan blokowania i blokowania w bazie danych w usłudze Azure SQL Database. Aby zebrać te dane, istnieją zasadniczo dwie metody.

Pierwszą z nich jest wykonywanie zapytań dotyczących obiektów zarządzania dynamicznego (DMO) i przechowywanie wyników do porównania w czasie. Niektóre obiekty, do których odwołuje się ten artykuł, to dynamiczne widoki zarządzania (DMV), a niektóre to dynamiczne funkcje zarządzania (DMF). Druga metoda polega na użyciu modułów XEvents w celu przechwycenia wykonywanych operacji.

Zbieranie informacji z widoków DMV

Odwoływanie się do widoków DMV w celu rozwiązywania problemów z blokowaniem ma na celu określenie identyfikatora SPID (identyfikatora sesji) na czele łańcucha blokowania i instrukcji SQL. Poszukaj zablokowanych identyfikatorów SPID ofiary. Jeśli jakikolwiek identyfikator SPID jest blokowany przez inny identyfikator SPID, zbadaj identyfikator SPID będący właścicielem zasobu (blokujący identyfikator SPID). Czy ten identyfikator SPID będący właścicielem jest również blokowany? Możesz przejść przez łańcuch, aby znaleźć bloker główny, a następnie zbadać, dlaczego utrzymuje blokadę.

Pamiętaj, aby uruchomić każdy z tych skryptów w docelowej bazie danych w usłudze Azure SQL Database.

  • Polecenia sp_who i sp_who2 są starszymi poleceniami, aby wyświetlić wszystkie bieżące sesje. Funkcja DMV sys.dm_exec_sessions zwraca więcej danych w zestawie wyników, który jest łatwiejszy do wykonywania zapytań i filtrowania. sys.dm_exec_sessions można znaleźć w rdzeniu innych zapytań.

  • Jeśli masz już określoną sesję, możesz użyć DBCC INPUTBUFFER(<session_id>), aby znaleźć ostatnią instrukcję przesłaną przez sesję. Podobne wyniki można zwrócić za pomocą funkcji zarządzania dynamicznego (DMF) sys.dm_exec_input_buffer w zestawie wyników, który jest łatwiejszy do odpytywania i filtrowania, zapewniając session_id i request_id. Aby na przykład zwrócić najnowsze zapytanie przesłane przez session_id 66 i request_id 0:

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

  • Zapoznaj się z kolumną w pliku blocking_session_idsys.dm_exec_requests. Gdy blocking_session_id = 0, sesja nie jest blokowana. Lista zawiera tylko żądania aktualnie wykonywane, ale sys.dm_exec_requests wszystkie połączenia (aktywne lub nie) są wyświetlane na liście .sys.dm_exec_sessions Skompiluj na tym typowe sprzężenia między sys.dm_exec_requests i sys.dm_exec_sessions w następnym zapytaniu.

  • Uruchom to przykładowe zapytanie, aby znaleźć aktywne wykonywanie zapytań i bieżącego SQL tekstu wsadowego lub wejściowego tekstu buforu przy użyciu DMV sys.dm_exec_sql_text lub sys.dm_exec_input_buffer. Jeśli dane zwrócone przez pole textsys.dm_exec_sql_text mają wartość NULL, zapytanie nie jest obecnie wykonywane. W takim przypadku event_info pole zawiera sys.dm_exec_input_buffer ostatni ciąg polecenia przekazany do aparatu SQL. To zapytanie może również służyć do identyfikowania sesji blokujących inne sesje, w tym listę session_ids zablokowanych według session_id.

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;
  • Uruchom to bardziej zaawansowane przykładowe zapytanie dostarczone przez pomoc techniczną Microsoft, aby zidentyfikować główny łańcuch blokowania wielu sesji, w tym tekst zapytania sesji biorących udział w łańcuchu blokowania.
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];
  • Odwołanie sys.dm_os_waiting_tasks , które znajduje się w warstwie wątku/zadania sql. Zwraca on informacje o typie oczekiwania SQL, którego dotyczy obecnie żądanie. Podobnie jak sys.dm_exec_requests, tylko aktywne żądania są zwracane przez sys.dm_os_waiting_tasks.

Uwaga

Aby uzyskać znacznie więcej informacji na temat typów oczekiwania, w tym zagregowanych statystyk oczekiwania w czasie, zobacz sys.dm_db_wait_stats DMV. Ten dynamiczny widok zarządzania zwraca zagregowane statystyki oczekiwania tylko dla bieżącej bazy danych.

  • Użyj sys.dm_tran_locks DMV, aby uzyskać bardziej szczegółowe informacje na temat blokad, które zostały umieszczone przez zapytania. Ten dynamiczny widok zarządzania może zwracać duże ilości danych w produkcyjnej bazie danych i jest przydatny do diagnozowania, jakie blokady są obecnie przechowywane.

Ze względu na INNER JOIN w sys.dm_os_waiting_tasks, następujące zapytanie ogranicza dane wyjściowe z sys.dm_tran_locks tylko do obecnie zablokowanych żądań, ich stanu oczekiwania i blokad:

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>';
  • Dzięki dynamicznym widokom zarządzania przechowywanie wyników zapytań w czasie dostarcza punktów danych, które pozwalają na przegląd blokad w określonym przedziale czasu, aby zidentyfikować utrwalone blokady lub trendy.

Zbieranie informacji z zdarzeń rozszerzonych

Oprócz poprzednich informacji często konieczne jest przechwycenie śladu działań na serwerze w celu dokładnego zbadania problemu blokującego w usłudze Azure SQL Database. Jeśli na przykład sesja wykonuje wiele instrukcji w ramach transakcji, jest reprezentowana tylko ostatnia instrukcja, która została przesłana. Jednak jedno z wcześniejszych stwierdzeń może być powodem, dla którego blokady są nadal przechowywane. Śledzenie umożliwia wyświetlanie wszystkich poleceń wykonywanych przez sesję w ramach bieżącej transakcji.

Istnieją dwa sposoby przechwytywania śledzenia w SQL Server; Zdarzenia rozszerzone (XEvents) i Śledzenie profilera. Jednak program SQL Server Profiler jest przestarzałą technologią śledzenia, która nie jest obsługiwana w przypadku usługi Azure SQL Database. Zdarzenia Rozszerzone to nowsza technologia śledzenia, która umożliwia większą wszechstronność i mniejszy wpływ na obserwowany system, a jest zintegrowana z programem SQL Server Management Studio (SSMS).

Zapoznaj się z dokumentem, w ramach którego wyjaśniono, jak używać Kreatora nowych sesji zdarzeń rozszerzonych w programie SSMS. Jednak w przypadku baz danych Azure SQL Database program SSMS udostępnia podfolder zdarzeń rozszerzonych w każdej bazie danych w Eksplorator obiektów. Użyj kreatora sesji zdarzeń rozszerzonych, aby przechwycić te przydatne zdarzenia:

  • Błędy kategorii:

    • Uwaga
    • Error_reported
    • Execution_warning

  • Ostrzeżenia kategorii:

    • Missing_join_predicate

  • Wykonanie kategorii:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Deadlock_monitor kategorii

    • database_xml_deadlock_report

  • Sesja kategorii

    • Existing_connection
    • Zaloguj się
    • Wyloguj

Uwaga

Aby uzyskać szczegółowe informacje na temat zakleszczeń, zobacz Analizowanie i zapobieganie zakleszczeniom w Azure SQL Database i Fabric SQL Database.

Identyfikowanie i rozwiązywanie typowych scenariuszy blokowania

Sprawdzając poprzednie informacje, możesz określić przyczynę większości problemów blokujących. W dalszej części tego artykułu omówiono sposób używania tych informacji do identyfikowania i rozwiązywania niektórych typowych scenariuszy blokowania. W tej dyskusji założono, że użyto skryptów blokujących (o których mowa wcześniej) do przechwytywania informacji o blokujących identyfikatorach SPID i przechwycono działanie aplikacji przy użyciu sesji XEvent.

Analizowanie danych o blokowaniu

  • Sprawdź dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions, aby określić główne łańcuchy blokujące, używając blocking_these i session_id. Najjjaśniej określa, które żądania są blokowane i które blokują. Przyjrzyj się potem zablokowanym i blokującym sesjom. Czy istnieje wspólny lub główny element łańcucha blokującego? Prawdopodobnie mają wspólną tabelę, a co najmniej jedna sesja biorąca udział w łańcuchu blokowania wykonuje operację zapisu.

  • Sprawdź dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions pod kątem informacji o identyfikatorach SPID na czele łańcucha blokowania. Poszukaj następujących pól:

    • sys.dm_exec_requests.status
      Ta kolumna przedstawia stan określonego żądania. Zazwyczaj stan uśpienia wskazuje, że identyfikator SPID zakończył wykonywanie i czeka, aż aplikacja prześle kolejne zapytanie lub partię. Stan uruchamiania lub uruchomienia wskazuje, że identyfikator SPID aktualnie przetwarza zapytanie. Poniższa tabela zawiera krótkie wyjaśnienia różnych wartości stanu.
    Stan Znaczenie
    Tło SpiD uruchamia zadanie w tle, takie jak wykrywanie zakleszczenia, zapis dzienników lub punkt kontrolny.
    Uśpienie SpiD nie jest obecnie wykonywany. Zwykle oznacza to, że spiD oczekuje na polecenie z aplikacji.
    Uruchomiono SpiD jest obecnie uruchomiony w harmonogramie.
    Możliwość uruchomienia SpiD znajduje się w możliwej do uruchomienia kolejce harmonogramu i czeka na uzyskanie czasu harmonogramu.
    Suspended SPID czeka na zasób, taki jak blokada lub zatrzask.

    • sys.dm_exec_sessions.open_transaction_count
      To pole informuje o liczbie otwartych transakcji w tej sesji. Jeśli ta wartość jest większa niż 0, SPID znajduje się w otwartej transakcji i może przechowywać blokady uzyskane przez dowolną instrukcję w ramach transakcji.

    • sys.dm_exec_requests.open_transaction_count
      Podobnie to pole informuje o liczbie otwartych transakcji w tym żądaniu. Jeśli ta wartość jest większa niż 0, SPID znajduje się w otwartej transakcji i może przechowywać blokady uzyskane przez dowolną instrukcję w ramach transakcji.

    • sys.dm_exec_requests.wait_type, wait_time i last_wait_type
      Jeśli sys.dm_exec_requests.wait_type ma wartość NULL, żądanie nie czeka obecnie na nic, a wartość last_wait_type wskazuje ostatnie wait_type napotkane przez żądanie. Aby uzyskać więcej informacji o sys.dm_os_wait_stats i opis najbardziej typowych przykładów oczekiwania, zobacz sys.dm_os_wait_stats. Wartość wait_time może służyć do określenia, czy żądanie czyni postęp. Gdy zapytanie względem sys.dm_exec_requests tabeli zwraca wartość w wait_time kolumnie, która jest mniejsza niż wait_time wartość z poprzedniego zapytania , oznacza to, że poprzednia blokada została pobrana i zwolniona, a teraz czeka na nową blokadę (przy założeniu sys.dm_exec_requests, że niezero wait_time). Można to sprawdzić, porównując dane wyjściowe wait_resource między sys.dm_exec_requests, które wyświetlają zasób, na który oczekuje żądanie.

    • sys.dm_exec_requests.wait_resource To pole wskazuje zasób, na który oczekuje zablokowane żądanie. W poniższej tabeli wymieniono typowe formaty wait_resource i ich znaczenie:

    Zasób Format Przykład Wyjaśnienie
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 W takim przypadku identyfikator bazy danych 5 to przykładowa baza danych pubs, a identyfikator obiektu 261575970 to tabela tytułów, a 1 to indeks klastrowany.
    Strona DatabaseID:FileID:PageID PAGE: 5:1:104 W tym przypadku identyfikator bazy danych 5 is pubs, identyfikator pliku 1 jest podstawowym plikiem danych, a strona 104 jest stroną należącą do tabeli tytułów. Aby zidentyfikować object_id, do którego należy strona, użyj funkcji dynamicznego zarządzania sys.dm_db_page_info, przekazując identyfikator DatabaseID, FileId, PageId z wait_resource.
    Klucz DatabaseID:Hobt_id (wartość skrótu dla klucza indeksu) KEY: 5:72057594044284928 (3300a4f361aa) W takim przypadku identyfikator bazy danych 5 jest pubs, a Hobt_ID 72057594044284928 odpowiada index_id 2 dla object_id 261575970 (tabela tytułów). Użyj widoku wykazu sys.partitions, aby skojarzyć hobt_id z określonym index_id i object_id. Nie ma sposobu na usunięcie skrótu klucza indeksu z określoną wartością klucza.
    Wiersz DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 W tym przypadku identyfikator bazy danych 5 jest pubs, identyfikator pliku 1 jest podstawowym plikiem danych, strona 104 jest stroną należącą do tabeli tytułów, a miejsce 3 wskazuje położenie wiersza na stronie.
    Kompilowanie DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 W tym przypadku identyfikator bazy danych 5 jest pubs, identyfikator pliku 1 jest podstawowym plikiem danych, strona 104 jest stroną należącą do tabeli tytułów, a miejsce 3 wskazuje położenie wiersza na stronie.
    • sys.dm_tran_active_transactionsDMV sys.dm_tran_active_transactions zawiera dane dotyczące otwartych transakcji, które mogą być przyłączone do innych widoków DMV, aby uzyskać pełny obraz transakcji oczekujących na zatwierdzenie lub wycofanie. Użyj następującego zapytania, aby zwrócić informacje o otwartych transakcjach przyłączonych do innych widoków DMV, w tym sys.dm_tran_session_transactions. Rozważ bieżący stan transakcji, transaction_begin_time i inne dane sytuacyjne, aby ocenić, czy może to być źródło blokowania.
    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;

    • Inne kolumny

      Pozostałe kolumny w sys.dm_exec_sessions i sys.dm_exec_request mogą również zapewnić wgląd w źródło problemu. Ich przydatność różni się w zależności od okoliczności problemu. Na przykład można określić, czy problem występuje tylko z niektórych klientów (nazwa hosta), w niektórych bibliotekach sieciowych (net_library), kiedy ostatnia partia przesłana przez SPID znajdowała last_request_start_time się w sys.dm_exec_sessionsobiekcie , jak długo żądanie było uruchomione w start_time programie sys.dm_exec_requestsitd.

Typowe scenariusze blokowania

Poniższa tabela mapuje typowe objawy oraz ich prawdopodobne przyczyny.

Kolumny Waittype, Open_Trani Status odwołują się do informacji zwracanych przez sys.dm_exec_request. Inne kolumny mogą być zwracane przez sys.dm_exec_sessions. Kolumna "Resolves?" wskazuje, czy blokada rozwiązuje się samodzielnie, czy też sesja powinna zostać zakończona za pomocą polecenia KILL. Aby uzyskać więcej informacji, zobacz KILL.

Scenariusz Typ oczekiwania Open_Tran Stan Rozwiązuje? Inne objawy
1 BRAK WARTOŚCI NULL >= 0 runnable Tak, po zakończeniu zapytania. W kolumnach sys.dm_exec_sessions, reads, cpu_timei/lub memory_usage następuje wzrost wraz z upływem czasu. Czas trwania zapytania okazuje się długi po jego zakończeniu.
2 NULL >0 uśpienie Nie, ale SPID może zostać zabity. W sesji zdarzenia rozszerzonego dla tego identyfikatora SPID może być widoczny sygnał uwagi wskazujący przekroczenie limitu czasu zapytania lub anulowanie.
3 NULL >= 0 runnable L.p. Nie rozwiązuje problemu, dopóki klient nie pobierze wszystkich wierszy ani nie zamknie połączenia. SpiD można zabić, ale może to potrwać do 30 sekund. Jeśli wartość open_transaction_count = 0, a identyfikator SPID przechowuje blokady, gdy poziom izolacji transakcji jest domyślny (READ COMMITTED), oznacza to, że jest to prawdopodobna przyczyna.
100 Różne >= 0 runnable L.p. Nie rozwiązuje problemu, dopóki klient nie anuluje zapytań ani nie zamyka połączeń. Identyfikatory SPID mogą zostać zabite, ale może potrwać do 30 sekund. Kolumna hostname w sys.dm_exec_sessions dla SPID znajdującego się na czele łańcucha blokującego jest taka sama jak jednej z SPID, które blokuje.
5 NULL >0 wycofanie Tak. Sygnał uwagi może być widoczny w sesji zdarzeń rozszerzonych dla tego identyfikatora SPID, wskazując, że wystąpił limit czasu zapytania lub anulowanie, lub po prostu wydano instrukcję wycofywania.
6 NULL >0 uśpienie Ostatecznie. Gdy system Windows ustali, że sesja nie jest już aktywna, połączenie usługi Azure SQL Database zostanie przerwane. Wartość last_request_start_time w sys.dm_exec_sessions jest znacznie wcześniejsza niż bieżąca godzina.

Szczegółowe scenariusze blokowania

  1. Blokowanie spowodowane przez normalnie działające zapytanie z długim czasem wykonywania

    Rozwiązanie: Rozwiązaniem tego typu problemu z blokowaniem jest wyszukanie sposobów optymalizacji zapytania. W rzeczywistości ta klasa problemu blokującego może być po prostu problemem z wydajnością i wymaga, aby go realizować. Aby uzyskać informacje na temat rozwiązywania problemów z określonym wolno działającym zapytaniem, zobacz Jak rozwiązywać problemy z wolno działającymi zapytaniami w systemie SQL Server. Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie pod kątem wydajności.

    Raporty z magazynu zapytań w programie SSMS są również wysoce zalecanym i cennym narzędziem do identyfikowania najbardziej kosztownych zapytań, nieoptymalnych planów wykonywania. Przejrzyj również szczegółowe informacje o wydajności zapytań.

    Jeśli zapytanie wykonuje tylko operacje SELECT, rozważ uruchomienie instrukcji w obszarze izolacji migawki, jeśli jest ona włączona w bazie danych, zwłaszcza jeśli funkcja RCSI została wyłączona. Tak jak w przypadku włączenia wersji RCSI zapytania odczytujące dane nie wymagają blokad udostępnionych (S) na poziomie izolacji migawki. Ponadto izolacja migawki zapewnia spójność na poziomie transakcji dla wszystkich instrukcji w jawnej transakcji z wieloma instrukcjami. Izolacja migawki może być już włączona w bazie danych. Izolacja migawki może być również używana z zapytaniami wykonującymi modyfikacje, ale należy obsługiwać konflikty aktualizacji.

    Jeśli masz długotrwałe zapytanie blokujące innych użytkowników i nie można go zoptymalizować, rozważ przeniesienie go ze środowiska OLTP do dedykowanego systemu raportowania, synchronicznej repliki bazy danych tylko do odczytu.

  2. Blokowanie spowodowane uśpionym identyfikatorem SPID, który ma niezatwierdzone transakcje

    Ten typ blokowania może być często identyfikowany przez identyfikator SPID, który znajduje się w stanie uśpienia lub oczekuje na polecenie, ale którego poziom zagnieżdżania transakcji (@@TRANCOUNTopen_transaction_count z sys.dm_exec_requests) jest większy niż zero. Może się tak zdarzyć, jeśli aplikacja doświadcza przekroczenia limitu czasu zapytania lub wystawia anulowanie bez wystawiania wymaganej liczby instrukcji ROLLBACK i/lub COMMIT. Gdy identyfikator SPID otrzymuje limit czasu zapytania lub anulowanie, przerywa bieżące zapytanie i partię, ale nie automatycznie cofa ani nie zatwierdza transakcji. Za to odpowiada aplikacja, ponieważ usługa Azure SQL Database nie może zakładać, że cała transakcja musi zostać wycofana z powodu anulowania pojedynczego zapytania. Przekroczenie czasu zapytania lub jego anulowanie jest wyświetlane jako zdarzenie sygnału ATTENTION dla identyfikatora SPID w sesji zdarzeń rozszerzonych.

    Aby zademonstrować niezatwierdzone jawne transakcje, wykonaj następujące zapytanie:

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

    Następnie wykonaj to zapytanie w tym samym oknie:

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

    Dane wyjściowe drugiego zapytania wskazują, że poziom zagnieżdżania transakcji wynosi jeden. Wszystkie blokady nabyte w transakcji są przechowywane do momentu zatwierdzenia lub wycofania transakcji. Jeśli aplikacje jawnie otwierają i zatwierdzają transakcje, błąd komunikacji lub inny może spowodować, że sesja i jej transakcja pozostaną w stanie otwartym.

    Użyj skryptu ze wcześniejszej części tego artykułu opartego na sys.dm_tran_active_transactions, aby zidentyfikować obecnie niezatwierdzone transakcje w całym wystąpieniu.

    Rozwiązania:

    • Ponadto ta klasa problemu z blokowaniem może być również problemem z wydajnością i wymagać wykonania tego problemu. Jeśli czas wykonywania zapytania może zostać zmniejszony, limit czasu zapytania lub anulowanie nie nastąpi. Ważne jest, aby aplikacja mogła obsłużyć scenariusze przekroczenia limitu czasu lub anulowania, jeśli wystąpią, ale możesz również skorzystać z badania wydajności zapytania.

    • Aplikacje muszą prawidłowo zarządzać poziomami zagnieżdżania transakcji lub mogą powodować problem z blokowaniem po anulowaniu zapytania w ten sposób. Rozważ:

      • W programie obsługi błędów aplikacji klienckiej wykonaj polecenie IF @@TRANCOUNT > 0 ROLLBACK TRAN po każdym błędzie, nawet jeśli aplikacja kliencka nie wierzy, że transakcja jest otwarta. Sprawdzanie otwartych transakcji jest wymagane, ponieważ procedura składowana wywoływana podczas partii mogła rozpocząć transakcję bez wiedzy aplikacji klienckiej. Niektóre warunki, takie jak anulowanie zapytania, uniemożliwiają wykonanie procedury po bieżącej instrukcji, więc nawet jeśli procedura ma logikę sprawdzania IF @@ERROR <> 0 i przerwania transakcji, ten kod wycofania nie jest wykonywany w takich przypadkach.
      • Jeśli grupowanie połączeń jest używane w aplikacji, która otwiera połączenie i wykonuje kilka zapytań przed zwolnieniem go z powrotem do puli, na przykład w aplikacji internetowej, tymczasowe wyłączenie grupowania połączeń może pomóc złagodzić problem, dopóki aplikacja kliencka nie będzie odpowiednio zmodyfikowana w celu obsługi błędów. Wyłączenie buforowania połączeń powoduje fizyczne rozłączenie połączenia z usługą Azure SQL Database, co powoduje wycofywanie wszystkich otwartych transakcji przez serwer.
      • Służy SET XACT_ABORT ON do nawiązywania połączenia lub w wszelkich procedurach składowanych, które rozpoczynają transakcje i nie są czyszczące po błędzie. W przypadku błędu czasu wykonywania to ustawienie przerywa wszystkie otwarte transakcje i zwraca kontrolę do klienta. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT.

    Uwaga

    Połączenie nie jest resetowane, dopóki nie zostanie ponownie wykorzystane z puli połączeń, więc możliwe jest, że użytkownik otworzy transakcję, a następnie zwolni połączenie do puli połączeń, ale może ono nie zostać ponownie wykorzystane przez kilka sekund, podczas których transakcja pozostanie otwarta. Jeśli połączenie nie zostanie ponownie użyte, transakcja zostanie przerwana po upłynął limit czasu połączenia i zostanie usunięta z puli połączeń. W związku z tym optymalne jest, aby aplikacja kliencka przerywała transakcje w programie obsługi błędów lub używała SET XACT_ABORT ON, aby uniknąć tych potencjalnych opóźnień.

    Uwaga

    Po SET XACT_ABORT ONinstrukcje T-SQL następujące po instrukcji, która powoduje błąd, nie są wykonywane. Może to mieć wpływ na zamierzony przepływ istniejącego kodu.

  3. Blokowanie spowodowane przez spiD, którego odpowiednia aplikacja kliencka nie pobrała wszystkich wierszy wyników do ukończenia

    Po wysłaniu zapytania do serwera wszystkie aplikacje muszą natychmiast pobrać wszystkie wiersze wyników w celu ukończenia. Jeśli aplikacja nie pobierze wszystkich wierszy wyników, blokady mogą pozostać w tabelach, blokując innych użytkowników. Jeśli używasz aplikacji, która w sposób jawny wysyła polecenia SQL do serwera, musi ona pobierać wszystkie wiersze wyniku. Jeśli tak nie jest (i jeśli nie można go skonfigurować), być może nie możesz rozwiązać problemu blokującego. Aby uniknąć tego problemu, można ograniczyć działanie niewłaściwie zachowujących się aplikacji do bazy danych raportowania lub wspomagania decyzji, oddzielonej od głównej bazy danych OLTP.

    Wpływ tego scenariusza jest mniejszy w przypadku włączenia migawki zatwierdzonego odczytu w bazie danych, co jest domyślną konfiguracją w usłudze Azure SQL Database. Dowiedz się więcej w sekcji Opis blokowania tego artykułu.

    Uwaga

    Zobacz wskazówki dotyczące logiki ponawiania prób dla aplikacji łączących się z usługą Azure SQL Database.

    Rozwiązanie: aplikacja musi zostać przepisana, aby pobrać wszystkie wiersze wyniku do ukończenia. Nie wyklucza to użycia funkcji OFFSET i FETCH w klauzuli ORDER BY zapytania do wykonywania stronicowania po stronie serwera.

  4. Blokowanie spowodowane przez sesję w stanie wycofania

    Zapytanie modyfikacji danych, które zostało zakończone poleceniem KILL lub anulowane poza transakcją zdefiniowaną przez użytkownika, jest cofane. Może to również wystąpić jako efekt uboczny rozłączenia sesji sieci klienta lub wybrania żądania jako ofiary zakleszczenia. Często można to zidentyfikować, obserwując dane wyjściowe sys.dm_exec_requestspolecenia , co może wskazywać na polecenie ROLLBACK, a kolumna percent_complete może pokazywać postęp.

    Dzięki przyspieszonemu odzyskiwaniu baz danych, które wprowadzono w 2019 roku, długie wycofywania powinny być rzadkie.

    Rozwiązanie: Poczekaj, aż spiD zakończy się wycofywanie wprowadzonych zmian.

    Aby uniknąć tej sytuacji, nie należy wykonywać dużych operacji zapisu wsadowego ani operacji tworzenia indeksu lub konserwacji w godzinach pracy w systemach OLTP. Jeśli to możliwe, wykonaj takie operacje w okresach niskiej aktywności.

  5. Blokowanie spowodowane przez oddzielone połączenie

    Jeśli aplikacja kliencka wychwyci błędy lub stacja robocza klienta zostanie uruchomiona ponownie, sesja sieciowa na serwerze może nie zostać natychmiast anulowana w pewnych warunkach. Z perspektywy usługi Azure SQL Database klient nadal wydaje się być obecny, a wszystkie uzyskane blokady mogą być nadal zachowywane. Aby uzyskać więcej informacji, zobacz Jak rozwiązywać problemy z połączeniami oddzielonych w programie SQL Server.

    Rozwiązanie: Jeśli aplikacja kliencka rozłączyła się bez odpowiedniego czyszczenia zasobów, możesz zakończyć spid przy użyciu KILL polecenia . Polecenie KILL przyjmuje wartość SPID jako dane wejściowe. Na przykład, aby zabić SPID 99, wydaj następujące polecenie:

    KILL 99

Powiązana zawartość