Omówienie i rozwiązywanie problemów z blokowaniem usługi Azure SQL Database

  • Artykuł

Dotyczy:Azure SQL Database

Cel

W tym artykule opisano blokowanie w bazach danych Azure SQL Database i pokazano, jak rozwiązywać problemy z blokowaniem i rozwiązywać problemy z blokowaniem.

W tym artykule termin połączenie odnosi się do jednej zalogowanej sesji bazy danych. Każde połączenie jest wyświetlane jako identyfikator sesji (SPID) lub session_id w wielu widokach 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 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.

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.

Omówienie blokowania

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 SPID blokuje zasób jest mały. Gdy sesja będąca właścicielem zwalnia blokadę, drugie połączenie jest następnie bezpłatne, aby uzyskać własną blokadę zasobu i kontynuować przetwarzanie. Jest to normalne zachowanie 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 parametrach połączenia aplikacji, wskazówkach dotyczących zapytań lub instrukcjach 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 zwróci wartość 1. Jeśli izolacja migawki jest włączona, kolumna snapshot_isolation_state_desc zwróci wartość WŁĄCZONE.

Czas trwania i kontekst transakcji zapytania określają, jak długo są przechowywane jego blokady, a tym samym ich wpływ na inne zapytania. Instrukcje SELECT uruchamiane w obszarze RCSI nie uzyskują udostępnionych blokad na odczytywanych danych i dlatego nie blokują transakcji modyfikujących dane. W przypadku instrukcji INSERT, UPDATE i DELETE blokady są przechowywane w zapytaniu, zarówno dla spójności danych, jak i w celu 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 blokady i poziomów izolacji transakcji można znaleźć w następujących artykułach:

W przypadku blokowania i blokowania występuje w punkcie, w którym występuje szkodliwy wpływ na wydajność systemu, wynika to z jednego z następujących powodów:

  • SpiD przechowuje blokady na zestawie zasobów przez dłuższy czas przed ich zwolnieniem. Ten typ blokowania rozwiązuje się w czasie, ale może spowodować obniżenie wydajności.

  • SpiD przechowuje blokady na zestawie zasobów i nigdy ich nie zwalnia. Ten typ blokowania nie rozwiązuje problemu 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 SPID powodują zablokowanie różnych zasobów w czasie, tworząc ruch docelowy. Te sytuacje są trudne do rozwiązania problemów przy użyciu programu 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

Może wystąpić tendencja do skupienia się na dostrajaniu po stronie serwera i problemach z platformą w przypadku wystąpienia problemu blokującego. Jednak uwaga zwracana tylko do bazy 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 tego, jaki poziom widoczności aplikacja uwidacznia wywołania bazy danych, problem blokujący jednak często wymaga zarówno inspekcji dokładnych instrukcji SQL przesłanych przez aplikację, jak i dokładnego 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, anulowanie zapytań, przekroczenie limitu czasu zapytania, pobieranie 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 podczas fazy projektowania i budowy bazy danych i aplikacji. W szczególności należy ocenić użycie zasobów, poziom izolacji i długość ścieżki transakcji dla każdego zapytania. Każde zapytanie i transakcja powinny być tak lekkie, jak to możliwe. Należy wykonać dobrą dyscyplinę zarządzania połączeniami, bez niej, 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 związane z usługami Azure SQL Database i Azure SQL Managed Instance oraz Obsługa błędów przejściowych.

Rozwiązywanie problemów z blokowaniem

Niezależnie od tego, w jakiej sytuacji blokujemy, metodologia rozwiązywania problemów z blokowaniem jest taka sama. Te separacje logiczne będą dyktować resztę kompozycji tego artykułu. Koncepcja polega na znalezieniu bloku głównego i zidentyfikowaniu tego, 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 przyczyny możemy wprowadzić zmiany, przeprojektując zapytanie i transakcję.

