Az Azure SQL Database blokkolási problémáinak ismertetése és megoldása

  • Cikk

A következőre vonatkozik: Azure SQL Database

Cél

A cikk az Azure SQL-adatbázisokban történő blokkolást ismerteti, és bemutatja, hogyan háríthatja el és oldhatja meg a blokkolást.

Ebben a cikkben a kapcsolat kifejezés az adatbázis egyetlen bejelentkezett munkamenetére hivatkozik. Minden kapcsolat munkamenet-azonosítóként (SPID) vagy session_id jelenik meg számos DMV-ben. Ezeket a SPID-ket gyakran folyamatnak nevezik, bár ez nem egy külön folyamatkörnyezet a szokásos értelemben. Ehelyett az egyes SPID-k az adott ügyféltől származó egyetlen kapcsolat kéréseinek kiszolgálásához szükséges kiszolgálói erőforrásokból és adatstruktúrákból állnak. Egyetlen ügyfélalkalmazásnak lehet egy vagy több kapcsolata. Az Azure SQL Database szempontjából nincs különbség az egyetlen ügyfélszámítógépen található egyetlen ügyfélalkalmazásból származó több kapcsolat és a több ügyfélalkalmazásból vagy több ügyfélszámítógépről származó több kapcsolat között; atomiak. Az egyik kapcsolat blokkolhat egy másik kapcsolatot, függetlenül a forrásügyféltől.

A holtpontok hibaelhárításával kapcsolatos információkért lásd : Holtpontok elemzése és megakadályozása az Azure SQL Database-ben.

Megjegyzés:

Ez a tartalom az Azure SQL Database-re összpontosít. Az Azure SQL Database a Microsoft SQL Server adatbázismotor legújabb stabil verzióján alapul, így a tartalom nagy része hasonló, bár a hibaelhárítási lehetőségek és az eszközök eltérőek lehetnek. Az SQL Server blokkolásával kapcsolatos további információkért tekintse meg és oldja meg az SQL Server blokkolási problémáit.

A blokkolás ismertetése

A blokkolás elkerülhetetlen és terezett jellemzője minden olyan relációsadatbázis-kezelő rendszernek (RDBMS), amelyben zároláson alapul az egyidejűség. Az Azure SQL Database-ben az adatbázisok blokkolása akkor fordul elő, ha egy munkamenet zárol egy adott erőforrást, és egy második SPID megkísérli megszerezni az ütköző zárolási típust ugyanazon az erőforráson. Általában kicsi az az időkeret, amelyre az első SPID zárolja az erőforrást. Amikor a tulajdonosi munkamenet feloldja a zárolást, a második kapcsolat ezután szabadon beszerezhet saját zárolást az erőforráson, és folytathatja a feldolgozást. Ez normális viselkedés, és egy nap során sokszor előfordulhat, és nincs észrevehető hatása a rendszer teljesítményére.

Az Azure SQL Database minden új adatbázisában alapértelmezés szerint engedélyezve van az írásvédett pillanatkép (RCSI) adatbázis-beállítás. Az adatokat olvasó munkamenetek és a munkamenetek adatírása közötti blokkolás az RCSI-ben minimálisra csökken, amely sorverziók használatával növeli az egyidejűséget. A blokkolás és a holtpontok azonban továbbra is előfordulhatnak az Azure SQL Database adatbázisaiban, mert:

  • Az adatokat módosító lekérdezések blokkolhatják egymást.
  • A lekérdezések olyan elkülönítési szinteken futhatnak, amelyek növelik a blokkolást. Az elkülönítési szintek megadhatók a Transact-SQL alkalmazáskapcsolati sztringjeiben, lekérdezési tippjeiben vagy SET utasításaiban .
  • Előfordulhat, hogy az RCSI le van tiltva, ezért az adatbázis megosztott (S) zárolásokat használ az olvasási lekötött elkülönítési szinten futó SELECT-utasítások védelméhez. Ez növelheti a blokkolást és a holtpontokat.

A pillanatképek elkülönítési szintje alapértelmezés szerint engedélyezve van az Azure SQL Database-ben lévő új adatbázisok esetében is. A pillanatkép-elkülönítés egy további soralapú elkülönítési szint, amely tranzakciószintű konzisztenciát biztosít az adatok számára, és amely sorverziókkal választja ki a frissítendő sorokat. A pillanatkép-elkülönítés használatához a lekérdezéseknek vagy kapcsolatoknak explicit módon kell beállítaniuk a tranzakcióelkülönítési szintet SNAPSHOT. Ez csak akkor lehetséges, ha az adatbázis pillanatkép-elkülönítése engedélyezve van.

Megállapíthatja, hogy az RCSI és/vagy a pillanatkép-elkülönítés engedélyezve van-e a Transact-SQL-ben. Csatlakozzon az adatbázishoz az Azure SQL Database-ben, és futtassa a következő lekérdezést:

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

Ha az RCSI engedélyezve van, az oszlop az is_read_committed_snapshot_on 1 értéket adja vissza. Ha a pillanatkép-elkülönítés engedélyezve van, az oszlop a snapshot_isolation_state_desc BE értéket adja vissza.

