Share via

Vysvětlení a řešení problémů s blokováním služby Azure SQL Database

  • Článek

Platí pro:Azure SQL Database

Cíl

Tento článek popisuje blokování v databázích Azure SQL a ukazuje, jak řešit potíže a řešit blokování.

V tomto článku termín připojení odkazuje na jednu přihlášenou relaci databáze. Každé připojení se zobrazí jako ID relace (SPID) nebo session_id v mnoha zobrazeních dynamické správy. Každý z těchto identifikátorů SPID se často označuje jako proces, i když se nejedná o samostatný kontext procesu v obvyklém smyslu. Místo toho se každý identifikátor SPID skládá z prostředků serveru a datových struktur nezbytných ke službě požadavků jednoho připojení z daného klienta. Jedna klientská aplikace může mít jedno nebo více připojení. Z pohledu služby Azure SQL Database neexistuje žádný rozdíl mezi více připojeními z jedné klientské aplikace na jednom klientském počítači a několika připojeními z více klientských aplikací nebo více klientských počítačů; jsou atomické. Jedno připojení může blokovat jiné připojení bez ohledu na zdrojového klienta.

Informace o řešení potíží s zablokováním najdete v tématu Analýza a zabránění zablokování ve službě Azure SQL Database.

Poznámka:

Tento obsah se zaměřuje na Azure SQL Database. Azure SQL Database je založená na nejnovější stabilní verzi databázového stroje Microsoft SQL Serveru, takže většina obsahu je podobná, i když se možnosti řešení potíží a nástroje můžou lišit. Další informace o blokování sql Serveru najdete v tématu Vysvětlení a řešení problémů s blokováním SQL Serveru.

Vysvětlení blokování

Blokování je nevyhnutelná a účelová vlastnost všech relačních databázových systémů (RDBMS) se souběžností založenou na zámcích. Blokování v databázi ve službě Azure SQL Database nastane, když jedna relace obsahuje zámek konkrétního prostředku a druhý pokus o získání konfliktního typu zámku ve stejném prostředku. Obvykle je časový rámec, pro který první SPID uzamkne prostředek malý. Když vlastnící relace uvolní zámek, druhé připojení pak může získat vlastní zámek prostředku a pokračovat ve zpracování. Jedná se o normální chování a může k tomu dojít mnohokrát v průběhu dne bez znatelného účinku na výkon systému.

Každá nová databáze ve službě Azure SQL Database má ve výchozím nastavení povolené nastavení databáze potvrzeného čtení (RCSI). Blokování mezi relacemi při čtení dat a zápisu relací se minimalizuje v rámci RCSI, které ke zvýšení souběžnosti využívá správu verzí řádků. Blokování a vzájemné zablokování ale může v databázích ve službě Azure SQL Database stále nastat, protože:

  • Dotazy, které upravují data, můžou navzájem blokovat.
  • Dotazy můžou běžet pod úrovněmi izolace, které zvyšují blokování. Úrovně izolace mohou být zadány v připojovacích řetězcích aplikace, nápovědách dotazů nebo příkazech SET v jazyce Transact-SQL.
  • Analýza RCSI může být zakázaná, což způsobí, že databáze používá sdílené zámky (S) k ochraně příkazů SELECT spuštěných pod úrovní izolace potvrzené čtením. To může zvýšit blokování a zablokování.

Úroveň izolace snímků je ve výchozím nastavení povolená také pro nové databáze ve službě Azure SQL Database. Izolace snímku je další úroveň izolace založená na řádcích, která poskytuje konzistenci na úrovni transakcí pro data a která používá verze řádků k výběru řádků k aktualizaci řádků. Chcete-li použít izolaci snímků, dotazy nebo připojení musí explicitně nastavit jejich úroveň izolace transakce na SNAPSHOT. To se dá provést jenom v případě, že je pro databázi povolená izolace snímků.

Pomocí jazyka Transact-SQL můžete zjistit, jestli je povolená izolace RCSI nebo snímku. Připojte se k databázi ve službě Azure SQL Database a spusťte následující dotaz:

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

Pokud je povolená analýza RCSI, is_read_committed_snapshot_on vrátí sloupec hodnotu 1. Pokud je povolená izolace snímků, snapshot_isolation_state_desc sloupec vrátí hodnotu ZAPNUTO.