Kroki rozwiązywania problemów:

  1. Identyfikowanie głównej sesji blokującej (blokada głowy)

  2. Znajdź zapytanie i transakcję powodującą blokowanie (co przechowuje blokady przez dłuższy czas)

  3. Analizowanie/zrozumienie, dlaczego występuje długotrwałe blokowanie

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

Teraz przyjrzyjmy się, jak wskazać główną sesję blokującą przy użyciu odpowiedniego przechwytywania danych.

Zbieranie informacji blokujących

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 dynamicznych obiektów zarządzania (DMO) i przechowywanie wyników w celu 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 (DMFS). 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 zidentyfikowanie identyfikatora SPID (identyfikatora sesji) na czele łańcucha blokowania i instrukcji SQL. Poszukaj identyfikatorów SPID ofiary, które są blokowane. Jeśli jakikolwiek SPID jest blokowany przez inny SPID, zbadaj SPID będący właścicielem zasobu (blokujący SPID). Czy ten właściciel SPID jest również blokowany? Możesz przejść przez łańcuch, aby znaleźć blokadę głowy, 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. Widok 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 Znajdziesz ją w rdzeniu innych zapytań.

  • Jeśli masz już zidentyfikowaną określoną sesję, możesz użyć DBCC INPUTBUFFER(<session_id>) polecenia , aby znaleźć ostatnią instrukcję przesłaną przez sesję. Podobne wyniki można zwrócić za pomocą funkcji dynamicznego sys.dm_exec_input_buffer zarządzania (DMF) w zestawie wyników, który jest łatwiejszy do wykonywania zapytań 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. Podczas gdy sys.dm_exec_requests lista zawiera tylko żądania aktualnie wykonywane, wszystkie połączenia (aktywne lub nie) będą wyświetlane na liście .sys.dm_exec_sessions Na podstawie tego wspólnego sprzężenia 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 sys.dm_exec_sql_text lub sys.dm_exec_input_buffer widoków DMV. Jeśli dane zwrócone przez text pole ma sys.dm_exec_sql_text wartość NULL, zapytanie nie jest obecnie wykonywane. W takim przypadku event_info pole elementu sys.dm_exec_input_buffer będzie zawierać 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ę zablokowanych session_ids na 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 bardziej rozbudowane przykładowe zapytanie dostarczone przez pomoc techniczną firmy Microsoft, aby zidentyfikować szefa łańcucha blokowania wielu sesji, w tym tekst zapytania sesji zaangażowanych w łańcuch 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ć 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 widoku 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 sprzężenie WEWNĘTRZNE w systemie sys.dm_os_waiting_tasksnastępujące zapytanie ogranicza dane wyjściowe tylko sys.dm_tran_locks do aktualnie 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>';
  • W przypadku widoków DMV przechowywanie wyników zapytania w czasie zapewni punkty danych, które umożliwią przeglądanie blokowania w określonym przedziale czasu w celu zidentyfikowania utrwalonego blokowania lub trendów.

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, będzie 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 przetrzymywane. Śledzenie umożliwi wyświetlanie wszystkich poleceń wykonywanych przez sesję w ramach bieżącej transakcji.

Istnieją dwa sposoby przechwytywania śladów w programie 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 bardziej wszechstronność i mniejszy wpływ na obserwowany system, a jego interfejs jest zintegrowany 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 Eksploratorze obiektów. Użyj kreatora sesji zdarzeń rozszerzonych, aby przechwycić te przydatne zdarzenia:

  • Błędy kategorii:

    • Uwagę
    • Error_reported
    • Execution_warning

  • Ostrzeżenia kategorii:

    • Missing_join_predicate

  • Wykonywanie kategorii:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Deadlock_monitor kategorii

    • database_xml_deadlock_report

  • Sesja kategorii

    • Existing_connection
    • Logowanie
    • Wyloguj

Uwaga

