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

A következőre vonatkozik: Azure SQL Database

Ez a cikk bemutatja, hogyan azonosíthatja a holtpontokat az Azure SQL Database-ben, hogyan azonosíthatja a holtpontban érintett lekérdezéseket holtpontgrafikonok és a Lekérdezéstár használatával, illetve hogyan tervezheti meg és tesztelheti a módosításokat úgy, hogy elkerülje a holtpontok újbóli előfordulását.

Ez a cikk a zárolási versengés miatti holtpontok azonosítására és elemzésére összpontosít. További információ a holtpontok egyéb típusairól az erőforrásokban , amelyek holtpontot is tartalmazhatnak.

Holtpontok előfordulása az Azure SQL Database-ben

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 szinteket ügyfélkódtár-metódusok, lekérdezési tippek vagy Standard kiadás T-utasítások segítségével adhatja meg a Transact-SQL-ben.
• Lehetséges, hogy az RCSI le van tiltva, ezért az adatbázis megosztott (S) zárolásokat használ a Standard kiadás LECT-utasítások olvasási elkülönítési szinten való futtatásához. Ez növelheti a blokkolást és a holtpontokat.

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. Az A munkamenet egy explicit tranzakciót indít el, és egy frissítési utasítást futtat, amely egy frissítési (U) zárolást szerez be a tábla SalesLT.Product egy olyan sorára, amely kizárólagos (X) zárolássá lesz konvertálva.
2. A B munkamenet egy frissítési utasítást futtat, amely módosítja a táblát SalesLT.ProductDescription . A frissítési utasítás a SalesLT.Product táblához csatlakozva megkeresi a megfelelő frissítendő sorokat.
   • A B munkamenet egy frissítési (U) zárolást szerez be a SalesLT.ProductDescription tábla 72 sorára.
   • A B munkamenetnek megosztott zárolásra van szüksége a tábla SalesLT.Productsoraiban, beleértve az A munkamenet által zárolt sort is. A B munkamenet le van tiltva.SalesLT.Product
3. Az A munkamenet folytatja a tranzakciót, és most egy frissítést futtat a SalesLT.ProductDescription táblán. Az A munkamenetet a B munkamenet blokkolja a következőn SalesLT.ProductDescription: .

A holtpontban lévő összes tranzakció korlátlan ideig várakozik, kivéve, ha az egyik résztvevő tranzakciót visszavonták, például mert a munkamenete megszűnt.

Az adatbázis-motor holtpontfigyelője rendszeresen ellenőrzi a holtponton lévő feladatokat. Ha a holtpontmonitor 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: "A tranzakció (N folyamatazonosító) holtpontra lett állítva a zárolási erőforrásokon egy másik folyamattal, és holtpontként lett kiválasztva. Futtassa újra a tranzakciót." A holtpont ily módon történő feltörése lehetővé teszi, hogy a holtponton lévő többi tevékenység vagy tevékenység végrehajtsa a tranzakciókat.

Megjegyzés:

A holtpont-áldozat kiválasztásának feltételeiről a cikk Holtpont folyamatlista szakaszában olvashat bővebben.

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 az újrapróbálkozás előtt, hogy ne találkozzon újra ugyanazzal a holtponttal. További információ az átmeneti hibák újrapróbálkozási logikájának tervezéséről.

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 véglegesített pillanatképet (RCSI). Az RCSI az olvasási lekötött elkülönítési szint viselkedését úgy módosítja, hogy a sorverziók használatával utasításszintű konzisztenciát biztosítson az Standard kiadás LECT-utasítások megosztott (S) zárolásainak használata nélkül.

Engedélyezve van az RCSI:

• 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.

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. Csatlakozás 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.

