Holtpontok elemzése és megakadályozása az Azure SQL Database-ben és az SQL Database-ben a Fabricben

A következőkre vonatkozik:Azure SQL DatabaseSQL Database a Fabricben

Ez a cikk bemutatja, hogyan azonosíthatja a holtpontokat, holtpont-gráfokat és lekérdezéstárat használhat a holtpont lekérdezéseinek azonosításához, és hogyan tervezheti meg és tesztelheti a módosításokat a holtpontok újbóli kiváltásának megakadályozása érdekében. Ez a cikk az Azure SQL Database-hez és a Fabricben található SQL Database-hez vonatkozik, amely az Azure SQL Database számos funkcióját osztja meg.

Ez a cikk a zárolási versengés miatti holtpontok azonosítására és elemzésére összpontosít. Tudjon meg többet az holtpontot okozható holtpontok más típusairól azerőforrások között.

Holtpontok előfordulása

Az alapértelmezés szerint az Azure SQL Database minden új adatbázisában engedélyezve van a Read Committed Snapshot Isolation (RCSI) adatbázis-beállítás. A munkamenetek adatainak olvasása és az adatok írása közötti blokkolása 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 szinteket ügyfélkönyvtár metódusaival, lekérdezési tippekkel, vagy a Transact-SQL-ben a TRANZAKCIÓ ELKÜLÖNÍTÉSI SZINT beállításával adhatja meg.

• Az RCSI letiltva lehet, ami miatt az adatbázis megosztott (S) zárolásokat használhat, hogy megvédje a SELECT utasításokat, amikor azok az olvasási közben lekötött elkülönítési szinten futnak. Ez növelheti a blokkolást és a holtpontokat.

Egy példa holtpontra

Holtpont akkor fordul elő, ha két vagy több tevékenység véglegesen letiltja egymást, mert minden tevékenység zárolt egy erőforrást, a másik tevékenység pedig zárolni próbál. A holtpontot ciklikus függőségnek is nevezik: kétfeladatos holtpont esetén az A tranzakció függ a B tranzakciótól, a B tranzakció pedig az A tranzakciótól való függőség révén zárja be a kört.

Például:

1. A munkamenet elindít egy explicit tranzakciót, és futtat egy frissítési utasítást, amely megszerez egy frissítési (U) zárolást a SalesLT.Product tábla egy során, amelyet kizárólagos (X) zárolássá alakít.

2. B munkamenet a SalesLT.ProductDescription táblát módosító frissítési utasítást futtat. A frissítési utasítás a SalesLT.Product táblához csatlakozva megkeresi a megfelelő frissítendő sorokat.

   • B munkamenet a SalesLT.ProductDescription tábla 72 sorára szerez frissítési zárolást (U).

   • A B munkamenet megosztott zárolást igényel a tábla SalesLT.Productsoraiban, beleértve az A munkamenetáltal zárolt sort is. A B munkamenet blokkolva van SalesLT.Product.

3. Az A munkamenet folytatja a tranzakciót, és most egy frissítést futtat a SalesLT.ProductDescription táblán. A munkamenet munkamenet B által van blokkolva .

A holtponton lévő összes tranzakció határozatlan ideig várakozik, kivéve, ha az egyik részt vevő tranzakció vissza lesz állítva, például azért, mert a munkamenete leállt.

Az adatbázismotor holtpont-figyelője rendszeresen ellenőrzi a holtponton lévő feladatokat. Ha a holtpont-figyelő ciklikus függőséget észlel, az áldozatként kiválasztja az egyik feladatot, és 1205-ös hibával leállítja a tranzakciót: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. A holtpont ily módon történő feltörése lehetővé teszi a holtponton lévő többi tevékenységnek vagy tevékenységnek a tranzakciók végrehajtását.

Jegyzet

További információ a holtpont-áldozat kiválasztásának feltételeiről ennek a cikknek a Holtpont-folyamatlista részében.

A holtpont áldozataként kiválasztott tranzakciót tartalmazó alkalmazásnak újra meg kell próbálkoznia a tranzakcióval, amely általában a holtponton lévő többi tranzakció vagy tranzakció befejezése után fejeződik be.

Ajánlott rövid, véletlenszerű késleltetést bevezetni, mielőtt újra próbálkozik, hogy elkerülje ugyanazt a holtpontot. További információ arról, hogyan tervezhet újrapróbálkozási mechanizmust átmeneti hibák esetén .

Alapértelmezett elkülönítési szint az Azure SQL Database-ben

Az Azure SQL Database új adatbázisai alapértelmezés szerint engedélyezik az Olvasási Kötelezett Kép (RCSI) funkciót. Az RCSI módosítja a leolvasott lekötött elkülönítési szint viselkedését, hogy sorverziós használva utasításszintű konzisztenciát biztosítson a SELECT utasítások megosztott (S) zárolása nélkül.

RCSI engedélyezve:

• Az adatokat olvasó utasítások nem tiltják le az adatokat módosító utasításokat.
• Az adatokat módosító utasítások nem tiltják le az adatokat olvasó utasításokat.

Pillanatkép-elkülönítési szint 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 SNAPSHOTkell beállítaniuk a tranzakcióelkülönítési szintet. Ez csak akkor végezhető el, 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, a is_read_committed_snapshot_on oszlop a 1értéket adja vissza. Ha a pillanatkép-elkülönítés engedélyezve van, a snapshot_isolation_state_desc oszlop a ONértéket adja vissza.

Ha RCSI le van tiltva egy Azure SQL Database-adatbázishoz, vizsgálja meg, hogy az RCSI miért lett letiltva az újbóli engedélyezés előtt. Az alkalmazáskód arra számíthat, hogy az adatokat író lekérdezések blokkolni fogják az adatokat olvasó lekérdezéseket, ami az RCSI engedélyezése esetén helytelen eredményeket eredményez a versenyfeltételekből.

Holtpont-események értelmezése

Holtpont eseményt ad ki, miután az Azure SQL Database holtpontkezelője holtpontot észlel, és kiválaszt egy tranzakciót áldozatként. Más szóval, ha beállítja a holtpontokra vonatkozó riasztásokat, az értesítés egy adott holtpont feloldása után aktiválódik. Ehhez a zárlathoz a felhasználónak nincs szüksége semmilyen műveletre. Az alkalmazásokat úgy kell megírni, hogy újrapróbálkozási logikával rendelkezzenek, így az 1205-ös hiba beérkezése után automatikusan folytatódjanak:

Érdemes azonban riasztásokat beállítani, mivel a holtpontok újra megjelenhetnek. A holtpont-riasztások lehetővé teszik annak vizsgálatát, hogy ismétlődő holtpontok mintája történik-e az adatbázisban, ebben az esetben dönthet úgy, hogy műveletet hajt végre a holtpontok újbóli kiújulásának megakadályozása érdekében. Tudjon meg többet a riasztásokról a cikk Monitor és riasztás holtpontokon című szakaszában.

A holtpontok megelőzésére szolgáló leggyakoribb módszerek

A holtpontok újra előfordulásának megakadályozására a legalacsonyabb kockázati módszer általában a nem klaszteresített indexek finomhangolása a holtpontban lévő lekérdezések optimalizálása érdekében.

• Ennek a módszernek a kockázata alacsony, mert a nemclustered indexek finomhangolása nem igényel módosításokat magában a lekérdezési kódban, ami csökkenti a felhasználói hibák kockázatát a helytelen adatok felhasználónak való visszaadását eredményező Transact-SQL újraírásakor.

• Az index hatékony finomhangolásával a lekérdezések hatékonyabban találják meg az adatokat az olvasáshoz és a módosításhoz. A lekérdezések által elérni kívánt adatok mennyiségének csökkentésével csökken a blokkolás valószínűsége, és a holtpontok gyakran megelőzhetők.

Bizonyos esetekben a fürtözött indexek létrehozása vagy finomhangolása csökkentheti a blokkolást és a holtpontokat. Mivel a fürtözött index minden nemclustered indexdefinícióban szerepel, a fürtözött indexek létrehozása vagy módosítása IO-igényes és időigényes művelet lehet a meglévő nemclustered indexekkel rendelkező nagyobb táblákon. Tudjon meg többet a fürtözött index tervezési irányelveiről.

Ha az indexhangolás nem sikerül megelőzni a holtpontokat, más módszerek is elérhetők:

• Ha a holtpont csak akkor fordul elő, ha egy adott tervet választ ki a holtpontban érintett lekérdezések egyikéhez, a lekérdezési terv lekérdezéstárral való kényszerítése megakadályozhatja a holtpontok újbóli előfordulását.

• A holtpontban lévő egy vagy több tranzakció Transact-SQL újraírása szintén segíthet megelőzni a holtpontokat. Az explicit tranzakciók kisebb tranzakciókra való lebontása gondos kódolást és tesztelést igényel az adatok érvényességének biztosítása érdekében, ha egyidejű módosítások történnek.

A jelen cikk szakaszában található Holtpont újbóli kiújulásának megakadályozása című témakörben talál további információt ezekről a megközelítésekről.

Figyelés és riasztás holtpontokon

Ebben a cikkben a AdventureWorksLT mintaadatbázis használatával állítunk be riasztásokat holtpontokhoz, bemutatunk egy példaholtpontot, elemezzük a példaként bemutatott holtpont gráfját, és teszteljük a módosításokat, hogy megakadályozzuk az újbóli holtpont kialakulását.

Ebben a cikkben az SQL Server Management Studio (SSMS) ügyfelet használjuk, mivel a holtpontdiagramok interaktív vizualizációs módban való megjelenítésére szolgáló funkciókat tartalmaz. A példák követéséhez használhat más ügyfeleket, például a Visual Studio Code MSSQL-bővítményét, az sqlcmdet vagy a kedvenc Transact-SQL lekérdezési eszközt, de előfordulhat, hogy csak XML-ként tekintheti meg a holtpontdiagramokat.

Az AdventureWorksLT-adatbázis létrehozása

A példák követéséhez hozzon létre egy új adatbázist az Azure SQL Database-ben, és válassza Minta adatokat adatforrásként.

Az Azure Portallal, az Azure CLI-vel vagy a PowerShell-lel való létrehozással AdventureWorksLT kapcsolatos részletes útmutatásért válassza ki a választott megközelítést a rövid útmutatóban: Egyetlen adatbázis létrehozása az Azure SQL Database-ben.

Holtpont-riasztások beállítása az Azure Portalon

A holtponti eseményekre vonatkozó riasztások beállításához kövesse az Azure SQL Database-hez és az Azure Synapse Analyticshez készült riasztások létrehozása az Azure Portalon című cikk lépéseit.

Válassza holtpontok a riasztás jelneveként. Konfigurálja a műveletcsoportot, hogy a választott módszerrel értesítse Önt, például a E-mail/SMS/Leküldés/Hang művelettípust.

Holtpontdiagramok gyűjtése az Azure SQL Database-ben kiterjesztett eseményekkel

A holtpont-gráfok gazdag információforrást jelentenek a holtpontban lévő folyamatokkal és zárolásokkal kapcsolatban. Az Azure SQL-adatbázisban és a Fabric SQL-adatbázisban a holtpontdiagramok gyűjtéséhez kiterjesztett eseményekkel (XEvents) rögzítse a sqlserver.database_xml_deadlock_report eseményt.

Az XEvents segítségével holtpontdiagramokat gyűjthet a gyűrűpuffer célhely vagy egy eseményfájl célhelyhasználatával. A megfelelő céltípus kiválasztásával kapcsolatos szempontokat az alábbi táblázat foglalja össze:

Megközelítés	Előnyök	Megfontolások	Használati forgatókönyvek
Gyűrűpuffer-cél	- A beállítás egyszerű, csak Transact-SQL szükséges.	– Az eseményadatok törlődnek, ha az XEvents-munkamenet bármilyen okból le van állítva, például az adatbázis offline állapotba helyezése vagy egy adatbázis feladatátvétele. – Az adatbázis-erőforrások a gyűrűpufferben lévő adatok karbantartására és a munkamenet-adatok lekérdezésére szolgálnak.	– Minta nyomkövetési adatok gyűjtése teszteléshez és tanuláshoz. – Hozzon létre megoldásokat rövid távú igényeihez, ha nem tud egy eseményfájl-cél segítségével azonnal munkamenetet beállítani. - A célzónaként szolgálhat a nyomkövetési adatokhoz, amikor automatizált folyamatot állított fel a nyomkövetési adatok táblába való megőrzésére.
Eseményfájl célpont	– Megőrzi az eseményadatokat egy blobban az Azure Storage-ban, így az adatok a munkamenet leállítása után is elérhetők lesznek. – Az eseményfájlok letölthetők az Azure Portalról, vagy Az Azure Storage Explorer és elemezhetők helyileg, ami nem igényel adatbázis-erőforrásokat a munkamenet-adatok lekérdezéséhez.	– A beállítás összetettebb, és azure Storage-tároló és adatbázis-hatókörű hitelesítő adatok konfigurálását igényli.	– Általános használat, ha azt szeretné, hogy az eseményadatok az esemény munkamenetének leállása után is megmaradjanak. – Olyan nyomkövetést szeretne futtatni, amely nagyobb mennyiségű eseményadatot hoz létre, mint amennyit a memóriában meg szeretne őrizni.