Kontext doby trvání a transakce dotazu určují, jak dlouho jsou zámky uloženy, a tím jejich vliv na jiné dotazy. Příkazy SELECT spuštěné v rámci RCSI nezískávají sdílené zámky (S) na přečtených datech, a proto neblokují transakce, které upravují data. V případě příkazů INSERT, UPDATE a DELETE se zámky uchovávají během dotazu, a to jak pro konzistenci dat, tak pro umožnění vrácení dotazu zpět v případě potřeby.

U dotazů spuštěných v rámci explicitní transakce je typ zámků a doby trvání, pro které jsou zámky uloženy, určeny typem dotazu, úrovní izolace transakce a zda jsou v dotazu použity pokyny k uzamčení. Popis uzamčení, tipů k uzamčení a úrovní izolace transakcí najdete v následujících článcích:

Při zamykání a blokování přetrvává v místě, kde je škodlivý účinek na výkon systému, je to z jednoho z následujících důvodů:

  • IDENTIFIKÁTOR SPID uchovává zámky na sadě prostředků po delší dobu před jejich vydáním. Tento typ blokování se v průběhu času vyřeší, ale může způsobit snížení výkonu.

  • SPID uchovává zámky na sadě prostředků a nikdy je nevyvolá. Tento typ blokování se nevyřešil a zabraňuje neomezenému přístupu k ovlivněným prostředkům.

V prvním scénáři může být situace velmi proměnlivá, protože různá identifikátory SPID způsobují blokování různých prostředků v průběhu času a vytváří se pohyblivý cíl. Tyto situace je obtížné vyřešit pomocí aplikace SQL Server Management Studio , aby se problém zúžil na jednotlivé dotazy. Druhá situace naopak vede k konzistentnímu stavu, který může být snazší diagnostikovat.

Optimalizované uzamčení

Optimalizované zamykání je nová funkce databázového stroje výrazně snižuje paměť zámků a počet zámků současně vyžadovaných pro zápisy. Optimalizované uzamykání používá dvě primární komponenty: uzamykání ID transakce (TID) (používá se také v jiných funkcích správy verzí řádků) a zámek po kvalifikaci (LAQ). Nevyžaduje žádnou další konfiguraci.

Tento článek se aktuálně týká chování databázového stroje bez optimalizovaného uzamčení.

Další informace a informace o tom, kde je k dispozici optimalizované uzamčení, najdete v tématu Optimalizované uzamčení.

Aplikace a blokování

Při blokování problému může dojít k tomu, že se můžete zaměřit na problémy s laděním na straně serveru a platformy. Pozornost, která byla věnována pouze databázi, však nemusí vést k řešení a může lépe absorbovat čas a energii zaměřenou na zkoumání klientské aplikace a dotazů, které odesílá. Bez ohledu na to, jakou úroveň viditelnosti aplikace zveřejňuje ohledně provedených volání databáze, blokující problém nicméně často vyžaduje kontrolu přesných příkazů SQL odeslaných aplikací a přesné chování aplikace týkající se zrušení dotazu, správy připojení, načtení všech řádků výsledků atd. Pokud vývojový nástroj nepovoluje explicitní kontrolu nad správou připojení, zrušením dotazu, vypršením časového limitu dotazu, načítáním výsledků atd., blokující problémy nemusí být možné vyřešit. Tento potenciál byste měli pečlivě prozkoumat před výběrem nástroje pro vývoj aplikací pro Azure SQL Database, zejména pro prostředí OLTP citlivá na výkon.

Věnujte pozornost výkonu databáze během fáze návrhu a výstavby databáze a aplikace. Konkrétně by se měla pro každý dotaz vyhodnotit spotřeba prostředků, úroveň izolace a délka cesty transakce. Každý dotaz a transakce by měly být co nejlehčí. Musí být uplatněna dobrá disciplína správy připojení, bez ní se může zdát, že aplikace má přijatelný výkon při nízkém počtu uživatelů, ale výkon se může výrazně snížit, protože počet uživatelů se škáluje nahoru.

Díky správnému návrhu aplikací a dotazů dokáže Azure SQL Database podporovat mnoho tisíc souběžných uživatelů na jednom serveru s malým blokováním.

Poznámka:

Další pokyny k vývoji aplikací najdete v tématu Řešení potíží s připojením a dalších chyb ve službě Azure SQL Database a spravované instanci Azure SQL a zpracování přechodných chyb.

Řešení potíží s blokováním