Ha az RCSI le van tiltva egy Azure SQL Database-adatbázishoz, vizsgálja meg, hogy miért tiltotta le az RCSI-t az újbóli engedélyezés előtt. Lehet, hogy az alkalmazáskód meg lett írva, és azt várta, hogy az adatokat író lekérdezések blokkolják az adatokat olvasó lekérdezéseket, ami helytelen eredményeket eredményez a versenyfeltételekből, ha az RCSI engedélyezve van.

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 holtponthoz nincs szükség felhasználói műveletre. Az alkalmazásokat újrapróbálkozással kell írni, hogy az 1205-ös hiba beérkezése után automatikusan folytatódjanak: "A tranzakció (N folyamatazonosító) holtpontra lett állítva egy másik folyamattal rendelkező zárolási erőforrásokon, és holtpontként lett kiválasztva. Futtassa újra a tranzakciót."

É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. További információ a riasztásokról a jelen cikk Figyelés és a holtpontokon című szakaszában.

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

A holtpontok újbóli kiújulásának megakadályozására szolgáló legalacsonyabb kockázati módszer általában a nemclustered 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ásához nem szükséges módosítani magát a lekérdezési kódot, ami csökkenti a felhasználó hibájának kockázatát a Transact-SQL újraírásakor, amely helytelen adatokat ad vissza a felhasználónak.
• 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. További információ a fürtözött indexek 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 Transact-SQL újraírása a holtpontban lévő egy vagy több tranzakció esetében 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.

További információ a cikk holtpont újbóli felmerülésének megakadályozása című szakaszában található megközelítésekről.

Figyelés és riasztás holtpontokon

Ebben a cikkben a mintaadatbázis használatával állítjuk be a AdventureWorksLT holtpontokra vonatkozó riasztásokat, például holtpontot okozunk, elemezzük a holtpont gráfot a holtponthoz, és teszteljük a módosításokat, hogy megakadályozzuk a holtpont újbóli kiváltását.

Ebben a cikkben az SQL Server Management Studio (SSMS) ügyfelet fogjuk használni, mivel a holtpontdiagramok interaktív vizualizációs módban való megjelenítésére szolgáló funkciókat tartalmaz. Más ügyfelek, például az Azure Data Studio is követhetik a példákat, 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 a Mintaadatok lehetőséget 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 : Önálló Azure SQL Database-adatbázis létrehozása.

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 a Holtpontot a riasztás jelneveként. Konfigurálja a műveletcsoportot úgy, hogy a választott módszerrel értesítse Önt, például az E-mail/SMS/Push/Voice 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. Ha holtpontdiagramokat szeretne gyűjteni kiterjesztett eseményekkel (XEvents) az Azure SQL Database-ben, rögzítse az eseményt sqlserver.database_xml_deadlock_report .

Az XEvents holtpont-diagramjait a gyűrűpuffer-cél vagy egy eseményfájl-cél használatával gyűjtheti össze. A megfelelő céltípus kiválasztásával kapcsolatos szempontokat az alábbi táblázat foglalja össze:

Módszer	Benefits	Considerations	Használati forgatókönyvek
Gyűrűpuffer-cél	Egyszerű beállítás csak Transact-SQL használatával.	Az eseményadatok törlődnek, ha az XEvents-munkamenet bármilyen okból leáll, 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 rövid távú igényeket, ha nem tud azonnal eseményfájl-cél használatával munkamenetet beállítani. A nyomkövetési adatok "kezdőpadjaként" használható, ha egy automatizált folyamatot állított be a nyomkövetési adatok táblában való megőrzéséhez.
Eseményfájl-cél	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 Explorerből , és helyileg elemezhetők, 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ánosan használható, 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él

A gyűrűpuffer-cél 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 lesz elérhető, amikor az adatbázismotor bármilyen okból újraindul, például feladatátvételkor. A gyűrűpuffer-cél a legjobban megfelel a tanulásnak és a rövid távú igényeknek, ha nem tudja azonnal beállítani az XEvents-munkamenetet egy eseményfájl-célhelyre.

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