Válassza ki a használni kívánt céltípust:

gyűrűpuffer célja

A gyűrűpuffer célpont kényelmes és könnyen beállítható, de korlátozott kapacitással rendelkezik, ami a régebbi események elvesztését okozhatja. A gyűrűpuffer nem tárolja az eseményeket a tárolóban, és a gyűrűpuffer-cél törlődik az XEvents-munkamenet leállításakor. Ez azt jelenti, hogy az összegyűjtött XEvents nem érhető el, amikor az adatbázismotor bármilyen okból újraindul, például feladatátvételkor. A gyűrűpuffer célobjektum a legjobban a tanulási és rövid távú igények kielégítésére alkalmas, ha nincs lehetősége azonnal beállítani egy XEvents-munkamenetet egy eseményfájl célpontjához.

Ez a mintakód létrehoz egy XEvents-munkamenetet, amely a memória holtpont-gráfjait rögzíti a gyűrű puffercéljánakhasználatával. A gyűrűpuffer-cél számára engedélyezett maximális memória 4 MB, és a munkamenet automatikusan fut, amikor az adatbázis online állapotba kerül, például feladatátvétel után.

Ha létre szeretne hozni egy XEvents-munkamenetet az sqlserver.database_xml_deadlock_report eseményhez, amely a gyűrűpuffer-tárolóba ír, csatlakozzon az adatbázishoz, és futtassa a következő Transact-SQL-t:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Eseményfájl célpont

Az eseményfájl-cél a holtpontdiagramokat a fájlokon megőrzi, így azok az XEvents-munkamenet leállítása után is elérhetők. Az eseményfájl-cél lehetővé teszi több holtpont-gráf rögzítését anélkül, hogy további memóriát biztosítanának egy gyűrűpuffer számára. Az eseményfájl-cél hosszú távú használatra és nagyobb mennyiségű nyomkövetési adat gyűjtésére alkalmas.

Egy eseményfájl-célba írható XEvents-munkamenet létrehozásához a következőt kell létrehoznunk:

1. Konfiguráljon egy Azure Storage-tárolót a nyomkövetési fájlok tárolásához az Azure Portal használatával.
2. Adatbázis-hatókörű hitelesítő adatok létrehozása a Transact-SQL használatával.
3. Hozza létre az XEvents-munkamenetet a Transact-SQL használatával.

Azure Storage-tároló konfigurálása

Egy Azure Storage-tároló konfigurálásához először hozzon létre vagy válasszon ki egy meglévő Azure Storage-fiókot, majd hozza létre a tárolót. Hozzon létre egy megosztott hozzáférési aláírással (SAS) rendelkező tokent a tárolóhoz. Ez a szakasz a folyamat Azure Portalon történő elvégzését ismerteti.

Jegyzet

Ha szeretné létrehozni és konfigurálni az Azure Storage blobtárolót a PowerShell-lel, tekintse meg a Eseménymunkamenet létrehozása event_file céltárolóval az Azure Storage-ban című témakört. Másik megoldásként célszerű lehet az Azure Storage Explorer használatával létrehozni és konfigurálni az Azure Storage blobtárolót az Azure Portal használata helyett.

Azure Storage-fiók létrehozása vagy kiválasztása

Használhat egy meglévő Azure Storage-fiókot, vagy létrehozhat egy új Azure Storage-fiókot a nyomkövetési fájlok tárolójának üzemeltetéséhez.

Meglévő Azure Storage-fiók használata:

1. Lépjen a használni kívánt erőforráscsoportra az Azure Portalon.
2. Az Áttekintés panelen, az Erőforrásokszakaszban állítsa a Típus legördülő listát a tárfiókértékre.
3. Válassza ki a használni kívánt tárfiókot.

Új Azure Storage-fiók létrehozásához kövesse Azure Storage-fiók létrehozásalépéseit. A folyamat befejezéséhez válassza az utolsó lépésben az Az erőforrás megnyitása lehetőséget.

Tároló létrehozása

Az Azure Portal tárfiók lapján:

1. Az Adattárolásicsoportban válassza Tárolóklehetőséget.
2. Új tároló létrehozásához válassza a + Tároló lehetőséget. Megjelenik az Új tároló panel.
3. Adja meg a tároló nevét a Névterületen.
4. Válassza a Létrehozás lehetőséget.
5. A létrehozás után válassza ki a tárolót a listából.

Megosztott hozzáférési jogkivonat létrehozása

Az Azure Portál konténeroldalán:

1. A Beállításokterületen válassza a Megosztott hozzáférési jogkivonatoklehetőséget.

2. Hagyja az aláírási mód választógombot az alapértelmezett kiválasztáson, a fiókkulcs-on.

3. Az Engedélyek legördülő listában válassza ki a Olvasási, Írásiés Lista engedélyeket.

4. Állítsa Start a nyomkövetési fájlok írásához használni kívánt dátumra és időpontra. Igény szerint konfigurálja az időzónát az alábbi legördülő listában Start.

5. Állítsa be a lejárati dátumát és időpontját arra, amikor szeretné, hogy ezek az engedélyek lejárjanak. Igény szerint konfigurálja az időzónát az alábbi legördülő listában Lejárat. Ezt a dátumot a jövőben is beállíthatja, például 10 évre, ha szeretné.

6. Válassza SAS-jogkivonat és URL-cím létrehozásalehetőséget. A Blob SAS-jogkivonat és a Blob SAS URL-címe megjelenik a képernyőn.

7. Másolja és őrizze meg a Blob SAS-jogkivonat és a Blob SAS URL értékeit a további lépésekben való használatra.

Adatbázis-hatókörű hitelesítő létrehozása

Az alábbi lépések futtatásához csatlakozzon az adatbázishoz az SSMS használatával.

Adatbázis-hatókörű hitelesítő adatok létrehozásához először létre kell hoznia egy adatbázis-főkulcsot (DMK) az adatbázisban, ha még nem létezik.

Futtassa a következő Transact-SQL parancsot egy DMK létrehozásához, ha még nem létezik.