Bez ohledu na to, ve které situaci blokování se nacházíme, je metodologie pro řešení potíží s uzamykáním stejná. Tyto logické oddělení určují zbytek složení tohoto článku. Koncept spočívá v nalezení hlavního blokátoru a identifikaci toho, co tento dotaz dělá, a proč blokuje. Jakmile je problematický dotaz identifikován (to znamená, co drží zámky po delší dobu), dalším krokem je analýza a určení, proč blokování probíhá. Jakmile pochopíme, proč můžeme provést změny tím, že přepracujeme dotaz a transakci.

Postup při řešení potíží:

  1. Identifikace hlavní blokující relace (hlavní blokování)

  2. Vyhledejte dotaz a transakci, která způsobuje blokování (co drží zámky po delší dobu).

  3. Analýza/vysvětlení, proč dochází k dlouhodobému blokování

  4. Řešení problému s blokováním přepracováním dotazu a transakce

Teď se podíváme na to, jak určit hlavní blokovací relaci pomocí vhodného zachytávání dat.

Shromáždění informací o blokování

Aby se zabránilo potížím při řešení problémů s blokováním, může správce databáze používat skripty SQL, které neustále monitorují stav uzamčení a blokování v databázi ve službě Azure SQL Database. Pro shromáždění těchto dat existují v podstatě dvě metody.

Prvním je dotazování objektů dynamické správy (DMO) a uložení výsledků pro porovnání v průběhu času. Některé objekty odkazované v tomto článku jsou zobrazení dynamické správy (DMV) a některé jsou funkce dynamické správy (DMF). Druhou metodou je použití XEvents k zachycení toho, co se provádí.

Shromáždění informací z zobrazení dynamické správy

Odkazování na zobrazení dynamické správy k řešení potíží má za cíl identifikovat IDENTIFIKÁTOR SPID (ID relace) v čele blokujícího řetězce a příkazu SQL. Vyhledejte identifikátory SPID obětí, které jsou blokované. Pokud je nějaký identifikátor SPID blokovaný jiným identifikátorem SPID, prozkoumejte IDENTIFIKÁTOR SPID, který vlastní prostředek (blokující IDENTIFIKÁTOR SPID). Je toto id spid vlastníka také blokované? Řetěz můžete projít a najít hlavový blok a pak zjistit, proč si udržuje zámek.

Nezapomeňte spustit každý z těchto skriptů v cílové databázi ve službě Azure SQL Database.

  • Příkazy sp_who a sp_who2 jsou starší příkazy, které zobrazují všechny aktuální relace. Zobrazení dynamické správy sys.dm_exec_sessions vrací více dat v sadě výsledků, která se snadněji dotazuje a filtruje. Najdete sys.dm_exec_sessions v jádru jiných dotazů.

  • Pokud už máte určitou relaci identifikovanou, můžete použít DBCC INPUTBUFFER(<session_id>) k vyhledání posledního příkazu odeslaného relací. Podobné výsledky je možné vrátit pomocí sys.dm_exec_input_buffer funkce dynamické správy (DMF) v sadě výsledků, která usnadňuje dotazování a filtrování a poskytuje session_id a request_id. Pokud chcete například vrátit nejnovější dotaz odeslaný session_id 66 a request_id 0:

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

  • Odkaz na blocking_session_id sloupec v .sys.dm_exec_requests Pokud blocking_session_id = 0, relace není blokována. I když sys.dm_exec_requests jsou uvedené pouze požadavky, které jsou aktuálně spuštěny, budou v seznamu uvedené sys.dm_exec_sessionsjakékoli připojení (aktivní nebo ne). Na základě tohoto společného spojení mezi sys.dm_exec_requests a sys.dm_exec_sessions v dalším dotazu.

  • Spuštěním tohoto ukázkového dotazu vyhledejte aktivně spouštěné dotazy a aktuální text dávky SQL nebo vstupní text vyrovnávací paměti pomocí sys.dm_exec_sql_text nebo sys.dm_exec_input_buffer zobrazení dynamické správy. Pokud jsou data vrácená polem textsys.dm_exec_sql_text NULL, dotaz se aktuálně nespouštějí. V takovém případě event_info pole sys.dm_exec_input_buffer bude obsahovat poslední řetězec příkazu předaný modulu SQL. Tento dotaz lze také použít k identifikaci relací blokujících jiné relace, včetně seznamu session_ids blokovaných 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;
  • Spusťte tento složitější ukázkový dotaz, který poskytuje podpora Microsoftu, a identifikujte tak vedoucího řetězu blokování relací, včetně textu dotazu relací zahrnutých do blokujícího řetězce.
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];
  • Referenční sys.dm_os_waiting_tasks , která je ve vrstvě vlákna nebo úlohy SQL. Tím se vrátí informace o typu čekání SQL, u jakého typu požadavku aktuálně dochází. Podobně jako sys.dm_exec_requestsaktivní požadavky jsou vráceny sys.dm_os_waiting_taskspouze aktivními požadavky .