Ha létre szeretne hozni egy XEvents-munkamenetet a sqlserver.database_xml_deadlock_report gyűrűpuffer-tárolóba írandó eseményhez, 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él

Az eseményfájl-cél továbbra is holtpontdiagramokat tartalmaz a fájlok számára, így 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 fogjuk tenni:

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 közös hozzáférésű jogosultságkód (SAS) jogkivonatot a tárolóhoz. Ez a szakasz a folyamat Azure Portalon történő elvégzését ismerteti.

Megjegyzés:

Ha az Azure Storage-blobtárolót a PowerShell-lel szeretné létrehozni és konfigurálni, tekintse meg az Eseményfájl célkódját az Azure SQL Database-ben lévő kiterjesztett eseményekhez. Alternatív megoldásként az Azure Storage Explorer használatával is kényelmesen létrehozhatja és konfigurálhatja 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 panel Erőforrások csoportjában állítsa a Típus legördülő menüt Tárfiókra.
3. Válassza ki a használni kívánt tárfiókot.

Új Azure Storage-fiók létrehozásához kövesse az Azure Storage-fiók létrehozása című témakörben leírt lépéseket. A folyamat befejezéséhez válassza az Utolsó lépésben 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ás területen válassza a Tárolók lehető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év területen.
4. Select Create.
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 Portal tárolóoldalán:

1. A Gépház csoportban válassza a Megosztott hozzáférési jogkivonatok lehetőséget.
2. Hagyja az Aláírási mód választógombot az alapértelmezett kijelölésre, a Fiókkulcsra.
3. Az Engedélyek legördülő menüben válassza ki az Olvasás, írás és lista engedélyeket.
4. Állítsa a kezdést arra a dátumra és időpontra, amikor nyomkövetési fájlokat szeretne írni. Igény szerint konfigurálja az időzónát a Start alatti legördülő menüben.
5. Állítsa be a lejárati időt arra a dátumra és időpontra, amikor le szeretné járni ezeket az engedélyeket. Igény szerint konfigurálja az időzónát a Lejárat alatti legördülő listában. Ezt beállíthatja a jövőben messzemenő dátumra, például tíz évre, ha szeretné.
6. Válassza az SAS-jogkivonat és AZ URL-cím létrehozása lehető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-jogkivonatot és a Blob SAS URL-értékeit a további lépésekben való használathoz.

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

Csatlakozás az Azure SQL Database-ben az SSMS-sel rendelkező adatbázisba az alábbi lépések futtatásához.

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

Ha nem létezik, futtassa a következő Transact-SQL-t egy főkulcs létrehozásához:

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 a megosztott hozzáférési jogkivonat létrehozásakor másolt Blob SAS URL-cím elején jelenik meg. Csak a sztring első ? előtti szövegére van szüksége.
• Módosítsa úgy a SECRETblob SAS-jogkivonat értékét, amelyet a megosztott hozzáférési jogkivonat létrehozásakor másolt.

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=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
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 az értéket, filename hogy tükrözze a tárfiók nevét és a tároló nevét. Ez az URL-cím a megosztott hozzáférési jogkivonat létrehozásakor másolt Blob SAS URL-cím elején jelenik meg. Csak a sztring első ? előtti szövegére van szüksége.
• Tetszés szerint módosíthatja a tárolt fájlnevet. Az itt megadott fájlnév a blob(ok) eseményadatainak tárolásához használt tényleges fájlnév(ek) része lesz: a további értékek hozzá lesznek fűzve, 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 az AdventureWorksLT-ben

Megjegyzés:

Ez a példa az AdventureWorksLT adatbázisban az alapértelmezett sémával és adatokkal működik, amikor az RCSI engedélyezve van. Az adatbázis létrehozásához útmutatást az AdventureWorksLT-adatbázis létrehozása című témakörben talál.

Holtpont létrehozásához két munkamenetet kell csatlakoztatnia az AdventureWorksLT adatbázishoz. Ezeket a munkameneteket A és B munkamenetként fogjuk hivatkozni.