IF 0 = (SELECT COUNT(*)
        FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101
              AND name = N'##MS_DatabaseMasterKey##')
    BEGIN
        PRINT N'Creating master key';
        CREATE MASTER KEY;
    END
ELSE
    BEGIN
        PRINT N'Master key already exists, no action taken';
    END
GO

Ezután hozzon létre egy adatbázis-hatókörű hitelesítő adatot a következő Transact-SQL használatával. A kód futtatása előtt:

• Módosítsa az URL-címet úgy, hogy tükrözze a tárfiók nevét és a tároló nevét. Ez az URL-cím annak a Blob SAS URL-címnek az elején található, amelyet a megosztott hozzáférési jogkivonat létrehozásakor másoltál. Csak a lánc első ? előtti szövegre van szüksége.

• Módosítsa a SECRET-t úgy, hogy tartalmazza a megosztott hozzáférési token létrehozásakor másolt Blob SAS-jogkivonat értékét.

CREATE DATABASE SCOPED CREDENTIAL [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=<sig>';
GO

Az XEvents-munkamenet létrehozása

Hozza létre és indítsa el az XEvents-munkamenetet a következő Transact-SQL használatával. Az utasítás futtatása előtt:

• Cserélje le a filename értéket, hogy tükrözze a tárfiók nevét és a tároló nevét. Ez az URL-cím annak a Blob SAS URL-címnek az elején található, amelyet a megosztott hozzáférési jogkivonat létrehozásakor másoltál. Csak a lánc első ? előtti szövegre van szüksége.

• Tetszés szerint módosíthatja a tárolt fájlnevet. Az itt megadott fájlnév az eseményadatokat tároló blobok tényleges fájlneveinek része lesz: a program további értékeket fűz hozzá, hogy minden eseményfájl egyedi névvel rendelkezzen.

• Opcionálisan további eseményeket is hozzáadhat a munkamenethez.

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = 'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
)
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Holtpont okozása

Mivel az optimalizált zárolás mindig engedélyezve van az Azure SQL Database-ben és az SQL Database-ben a Fabricben, a holtpontok kevésbé valószínűek. További információkért és az optimalizált zárolással előforduló holtpontra vonatkozó példát lásd: Optimalizált zárolás és holtpontok.

Holtpontdiagramok megtekintése XEvents-munkamenetből

Ha egy XEvents-munkamenetet beállítani holtpontok gyűjtésére és holtpont a munkamenet megkezdése után következik be, megtekintheti a holtpont gráf és a holtpont gráf XML-fájljának interaktív grafikus megjelenítését.

A gyűrűpuffer cél- és eseményfájl-célhelyeinek holtpont-információinak lekérésére különböző módszerek állnak rendelkezésre. Válassza ki az XEvents-munkamenethez használt célt:

gyűrűpuffer célja

Ha beállít egy XEvents-munkamenetet, amely a gyűrűpufferbe ír, a holtpont adatait az alábbi Transact-SQL használatával kérdezheti le. A lekérdezés futtatása előtt cserélje le a @tracename értékét az XEvents-munkamenet nevére.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Eseményfájl célpont

Ha XEvents-munkamenetet állít be egy eseményfájlba való íráshoz, letöltheti a fájlokat az Azure Portalról, és megtekintheti őket helyileg, vagy lekérdezheti az eseményfájlokat a Transact-SQL-lel.

Fájlok letöltése az Azure Portalról ajánlott, mert ez a módszer nem igényel adatbázis-erőforrásokat a munkamenet-adatok lekérdezéséhez.

Igény szerint indítsa újra az XEvents-munkamenetet

Ha egy Bővített események munkamenet jelenleg fut, és egy eseményfájl-tárolóba ír, a megírt blobtároló bérletállapotúBérelt az Azure Portalon. A méret a fájl maximális mérete. Kisebb fájl letöltéséhez a fájlok letöltése előtt leállíthatja és újraindíthatja a Bővített események munkamenetet. Emiatt a fájl a bérletállapotátElérhető-ra módosítja, a fájlméret pedig a fájl eseményei által használt terület lesz.

XEvents-munkamenet leállításához és újraindításához csatlakozzon az adatbázishoz, és futtassa a következő Transact-SQL-t. A kód futtatása előtt cserélje le az XEvents-munkamenet nevét a megfelelő értékre.

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = STOP;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Nyomkövetési fájlok letöltése az Azure Portalról

A több fájlban összegyűjtött holtponti események megtekintéséhez töltse le az esemény munkamenetfájljait a helyi számítógépre, és tekintse meg a fájlokat az SSMS-ben.

Jegyzet

Az Azure Storage Explorer használatával gyorsan és kényelmesen letöltheti a munkamenetfájlokat az Azure Storage blobtárolójából.

A fájlok letöltése az Azure Portalról:

1. Lépjen a tárolót üzemeltető tárfiókra az Azure Portalon.
2. Az Adattárolásicsoportban válassza Tárolóklehetőséget.
3. Válassza ki azt a tárolót, amely az XEvent-nyomkövetési fájlokat tartalmazza.
4. Minden letölteni kívánt fájlhoz válassza a ..., majd Letöltéslehetőséget.

XEvents-nyomkövetési fájlok megtekintése az SSMS-ben

Ha több fájlt is letölt, az SSMS XEvents megjelenítőjében az összes fájlból megnyithatja az eseményeket. Ehhez tegye a következőt:

1. Nyissa meg az SSMS-eket.
2. Válassza Fájl, majd Megnyitás, majd Bővített események fájljainak egyesítése....
3. Válassza a , majd adja hozzá aelemet.
4. Arra a könyvtárra navigált, ahol letöltötte a fájlokat. Több fájl kijelöléséhez használja a Shift billentyűt.
5. Válassza a lehetőséget, majd nyissa meg aelemet.
6. Válassza az OK lehetőséget a Bővített események fájljainak egyesítése párbeszédpanelen.

Ha egyetlen fájlt töltött le, kattintson a jobb gombbal a fájlra, és válassza a Megnyitás, majd SSMSlehetőséget. Ezzel megnyitja az XEvents-megjelenítőt az SSMS-ben.

Navigálás az összegyűjtött események között a megfelelő időbélyeg kiválasztásával. A holtpont XML-fájljának megtekintéséhez kattintson duplán az alsó panel xml_report sorára.

Nyomkövetési fájlok lekérdezése Transact-SQL

Fontos

A nagy (1 GB-os és nagyobb) XEvents nyomkövetési fájlok lekérdezése ezzel a módszerrel nem ajánlott, mert nagy mennyiségű memóriát használ az adatbázisban vagy a rugalmas készletben.