Aby uzyskać szczegółowe informacje na temat zakleszczeń, zobacz Analizowanie i zapobieganie zakleszczeniom w usłudze Azure 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. Pozostała część tego artykułu to omówienie sposobu używania tych informacji do identyfikowania i rozwiązywania typowych scenariuszy blokowania. W tej dyskusji założono, że użyto skryptów blokujących (przywoływanych wcześniej) do przechwytywania informacji na temat blokujących identyfikatorów SPID i przechwycenia aktywności aplikacji przy użyciu sesji XEvent.

Analizowanie danych blokujących

  • Zbadaj dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions określ głowice łańcuchów blokujących, używając elementów blocking_these i session_id. Najjjaśniej określi, które żądania są blokowane i które blokują. Przyjrzyj się kolejnym sesjom, które są blokowane i blokowane. Czy istnieje wspólny lub główny łańcuch blokowania? Prawdopodobnie współużytkują wspólną tabelę, a co najmniej jedna sesja zaangażowana w łańcuch blokowania wykonuje operację zapisu.

  • Sprawdź dane wyjściowe dynamicznych widoków sys.dm_exec_requests zarządzania i sys.dm_exec_sessions , aby uzyskać informacje na temat identyfikatorów SPID na czele łańcucha blokującego. Wyszukaj następujące pola:

    • sys.dm_exec_requests.status
      Ta kolumna zawiera stan określonego żądania. Zazwyczaj stan uśpienia wskazuje, że SPID zakończył wykonywanie i oczekuje na przesłanie przez aplikację innego zapytania lub partii. Stan uruchamiania lub uruchamiania wskazuje, że identyfikator SPID obecnie przetwarza zapytanie. W poniższej tabeli przedstawiono 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.
    Śpiąca SpiD nie jest obecnie wykonywany. Zwykle oznacza to, że spiD oczekuje na polecenie z aplikacji.
    Uruchomiono SpiD jest obecnie uruchomiony w harmonogramie.
    Sprawne SpiD znajduje się w możliwej do uruchomienia kolejce harmonogramu i czeka na uzyskanie czasu harmonogramu.
    Zawieszona 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_timei last_wait_type
      Jeśli wartość sys.dm_exec_requests.wait_type ma wartość NULL, żądanie nie oczekuje obecnie na żadne elementy, a last_wait_type wartość wskazuje ostatnią wait_type napotkaną żądaną wartość. Aby uzyskać więcej informacji o sys.dm_os_wait_stats typach oczekiwania i opis najbardziej typowych typów oczekiwania, zobacz sys.dm_os_wait_stats. Wartość wait_time może służyć do określenia, czy żądanie postępuje. 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 niezerowa wait_time). Można to zweryfikować, porównując wait_resource między danymi wyjściowymi sys.dm_exec_requests , który wyświetla zasób, dla którego żądanie oczekuje.

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

    Zasób Format Przykład Objaśnienie
    Tabela 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 STRONA: 5:1:104 W takim przypadku identyfikator bazy danych 5 to puby, 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 klucza indeksu) KLUCZ: 5:72057594044284928 (3300a4f361aa) W takim przypadku identyfikator bazy danych 5 to Pubs, Hobt_ID 72057594044284928 odpowiada index_id 2 dla object_id 261575970 (tabela tytułów). sys.partitions Użyj widoku wykazu, aby skojarzyć hobt_id z określonym index_id elementem i object_id. Nie można usunąć skrótu klucza indeksu z określoną wartością klucza.
    Wiersz DatabaseID:FileID:PageID:Slot(wiersz) RID: 5:1:104:3 W takim przypadku identyfikator bazy danych 5 to puby, 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.
    Kompiluj DatabaseID:FileID:PageID:Slot(wiersz) RID: 5:1:104:3 W takim przypadku identyfikator bazy danych 5 to puby, 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_transactions Widok DMV sys.dm_tran_active_transactions zawiera dane dotyczące otwartych transakcji, które można połączyć z innymi widokami DMV w celu uzyskania pełnego obrazu transakcji oczekujących na zatwierdzenie lub wycofanie. Użyj następującego zapytania, aby zwrócić informacje dotyczące otwartych transakcji, dołączone do innych widoków ZARZĄDZANIA, w tym sys.dm_tran_session_transactions. Rozważ bieżący stan transakcji, i inne dane sytuacyjne, aby ocenić, transaction_begin_timeczy 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 główny problem. Ich użyteczność 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 przedstawia typowe objawy ich prawdopodobnych przyczyn.