Az A munkamenetben futtassa a következő Transact-SQL-t. Ez a kód egy explicit tranzakciót indít el, és egyetlen utasítást futtat, amely frissíti a táblát SalesLT.Product . Ehhez a tranzakció egy frissítési (U) zárolást szerez be a tábla SalesLT.Product egy sorára, amelyet kizárólagos (X) zárolássá alakít át. Nyitva hagyjuk a tranzakciót.

BEGIN TRAN

    UPDATE SalesLT.Product SET SellEndDate = SellEndDate + 1
        WHERE Color = 'Red';

Most futtassa a következő Transact-SQL-t a B munkamenetben. Ez a kód nem indít el explicit módon tranzakciót. Ehelyett automatikus kiküldéses tranzakciós módban működik. Ez az utasítás frissíti a táblát SalesLT.ProductDescription . A frissítés egy frissítési (U) zárolást hoz létre a SalesLT.ProductDescription tábla 72 sorában. A lekérdezés más táblákhoz csatlakozik, beleértve a táblát SalesLT.Product is.

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';

A frissítés végrehajtásához a B munkamenetnek megosztott (S) zárolásra van szüksége a tábla SalesLT.Productsoraiban, beleértve az A munkamenet által zárolt sort is. A B munkamenet le lesz tiltva.SalesLT.Product

Vissza az A munkamenethez. Futtassa a következő Transact-SQL utasítást. Ez egy második UPDATE utasítást futtat a nyitott tranzakció részeként.

    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';

Az A munkamenet második frissítési utasítását a B munkamenet blokkolja a SalesLT.ProductDescriptionkövetkezőn: .

Az A és a B munkamenet mostantól kölcsönösen blokkolja egymást. Egyik tranzakció sem folytatható, mivel mindegyiknek olyan erőforrásra van szüksége, amelyet a másik zárol.

Néhány másodperc elteltével a holtpont figyelője azonosítja, hogy az A munkamenetben és a B munkamenetben lévő tranzakciók kölcsönösen blokkolják egymást, és egyik sem tud előrehaladást elérni. Holtpontot kell látnia, és az A munkamenetet választotta holtpont áldozatának. Hibaüzenet jelenik meg az A munkamenetben a következőhöz hasonló szöveggel:

Az Msg 1205, Level 13, State 51, Line 7 Transaction (Folyamatazonosító: 91) holtpontot kapott a zárolási erőforrásokon egy másik folyamattal, és a holtpont áldozataként lett kiválasztva. Futtassa újra a tranzakciót.

A B munkamenet sikeresen befejeződik.

Ha az Azure Portalon beállítja a holtpontra vonatkozó riasztásokat, a holtpont bekövetkezése után röviddel értesítést kell kapnia.

Holtpontdiagramok megtekintése XEvents-munkamenetből

Ha XEvents-munkamenetet állított be holtpontok gyűjtésére, és a munkamenet elindítása után holtpont történt, megtekintheti a holtpont gráf interaktív grafikus megjelenítését, valamint a holtpont gráf XML-fájljá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él

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 az értékét @tracename az xEvents-munkamenet nevére.

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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él

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 munkamenete jelenleg fut, és egy eseményfájl-célba ír, a blobtároló, amelyre ír, az Azure Portalon bérlet állapotúlesz. A méret a fájl maximális mérete lesz. Kisebb fájl letöltéséhez a fájlok letöltése előtt le kell állítania és újra kell indítania a Bővített események munkamenetet. Ez azt eredményezi, hogy a fájl a bérlet állapotátElérhetőre 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.

Megjegyzés:

Az Azure Storage Explorer használatával gyorsan és kényelmesen letöltheti az esemény munkamenetfájljait egy Blob-tárolóból az Azure Storage-ban.

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ás területen válassza a Tárolók lehető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 a Letöltés lehető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. To do so:

1. Nyissa meg az SSMS-t.
2. Válassza a Fájl, majd a Megnyitás, majd a Bővített események fájlok egyesítése... lehetőséget.
3. Select Add.
4. Arra a könyvtárra navigált, ahol letöltötte a fájlokat. Több fájl kijelölése a Shift billentyű használatával.
5. Válassza a Megnyitás lehetőséget.
6. Válassza az OK gombot 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 ezzel, majd az SSMS lehető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 xml_report alsó panelen lévő sorra.

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

Fontos

A nagy (1 GB-os és nagyobb) XEvents nyomkövetési fájlok lekérdezése nem ajánlott ezzel a módszerrel, mert nagy mennyiségű memóriát használhat 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.

Futtassa a következő Transact-SQL-t a jelenleg aktív XEvents-nyomkövetési fájl lekérdezéséhez. A lekérdezés futtatása előtt cserélje le @tracename az XEvents-munkamenet nevére.

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

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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 a tárolóra az Azure Portalon a fájlnevek azonosításához.

Futtassa a következő 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 következő @filenameURL-címre:

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.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átumú megtekintése lehetővé teszi a inputbuffer holtpontban lévő Transact-SQL-utasítások másolását. A holtpontok szövegalapú formátumban is elemezhetők.

Ha 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 válassza ki az deadlock_xml oszlopban lévő értéket bármelyik sorból, és nyissa meg a holtpont gráf XML-jé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 a Fájl és a Mentés másként... lehetőséget.
2. Hagyja meg a Mentés típusként értéket alapértelmezett XML-fájlként (*.xml)
3. Állítsa a Fájlnevet a kívánt névre.
4. Válassza a Mentés parancsot.

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. A holtpont gráf XML-fájljának SSMS-ben egy új ablakban való megnyitásához válassza ki bármelyik sor oszlopának értékét deadlock_xml .

2. Válassza a Fájl és a Mentés másként... lehetőséget.

3. A Mentés típus beállítása minden fájlra.

4. Állítsa a Fájlnevet a kívánt névre, a bővítmény pedig .xdl értékre van állítva.

5. Válassza a Mentés parancsot.

   

6. Zárja be a fájlt az ablak tetején található lap X elemének kiválasztásával, vagy a Fájl, majd a Bezárás lehetőség kiválasztásával.

7. Nyissa meg újra a fájlt az SSMS-ben a Fájl, majd a Megnyitás, majd a Fájl elemre kattintva. Válassza ki a kiterjesztéssel .xdl 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 holtpontot okozó folyamat azonosítója.
• Folyamatlista. Információ a holtpontban érintett ö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 holtpontban érintett 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 folyamat egy "X" húzva az ovális.

A holtpont-gráf XML-nézetében a victim-list csomópont megadja a holtpont áldozatává lett folyamat azonosítóját.

A példa holtponton az áldozat folyamatazonosítója a process24756e75088. 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.

Holtpont folyamatlistá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:

• Kiszolgálói folyamatazonosító, más néven munkamenet-azonosító vagy 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 munkamenet ugyanazzal a holtpont prioritással rendelkezik.

• 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, amely kevésbé költséges a holtpont áldozataként való visszaállításhoz. 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 89-session_id alacsonyabb tranzakciónaplót használt, és holtpontként lett kiválasztva.

Emellett megtekintheti a holtpont előtti munkamenetekben futtatott utolsó utasítás bemeneti pufferét is, ha az egérmutatót az egyes folyamatokra viszi. A bemeneti puffer egy elemleírásban jelenik meg.

További információk érhetők el a holtpont gráf XML-nézetében lévő 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ésterv kivonata. A lekérdezésterv kivonata hasznos a lekérdezésről a Lekérdezéstárból történő további információk lekéréséhez.

A példánkban holtpont:

• Láthatjuk, hogy mindkét munkamenetet az SSMS-ügyfél használatával futtatták a chrisqpublic bejelentkezés alatt.
• A holtpont áldozata által a holtpont előtt futtatott utolsó utasítás lekérdezési terv kivonata 0x02b0f58d7730f798. Ennek az utasításnak a szövegét a bemeneti pufferben láthatjuk.
• A holtpont másik munkamenete által futtatott utolsó utasítás lekérdezésterv kivonata 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 fogjuk használni további információk kereséséhez 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 csonkolt a bemeneti pufferben. A bemeneti puffer a végrehajtott utasítás első 4000 karakterére korlátozódik.

Emellett előfordulhat, hogy a holtpont gráfja nem tartalmaz néhány, a holtponttal kapcsolatos utasítást. A példánkban az 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 a B munkamenet blokkolásával szerepet játszott a holtponton. Az A munkamenet által futtatott első utasítás bemeneti puffere query_hashés kapcsolódó információi nem szerepelnek a holtpont grafikonon.

Ha azonosítani szeretné a Transact-SQL teljes futtatását egy holtponthoz tartozó többutas tranzakcióban, vagy meg kell keresnie a lekérdezést futtató tárolt eljárásban vagy alkalmazáskódban szereplő releváns információkat, vagy le kell futtatnia egy nyomkövetést kiterjesztett események használatával a holtpontban lévő munkamenetek által futtatott teljes utasítások rögzítéséhez. Ha a holtponthoz tartozó utasítás csonkolt, és csak részleges Transact-SQL jelenik meg a bemeneti pufferben, a Végrehajtási tervvel együtt megtalálhatja a Lekérdezéstárban található utasítás Transact-SQL-ét.

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:

Megjegyzés:

Megfigyelheti, hogy az adatbázisnevek egyedi azonosítóként jelennek meg az Azure SQL Database-adatbázisok holtponti gráfjaiban. Ez a physical_database_name sys.databasesés sys.dm_user_db_resource_governance dinamikus felügyeleti nézetekben felsorolt adatbázishoz tartozik.

Ebben a példában holtpont:

• A holtpont áldozata, amit A munkamenetnek hívunk:

  • Kizárólagos (X) zárolással rendelkezik a PK_Product_ProductID tábla indexének egyik kulcsán SalesLT.Product .
  • Frissítési (U) zárolást kér a tábla indexének PK_ProductDescription_ProductDescriptionID egyik kulcsához SalesLT.ProductDescription .

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

  • Egy frissítési (U) zárolás tulajdonosa a PK_ProductDescription_ProductDescriptionID tábla indexének egyik kulcsán SalesLT.ProductDescription .
  • Megosztott (S) zárolást kér a tábla indexének PK_ProductDescription_ProductDescriptionID egyik kulcsához SalesLT.ProductDescription .

Ugyanezeket az információkat az erőforráslista csomópont holtponti gráfjának XML-jében is láthatjuk.

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

Gyakran hasznos megvizsgálni a holtponthoz tartozó 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éből származó lekérdezésterv kivonatá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. Csatlakozás az Azure SQL Database felhasználói adatbázisába a lekérdezés futtatásához.

DECLARE @query_plan_hash 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
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval 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éstár CLEANUP_POLICY vagy QUERY_CAPTURE_MODE beállításaitól függően nem tud lekérdezés-végrehajtási tervet beszerezni a Lekérdezéstárból. Ebben az esetben gyakran szükséges információkat kaphat a lekérdezés becsült végrehajtási tervének megjelenítésével.

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ábla- 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 felszabadítja a frissítési zárolást, és a következő sort zárolja és ellenőrzi.

  Az indexek módosítása a lekérdezések hatékonyabb kereséséhez 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.

• Egynél 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árolást kell kivenni, ami fokozott blokkolást és holtpontot eredményezhet. Az indexelt nézetek azt is okozhatják, hogy a frissítési műveletek belsőleg futnak az olvasási lekötött elkülönítési szinten.