A lekérdezés időtartama és tranzakciós környezete határozza meg a zárolások időtartamát, és ezáltal a többi lekérdezésre gyakorolt hatásukat. Az RCSI alatt futó SELECT-utasítások nem szereznek be megosztott (S) zárolásokat az éppen beolvasott adatokon, ezért nem tiltják le az adatokat módosító tranzakciókat. AZ INSERT, UPDATE és DELETE utasítások esetén a zárolások a lekérdezés során is megtarthatók, mind az adatkonzisztenciáért, mind a lekérdezés szükség esetén történő visszaállításának engedélyezéséhez.

Az explicit tranzakción belül végrehajtott lekérdezések esetében a zárolások típusát és időtartamát a lekérdezés típusa, a tranzakcióelkülönítési szint és a lekérdezésben használt zárolási tippek határozzák meg. A zárolási, zárolási tippek és tranzakcióelkülönítési szintek leírását a következő cikkekben talál:

Ha a zárolás és a blokkolás olyan pontig tart, ahol káros hatással van a rendszer teljesítményére, az alábbi okok egyikének köszönhető:

  • A SPID hosszabb ideig zárolja az erőforrásokat, mielőtt felszabadítja őket. Az ilyen típusú blokkolás idővel feloldja önmagát, de teljesítménycsökkenést okozhat.

  • A SPID zárolja az erőforrások egy készletét, és soha nem bocsátja ki őket. Az ilyen típusú blokkolás nem oldja fel önmagát, és határozatlan időre megakadályozza az érintett erőforrásokhoz való hozzáférést.

Az első forgatókönyvben a helyzet nagyon folyékony lehet, mivel a különböző SPID-k idővel blokkolják a különböző erőforrásokat, és mozgó célt hoznak létre. Ezeket a helyzeteket nehéz elhárítani az SQL Server Management Studio használatával, hogy az egyes lekérdezésekre szűkítse a problémát. Ezzel szemben a második helyzet konzisztens állapotot eredményez, amely könnyebben diagnosztizálható.

Optimalizált zárolás

Az optimalizált zárolás egy új adatbázismotor-funkció, amely drasztikusan csökkenti a zárolási memóriát és az íráshoz egyidejűleg szükséges zárolások számát. Az optimalizált zárolás két elsődleges összetevőt használ: a tranzakcióazonosító (TID) zárolását (más sorverziós funkciókban is használják), és a minősítés utáni zárolást (LAQ). Nincs szükség további konfigurációra.

Ez a cikk jelenleg az adatbázismotor viselkedésére vonatkozik optimalizált zárolás nélkül.

További információkért és az optimalizált zárolás elérhetőségének megismeréséhez tekintse meg az optimalizált zárolást ismertető témakört.

Alkalmazások és blokkolás

Előfordulhat, hogy a kiszolgálóoldali hangolással és a platformmal kapcsolatos problémákra kell összpontosítani, amikor blokkolási problémával szembesülnek. A csak az adatbázisra fordított figyelem azonban nem vezethet megoldáshoz, és jobban elnyelheti az ügyfélalkalmazás és az általa küldött lekérdezések vizsgálatára fordított időt és energiát. Függetlenül attól, hogy az alkalmazás milyen szintű láthatóságot tesz elérhetővé az adatbázis-hívások tekintetében, a blokkolási probléma azonban gyakran megköveteli az alkalmazás által küldött pontos SQL-utasítások ellenőrzését és az alkalmazás pontos viselkedését a lekérdezések megszakításával, a kapcsolatkezeléssel, az összes eredménysor lekérésével stb. kapcsolatban. Ha a fejlesztőeszköz nem teszi lehetővé a kapcsolatkezelés, a lekérdezések megszakítása, a lekérdezés időtúllépése, az eredmények beolvasása és így tovább, előfordulhat, hogy a blokkolási problémák nem oldhatók meg. Ezt a potenciált alaposan meg kell vizsgálni az Azure SQL Database alkalmazásfejlesztési eszközének kiválasztása előtt, különösen a teljesítményérzékeny OLTP-környezetek esetében.

Az adatbázis és az alkalmazás tervezési és építési fázisában ügyeljen az adatbázis teljesítményére. Különösen az erőforrás-felhasználást, az elkülönítési szintet és a tranzakciós útvonal hosszát kell kiértékelni az egyes lekérdezések esetében. Minden lekérdezésnek és tranzakciónak a lehető legkönnyűsnek kell lennie. Jó kapcsolatkezelési szemléletet kell gyakorolni anélkül, hogy az alkalmazás elfogadható teljesítményt mutathat alacsony számú felhasználó esetén, de a teljesítmény jelentősen csökkenhet, mivel a felhasználók száma felfelé skálázható.

A megfelelő alkalmazás- és lekérdezéstervezéssel az Azure SQL Database több ezer egyidejű felhasználót képes támogatni egyetlen kiszolgálón, kevés blokkolással.

Megjegyzés:

További alkalmazásfejlesztési útmutatásért tekintse meg az Azure SQL Database és az Azure SQL Managed Instanceés az Átmeneti hibakezelés csatlakozási problémáinak és egyéb hibáinak hibaelhárítását.

Blokkolási hibák elhárítása

Függetlenül attól, hogy milyen blokkoló helyzetben vagyunk, a zárolás hibaelhárításának módszertana ugyanaz. Ezek a logikai elválasztások határozzák meg a cikk további összetételét. A koncepció az, hogy megkeresse a fejblokkolót, és azonosítsa, hogy a lekérdezés mit és miért blokkolja. Miután azonosította a problémás lekérdezést (vagyis azt, hogy mi tartja a zárolásokat a hosszabb ideig), a következő lépés a blokkolás okának elemzése és meghatározása. Miután megértettük a miértet, a lekérdezés és a tranzakció újratervezésével végezhetünk módosításokat.

A hibaelhárítás lépései:

  1. A fő blokkoló munkamenet azonosítása (fejblokkoló)

  2. Keresse meg a blokkolást okozó lekérdezést és tranzakciót (mi tartja a zárolásokat hosszabb ideig)

  3. Annak elemzése/megértése, hogy miért fordul elő a hosszan tartó blokkolás

  4. A blokkolási probléma megoldása lekérdezés és tranzakció újratervezésével

Most vizsgáljuk meg, hogyan rögzítheti a fő blokkoló munkamenetet egy megfelelő adatrögzítéssel.

Blokkoló információk gyűjtése

A blokkolási problémák elhárításának nehézségeinek elhárítása érdekében az adatbázisgazdák olyan SQL-szkripteket használhatnak, amelyek folyamatosan figyelik a zárolás és a blokkolás állapotát az Azure SQL Database adatbázisában. Az adatok gyűjtéséhez alapvetően két módszer létezik.

Az első a dinamikus felügyeleti objektumok (DMOS-k) lekérdezése és az eredmények időbeli összehasonlításhoz való tárolása. A cikkben hivatkozott objektumok némelyike dinamikus felügyeleti nézet (DMV), néhány pedig dinamikus felügyeleti függvény (DMF). A második módszer az XEvents használata a végrehajtás rögzítésére.

Információgyűjtés DMV-kből

A DMV-k blokkolási hibák elhárítására való hivatkozásának célja az SPID (munkamenet-azonosító) azonosítása a blokkolási lánc és az SQL-utasítás élén. Keresse meg a letiltott áldozati SPID-ket. Ha valamelyik SPID-t egy másik SPID blokkolja, vizsgálja meg az erőforrást (a blokkoló SPID-t) tulajdonoló SPID-t. Ez a tulajdonos SPID is blokkolva van? A láncot sétálva megkeresheti a fejblokkolót, majd megvizsgálhatja, hogy miért tartja karban a zárat.

Ne felejtse el futtatni ezeket a szkripteket a céladatbázisban az Azure SQL Database-ben.

  • A sp_who és sp_who2 parancsok régebbi parancsok az összes aktuális munkamenet megjelenítéséhez. A DMV sys.dm_exec_sessions több adatot ad vissza egy könnyebben lekérdezni és szűrni kívánt eredményhalmazban. A többi lekérdezés központi eleme.sys.dm_exec_sessions

  • Ha már azonosított egy adott munkamenetet, a munkamenet által elküldött utolsó utasítást is megkeresheti DBCC INPUTBUFFER(<session_id>) . Hasonló eredményeket a dinamikus felügyeleti függvény (DMF) is sys.dm_exec_input_buffer visszaadhat egy könnyebben lekérdezhető és szűrhető eredményhalmazban, amely biztosítja a session_id és a request_id. Ha például a 66-os és request_id 0-s session_id által küldött legutóbbi lekérdezést szeretné visszaadni:

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

  • Tekintse meg a következő oszlopot: .blocking_session_id>sys.dm_exec_requests Ha blocking_session_id = 0, a munkamenet nem lesz blokkolva. Bár sys.dm_exec_requests a listák csak a jelenleg futó kéréseket sorolják fel, a kapcsolatok (aktívak vagy nem) megjelennek a sys.dm_exec_sessionslistában. Építsen erre a közös illesztésre a következő lekérdezés között sys.dm_exec_requests és sys.dm_exec_sessions a következő lekérdezésben.

  • A minta lekérdezés futtatásával megkeresheti az aktívan végrehajtó lekérdezéseket és az SQL-köteg aktuális szövegét vagy bemeneti pufferszövegét a sys.dm_exec_sql_text vagy sys.dm_exec_input_buffer DMV-k használatával. Ha a text mező sys.dm_exec_sql_text által visszaadott adatok NULL értékűek, a lekérdezés jelenleg nem fut. Ebben az esetben a mező sys.dm_exec_input_buffer az event_info SQL-motornak átadott utolsó parancssztringet tartalmazza. Ez a lekérdezés más munkameneteket blokkoló munkamenetek azonosítására is használható, beleértve a session_id letiltott session_ids listáját is.

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;
  • Futtassa a Microsoft támogatási szolgálata által biztosított részletesebb mintalekérdezéseket egy több munkamenet-blokkoló lánc vezetőjének azonosításához, beleértve a blokkolási láncban részt vevő munkamenetek lekérdezési szövegét is.
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];
  • Referencia sys.dm_os_waiting_tasks , amely az SQL szál-/tevékenységrétegében található. Ez információkat ad vissza arról, hogy milyen SQL-várakozási típust tapasztal a kérés. Például sys.dm_exec_requestscsak az aktív kéréseket adja vissza a sys.dm_os_waiting_tasksrendszer.