Poznámka:

Další informace o typech čekání, včetně agregovaných statistik čekání v průběhu času, najdete v sys.dm_db_wait_stats zobrazení dynamické správy. Toto zobrazení dynamické správy vrací agregované statistiky čekání pouze pro aktuální databázi.

  • Pomocí zobrazení dynamické správy sys.dm_tran_locks potřebujete podrobnější informace o tom, jaké zámky byly umístěny dotazy. Toto zobrazení dynamické správy může vracet velké objemy dat v produkční databázi a je užitečné při diagnostice zámků, které se aktuálně uchovávají.

Vzhledem k tomu, že funkce INNER JOIN zapnutá sys.dm_os_waiting_tasks, následující dotaz omezuje výstup sys.dm_tran_locks pouze na aktuálně blokované požadavky, jejich stav čekání a zámky:

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>';
  • Při zobrazení dynamické správy bude ukládání výsledků dotazu v průběhu času poskytovat datové body, které vám umožní zkontrolovat blokování v zadaném časovém intervalu a identifikovat trvalé blokování nebo trendy.

Shromažďování informací z rozšířených událostí

Kromě předchozích informací je často nutné zachytit trasování aktivit na serveru, aby bylo možné důkladně prozkoumat problém blokování ve službě Azure SQL Database. Pokud například relace provádí více příkazů v rámci transakce, bude reprezentován pouze poslední příkaz, který byl odeslán. Jedním z předchozích tvrzení však může být důvod, proč se zámky stále uchovávají. Trasování vám umožní zobrazit všechny příkazy spuštěné relací v rámci aktuální transakce.

Trasování v SQL Serveru lze zachytit dvěma způsoby; Rozšířené události (XEvents) a trasování profileru Sql Server Profiler je však zastaralá trasovací technologie, která není pro Azure SQL Database podporovaná. Extended Events je novější technologie trasování, která umožňuje všestrannější a menší dopad na pozorovaný systém a jeho rozhraní je integrované do aplikace SQL Server Management Studio (SSMS).

Přečtěte si dokument, který vysvětluje, jak používat Průvodce vytvořením nové relace rozšířených událostí v nástroji SSMS. Pro databáze Azure SQL však SSMS poskytuje podsložku Rozšířených událostí v každé databázi v Průzkumníku objektů. K zachycení těchto užitečných událostí použijte průvodce relací rozšířených událostí:

  • Chyby kategorií:

    • Pozornost
    • Error_reported
    • Execution_warning

  • Upozornění kategorií:

    • Missing_join_predicate

  • Provádění kategorií:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Deadlock_monitor kategorie

    • database_xml_deadlock_report

  • Relace kategorií

    • Existing_connection
    • Přihlášení
    • Odhlášení

Poznámka:

Podrobné informace o vzájemném zablokování najdete v tématu Analýza a zabránění zablokování ve službě Azure SQL Database.

Identifikace a řešení běžných scénářů blokování

Prozkoumáním předchozích informací můžete určit příčinu většiny blokujících problémů. Zbývající část tohoto článku popisuje, jak tyto informace použít k identifikaci a řešení některých běžných scénářů blokování. V této diskuzi se předpokládá, že jste k zachycení informací o blokujících identifikátorech SPID použili blokující skripty (odkazované dříve) a zaznamenali aktivitu aplikace pomocí relace XEvent.