Ha XEvents-nyomkövetési fájlokat szeretne lekérdezni egy Azure Storage-tárolóból a Transact-SQL használatával, meg kell adnia a nyomkövetési fájl pontos fájlnevét. A lekérdezést az adatbázis környezetében is futtatnia kell a hitelesítő adatokkal a tároló eléréséhez, vagyis ugyanazt az adatbázist, amely létrehozta az XEvents-fájlokat.

Az alábbi Transact-SQL parancs futtatásával lekérdezheti a jelenleg aktív XEvents nyomkövetési fájlt. A lekérdezés futtatása előtt cserélje le @tracename az XEvents-munkamenet nevére.

DECLARE @tracename AS sysname = N'deadlocks_eventfile', @filename AS NVARCHAR (2000);

WITH eft
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'event_file')
SELECT @filename = ft.evtdata.value('(@name)[1]', 'nvarchar(2000)')
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') AS ft(evtdata);

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

A nem aktív fájlok lekérdezéséhez lépjen a tárfiókra és tárolóra az Azure Portalon a fájlnevek azonosításához.

Futtassa az alábbi Transact-SQL lekérdezést az adatbázison egy adott XEvents-fájl lekérdezéséhez. A lekérdezés futtatása előtt cserélje le a tárfiók nevét, tárolónevét és fájlnevét a @filenameURL-címében:

DECLARE @filename AS NVARCHAR (2000) = N'https://<storage-account>.blob.core.windows.net/yourcontainername/yourfilename.xel';

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Holtpontdiagram megtekintése és mentése XML-ben

A holtpontdiagram XML-formátumban való megtekintése lehetővé teszi a holtponton lévő Transact-SQL utasítások inputbuffer másolását. A holtpontok szöveges formátumban is elemezhetők.

Ha egy Transact-SQL lekérdezést használt a holtpont gráf adatainak visszaadásához, a holtpont gráf XML-jének megtekintéséhez jelölje ki a deadlock_xml oszlopban lévő értéket bármelyik sorból, és nyissa meg a holtpont gráf XML-fájlját egy új ablakban az SSMS-ben.

A példa holtponti gráf XML-je a következő:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

A holtpont gráf mentése XML-fájlként:

1. Válassza Fájl és Mentés másként....
2. Hagyja a Mentés típusként beállítást az alapértelmezett XML-fájlok (*.xml) értékén
3. Állítsa be a fájlnevet a választott névre.
4. Válassza Mentéslehetőséget.

Holtpontdiagram mentése XDL-fájlként, amely interaktív módon jeleníthető meg az SSMS-ben

A holtpontdiagram interaktív ábrázolásának megtekintése hasznos lehet a holtponthoz tartozó folyamatok és erőforrások gyors áttekintéséhez és a holtpont áldozatának gyors azonosításához.

Holtpontdiagram mentése olyan fájlként, amelyet az SSMS grafikusan megjeleníthet:

1. Válassza ki a deadlock_xml oszlopban lévő értéket bármelyik sorból a holtpont gráf XML-fájljának megnyitásához az SSMS új ablakában.

2. Válassza Fájl és Mentés másként....

3. Állítsa a Fájltípus mentése beállítást Minden fájlra.

4. Állítsa a fájlnevet a kívánt névre, a bővítmény pedig .xdl.

5. Válassza Mentéslehetőséget.

   

6. Zárja be a fájlt úgy, hogy az ablak tetején található fülön kiválasztja a X ikont, vagy válassza a Fájllehetőséget, majd kattintson a Bezárásgombra.

7. Nyissa meg újra a fájlt az SSMS-ben a Fájl, majd a Megnyitás, és végül ismét a Fájllehetőség kiválasztásával. Válassza ki a .xdl kiterjesztéssel mentett fájlt.

   A holtpont gráf mostantól megjelenik az SSMS-ben a holtpontban érintett folyamatok és erőforrások vizuális ábrázolásával.

   

Holtpont elemzése az Azure SQL Database-hez

A holtpontdiagram általában három csomóponttal rendelkezik:

• Áldozatlista. A holtponti áldozat folyamatazonosítója.

• Folyamatlista. Információ a holtponthoz kapcsolódó összes folyamatról. A holtpontdiagramok a "folyamat" kifejezést használják egy tranzakciót futtató munkamenet ábrázolására.

• erőforráslista. Információ a holtponthoz kapcsolódó erőforrásokról.

Holtpont elemzésekor érdemes végiglépkedni ezeken a csomópontokon.

Holtpont áldozatlistája

A holtpont áldozatlistáján látható a holtpont áldozataként kiválasztott folyamat. A holtpont gráf vizuális ábrázolásán a folyamatokat oválisak jelölik. A holtpont áldozati folyamata egy ovális fölött áthúzott "X"-el rendelkezik.

A holtpont gráf XML-nézetében a victim-list csomópont megadja a holtpont áldozataként szolgáló folyamat azonosítóját.

A példánkban a holtpont áldozataként a folyamatazonosító process24756e75088szerepel. Ezt az azonosítót használhatjuk a folyamatlista és az erőforráslista csomópontjának vizsgálatakor, hogy többet megtudjunk az áldozati folyamatról, valamint a zárolt vagy zárolást kérő erőforrásokról.

Holtponti folyamatok listája

A holtpont folyamatlistája gazdag információforrás a holtponton lévő tranzakciókról.

A holtpont gráf grafikus ábrázolása csak a holtpont gráf XML-jében található információk egy részét jeleníti meg. A holtpont gráfban lévő oválisak képviselik a folyamatot, és az alábbi információkat jelenítik meg:

• Munkamenet-azonosító, más néven SPID.

• A munkamenet holtpont prioritása. Ha két munkamenet eltérő holtpont-prioritással rendelkezik, az alacsonyabb prioritású munkamenet lesz a holtpont áldozata. Ebben a példában mindkét munkamenetnek ugyanaz a holtponti prioritása.

• A munkamenet által használt tranzakciónapló mennyisége bájtban. Ha mindkét munkamenet azonos holtpont-prioritással rendelkezik, a holtpont figyelője azt a munkamenetet választja ki, amelynek a visszaállítása kevésbé költséges holtpont-áldozatként. A költségeket az egyes tranzakciók adott pontjára írt naplóbájtok számának összehasonlítása határozza meg.

  A példánkban a session_id 89 kevesebb tranzakciónaplót használt, és holtpont áldozataként lett kiválasztva.