Megjegyzés:

A várakozási típusokról, beleértve az összesített várakozási statisztikákat az idő függvényében, tekintse meg a DMV sys.dm_db_wait_stats. Ez a DMV csak az aktuális adatbázis összesített várakozási statisztikáit adja vissza.

  • A sys.dm_tran_locks DMV használatával részletesebb információkat kaphat arról, hogy a lekérdezések milyen zárolásokat helyeztek el. Ez a DMV nagy mennyiségű adatot képes visszaadni egy éles adatbázisban, és hasznos a jelenleg tárolt zárolások diagnosztizálásához.

Az INNER JOIN bekapcsolva sys.dm_os_waiting_tasksmiatt a következő lekérdezés csak a jelenleg blokkolt kérésekre, várakozási állapotukra és zárolásaikra korlátozza a kimenetet sys.dm_tran_locks :

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>';
  • A DMV-k esetén a lekérdezési eredmények időbeli tárolása olyan adatpontokat biztosít, amelyek lehetővé teszik a letiltás adott időintervallumon keresztüli áttekintését a tartós blokkolások vagy trendek azonosítása érdekében.

Információk gyűjtése kiterjesztett eseményekből

Az előző információkon kívül gyakran szükséges a kiszolgálón végzett tevékenységek nyomon követése az Azure SQL Database blokkolási problémájának alapos kivizsgálásához. Ha például egy munkamenet több utasítást hajt végre egy tranzakción belül, csak az utolsó elküldött utasítás jelenik meg. Előfordulhat azonban, hogy a korábbi állítások egyike az oka annak, hogy a zárolások továbbra is fennállnak. A nyomkövetés lehetővé teszi a munkamenet által végrehajtott összes parancs megtekintését az aktuális tranzakción belül.

A nyomkövetések kétféleképpen rögzíthetők az SQL Serveren; Bővített események (XEvents) és Profiler-nyomkövetések. Az SQL Server Profiler azonban elavult nyomkövetési technológia, amely nem támogatott az Azure SQL Database-ben. A Kiterjesztett események az újabb nyomkövetési technológia, amely sokoldalúbb és kevésbé befolyásolja a megfigyelt rendszert, és a felülete integrálva van az SQL Server Management Studióba (SSMS).

Tekintse meg azt a dokumentumot, amely bemutatja, hogyan használható a Bővített események új munkamenet varázslója az SSMS-ben. Az Azure SQL-adatbázisok esetében azonban az SSMS egy Kiterjesztett események almappát biztosít az Object Explorerben lévő adatbázisok alatt. A bővített események munkamenetvarázslója a következő hasznos események rögzítésére használható:

  • Kategóriahibák:

    • Figyelmet
    • Error_reported
    • Execution_warning

  • Kategória figyelmeztetései:

    • Missing_join_predicate

  • Kategória végrehajtása:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategória deadlock_monitor

    • database_xml_deadlock_report

  • Kategória-munkamenet

    • Existing_connection
    • Bejelentkezés
    • Kijelentkezés

Megjegyzés:

A holtpontokra vonatkozó részletes információkért lásd : Holtpontok elemzése és megakadályozása az Azure SQL Database-ben.

Gyakori blokkolási forgatókönyvek azonosítása és megoldása

Az előző információk vizsgálatával meghatározhatja a legtöbb blokkolási probléma okát. A cikk további része azt ismerteti, hogyan használhatja ezeket az információkat néhány gyakori blokkolási forgatókönyv azonosítására és megoldására. Ez a vitafórum feltételezi, hogy a blokkoló szkriptekkel (korábban hivatkozott) rögzítette a blokkoló SPID-k adatait, és rögzítette az alkalmazástevékenységet egy XEvent-munkamenettel.

