Udostępnij za pośrednictwem

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

  • Artykuł

Dotyczy:Azure SQL DatabaseSQL Database w Fabric

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

Cel

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 dynamicznych widokach 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 tradycyjnym znaczeniu. 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 a wieloma połączeniami z różnych aplikacji klienckich lub z różnych komputerów klienckich; wszystkie są traktowane jako atomowe operacje. 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.

Zrozum blokowanie

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 trzyma blokadę na określonym zasobie, a drugi SPID próbuje nałożyć konfliktowy typ blokady na ten sam zasób. 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 może swobodnie uzyskać własną blokadę na zasobie i 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 sesjami zapisywania danych jest zminimalizowane podczas użycia RCSI, który używa wersjonowania 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ą działać na poziomach izolacji, które zwiększają blokowanie. Poziomy izolacji można określić w ciągach połączenia aplikacji, podpowiedzi 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 migawkowej 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ść danych na poziomie transakcji i wykorzystuje wersje wierszy do wybierania wierszy do aktualizacji. Aby użyć izolacji migawki, zapytania lub połączenia muszą jawnie ustawić poziom izolacji transakcji na SNAPSHOT. 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 kontekście RCSI nie nabywają blokad współdzielonych (S) na dane odczytywane, a tym samym 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 utrzymuje blokady na zestawie zasobów przez dłuższy czas przed ich zwolnieniem. 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 elementów: blokowania identyfikatora transakcji (TID) (stosowanego również w innych funkcjach wersjonowania wierszy) oraz blokowania po zakwalifikowaniu (LAQ). Nie wymaga żadnej dodatkowej konfiguracji.

Ten artykuł dotyczy aktualnie zachowania silnika 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ą, gdy mamy do czynienia z problemem blokującym. 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. Zadanie polega na znalezieniu głównego blokującego i ustaleniu, co robi to zapytanie 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 zagłębmy się w to, jak zidentyfikować główną sesję blokującą przy użyciu odpowiedniego pozyskiwania 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 początku łańcucha blokowania oraz powiązanej 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 identyfikator SPID właściciela jest również blokowany? Możesz prześledzić łańcuch, aby znaleźć główny blokujący element, a następnie zbadać, dlaczego utrzymuje swoją 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źć ostatnie zapytanie przesłane 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 zapytania i filtrowania, zawierają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);

  • Odnieś się do kolumny blocking_session_id w sys.dm_exec_requests. Gdy blocking_session_id = 0, sesja nie jest blokowana. Podczas gdy sys.dm_exec_requests lista zawiera tylko żądania aktualnie wykonywane, a każde połączenie (aktywne lub nie) jest wymienione w sys.dm_exec_sessions. Oprzyj się na typowym połączeniu między sys.dm_exec_requests i sys.dm_exec_sessions w następnym zapytaniu.

  • Uruchom to przykładowe zapytanie, aby znaleźć aktywnie wykonywane zapytania i ich bieżący tekst wsadowy SQL lub tekst buforu wejściowego, 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 pole event_infosys.dm_exec_input_buffer zawiera to 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 do sys.dm_os_waiting_tasks, które znajduje się na poziomie 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 widok zarządzania dynamicznego 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 widokom zarządzania dynamicznego, przechowywanie wyników zapytań na przestrzeni czasu dostarcza danych, które umożliwiają przeglądanie blokad w określonym przedziale czasu w celu identyfikacji utrwalonych blokad lub trendów.

Zbieranie informacji ze 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 utrzymywane. Śledzenie umożliwia wyświetlanie wszystkich poleceń wykonywanych przez sesję w ramach bieżącej transakcji.