Analýza blokujících dat

  • Prozkoumejte výstup zobrazení dynamické sys.dm_exec_requests správy a sys.dm_exec_sessions určete hlavy blokujících řetězců pomocí blocking_these a session_id. Tím se nejjasněji určí, které požadavky jsou blokované a které blokují. Podívejte se dále na relace, které jsou blokované a blokované. Je pro blokující řetězec společný nebo kořenový adresář? Pravděpodobně sdílí společnou tabulku a jedna nebo více relací zapojených do blokujícího řetězce provádí operaci zápisu.

  • Prohlédněte si výstup zobrazení dynamické sys.dm_exec_requests správy a sys.dm_exec_sessions informace o identifikátorech SPID v čele blokujícího řetězce. Vyhledejte následující pole:

    • sys.dm_exec_requests.status
      Tento sloupec zobrazuje stav konkrétního požadavku. Stav spánku obvykle značí, že spID se dokončilo spuštění a čeká na odeslání jiného dotazu nebo dávky aplikace. Spustitelný nebo spuštěný stav označuje, že SPID právě zpracovává dotaz. Následující tabulka obsahuje stručné vysvětlení různých hodnot stavu.
    Stav Význam
    Pozadí SPID spouští úlohu na pozadí, jako je detekce zablokování, zapisovač protokolů nebo kontrolní bod.
    Spaní SPID se aktuálně nespouštějí. Obvykle to značí, že SPID čeká na příkaz z aplikace.
    Běh SPID je aktuálně spuštěný v plánovači.
    Runnable SPID je ve spuštěné frontě plánovače a čeká na získání času plánovače.
    Odloženo SPID čeká na prostředek, jako je zámek nebo západka.

    • sys.dm_exec_sessions.open_transaction_count
      Toto pole vám řekne počet otevřených transakcí v této relaci. Pokud je tato hodnota větší než 0, SPID je v otevřené transakci a může obsahovat zámky získané libovolným příkazem v rámci transakce.

    • sys.dm_exec_requests.open_transaction_count
      Podobně toto pole informuje o počtu otevřených transakcí v tomto požadavku. Pokud je tato hodnota větší než 0, SPID je v otevřené transakci a může obsahovat zámky získané libovolným příkazem v rámci transakce.

    • sys.dm_exec_requests.wait_type, wait_timea last_wait_type
      sys.dm_exec_requests.wait_type Pokud je hodnota NULL, požadavek aktuálně nečeká na nic a last_wait_type hodnota označuje posledníwait_type, ke kterému došlo. Další informace o sys.dm_os_wait_stats nejběžnějších typech čekání a jejich popis najdete v tématu sys.dm_os_wait_stats. Hodnotu wait_time lze použít k určení, jestli požadavek postupuje. Když dotaz na sys.dm_exec_requests tabulku vrátí hodnotu ve wait_time sloupci, který je menší než wait_time hodnota z předchozího dotazu sys.dm_exec_requests, znamená to, že předchozí zámek byl získán a uvolněn a nyní čeká na nový zámek (za předpokladu, že nenulový wait_time). To lze ověřit porovnáním wait_resource mezi sys.dm_exec_requests výstupem, který zobrazuje prostředek, pro který požadavek čeká.

    • sys.dm_exec_requests.wait_resource Toto pole označuje prostředek, na který čeká blokovaný požadavek. Následující tabulka uvádí běžné wait_resource formáty a jejich význam:

    Resource Formát Příklad Vysvětlení
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 V tomto případě je ID databáze 5 ukázková databáze pubs a ID objektu 261575970 je tabulka názvů a 1 je clusterovaný index.
    Stránka DatabaseID:FileID:PageID STRÁNKA: 5:1:104 V tomto případě je ID databáze 5 pubs, ID souboru 1 je primární datový soubor a stránka 104 je stránka, která patří do tabulky názvů. K identifikaci object_id stránky patří, použijte funkci dynamické správy sys.dm_db_page_info, předání ID databáze, FileId, PageId z wait_resource.
    Key DatabaseID:Hobt_id (hodnota hash pro klíč indexu) KLÍČ: 5:72057594044284928 (3300a4f361aa) V tomto případě je ID databáze 5 Pubs, Hobt_ID 72057594044284928 odpovídá index_id 2 pro object_id 261575970 (tabulka názvů). sys.partitions Pomocí zobrazení katalogu přidružte hobt_id k určitému index_id a object_id. Neexistuje způsob, jak zrušit uvolnění hodnoty hash klíče indexu na konkrétní hodnotu klíče.
    Řádka DatabaseID:FileID:PageID:Slot(řádek) RID: 5:1:104:3 V tomto případě je ID databáze 5 pubs, ID souboru 1 je primární datový soubor, stránka 104 je stránka, která patří do tabulky názvů, a slot 3 označuje pozici řádku na stránce.
    Kompilovat DatabaseID:FileID:PageID:Slot(řádek) RID: 5:1:104:3 V tomto případě je ID databáze 5 pubs, ID souboru 1 je primární datový soubor, stránka 104 je stránka, která patří do tabulky názvů, a slot 3 označuje pozici řádku na stránce.
    • sys.dm_tran_active_transactionsZobrazení dynamické správy sys.dm_tran_active_transactions obsahuje data o otevřených transakcích, které je možné připojit k jiným zobrazením dynamické správy pro kompletní obrázek transakcí čekajících na potvrzení nebo vrácení zpět. Následující dotaz slouží k vrácení informací o otevřených transakcích, připojených k jiným zobrazením dynamické správy včetně sys.dm_tran_session_transactions. Zvažte aktuální stav transakce a další situační data k vyhodnocení, transaction_begin_timezda by mohla být zdrojem blokování.
    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;

    • Další sloupce

      Zbývající sloupce v sys.dm_exec_sessions a sys.dm_exec_request můžou také poskytnout přehled o kořenu problému. Jejich užitečnost se liší v závislosti na okolnostech problému. Můžete například určit, jestli k problému dochází pouze u určitých klientů (název hostitele), v určitých síťových knihovnách (net_library), kdy poslední dávka odeslaná identifikátorem SPID byla last_request_start_time , sys.dm_exec_sessionsjak dlouho se žádost používala a start_timesys.dm_exec_requeststak dále.