Emellett az egérmutatót az egyes folyamatokra húzva megtekintheti a bemeneti puffert, amely az egyes munkamenetekben a holtpont előtti utolsó utasítást tartalmazza. A bemeneti puffer eszköztippben jelenik meg.

További információk érhetők el a holtpont gráf XML-nézetében található folyamatokról, beleértve a következőket:

• A munkamenet adatainak azonosítása, például az ügyfélnév, a gazdagép neve és a bejelentkezési név.

• Az egyes munkamenetek által a holtpont előtt futtatott utolsó utasítás lekérdezési tervének kivonata. A lekérdezésterv kivonata akkor hasznos, ha további információkat kér le a lekérdezésről Lekérdezéstár.

A példánkban holtpont:

• Láthatjuk, hogy mindkét munkamenet az SSMS-ügyféllel futott a chrisqpublic bejelentkezés alatt.

• Az elakadás áldozatának az a lekérdezési tervhez tartozó hash-kivonat, amely az utolsó utasítását tartalmazza, amelyet az elakadás előtt futtatott, 0x02b0f58d7730f798. Ennek az utasításnak a szövegét a bemeneti pufferben láthatjuk.

• A másik munkamenet által a holtpontban futtatott utolsó utasítás lekérdezéstervének hash-értéke szintén 0x02b0f58d7730f798. Ennek az utasításnak a szövegét a bemeneti pufferben láthatjuk. Ebben az esetben mindkét lekérdezés ugyanazzal a lekérdezésterv kivonattal rendelkezik, mivel a lekérdezések azonosak, kivéve az egyenlőségi predikátumként használt literális értéket.

A cikk későbbi részében ezeket az értékeket használjuk, hogy további információt találjunk a Lekérdezéstárban.

A bemeneti puffer korlátozásai a holtpont folyamatlistájában

A holtpont folyamatlistájában a bemeneti puffer információira vonatkozóan bizonyos korlátozásokat kell figyelembe venni.

Előfordulhat, hogy a lekérdezés szövege rövidült a bemeneti pufferben. A bemeneti puffer a végrehajtott utasítás első 4000 karakterére korlátozódik.

Emellett előfordulhat, hogy a holtponttal kapcsolatos néhány nyilatkozat nem szerepel a holtpont gráfjában. Példánkban A munkamenet két frissítési utasítást futtatott egyetlen tranzakción belül. A holtpont grafikonon csak a második frissítési utasítás, a holtpontot okozó frissítés szerepel. Az A munkamenet által futtatott első frissítési utasítás szerepet játszott a holtponton azáltal, hogy letiltotta B munkamenet. A query_hash által futtatott első utasítás bemeneti puffere, és kapcsolódó adatai nem szerepelnek a holtpont grafikonon.

A holtpontot okozó többutas tranzakció teljes Transact-SQL futtatásának azonosításához vagy meg kell keresnie a lekérdezést futtató tárolt eljárásban vagy az alkalmazáskódban szereplő releváns információkat, vagy le kell futtatnia egy nyomkövetést a Kiterjesztett Események használatával, hogy rögzítse a holtpont során érintett munkamenetek által futtatott teljes utasításokat. Ha a holtponthoz tartozó utasítás csonkolt, és csak részleges Transact-SQL jelenik meg a bemeneti pufferben, megtalálhatja az utasításhoz tartozó Transact-SQL a lekérdezéstárban a végrehajtási tervsegítségével.

Holtpont erőforráslista

A holtpont erőforráslistáján láthatók a holtponton lévő folyamatok által birtokolt és várt zárolási erőforrások.

Az erőforrásokat téglalapok jelölik a holtpont vizualizációjában:

Jegyzet

Az adatbázisnevek GUID-ként (uniqueidentifier) jelennek meg az Azure SQL Database-ben és a Fabricben található SQL Database-adatbázisok holtponti gráfjaiban. Ez a physical_database_name a sys.databases és a sys.dm_user_db_resource_governance dinamikus felügyeleti nézetekben felsorolt adatbázis.

Ebben a példában holtpont:

• A zsákutca áldozata, amelyet úgy említünk, mint Session A:

  • Kizárólagos (X) zárolással rendelkezik a PK_Product_ProductID tábla SalesLT.Product indexének egyik kulcsán.

  • Kér egy frissítési (U) zárolást a PK_ProductDescription_ProductDescriptionID index egyik kulcsára a SalesLT.ProductDescription táblán.

• A másik folyamat, amelyet B munkamenetnek nevezünk,:

  • A PK_ProductDescription_ProductDescriptionID tábla SalesLT.ProductDescription indexének egyik kulcsán található frissítési (U) zárolás tulajdonosa.

  • Megosztott (S) zárolást kér a PK_ProductDescription_ProductDescriptionID index egyik kulcsához a SalesLT.ProductDescription táblán.

Ugyanezeket az információkat a holtpont gráf XML-jében is láthatjuk az erőforráslista csomópontban.

Lekérdezés-végrehajtási tervek keresése a Lekérdezéstárban

Gyakran hasznos megvizsgálni a holtpontban lévő utasítások lekérdezés-végrehajtási terveit. Ezek a végrehajtási tervek gyakran megtalálhatók a Lekérdezéstárban a holtpont gráf folyamatlistájának XML nézetében lévő lekérdezésterv-kivonat használatával.

Ez a Transact-SQL lekérdezés olyan lekérdezésterveket keres, amelyek megfelelnek a példa holtponthoz talált lekérdezésterv kivonatának. Csatlakozzon a felhasználói adatbázishoz az Azure SQL Database-ben a lekérdezés futtatásához.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Előfordulhat, hogy a lekérdezés-végrehajtási tervet nem tudja lekérni a Lekérdezéstárból a Lekérdezéstár CLEANUP_POLICY vagy QUERY_CAPTURE_MODE beállításaitól függően. Ebben az esetben gyakran szükséges információkat kaphat, ha megjeleníti a lekérdezés becsült végrehajtási tervének.

Olyan minták keresése, amelyek növelik a blokkolást

A holtpontokban érintett lekérdezés-végrehajtási tervek vizsgálatakor keresse meg azokat a mintákat, amelyek hozzájárulhatnak a blokkoláshoz és a holtpontokhoz.