Kolumny Waittype, Open_Tran i Status odwołują się do informacji zwróconych przez sys.dm_exec_request, inne kolumny mogą być zwracane przez sys.dm_exec_sessions. Kolumna "Resolves?" wskazuje, czy blokowanie zostanie rozpoznane samodzielnie, czy sesja powinna zostać zabita za pomocą KILL polecenia . Aby uzyskać więcej informacji, zobacz KILL (Transact-SQL).

Scenariusz Typ oczekiwania Open_Tran Stan Rozwiązuje? Inne objawy
1 NOT NULL >= 0 Sprawne Tak, po zakończeniu zapytania. W sys.dm_exec_sessionskolumnach , readscpu_timei/lub memory_usage z czasem wzrośnie. Czas trwania zapytania będzie wysoki po zakończeniu.
2 NULL >0 Śpiąca Nie, ale SPID można zabić. 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 Sprawne Nie. Nie rozwiąże problemu, dopóki klient nie pobierze wszystkich wierszy ani nie zamknie połączenia. SpiD może zostać zabity, ale może upłynąć do 30 sekund. Jeśli open_transaction_count = 0, a SPID przechowuje blokady, podczas gdy poziom izolacji transakcji jest domyślny (READ COMMMITTED), jest to prawdopodobna przyczyna.
4 Różne wartości >= 0 Sprawne Nie. Nie rozwiąże problemu, dopóki klient anuluje zapytania lub zamyka połączenia. Można zabić SPID, ale może potrwać do 30 sekund. Kolumna hostname w sys.dm_exec_sessions kolumnie DLA SPID na czele łańcucha blokującego będzie taka sama jak jedna z SPID, która blokuje.
5 NULL >0 Wycofywanie Tak. W sesji zdarzeń rozszerzonych dla tego identyfikatora SPID może być widoczny sygnał uwagi wskazujący przekroczenie limitu czasu zapytania lub anulowanie, lub po prostu wydano instrukcję wycofywania.
6 NULL >0 Śpiąca Ostatecznie. Gdy system Windows NT ustali, że sesja nie jest już aktywna, połączenie usługi Azure SQL Database zostanie przerwane. Wartość w sys.dm_exec_sessions elemencie last_request_start_time jest znacznie wcześniejsza niż bieżąca godzina.