• Az idegenkulcs-korlátozásokban hivatkozott oszlopok módosítása. Ha a KÜLSŐ KULCS kényszerben hivatkozott táblák oszlopait módosítja, az adatbázismotornak meg kell keresnie a kapcsolódó sorokat a hivatkozási táblában. Ezekhez az olvasásokhoz nem használhatók sorverziók. Ha a lépcsőzetes frissítések vagy törlések engedélyezve vannak, az elkülönítési szint szerializálhatóvá eszkalálható az utasítás időtartama alatt a fantom beszúrások elleni védelem érdekében.

• Zárolási tippek. Keressen olyan táblázatos tippeket , amelyek több zárolást igénylő elkülönítési szinteket határoznak meg. Ezek a tippek közé tartozik HOLDLOCK (ami egyenértékű a szerializálható), SERIALIZABLE, READCOMMITTEDLOCK (ami letiltja az RCSI), és REPEATABLEREAD. Emellett olyan tippeket is találhat, mint PAGLOCKpéldául a , TABLOCK, UPDLOCKés XLOCK növelheti 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ályozhatják a versenyfeltételeket, és biztosíthatják az adatok érvényességét. Lehetséges, hogy ezeket a tippeket a helyén hagyja, és megakadályozza a jövőbeli holtpontok használatát a cikk Holtpont ismételt kiújulásának megakadályozása című szakaszában egy másik módszerrel, ha szükséges.

  Megjegyzés:

  A tranzakciózárolási és sorverziós verziószámozási útmutatóban további információt kaphat az adatok sorverzióval történő módosításakor használt viselkedésrő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:

• Felhasználói interakció a tranzakciókban. 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.

• Az objektumokhoz különböző megrendelésekben hozzáférő tranzakciók. 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 módszer is elérhető, például az indexhangolás, a csomagok lekérdezéstárral való kényszerítése és a Transact-SQL-lekérdezések módosítása.

• Tekintse át a tábla fürtözött indexét. A legtöbb tábla kihasználja a fürtözött indexeket, de a táblák gyakran véletlenül halomként vannak implementálva.

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

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Tekintse át a index_description oszlopot. Egy táblának csak egy fürtözött indexe lehet. Ha a táblához fürtözött index lett implementálva, a index_description a "fürtözött" szót fogja tartalmazni.

  Ha nincs fürtözött index, a tábla 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. Érdemes lehet fürtözött indexet implementálni a fürtözött index tervezési irányelvei 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 előfordulhat, hogy egy további technikát kell alkalmaznia, például a listán szereplő többi technikát.

• Nemclustered 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élda holtponton a Lekérdezéstárban található lekérdezés-végrehajtási terv tartalmaz egy fürtözött indexvizsgálatot az 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 indexelt nézetet.vProductAndDescription Ahogy a jelen cikk blokkolási szakaszában 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 oszlopokat SalesLT.Product , ez segít a lekérdezésnek a sorok hatékonyabb 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 KÜLFÖLDI KULCS hivatkozási táblájában lévő indexek hatékonyan támogatják a kapcsolódó sorok 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 az indexek általános tervezési irányelveit , amelyek segítenek felmérni az indexek előnyeit az indexek létrehozása előtt, különösen a nagyméretű táblák széles indexei és indexei előtt.

• Értékelje az indexelt nézetek értékét. Az indexelt nézet elvetése SalesLT.vProductAndDescription egy másik lehetőség arra, hogy megakadályozza a példa holtpont újbóli kialakulását. Ha az indexelt nézet nincs használatban, ez csökkenti az indexelt nézet időbeli fenntartásának többletterhelését.