Běžné scénáře blokování

Následující tabulka mapuje běžné příznaky jejich pravděpodobné příčiny.

Sloupce Waittype, Open_Tran a Status odkazují na informace vrácené sys.dm_exec_request, další sloupce mohou být vráceny sys.dm_exec_sessions. Sloupec "Vyřešit?" označuje, jestli se blokování vyřeší samostatně, nebo jestli má být relace zabita příkazem KILL . Další informace naleznete v tématu KILL (Transact-SQL).

Scénář Waittype Open_Tran Stav Řeší? Jiné příznaky
1 NOT NULL >= 0 Runnable Ano, po dokončení dotazu. V sys.dm_exec_sessionsčase se zvýší počet sloupců , reads, cpu_timea/nebo memory_usage sloupců. Doba trvání dotazu bude po dokončení vysoká.
2 NULL >0 Spaní Ne, ale SPID může být zabit. V relaci rozšířené události pro tento identifikátor SPID může být zobrazen signál upozornění, který označuje, že došlo k vypršení časového limitu dotazu nebo zrušení dotazu.
3 NULL >= 0 Runnable Č. Nepřeloží se, dokud klient nenačte všechny řádky nebo nezavře připojení. SPID může být zabit, ale může to trvat až 30 sekund. Pokud open_transaction_count = 0 a SPID uchovává zámky, zatímco úroveň izolace transakce je výchozí (READ COMMMITTED), je to pravděpodobně příčina.
4 Je to různé. >= 0 Runnable Č. Nevyřeší se, dokud klient nezruší dotazy nebo nezavře připojení. Identifikátory SPID se dají zabít, ale může to trvat až 30 sekund. hostname Sloupec sys.dm_exec_sessions pro SPID v záhlaví blokujícího řetězce bude stejný jako jeden z IDENTIFIKÁTORů SPID, který blokuje.
5 NULL >0 Vrácení zpět Ano. Signál pozornosti může být zobrazen v relaci rozšířených událostí pro tento identifikátor SPID, což znamená, že došlo k vypršení časového limitu dotazu nebo zrušení, nebo byl vydán příkaz vrácení zpět.
6 NULL >0 Spaní Nakonec. Pokud systém Windows NT zjistí, že relace už není aktivní, připojení ke službě Azure SQL Database bude přerušeno. Hodnota last_request_start_timesys.dm_exec_sessions je mnohem dřívější než aktuální čas.