Szczegółowe scenariusze blokowania

  1. Blokowanie spowodowane przez zwykle uruchomione zapytanie z długim czasem wykonywania

    Rozwiązanie: Rozwiązanie tego typu problemu z blokowaniem polega na wyszukiwaniu sposobów optymalizacji zapytania. W rzeczywistości ta klasa problemu z blokowaniem może być po prostu problemem z wydajnością i wymaga wykonania go jako takiego. Aby uzyskać informacje na temat rozwiązywania problemów z konkretnym wolno działającym zapytaniem, zobacz Jak rozwiązywać problemy z wolno działającymi zapytaniami w programie SQL Server. Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie 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. Zapoznaj się również z sekcją Inteligentnej wydajności w witrynie Azure Portal dla bazy danych Azure SQL Database, w tym 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 funkcji 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 przez spany SPID, który ma niezatwierdzonej transakcji

    Ten typ blokowania może być często identyfikowany przez SPID, który śpi lub oczekuje na polecenie, ale którego poziom zagnieżdżania transakcji (@@TRANCOUNT, open_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 spiD otrzyma limit czasu zapytania lub anulowanie, zakończy bieżące zapytanie i partię, ale nie automatycznie wycofa ani nie zatwierdzi transakcji. Aplikacja jest odpowiedzialna za to, ponieważ usługa Azure SQL Database nie może zakładać, że cała transakcja musi zostać wycofana z powodu anulowania pojedynczego zapytania. Limit czasu lub anulowanie zapytania będzie wyświetlane jako zdarzenie sygnału UWAGI dla identyfikatora SPID w sesji zdarzenia rozszerzonego.

    Aby zademonstrować niezatwierdzonej jawnej transakcji, 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 jest jeden. Wszystkie blokady uzyskane w transakcji są nadal przechowywane do momentu zatwierdzenia lub wycofania transakcji. Jeśli aplikacje jawnie otwierają i zatwierdzają transakcje, komunikacja lub inny błąd może pozostawić sesję i jej transakcję w stanie otwartym.

    Użyj skryptu wcześniej w tym artykule na podstawie, sys.dm_tran_active_transactions aby zidentyfikować aktualnie niezatwierdzone transakcje w 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ż następujące źródła:

      • W procedurze obsługi błędów aplikacji klienckiej wykonaj następujące IF @@TRANCOUNT > 0 ROLLBACK TRAN czynności, nawet jeśli aplikacja kliencka nie uważa, ż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 w przeszłości bieżącej instrukcji, więc nawet jeśli procedura ma logikę sprawdzania IF @@ERROR <> 0 i przerwania transakcji, ten kod wycofywania nie zostanie wykonany w takich przypadkach.
      • Jeśli buforowanie połączeń jest używane w aplikacji, która otwiera połączenie i uruchamia niewielką liczbę zapytań przed zwolnieniem połączenia z powrotem do puli, takich jak aplikacja internetowa, tymczasowe wyłączenie puli połączeń może pomóc złagodzić problem, dopóki aplikacja kliencka nie zostanie odpowiednio zmodyfikowana w celu obsługi błędów. Wyłączenie buforowania połączeń spowoduje fizyczne rozłączenie połączenia z usługą Azure SQL Database, co spowoduje 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 spowoduje przerwanie wszystkich otwartych transakcji i zwrócenie kontroli do klienta. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).

    Uwaga

    Połączenie nie jest resetowane, dopóki nie zostanie ponownie użyte z puli połączeń, więc istnieje możliwość, że użytkownik może otworzyć transakcję, a następnie zwolnić połączenie z pulą połączeń, ale może nie zostać ponownie użyte przez kilka sekund, w tym czasie 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 przerwanie transakcji w programie obsługi błędów przez aplikację kliencją lub użycie jej SET XACT_ABORT ON w celu uniknięcia tego potencjalnego opóźnienia.

    Uwaga

    Po SET XACT_ABORT ONinstrukcjach języka T-SQL po instrukcji, która powoduje, że błąd nie zostanie wykonany. 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 pobiera wszystkich wierszy wyników, blokady można pozostawić w tabelach, blokując innych użytkowników. Jeśli używasz aplikacji, która w sposób niewidoczny przesyła instrukcje SQL do serwera, aplikacja musi pobrać wszystkie wiersze wyników. Jeśli nie (i jeśli nie można go skonfigurować do tego celu), być może nie możesz rozwiązać problemu blokującego. Aby uniknąć tego problemu, możesz ograniczyć nieprawidłowo zachowujące się aplikacje do bazy danych raportowania lub wspierania decyzji, oddzielnej 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 wycofywania

    Zapytanie modyfikacji danych, które jest KILLed lub anulowane poza transakcją zdefiniowaną przez użytkownika, zostanie wycofane. 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_requestspolecenia , co może wskazywać na polecenie ROLLBACK, a kolumna percent_complete może pokazywać postęp.

    Dzięki funkcji przyspieszonego odzyskiwania bazy danych wprowadzonej w 2019 r. długie wycofywanie powinno być rzadkie.

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

    Aby uniknąć takiej sytuacji, nie wykonuj dużych operacji zapisu wsadowego ani tworzenia indeksu ani operacji 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 bazy danych Azure SQL 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

Zobacz także

Następne kroki