• Táblavizsgálatok vagy indexvizsgálatok. Ha az adatokat módosító lekérdezések az RCSI-ben futnak, a frissítendő sorok kijelölése blokkolási vizsgálattal történik, ahol az adatértékek olvasása során frissítési (U) zárolás történik az adatsoron. Ha az adatsor nem felel meg a frissítési feltételeknek, a rendszer feloldja a frissítési zárolást, és a következő sort zárolja és ellenőrzi.

  Az indexek hangolása, hogy a módosítói lekérdezések hatékonyabban találják meg a sorokat, csökkenti a kiadott frissítési zárolások számát. Ez csökkenti a blokkolás és a holtpontok esélyét.

• több táblára hivatkozó indexelt nézetek. Indexelt nézetben hivatkozott tábla módosításakor az adatbázismotornak is fenn kell tartania az indexelt nézetet. Ehhez több zárat kell kivenni, ami fokozott blokkolást és holtpontokat eredményezhet. Az indexelt nézetek azt eredményezhetik, hogy a frissítési műveletek belsőleg az elkötelezett olvasási izolációs szinten hajtódnak végre.

• Külső kulcsokra vonatkozó korlátozásokban hivatkozott oszlopok módosításai. Ha egy FOREIGN KEY kényszerben hivatkozott tábla oszlopait módosítja, az adatbázismotornak a hivatkozási táblában kell keresnie a kapcsolódó sorokat. A sorverziók nem használhatók ezekhez az olvasásokhoz. Azokban az esetekben, amikor engedélyezve vannak a kaszkádolt frissítések vagy törlések, az elkülönítési szint szerializálhatóvá válhat az utasítás időtartama alatt a fantom beszúrások elleni védelem érdekében.

• Zárolási tippek. Keresse meg táblázat tippjeit, amelyek több zárolást igénylő elkülönítési szinteket határoznak meg. Ezek a tippek közé tartozik a HOLDLOCK (ami egyenértékű a szerializálhatóval), SERIALIZABLE, READCOMMITTEDLOCK (amely letiltja az RCSI-t) és REPEATABLEREAD. Emellett az olyan tippek, mint a PAGLOCK, TABLOCK, UPDLOCKés XLOCK növelhetik a blokkolás és a holtpontok kockázatát.

  Ha ezek a tippek a helyén vannak, vizsgálja meg, hogy miért hajtották végre a tippeket. Ezek a tippek megakadályozzák a versenyfeltételeket, és biztosítják az adatok érvényességét. Lehetséges, hogy ezeket a figyelmeztetéseket helyben hagyva és a cikk Holtpont újbóli felmerülésének megakadályozása szakaszában található alternatív módszer használatával megakadályozzuk a jövőbeni holtpontok kialakulását, ha szükséges.

  Jegyzet

  Tudjon meg többet az adatok módosításának viselkedéséről a sor verziózás használatakor a Tranzakciózárolási és sor verziózás útmutatóból.

Egy tranzakció teljes kódjának vizsgálatakor, akár végrehajtási tervben, akár alkalmazás-lekérdezési kódban, keressen további problémás mintákat:

• Tranzakciók felhasználói interakciója. Egy explicit többutas tranzakción belüli felhasználói interakció jelentősen növeli a tranzakciók időtartamát. Ez nagyobb valószínűséggel teszi lehetővé, hogy ezek a tranzakciók átfedődjenek, illetve blokkolás és holtpontok következhessenek be.

  Hasonlóképpen, egy nyitott tranzakció megtartása és egy nem kapcsolódó adatbázis vagy rendszer középső tranzakciójának lekérdezése jelentősen növeli a blokkolás és a holtpontok esélyét.

• Tranzakciók, amelyek különböző sorrendben elérik az objektumokat. Holtpontok kisebb valószínűséggel fordulnak elő, ha az egyidejű explicit többutas tranzakciók ugyanazokat a mintákat követik, és ugyanabban a sorrendben érik el az objektumokat.

Holtpont újbóli kiújulásának megakadályozása

A holtpontok újbóli felmerülésének megakadályozására több technika is rendelkezésre áll, például az indexhangolás, a tervek kényszerítése a Lekérdezés Tárral, és a Transact-SQL lekérdezések módosítása.

• A tábla fürtözött indexénekáttekintése. A legtöbb tábla kihasználja a fürtözött indexeket, de a táblák gyakran halomként véletlenül kerülnek implementálásra.

  A fürtözött indexek keresésének egyik módja a sp_helpindex rendszer által tárolt eljárás használata. A SalesLT.Product táblában lévő indexek összegzését például a következő utasítás végrehajtásával tekinthetjük meg:

  EXECUTE sp_helpindex 'SalesLT.Product';
  GO
  

  Tekintse át a index_description oszlopot. Egy táblának csak egy klaszterezett indexe lehet. Ha klaszteres indexet implementáltak a táblához, a index_description a clusteredszót tartalmazza.

  Ha nincs fürtözött index, a tábla egy halom. Ebben az esetben ellenőrizze, hogy a tábla szándékosan halomként lett-e létrehozva egy adott teljesítményproblémának a megoldásához. Fontolja meg egy klaszteres index implementálását a klaszteres index tervezési irányelvek alapján.

  Bizonyos esetekben a fürtözött indexek létrehozása vagy finomhangolása csökkentheti vagy kiküszöbölheti a holtpontok blokkolását. Más esetekben egy további technikát is alkalmazhat, például a listán szereplő többi technikát.

• Nem klaszterezett indexek létrehozása vagy módosítása. A nemclustered indexek finomhangolásával a módosító lekérdezések gyorsabban megtalálhatják a frissíteni kívánt adatokat, ami csökkenti a szükséges frissítési zárolások számát.

  A példánkban a lekérdezési terv, amely a Lekérdezéstárban található, tartalmaz egy fürtözött indexvizsgálatot a PK_Product_ProductID indexen. A holtpont gráf azt jelzi, hogy az index megosztott (S) zárolási várakozása a holtpont egyik összetevője.

  

  Ez az indexvizsgálat azért van folyamatban, mert a frissítési lekérdezésnek módosítania kell egy vProductAndDescriptionnevű indexelt nézetet. Ahogy a cikk A blokkolást növelő mintázatok keresése című cikkben említettük, a több táblára hivatkozó indexelt nézetek növelhetik a blokkolást és a holtpontok valószínűségét.

  Ha az alábbi nemclustered indexet hozjuk létre az AdventureWorksLT adatbázisban, amely "lefedi" az indexelt nézet által hivatkozott SalesLT.Product oszlopait, ez sokkal hatékonyabban segít a lekérdezésnek a sorok megtalálásában:

  CREATE INDEX IX_Product_ProductID_Name_ProductModelID
      ON SalesLT.Product(ProductID, Name, ProductModelID);
  GO
  

  Az index létrehozása után a holtpont többé nem ismétlődik.

  Ha a holtpontok az idegenkulcs-megkötésekben hivatkozott oszlopok módosítását is magukban foglalják, győződjön meg arról, hogy a FOREIGN KEY hivatkozó táblájában lévő indexek támogatják a kapcsolódó sorok hatékony megtalálását.

  Bár az indexek bizonyos esetekben jelentősen javíthatják a lekérdezési teljesítményt, az indexek többletterheléssel és felügyeleti költségekkel is járnak. Tekintse át általános indextervezési irányelveket, amelyek segítenek felmérni az indexek előnyeit, mielőtt indexeket, különösen széles indexeket és indexeket hoz létre nagy táblákon.