Blokkoló adatok elemzése

  • Vizsgálja meg a DMV-k kimenetét, és sys.dm_exec_sessions állapítsa sys.dm_exec_requests meg a blokkoló láncok fejét, és használja blocking_these éssession_id. Ez egyértelműen azonosítja, hogy mely kérések vannak letiltva, és melyek blokkolva. Tekintse át a letiltott és letiltott munkameneteket. Gyakori vagy gyökerű a blokkoló lánc? Valószínűleg közös táblát használnak, és a blokkolási láncban részt vevő munkamenetek közül legalább egy írási műveletet hajt végre.

  • Vizsgálja meg a DMV-k sys.dm_exec_requests kimenetét, és sys.dm_exec_sessions a blokkoló lánc élén lévő SPID-kkel kapcsolatos információkat. Keresse meg a következő mezőket:

    • sys.dm_exec_requests.status
      Ez az oszlop egy adott kérés állapotát mutatja. Az alvó állapot általában azt jelzi, hogy az SPID végrehajtotta a végrehajtást, és arra vár, hogy az alkalmazás egy másik lekérdezést vagy köteget küldjön be. A futtatható vagy futtatható állapot azt jelzi, hogy az SPID jelenleg egy lekérdezést dolgoz fel. Az alábbi táblázat röviden ismerteti a különböző állapotértékeket.
    Állapot Jelentés
    Háttér Az SPID egy háttérfeladatot futtat, például holtpont-észlelést, naplóírót vagy ellenőrzőpontot.
    Alszik Az SPID jelenleg nem fut. Ez általában azt jelzi, hogy az SPID az alkalmazás parancsára vár.
    Fut Az SPID jelenleg ütemezőn fut.
    Futtatható Az SPID egy ütemező futtatható üzenetsorában található, és az ütemező idő lekérésére vár.
    Felfüggesztett Az SPID egy erőforrásra, például zárolásra vagy reteszre vár.

    • sys.dm_exec_sessions.open_transaction_count
      Ez a mező a munkamenetben megnyitott tranzakciók számát mutatja. Ha ez az érték 0-nál nagyobb, az SPID egy nyitott tranzakción belül van, és a tranzakció bármely utasítása által beszerzett zárolásokat tartalmazhat.

    • sys.dm_exec_requests.open_transaction_count
      Ez a mező a kérelemben lévő nyitott tranzakciók számát is jelzi. Ha ez az érték 0-nál nagyobb, az SPID egy nyitott tranzakción belül van, és a tranzakció bármely utasítása által beszerzett zárolásokat tartalmazhat.

    • sys.dm_exec_requests.wait_type, wait_timeés last_wait_type
      Ha a sys.dm_exec_requests.wait_type kérelem null értékű, a kérés jelenleg nem vár semmire, és az last_wait_type érték azt jelzi, hogy a kérelem utoljára wait_type találkozott. A leggyakoribb várakozási típusokkal kapcsolatos sys.dm_os_wait_stats további információkért és leírásért lásd : sys.dm_os_wait_stats. Az wait_time érték segítségével megállapíthatja, hogy a kérés folyamatban van-e. Ha egy sys.dm_exec_requests tábla lekérdezése olyan értéket ad vissza az wait_time oszlopban, amely kisebb, mint wait_time egy korábbi lekérdezés sys.dm_exec_requestsértéke, az azt jelzi, hogy az előző zárolást megszerezték és feloldották, és most egy új zárolásra vár (feltéve, hogy nem nulla wait_time). Ez a két kimenet összehasonlításával wait_resourcesys.dm_exec_requests ellenőrizhető, amely megjeleníti azt az erőforrást, amelyre a kérés várakozik.

    • sys.dm_exec_requests.wait_resource Ez a mező azt az erőforrást jelzi, amelyen egy blokkolt kérés várakozik. Az alábbi táblázat a gyakori wait_resource formátumokat és azok jelentését sorolja fel:

    Erőforrás Formátum Példa Magyarázat
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 Ebben az esetben az 5. adatbázis-azonosító a pubok mintaadatbázisa, az objektumazonosító pedig 261575970 a címtáblázat, az 1 pedig a fürtözött index.
    Oldal DatabaseID:FileID:PageID OLDAL: 5:1:104 Ebben az esetben az 5. adatbázis-azonosító pub, az 1. fájl az elsődleges adatfájl, a 104. oldal pedig a címtáblához tartozó lap. A lap object_id azonosításához használja az sys.dm_db_page_info dinamikus felügyeleti függvényt, és adja meg a DatabaseID, FileId, PageId értéket a wait_resource.
    Kulcs DatabaseID:Hobt_id (indexkulcs kivonatértéke) KULCS: 5:72057594044284928 (3300a4f361aa) Ebben az esetben az 5. adatbázis-azonosító a Pubs, Hobt_ID 72057594044284928 index_id 2-nek felel meg object_id 261575970 (címtáblázat). sys.partitions A katalógusnézet használatával társíthatja a hobt_id egy adott és object_idegy .index_id Nincs mód az indexkulcs kivonatának egy adott kulcsértékre való lemosására.
    Sor DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Ebben az esetben az 5. adatbázis-azonosító pubs, az 1. fájlazonosító az elsődleges adatfájl, a 104. oldal a címtáblához tartozó lap, a 3. pont pedig a sor pozícióját jelzi az oldalon.
    Fordítás DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Ebben az esetben az 5. adatbázis-azonosító pubs, az 1. fájlazonosító az elsődleges adatfájl, a 104. oldal a címtáblához tartozó lap, a 3. pont pedig a sor pozícióját jelzi az oldalon.
    • sys.dm_tran_active_transactions A sys.dm_tran_active_transactions DMV olyan nyitott tranzakciók adatait tartalmazza, amelyek más DMV-khez csatlakoztathatók a véglegesítésre vagy visszaállításra váró tranzakciók teljes képéhez. Az alábbi lekérdezés segítségével adatokat ad vissza a nyitott tranzakciókról, más DMV-khez csatlakozva, beleértve a sys.dm_tran_session_transactions. Fontolja meg egy tranzakció aktuális állapotát és egyéb helyzetadatait annak kiértékeléséhez, transaction_begin_timehogy az blokkolható-e.
    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;

    • Egyéb oszlopok

      A sys.dm_exec_sessions és sys.dm_exec_request többi oszlopa is betekintést nyújt a probléma gyökerébe. A hasznosságuk a probléma körülményeitől függően változik. Megállapíthatja például, hogy a probléma csak bizonyos ügyfelektől (gazdagépnévtől), bizonyos hálózati kódtáraktól (net_library), mikor volt last_request_start_timesys.dm_exec_sessionsaz spid által küldött utolsó köteg, mennyi ideig futott start_time egy kérés a használatban sys.dm_exec_requests, és így tovább.