• Pillanatkép-elkülönítés használata. Bizonyos esetekben a tranzakcióelkülönítési szint pillanatképre állítása a holtpontban érintett tranzakciók egy vagy több esetében megakadályozhatja a blokkolást és a holtpont újbóli létrejöttét.

  Ez a technika valószínűleg sikeres lesz, ha Standard kiadás LECT-utasításokban használják, ha a véglegesített pillanatkép olvasása le van tiltva egy adatbázisban. Ha az olvasási véglegesített pillanatkép le van tiltva, Standard kiadás LECT-lekérdezések az olvasási lekötött elkülönítési szintet használva 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épek elkülönítése, Standard kiadás LECT-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 a holtpont helyett frissítési ütközést eredményezhet. Ehhez hasonlóan az egyik tranzakciónak újra meg kell próbálkoznia a művelettel.

• Terv kényszerítve a Lekérdezéstár használatával. 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 újra kiüresedjön, ha egy tervet kényszerít ki a Lekérdezéstárban.

• Módosítsa a Transact-SQL-t. Előfordulhat, hogy módosítania kell a Transact-SQL-t, hogy megakadályozza a holtpont újbóli kialakulását. A Transact-SQL módosítását körültekintően kell elvégezni, és a módosításokat szigorúan tesztelni kell annak érdekében, hogy az adatok helyesek legyenek, amikor a módosítások párhuzamosan futnak. A Transact-SQL újraírása során vegye figyelembe a következő szempontokat:

  • Utasítások rendelése tranzakciókban, hogy azonos sorrendben férhessenek hozzá az objektumokhoz.
  • 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. A Lekérdezéstár használatával az alkalmazáskód módosítása nélkül is alkalmazhat tippeket.

A Holtpontok útmutatójában további módszereket találhat a holtpontok minimalizálására.

Megjegyzés:

Bizonyos esetekben előfordulhat, hogy módosítani szeretné a holtpontban részt vevő egy vagy több munkamenet holtponti prioritását , ha fontos, hogy az egyik munkamenet sikeresen befejeződjön újrapróbálkozás nélkül, vagy ha a holtpontban érintett lekérdezések egyike nem kritikus, és mindig áldozatként kell kivá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

Előfordulhat, hogy egy XEvents-munkamenetet szeretne hagyni, amely hosszú ideig gyűjti a kritikus adatbázisokon futó holtpontadatokat. Vegye figyelembe, hogy ha eseményfájl-célhelyet használ, az nagy méretű fájlokat eredményezhet, ha több holtpont is előfordul. Az Azure Storage-ból törölhet blobfájlokat egy aktív nyomkövetéshez, kivéve azt a fájlt, amelybe éppen ír.

Ha el szeretne távolítani egy XEvents-munkamenetet, a Transact-SQL elveti a munkamenetet, függetlenül a kiválasztott céltípustól.

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

Az Azure Storage Explorer egy ö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.
• Kérje le a blobtároló közös hozzáférésű jogosultságkódját (SAS ).
  • Ahogy az Azure SQL Database-ben a kiterjesztett eseményekkel rendelkező holtpont-gráfok gyűjtésében is szerepel, olvasási, írási és listaengedélyekre van szükség.
  • Az adatbázis hatókörébe tartozó hitelesítő adatok létrehozásakor távolítsa el az összes kezdő ? karaktert a Query string kódként használni kívánt értékből.
• Bővített eseményfájlok megtekintése és letöltése blobtárolóból.

Töltse le az Azure Storage Explorert.

Következő lépések

További információ az Azure SQL Database teljesítményéről:

• Az Azure SQL Database blokkolási problémáinak ismertetése és megoldása
• Tranzakciók zárolása és a sorok verziókezelése – útmutató
• Holtpontok útmutatója
• Standard kiadás T TRANZAKCIÓELKÜLÖNÍTÉSI SZINT
• Azure SQL Database: a teljesítmény finomhangolásának javítása automatikus hangolással
• Konzisztens teljesítmény biztosítása az Azure SQL-vel
• Az átmeneti hibák újrapróbálkozási logikája.