Istnieją dwa sposoby przechwytywania śladów w SQL Server: Zdarzenia Rozszerzone (XEvents) i Ślady 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 program SSMS udostępnia w Eksploratorze obiektów podfolder zdarzeń rozszerzonych pod każdą bazą danych. Użyj kreatora sesji zdarzeń rozszerzonych, aby przechwycić te przydatne zdarzenia:

  • Błędy kategorii:

    • Uwaga
    • Zgłoszony_błąd
    • Ostrzeżenie o wykonywaniu

  • Ostrzeżenia kategorii:

    • Brak_predykatu_połączenia

  • Realizacja kategorii

    • Rpc_zakończono
    • Rpc_starting
    • Pakiet_SQL_zakończony
    • Rozpoczęcie_baterii_SQL

  • Kategoria deadlock_monitor

    • raport zakleszczenia XML bazy danych

  • Sesja kategorii

    • Istniejące_połączenie
    • 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 węzły łańcuchów blokujących, 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 w poszukiwaniu informacji o identyfikatorach SPID na początku ł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 gotowości lub aktywny 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 aktualnie wykonywany. Zwykle oznacza to, że spiD oczekuje na polecenie z aplikacji.
    Działa SpiD jest obecnie uruchomiony w harmonogramie.
    Wykonywalny SpiD znajduje się w możliwej do uruchomienia kolejce harmonogramu i czeka na uzyskanie czasu harmonogramu.
    Zawieszony SPID oczekuje 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 trzymać blokady nabyte przez dowolne polecenie w ramach tej 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 typów oczekiwania, zapoznaj się z 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ż wartość wait_time z poprzedniego zapytania, oznacza to, że poprzednia blokada została założona i zwolniona, a teraz czeka na nową blokadę (przy założeniu, że wait_time jest różny od zera). 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
    Stół ID bazy danych:ID obiektu:ID indeksu 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 odtworzenie skrótu klucza indeksu do określonej 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 połączonych z innymi widokami 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 u pewnych klientów (nazwa hosta), w określonych bibliotekach sieciowych (net_library), kiedy ostatnia partia przesłana przez SPID była last_request_start_time w sys.dm_exec_sessions, jak długo żądanie było uruchomione w start_time programie sys.dm_exec_requests i tak dalej.

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 NIE NULL >= 0 wykonywalny Tak, po zakończeniu zapytania. W kolumnach sys.dm_exec_sessions, reads, cpu_time i/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ł ostrzegawczy, wskazujący, że doszło do przekroczenia limitu czasu zapytania lub jego anulowania.
3 NULL >= 0 uruchamialny Nie. 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.
4 Różne >= 0 wykonywalny Nr. 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 cofnięcie 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żący czas.

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. Tak naprawdę ta klasa problemów blokujących może być po prostu problemem z wydajnością i wymaga, abyś traktował ją w ten sposób. 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 pod izolacją migawki, jeśli jest ona włączona w twojej bazie danych, zwłaszcza jeśli funkcja RCSI została wyłączona. Po włączeniu wersji RCSI, zapytania odczytujące dane nie wymagają blokad udostępnionych (S) przy poziomie izolacji migawkowej. 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 niezatwierdzoną transakcję

    Ten typ blokowania można często zidentyfikować przez identyfikator SPID, który jest w stanie uśpienia lub oczekuje na polecenie, i 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 ma do czynienia z przekroczeniem limitu czasu zapytania lub wykonuje anulowanie bez wydania wymaganej liczby instrukcji ROLLBACK i/lub COMMIT. Gdy identyfikator SPID otrzymuje przekroczenie limitu czasu zapytania lub anulowanie, przerywa bieżące zapytanie i partię, jednak 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ą nadal przetrzymywane aż 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ć od ciebie, aby rozwiązać go jako taki. Jeśli czas wykonywania zapytania da się skrócić, nie wystąpi limit czasu zapytania ani jego anulowanie. 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 w ten sposób zapytania. 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.
      • Użyj SET XACT_ABORT ON do nawiązywania połączenia lub w dowolnych procedurach składowanych, które rozpoczynają transakcje i nie czyszczą 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 końca

    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, gdy w bazie danych włączona jest migawka zatwierdzonego odczytu, 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 tak, aby pobrać wszystkie wiersze wyniku w całości. 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ę znajdującą się 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 gdy żądanie zostanie wybrane jako ofiara zakleszczenia. Często można to zidentyfikować, obserwując dane wyjściowe sys.dm_exec_requests, 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 wycofa się ze zmian, które zostały wprowadzone.

    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 osierocone 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 wszelkie nabyte blokady mogą być nadal zachowywane. Aby uzyskać więcej informacji, zobacz Jak rozwiązywać problemy z osieroconymi połączeniami w SQL Serverze.

    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ść