Gyakori blokkolási forgatókönyvek

Az alábbi táblázat leképozza a gyakori tüneteket a valószínű okokra.

A Waittype, Open_Tran és Status oszlopok az sys.dm_exec_request által visszaadott információkra vonatkoznak, más oszlopokat pedig sys.dm_exec_sessions adhat vissza. A "Feloldások?" oszlop azt jelzi, hogy a blokkolás önállóan megoldódik-e, vagy a munkamenetet a KILL parancson keresztül kell-e megölni. További információ: KILL (Transact-SQL).

Forgatókönyv Waittype Open_Tran Állapot Megoldja? Egyéb tünetek
1 NOT NULL >= 0 Futtatható Igen, amikor a lekérdezés befejeződik. A sys.dm_exec_sessions, reads, cpu_timeés/vagy memory_usage oszlopok idővel növekedni fognak. A lekérdezés időtartama a befejezéskor magas lesz.
2 NULL >0 Alszik Nem, de a SPID-t meg lehet ölni. Az SPID kiterjesztett esemény munkamenetében figyelemjel jelenhet meg, amely azt jelzi, hogy a lekérdezés időtúllépése vagy megszakítása történt.
3 NULL >= 0 Futtatható Nem. Nem oldódik fel, amíg az ügyfél be nem olvassa az összes sort, vagy be nem zárja a kapcsolatot. A SPID megölhető, de akár 30 másodpercig is eltarthat. Ha open_transaction_count = 0, és az SPID zárolja a tranzakcióelkülönítési szintet (READ COMMMITTED), ez valószínű ok.
4 Eltérő >= 0 Futtatható Nem. Nem oldódik fel, amíg az ügyfél nem szakítja meg a lekérdezéseket, vagy nem zárja be a kapcsolatokat. A SPID-k megölhetők, de akár 30 másodpercet is igénybe vehetnek. A hostname spid oszlopa sys.dm_exec_sessions a blokkoló lánc fejénél ugyanaz lesz, mint az egyik SPID, amelyet blokkol.
5 NULL >0 Visszagurítás Igen, Az SPID Kiterjesztett események munkamenetében figyelemjel jelenhet meg, amely azt jelzi, hogy a lekérdezés időtúllépése vagy megszakítása történt, vagy egyszerűen egy visszaállítási utasítás lett kiadva.
6 NULL >0 Alszik Végül. Ha a Windows NT megállapítja, hogy a munkamenet már nem aktív, az Azure SQL Database-kapcsolat megszakad. A last_request_start_time benne lévő sys.dm_exec_sessions érték sokkal korábbi, mint az aktuális idő.