• Indexelt nézetek értékének felmérése. A holtpontunk újbóli előfordulásának megakadályozására egy másik lehetőség a SalesLT.vProductAndDescription indexelt nézet eldobása. Ha az indexelt nézet nincs használatban, ez csökkenti az indexelt nézet időbeli fenntartásának többletterhelését.

• A pillanatkép elkülönítés használata. Bizonyos esetekben a tranzakcióelkülönítési szint egy vagy több holtponthoz tartozó tranzakció pillanatképének beállítása megakadályozhatja a blokkolást és a holtpont újbóli kialakulását.

  Ez a technika valószínűleg sikeres, ha SELECT utasításokban használják, amikor ki van kapcsolva a függőben levő pillanatkép olvasása egy adatbázisban. Ha az olvasás-commitált pillanatkép le van tiltva, SELECT olvasás-commitált elkülönítési szintet használó lekérdezések megosztott (S) zárolást igényelnek. A tranzakciók pillanatkép-elkülönítésének használata megszünteti a megosztott zárolások szükségességét, ami megakadályozhatja a blokkolást és a holtpontokat.

  Azokban az adatbázisokban, ahol engedélyezve van az olvasási véglegesített pillanatkép-elkülönítés, SELECT lekérdezések nem igényelnek megosztott (S) zárolást, így nagyobb valószínűséggel fordulnak elő holtpontok az adatokat módosító tranzakciók között. Azokban az esetekben, amikor holtpontok lépnek fel az adatokat módosító több tranzakció között, a pillanatképek elkülönítése frissítési ütközést eredményezhet, holtpont helyett. Ehhez hasonlóan az egyik tranzakciónak újra meg kell próbálkoznia a művelettel.

• Terv végrehajtása a Lekérdezéstár segítségével. Előfordulhat, hogy a holtpont egyik lekérdezése több végrehajtási tervvel rendelkezik, és a holtpont csak egy adott terv használatakor fordul elő. Megakadályozhatja, hogy a holtpont ismét előforduljon, ha a Lekérdezéstárban kényszerít egy tervet.

• Módosítsa a Transact-SQL. Előfordulhat, hogy módosítania kell Transact-SQL, hogy a holtpont ne ismétlődjön. A Transact-SQL módosítását gondosan kell elvégezni, és a módosításokat szigorúan tesztelni kell annak érdekében, hogy a módosítások egyidejű futtatásakor az adatok helyesek legyenek. A Transact-SQL újraírása során vegye figyelembe a következő szempontokat:

  • Tranzakciókban az utasítások sorrendbe állítása annak érdekében, hogy az objektumokhoz azonos sorrendben férhessenek hozzá.

  • Ha lehetséges, kisebb tranzakciókra bontja a tranzakciókat.

  • Ha szükséges, lekérdezési tippeket használ a teljesítmény optimalizálásához. Alkalmazhat tippeket anélkül, hogy módosítaná az alkalmazáskódot , a Query Storehasználatával.

A holtpontok csökkentésének további módjai a Holtpontok útmutatójában.

Jegyzet

Bizonyos esetekben módosíthatja a holtpontban részt vevő egy vagy több munkamenet prioritását, ha fontos, hogy az egyik munkamenet sikeresen befejeződjön megismétlés nélkül, vagy ha a holtpontban érintett lekérdezések egyike nem kritikus, és mindig áldozatként kell megválasztani. Bár ez nem akadályozza meg a holtpont újbóli kialakulását, csökkentheti a jövőbeli holtpontok hatását.

XEvents-munkamenet elvetése

Hosszú ideig futtathat XEvents-munkameneteket, amelyek holtponttal kapcsolatos adatokat gyűjtenek fontos adatbázisokon. Ha eseményfájl-célhelyet használ, ez nagy fájlokat eredményezhet, ha több holtpont is előfordul. Az Azure Storage-ban lévő blobfájlokat törölheti egy aktív nyomkövetés esetén, nem beleértve azt a fájlt, amelybe éppen ír.

Ha el szeretne távolítani egy XEvents-munkamenetet, a Transact-SQL törölni fogja a munkamenetet, függetlenül attól, milyen céltípus van kiválasztva.

XEvents-munkamenet eltávolításához futtassa a következő Transact-SQL-t. A kód futtatása előtt cserélje le a munkamenet nevét a megfelelő értékre.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Az Azure Storage Explorer használata

Azure Storage Explorer egy különálló alkalmazás, amely egyszerűbben használható az Azure Storage blobjaiban tárolt eseményfájl-célokkal. A Storage Explorerrel a következőt használhatja:

• Hozzon létre egy blobtárolót az XEvent-munkamenet adatainak tárolásához.

• A blobtároló közös hozzáférésű jogosultságkódjának (SAS) lekérése.

  • Ahogy az a(z) Holtpontdiagramok gyűjtése az Azure SQL Database-ben kiterjesztett eseményekkelrészben említve van, az olvasási, írási és listázási engedélyek szükségesek.

  • Távolítsa el az összes kezdő ? karaktert a Query string értékből, hogy azt titkosként használja az adatbázis hatókörébe tartozó hitelesítő adatok létrehozásakor .

• Kibővített eseményfájlok megtekintése és letöltése blobtárolóból.

Az Azure Storage Explorerletöltése.

Kapcsolódó tartalom

• Blokkolási problémák ismertetése és megoldása
• Tranzakciózárolási és sorverziózás útmutató
• Holtpontok útmutatója
• TRANZAKCIÓELKÜLÖNÍTÉSI SZINT BEÁLLÍTÁSA
• Azure SQL Database: a teljesítmény finomhangolásának javítása automatikus hangolással
• Szolgáltasson konzisztens teljesítményt az Azure SQL-lel
• Átmeneti hibák újrapróbálkozási logikája