Podrobné scénáře blokování

  1. Blokování způsobené běžně běžícím dotazem s dlouhou dobou provádění

    Řešení: Řešením tohoto typu problému blokování je hledat způsoby optimalizace dotazu. Ve skutečnosti, tato třída blokující problém může být pouze problém s výkonem a vyžadovat, abyste ho sledovali jako takové. Informace o řešení potíží s konkrétním pomalým dotazem najdete v tématu Řešení potíží s pomalými dotazy na SQL Serveru. Další informace naleznete v tématu Monitorování a ladění výkonu.

    Sestavy z úložiště dotazů v SSMS jsou také vysoce doporučeným a cenným nástrojem pro identifikaci nejnákladnějších dotazů a neoptimálních plánů provádění. Projděte si také část Inteligentní výkon na webu Azure Portal pro databázi Azure SQL, včetně query Performance Insight.

    Pokud dotaz provádí pouze operace SELECT, zvažte spuštění příkazu pod izolací snímků, pokud je ve vaší databázi povolené, zejména pokud je analýza RCSI zakázaná. Stejně jako když je povolená analýza RCSI, dotazy, které čtou data, nevyžadují sdílené zámky (S) na úrovni izolace snímků. Izolace snímků navíc poskytuje konzistenci na úrovni transakce pro všechny příkazy v explicitní multi-příkaz transakce. Izolace snímků už může být ve vaší databázi povolená. Izolaci snímků lze také použít s dotazy provádějícími úpravy, ale musíte zpracovat konflikty aktualizací.

    Pokud máte dlouhotrvající dotaz, který blokuje ostatní uživatele a nelze ho optimalizovat, zvažte jeho přesun z prostředí OLTP do vyhrazeného systému generování sestav, synchronní repliku databáze jen pro čtení.

  2. Blokování způsobené spící SPID, který má nepotvrzenou transakci

    Tento typ blokování lze často identifikovat pomocí SPID, který spí nebo čeká na příkaz, ale jehož úroveň vnoření transakcí (@@TRANCOUNTod open_transaction_countsys.dm_exec_requests) je větší než nula. K tomu může dojít v případě, že aplikace dojde k vypršení časového limitu dotazu nebo vydá zrušení, aniž by také vydal požadovaný počet příkazů ROLLBACK a/nebo COMMIT. Když SPID obdrží vypršení časového limitu dotazu nebo zrušení, ukončí aktuální dotaz a dávku, ale automaticky nevrátí zpět ani potvrdí transakci. Za to zodpovídá aplikace, protože Azure SQL Database nemůže předpokládat, že se celá transakce musí vrátit zpět kvůli zrušení jednoho dotazu. Časový limit dotazu nebo zrušení dotazu se zobrazí jako událost UPOZORNĚNÍ pro SPID v relaci rozšířené události.

    Pokud chcete předvést nepotvrzenou explicitní transakci, zadejte následující dotaz:

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

    Potom spusťte tento dotaz ve stejném okně:

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

    Výstup druhého dotazu označuje, že úroveň vnoření transakcí je jedna. Všechny zámky získané v transakci jsou stále uloženy, dokud transakce nebyla potvrzena nebo vrácena zpět. Pokud aplikace explicitně otevřou a potvrdí transakce, může komunikace nebo jiná chyba opustit relaci a její transakce v otevřeném stavu.

    Použijte skript dříve v tomto článku na sys.dm_tran_active_transactions základě identifikace aktuálně nepotvrzených transakcí v instanci.

    Řešení:

    • Kromě toho, tato třída blokující problém může být také problém s výkonem a vyžadovat, abyste ho například sledovali. Pokud může dojít ke snížení doby provádění dotazu, nedojde k vypršení časového limitu dotazu nebo zrušení. Je důležité, aby aplikace dokázala zpracovat scénáře vypršení časového limitu nebo zrušení, pokud k nim dojde, ale můžete také využít při zkoumání výkonu dotazu.

    • Aplikace musí správně spravovat úrovně vnoření transakcí nebo mohou způsobit problém blokování po zrušení dotazu tímto způsobem. Vezměte v úvahu následující skutečnosti:

      • V obslužné rutině chyby klientské aplikace spusťte IF @@TRANCOUNT > 0 ROLLBACK TRAN následující chybu, i když klientská aplikace nevěří, že je transakce otevřená. Vyžaduje se kontrola otevřených transakcí, protože uložená procedura volaná během dávky mohla spustit transakci bez znalosti klientské aplikace. Určité podmínky, jako je zrušení dotazu, brání provádění procedury za aktuálním příkazem, takže i když má procedura logiku pro kontrolu IF @@ERROR <> 0 a přerušení transakce, tento kód vrácení zpět se v takových případech neprovede.
      • Pokud se sdružování připojení používá v aplikaci, která připojení otevře a spustí malý počet dotazů před uvolněním připojení zpět do fondu, jako je například webová aplikace, může dočasné zakázání sdružování připojení zmírnit problém, dokud se klientská aplikace nezmění, aby se chyby správně zpracovávaly. Zakázáním sdružování připojení způsobí uvolnění připojení fyzické odpojení připojení ke službě Azure SQL Database, což způsobí vrácení všech otevřených transakcí na serveru.
      • Používá se SET XACT_ABORT ON pro připojení nebo v jakýchkoli uložených procedurách, které začínají transakcemi, a nečistí se po chybě. V případě chyby za běhu toto nastavení přeruší všechny otevřené transakce a vrátí řízení klientovi. Další informace najdete v tématu SET XACT_ABORT (Transact-SQL).

    Poznámka:

    Připojení se neobnovuje, dokud se znovu nepoužívá z fondu připojení, takže je možné, že uživatel může otevřít transakci a poté uvolnit připojení k fondu připojení, ale nemusí se znovu použít po dobu několika sekund, během které transakce zůstane otevřená. Pokud se připojení znovu nepoužívá, transakce se přeruší, když vyprší časový limit připojení a odebere se z fondu připojení. Proto je optimální, aby klientská aplikace přerušila transakce v obslužné rutině chyby nebo aby SET XACT_ABORT ON se zabránilo tomuto potenciálnímu zpoždění.

    Upozornění

    Následující SET XACT_ABORT ONpříkazy T-SQL za příkazem, který způsobí, že se nespustí chyba. To může mít vliv na zamýšlený tok existujícího kódu.

  3. Blokování způsobené identifikátorem SPID, jehož odpovídající klientská aplikace nenačetla všechny řádky výsledků k dokončení

    Po odeslání dotazu na server musí všechny aplikace okamžitě načíst všechny řádky výsledků. Pokud aplikace nenačte všechny řádky výsledků, můžou být zámky ponechány v tabulkách a blokují ostatní uživatele. Pokud používáte aplikaci, která transparentně odesílá příkazy SQL na server, musí aplikace načíst všechny řádky výsledků. Pokud ne (a pokud to nejde nakonfigurovat), možná nebudete moct problém s blokováním vyřešit. Pokud se chcete tomuto problému vyhnout, můžete nevhodně se chovající aplikace omezit na databázi sestav nebo databázi pro podporu rozhodování oddělenou od hlavní databáze OLTP.

    Dopad tohoto scénáře je možné snížit povolením možnosti READ_COMMITTED_SNAPSHOT v databázi, což je výchozí konfigurace ve službě Azure SQL Database. Další informace najdete v části Vysvětlení blokování tohoto článku.

    Poznámka:

    Pokyny pro logiku opakování pro aplikace připojující se ke službě Azure SQL Database

    Řešení: Aplikace se musí přepsat, aby se načítly všechny řádky výsledku k dokončení. To nevyloučí použití posunu a funkce FETCH v klauzuli ORDER BY dotazu k provedení stránkování na straně serveru.

  4. Blokování způsobené relací ve stavu vrácení zpět

    Dotaz na úpravu dat, který je KILLed nebo zrušen mimo uživatelem definovanou transakci, se vrátí zpět. Může k tomu dojít také jako vedlejší účinek odpojení relace klientské sítě nebo když je jako oběť zablokování vybrána žádost. To lze často identifikovat pozorováním výstupu sys.dm_exec_requests, který může znamenat příkaz ROLLBACK a percent_complete sloupec může zobrazit průběh.

    Díky funkci zrychleného obnovení databáze představené v roce 2019 by měla být zdlouhavá vrácení zpět vzácné.

    Řešení: Počkejte, až spID dokončí vrácení změn, které byly provedeny.

    Abyste se této situaci vyhnuli, neprovádějte velké dávkové operace zápisu nebo operace vytváření nebo údržby indexů během zaneprázdněných hodin v systémech OLTP. Pokud je to možné, proveďte tyto operace během období nízké aktivity.

  5. Blokování způsobené osamoceným připojením

    Pokud klientská aplikace zachytí chyby nebo se restartuje klientská pracovní stanice, nemusí být síťová relace serveru za určitých podmínek okamžitě zrušena. Z pohledu služby Azure SQL Database se zdá, že je klient stále připojený, a všechny získané zámky se můžou zachovat. Další informace naleznete v tématu Řešení potíží se osamocenými připojeními na SQL Serveru.

    Řešení: Pokud se klientská aplikace odpojila bez odpovídajícího čištění prostředků, můžete id spID ukončit pomocí KILL příkazu. Příkaz KILL přebírá jako vstup hodnotu SPID. Pokud chcete například zabít SPID 99, zadejte následující příkaz:

    KILL 99

Viz také

Další kroky