Részletes blokkolási forgatókönyvek

  1. Hosszú végrehajtási idővel rendelkező, általában futó lekérdezés által okozott blokkolás

    Megoldás: Az ilyen típusú blokkolási probléma megoldása a lekérdezés optimalizálásának módja. Valójában ez a blokkolási probléma osztálya csak teljesítményproblémát jelenthet, és megköveteli, hogy ennek megfelelően folytassa. Egy adott lassú lekérdezés hibaelhárításával kapcsolatos információkért tekintse meg a lassan futó lekérdezések hibaelhárítását az SQL Serveren. További információt a Teljesítmény figyelése és finomhangolása című témakörben talál.

    Az SSMS lekérdezéstárából származó jelentések szintén erősen ajánlott és értékes eszközök a legköltségesebb lekérdezések, a legoptimálisabb végrehajtási tervek azonosításához. Tekintse át az Azure SQL-adatbázisHoz tartozó Azure Portal Intelligens teljesítmény szakaszát is, beleértve a Lekérdezési teljesítményelemzést is.

    Ha a lekérdezés csak SELECT műveleteket hajt végre, fontolja meg az utasítás pillanatkép-elkülönítésben történő futtatását, ha az engedélyezve van az adatbázisban, különösen akkor, ha az RCSI le van tiltva. Ahogy az RCSI engedélyezve van, az adatokat olvasó lekérdezések nem igényelnek megosztott (S) zárolást pillanatkép-elkülönítési szinten. Emellett a pillanatkép-elkülönítés tranzakciószintű konzisztenciát biztosít egy explicit többutas tranzakció összes utasításához. Előfordulhat, hogy a pillanatképek elkülönítése már engedélyezve van az adatbázisban. A pillanatkép-elkülönítés használható módosításokat végrehajtó lekérdezésekhez is, de a frissítési ütközéseket kezelnie kell.

    Ha olyan hosszú ideig futó lekérdezése van, amely blokkolja a többi felhasználót, és nem optimalizálható, érdemes lehet áthelyezni egy OLTP-környezetből egy dedikált jelentéskészítő rendszerbe, amely az adatbázis szinkron írásvédett replikája.

  2. Nem véglegesített tranzakcióval rendelkező alvó SPID által okozott blokkolás

    Ezt a típusú blokkolást gyakran azonosíthatja egy olyan SPID, amely alvó vagy parancsra vár, de amelynek tranzakciós beágyazási szintje (@@TRANCOUNTopen_transaction_counta forrásbólsys.dm_exec_requests) nagyobb, mint nulla. Ez akkor fordulhat elő, ha az alkalmazás időtúllépést tapasztal a lekérdezésnél, vagy a szükséges számú VISSZAÁLLÍTÁSi és/vagy VÉGLEGESítési utasítás kiadása nélkül is leállít egy megszakítást. Ha egy SPID időtúllépést vagy megszakítást kap a lekérdezéshez, az leállítja az aktuális lekérdezést és köteget, de nem hajtja végre automatikusan a tranzakciót. Ezért az alkalmazás a felelős, mivel az Azure SQL Database nem feltételezheti, hogy egy teljes tranzakciót vissza kell vonni egyetlen lekérdezés megszakítása miatt. A lekérdezés időtúllépése vagy megszakítása a kiterjesztett esemény munkamenetében az SPID FIGYELEM jeleseményeként jelenik meg.

    A nem véglegesített explicit tranzakció bemutatásához adja ki a következő lekérdezést:

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

    Ezután hajtsa végre ezt a lekérdezést ugyanabban az ablakban:

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

    A második lekérdezés kimenete azt jelzi, hogy a tranzakció beágyazási szintje egy. A tranzakcióban beszerzett összes zárolást a rendszer a tranzakció véglegesítése vagy visszagördítése előtt is megtartja. Ha az alkalmazások kifejezetten megnyitnak és véglegesítenek tranzakciókat, egy kommunikáció vagy egyéb hiba nyitott állapotban hagyhatja el a munkamenetet és annak tranzakcióját.

    A jelen cikk korábbi, nem véglegesített tranzakcióinak azonosítására szolgáló sys.dm_tran_active_transactions szkript használata a példányon belül.

    Megoldások:

    • Emellett a blokkolási probléma ezen osztálya teljesítményproblémát is okozhat, és ennek megfelelően kell folytatnia. Ha a lekérdezés végrehajtási ideje csökkenthető, a lekérdezés időtúllépése vagy megszakítása nem fordul elő. Fontos, hogy az alkalmazás képes legyen kezelni az időtúllépési vagy megszakítási forgatókönyveket, ha felmerülnek, de a lekérdezés teljesítményének vizsgálata is előnyös lehet.

    • Az alkalmazásoknak megfelelően kell kezelniük a tranzakcióbe ágyazási szinteket, vagy blokkolhatják a lekérdezés ilyen módon történő törlését követően. Figyelmébe ajánljuk a következő információkat:

      • Az ügyfélalkalmazás hibakezelőjében akkor is végrehajthat IF @@TRANCOUNT > 0 ROLLBACK TRAN bármilyen hibát, ha az ügyfélalkalmazás nem hiszi, hogy egy tranzakció meg van nyitva. A nyitott tranzakciók ellenőrzésére azért van szükség, mert a köteg során meghívott tárolt eljárás az ügyfélalkalmazás tudta nélkül is elindíthatott volna egy tranzakciót. Bizonyos feltételek, például a lekérdezés megszakítása, megakadályozzák, hogy az eljárás az aktuális utasításon túl futjon, így még ha az eljárás logikája is van a tranzakció ellenőrzésére IF @@ERROR <> 0 és megszakítására, ez a visszaállítási kód ilyen esetekben nem lesz végrehajtva.
      • Ha a kapcsolatkészletezést egy olyan alkalmazásban használják, amely megnyitja a kapcsolatot, és kis számú lekérdezést futtat, mielőtt a kapcsolatot visszaengedné a készletbe( például egy webalapú alkalmazásba), a kapcsolatkészletezés ideiglenes letiltása segíthet enyhíteni a problémát, amíg az ügyfélalkalmazást nem módosítják a hibák megfelelő kezelése érdekében. A kapcsolatkészletezés letiltásával a kapcsolat felszabadítása az Azure SQL Database-kapcsolat fizikai leválasztását eredményezi, ami azt eredményezi, hogy a kiszolgáló visszaállítja a nyitott tranzakciókat.
      • Használja SET XACT_ABORT ON a kapcsolathoz, vagy bármely tárolt eljáráshoz, amely tranzakciókat kezd, és nem tisztítja meg a hibát. Futásidejű hiba esetén ez a beállítás megszakítja a nyitott tranzakciókat, és visszaadja az irányítást az ügyfélnek. További információ: SET XACT_ABORT (Transact-SQL).

    Megjegyzés:

    A kapcsolat csak akkor lesz visszaállítva, ha a kapcsolatkészletből újra felhasználják, így lehetséges, hogy a felhasználó megnyithat egy tranzakciót, majd feloldhatja a kapcsolatot a kapcsolatkészlethez, de előfordulhat, hogy a kapcsolat több másodpercig nem lesz újra felhasználva, ami alatt a tranzakció nyitva marad. Ha a kapcsolat nem lesz újra felhasználva, a tranzakció megszakad, amikor a kapcsolat túllépi az időkorlátot, és eltávolítja a kapcsolatkészletből. Így optimális, ha az ügyfélalkalmazás megszakítja a tranzakciókat a hibakezelőben, vagy használja SET XACT_ABORT ON ezt a lehetséges késést.

    Vigyázat!

    A következő SET XACT_ABORT ONT-SQL-utasítások nem lesznek végrehajtva egy hibát okozó utasítást követően. Ez hatással lehet a meglévő kód tervezett folyamatára.

  3. Olyan SPID által okozott blokkolás, amelynek megfelelő ügyfélalkalmazása nem tudta beolvasni az összes eredménysort a befejezéshez

    A lekérdezés kiszolgálónak való elküldése után az összes alkalmazásnak azonnal le kell kérnie az összes eredménysort. Ha egy alkalmazás nem kéri le az összes eredménysort, a zárolások megmaradhatnak a táblákon, és letilthatja a többi felhasználót. Ha olyan alkalmazást használ, amely transzparens módon küldi el az SQL-utasításokat a kiszolgálónak, az alkalmazásnak le kell kérnie az összes eredménysort. Ha nem (és nem konfigurálható erre), előfordulhat, hogy nem tudja megoldani a blokkolási problémát. A probléma elkerüléséhez a helytelenül viselkedő alkalmazásokat a fő OLTP-adatbázistól elkülönített jelentéskészítési vagy döntéstámogatási adatbázisra korlátozhatja.

    Ennek a forgatókönyvnek a hatása csökken, ha a végrehajtás utáni olvasás pillanatképe engedélyezve van az adatbázison, ami az alapértelmezett beállítás az Azure SQL Database-ben. További információ a cikk blokkolási szakaszában.

    Megjegyzés:

    Útmutatás az Azure SQL Database-hez csatlakozó alkalmazások újrapróbálkozási logikájának használatához.

    Megoldás: Az alkalmazást újra kell írni az eredmény összes sorának befejezéséhez. Ez nem zárja ki az OFFSET és a FETCH használatát egy lekérdezés ORDER BY záradékában a kiszolgálóoldali lapozás végrehajtásához.

  4. Visszaállítási állapotú munkamenet által okozott blokkolás

    A kilLed vagy a felhasználó által definiált tranzakción kívül megszakított adatmódosítási lekérdezések vissza lesznek állítva. Ez az ügyfél hálózati munkamenetének leválasztásának mellékhatása is lehet, vagy ha egy kérés holtpontként van kiválasztva. Ez gyakran azonosítható a ROLLBACK parancs kimenetének sys.dm_exec_requestsmegfigyelésével, és az percent_complete oszlop előrehaladását is jelezheti.

    A 2019-ben bevezetett Gyorsított adatbázis-helyreállítás funkciónak köszönhetően a hosszú visszaállításoknak ritkáknak kell lenniük.

    Megoldás: Várja meg, amíg az SPID befejezi a módosítások visszaállítását.

    A helyzet elkerülése érdekében ne végezzen nagy kötegírási műveleteket, indexlétrehozási vagy karbantartási műveleteket az OLTP-rendszerek forgalmas idején. Ha lehetséges, hajtsa végre ezeket a műveleteket alacsony tevékenységi időszakokban.

  5. Árva kapcsolat által okozott blokkolás

    Ha az ügyfélalkalmazás csapdába ejti a hibákat, vagy az ügyfél-munkaállomás újraindul, előfordulhat, hogy bizonyos feltételek mellett a kiszolgáló hálózati munkamenete nem lesz azonnal megszakítva. Az Azure SQL Database szempontjából az ügyfél továbbra is jelen van, és a megszerzett zárolások továbbra is megmaradhatnak. További információ: Az árva kapcsolatok hibaelhárítása az SQL Serveren.

    Megoldás: Ha az ügyfélalkalmazás az erőforrások megfelelő tisztítása nélkül megszakadt, a parancs használatával leállíthatja az KILL SPID-t. A KILL parancs bemenetként a SPID értéket veszi fel. Az SPID 99 futtatásához például adja ki a következő parancsot:

    KILL 99

Kapcsolódó információk

További lépések