A reteszes versengés diagnosztizálása és megoldása az SQL Serveren

Ez az útmutató bemutatja, hogyan azonosíthatja és oldhatja meg az SQL Server-alkalmazások bizonyos számítási feladatokkal rendelkező nagy egyidejűségi rendszereken való futtatásakor tapasztalt reteszelő versengéseket.

A kiszolgálók processzormagjainak számának növekedésével az egyidejűség növekedésével versengési pontok léphetnek fel az adatstruktúrákban, amelyeket az adatbázismotoron belül soros módon kell elérni. Ez különösen igaz a nagy átviteli sebességű/magas egyidejűségi tranzakciófeldolgozási (OLTP) számítási feladatokra. Számos olyan eszköz, technika és módszer létezik, amellyel ezeket a kihívásokat meg lehet közelíteni, valamint az alkalmazások tervezése során követhető gyakorlatok, amelyek segíthetnek elkerülni azokat. Ez a cikk az adatstruktúrákhoz való hozzáférés szerializálására szolgáló spinlockokat használó adatstruktúrák egy bizonyos típusú versengését ismerteti.

Megjegyzés:

Ezt a tartalmat a Microsoft SQL Server Customer Advisory Team (SQLCAT) csapata írta a magas párhuzamosságú rendszereken futó SQL Server-alkalmazások lapzárolásával kapcsolatos problémák azonosítására és megoldására irányuló folyamatuk alapján. Az itt dokumentált javaslatok és ajánlott eljárások a valós OLTP-rendszerek fejlesztése és üzembe helyezése során szerzett valós tapasztalatokon alapulnak.

Mi az SQL Server reteszelési konfliktus?

A zárolások olyan könnyű szinkronizációs primitívek, amelyeket az SQL Server motorja használ a memórián belüli struktúrák konzisztenciájának biztosítására, beleértve az index- és adatoldalakat és a belső struktúrákat, például a B-fa nem leveles oldalait. Az SQL Server a pufferkészletben lévő oldalak védelmére pufferzárolásokat, a pufferkészletbe még be nem töltött oldalak védelmére pedig I/O-zárolásokat használ. Amikor az SQL Server-pufferkészlet egy lapjára írnak vagy olvasnak be adatokat, a feldolgozó szálnak először be kell szereznie egy pufferzárat a laphoz. A pufferkészlet lapjainak eléréséhez különböző pufferzártípusok érhetők el, beleértve a kizárólagos reteszet (PAGELATCH_EX) és a megosztott reteszet (PAGELATCH_SH). Amikor az SQL Server olyan lapot próbál elérni, amely még nem található a pufferkészletben, a rendszer egy aszinkron I/O-t tesz közzé, amely betölti a lapot a pufferkészletbe. Ha az SQL Servernek meg kell várnia, hogy az I/O-alrendszer válaszoljon, a kérés típusától függően kizárólagos (PAGEIOLATCH_EX) vagy megosztott (PAGEIOLATCH_SH) I/O-reteszelésre vár. Ez azért történik, hogy egy másik feldolgozószál ne töltse be ugyanazt a lapot a pufferkészletbe egy nem kompatibilis reteszelővel. A zárakat a buffer pool lapjaitól eltérő belső memóriastruktúrákhoz való hozzáférés védelmére is használják; ezeket nem-puffer záraknak nevezzük.

A több processzoros rendszereken előforduló leggyakoribb forgatókönyv az oldalzárlatokkal kapcsolatos versengés, ezért a cikk nagy része ezekre összpontosít.

A reteszes versengés akkor fordul elő, ha egyszerre több szál is megkísérel inkompatibilis reteszeket szerezni ugyanahhoz a memóriabeli struktúrához. Mivel a retesz egy belső ellenőrzési mechanizmus; az SQL-motor automatikusan meghatározza, hogy mikor érdemes használni őket. Mivel a zárak viselkedése determinisztikus, az alkalmazás döntései, beleértve a sématervet is, hatással lehetnek erre a viselkedésre. A cikk célja, hogy a következő információkat adja meg:

• Háttérinformációk arról, hogy az SQL Server hogyan használja a rácsokat.
• A reteszezés elemzésére használt eszközök.
• Annak megállapítása, hogy a megfigyelt versengés mennyisége problémás-e.

Bemutatunk néhány gyakori forgatókönyvet, és hogy hogyan kezelhetők a legjobban a versengés enyhítése érdekében.

Hogyan használja az SQL Server a rácsokat?

Az SQL Server egy lapja 8 KB, és több sort is tárolhat. Az egyidejűség és a teljesítmény növelése érdekében a pufferzárak csak az oldalon lévő fizikai művelet időtartamára vannak tárolva, ellentétben a zárolásokkal, amelyek a logikai tranzakció időtartamára vannak tárolva.

A zárak az SQL-motor belső elemei, és memóriakonzisztenciát biztosítanak, míg a zárolásokat az SQL Server használja a logikai tranzakciós konzisztencia biztosításához. Az alábbi táblázat a kapcsokat a zárakkal hasonlítja össze.

Struktúra	Cél	Vezérli	Teljesítményköltség	Feltárva által
Retesz	A memóriabeli struktúrák konzisztenciájának garantálása.	Csak SQL Server-motor.	A teljesítmény költsége alacsony. A maximális egyidejűség és a maximális teljesítmény biztosítása érdekében a reteszek csak a memóriabeli struktúra fizikai műveletének időtartamára vannak tárolva, ellentétben a logikai tranzakció időtartamára tárolt zárolásokkal.	sys.dm_os_wait_stats – Információt nyújt a PAGELATCH, PAGEIOLATCH és LATCH várakozási típusokról (LATCH_EX, a LATCH_SH az összes nem puffer reteszes várakozás csoportosítására szolgál). sys.dm_os_latch_stats – Részletes információkat nyújt a nem-puffer reteszzárási várakozásokról. sys.dm_db_index_operational_stats – Ez a DMV összesített várakozásokat biztosít az egyes indexekre, ami hasznos a reteszekkel kapcsolatos teljesítményproblémák elhárításához.
Zár	A tranzakciók konzisztenciájának garantálása.	A felhasználó vezérelheti.	A teljesítményköltség a zárakhoz képest magas, mivel a zárolásokat a tranzakció időtartamára kell tartani.	sys.dm_tran_locks. sys.dm_exec_sessions.

A SQL Server reteszelési módok és kompatibilitása

Az SQL Server-motor működésének szokásos részeként némi reteszelési versengés várható. Elkerülhetetlen, hogy több egyidejű, különböző kompatibilitású reteszelési kérések fordulnak elő egy magas egyidejűségi rendszeren. Az SQL Server úgy kényszeríti ki a reteszkompatibilitást, hogy az inkompatibilis reteszkérelmeket sorba állítják, amíg a függőben lévő reteszkérelmek be nem fejeződnek.

A zárak az öt különböző mód egyikében érhetők el, amelyek a hozzáférési szinthez kapcsolódnak. Az SQL Server reteszes üzemmódjai az alábbiak szerint foglalhatók össze:

• KP: Tartsa a zárat. Biztosítja, hogy a hivatkozott szerkezet ne pusztulhat el. Akkor használatos, ha egy szál pufferstruktúrát szeretne vizsgálni. Mivel a KP retesz kompatibilis az összes zárral, kivéve a megsemmisítési (DT) reteszet, a KP-retesz könnyűnek minősül, ami azt jelenti, hogy a teljesítményre gyakorolt hatás minimális. Mivel a KP-retesz nem kompatibilis a DT-reteszpel, megakadályozza, hogy bármely más szál elpusztítsa a hivatkozott struktúrát. Egy KP-retesz például megakadályozza, hogy a lusta írófolyamat elpusztítsa a hivatkozott struktúrát. A lusta írói folyamat sql serveres pufferoldal-kezeléssel való használatáról további információt a Lapok írása az adatbázismotorban című témakörben talál.

• SH: Megosztott retesz. A hivatkozott struktúra (például egy adatoldal olvasása) olvasásához szükséges. Egyszerre több szál is hozzáférhet egy megosztott retesz alatti olvasáshoz szükséges erőforráshoz.

• UP: Retesz frissítése. Kompatibilis a SH (megosztott reteszekkel) és a KP-vel, de másokkal nem, ezért nem engedélyezi a EX retesz írását a hivatkozott struktúrába.

• EX: Exkluzív retesz. Megakadályozza, hogy a többi szál írjon a hivatkozott struktúrába vagy olvasson abból. Ilyen például egy lap tartalmának módosítása szakadás elkerülése érdekében.

• DT: Pusztítsa el a reteszt. A hivatkozott szerkezet tartalmának megsemmisítése előtt be kell szerezni. A "lazy writer" folyamatnak például be kell szereznie egy DT-reteszt annak érdekében, hogy felszabadítson egy tiszta oldalt, mielőtt hozzáadná a más szálak által használható szabad pufferek listájához.

A retesz üzemmódok különböző kompatibilitási szinttel rendelkeznek, például a megosztott retesz (SH) kompatibilis a frissítéssel (UP) vagy a (KP) reteszeléssel, de nem kompatibilis a megsemmisítési reteszekkel (DT). Egyszerre több zár is megszerezhető ugyanazon a struktúrán, amíg a zárak kompatibilisek. Amikor egy szál egy nem kompatibilis módban tartott zárolást próbál megszerezni, egy várakozási sorba kerül, hogy megvárja az erőforrás elérhetőségét jelző jelet. A várakozási várólista védelmére egy SOS_Task típusú spinlock szolgál a sor szerializált hozzáférésének kényszerítésével. Ezt a spinlockot be kell szerezni, hogy elemeket vegyen fel az üzenetsorba. A SOS_Task spinlock a nem kompatibilis reteszek felszabadításakor is jelzi az üzenetsorban lévő szálakat, így a várakozó szálak kompatibilis reteszet szerezhetnek be, és folytathatják a munkát. A várakozási várólista feldolgozása az érkezési sorrendnek megfelelően (FIFO) történik, a retesz-kérelmek feloldása esetén. A reteszek ezt a FIFO rendszert követve biztosítják az igazságosságot, és megakadályozzák a szál éhezését.

A zár mód kompatibilitása az alábbi táblázatban található (Igen a kompatibilitást, a Nem pedig az inkompatibilitást jelzi):

Reteszes mód	KP	SH	FEL	EX	DT
KP	Igen	Igen	Igen	Igen	Nem
SH	Igen	Igen	Igen	Nem	Nem
UP	Igen	Igen	Nem	Nem	Nem
EX	Igen	Nem	Nem	Nem	Nem
DT	Nem	Nem	Nem	Nem	Nem

SQL Server superlatches és sublatches

A NUMA-alapú többcsatornás / többmagos rendszerek növekvő jelenlétével az SQL Server 2005 bevezette a Superlatches-eket, más néven alzárokat, amelyek csak a 32 vagy több logikai processzorral rendelkező rendszereken hatékonyak. A szuperzárak javítják az SQL-motor hatékonyságát bizonyos használati minták esetén a magas egyidejű OLTP-számítási feladatokban, például ha bizonyos oldalakhoz intenzív olvasásra korlátozott (SH) hozzáférés van, de ritkán íródnak. Ilyen hozzáférési mintával rendelkező lap például egy B-fa (vagyis index) gyökérlap; az SQL-motor megköveteli, hogy a gyökéroldalon egy megosztott retesz legyen, amikor a B-fa bármely szintjén oldaleloszlás történik. A beszúrási terheléses és nagy egyidejűségi szintű OLTP-számítási feladatokban az oldaleloszlások száma az átviteli sebességnek megfelelően széles körben növekszik, ami csökkentheti a teljesítményt. A szuperreteszek nagyobb teljesítményt tesznek lehetővé a megosztott lapok elérésénél, ahol több egyidejűleg futó munkaszálhoz szükséges a reteszelés. Ennek érdekében az SQL Server motorja dinamikusan előléptet egy reteszt egy ilyen SQL lapon superretesz szintre. A Superlatch egyetlen reteszt particionál szubretesz-struktúrák tömbjeként, egy szubreteszt partíciónként processzormagonként, így a fő retesz proxy átirányítóvá válik, és a csak olvasható reteszek esetében nincs szükség globális állapotszinkronizálásra. Ennek során a mindig egy adott CPU-hoz hozzárendelt feldolgozónak csak a helyi ütemezőhöz rendelt megosztott (SH) alrácsot kell beszereznie.

Megjegyzés:

A dokumentáció általában a B-fa kifejezést használja az indexekre hivatkozva. A sorkataszterekben az adatbázismotor egy B+ fát implementál. Ez nem vonatkozik az oszlopcentrikus indexekre vagy a memóriaoptimalizált táblák indexére. További információ: SQL Server és Azure SQL index architektúrája és tervezési útmutatója.

A kompatibilis reteszek, például a megosztott Superlatch beszerzése kevesebb erőforrást használ, és jobban skálázza a gyakori lapokhoz való hozzáférést, mint egy nem particionált megosztott retesz, mivel a globális állapotszinkronizálási követelmény eltávolítása jelentősen javítja a teljesítményt, ha csak a helyi NUMA-memóriához fér hozzá. Ezzel szemben a kizárólagos (EX) Superlatch beszerzése drágább, mint egy EX normál retesz beszerzése, mivel az SQL-nek minden alhálózaton jeleznie kell. Ha egy Superlatch gyakori EX hozzáférési mintát használ, az SQL Engine lefokozhatja azt, miután a lap kivetésre kerül a pufferkészletből. Az alábbi ábra egy normál zárat és egy particionált Superlatch-et ábrázol:

Az SQL Server:Latches objektummal és a teljesítményfigyelő társított számlálóival információkat gyűjthet a Superlatches-ekről, beleértve a Superlatches- és Superlatch-előléptetések másodpercenkénti számát, valamint a Superlatch lefokozások másodpercenkénti számát. További információ az SQL Server:Latches objektumról és a kapcsolódó számlálókról: SQL Server, Latches objektum.

Reteszes várakozási típusok

A halmozott várakozási adatokat az SQL Server követi nyomon, és a Dinamikus felügyeleti nézet (DMW) sys.dm_os_wait_statshasználatával érhető el. Az SQL Server a DMV megfelelője wait_typesys.dm_os_wait_stats által meghatározott három reteszes várakozási típust alkalmaz:

• Puffer (BUF) retesz: a felhasználói objektumok indexének és adatoldalainak konzisztenciájának garantálására szolgál. Emellett az SQL Server által a rendszerobjektumokhoz használt adatlapokhoz való hozzáférés védelmére is használhatók. A foglalásokat kezelő oldalakat például pufferzárolások védik. Ezek közé tartoznak a szabad lapterület (PFS), a globális foglalási térkép (GAM), a megosztott globális foglalási térkép (SGAM) és az indexfoglalási térkép (IAM) oldalai. A pufferzárak a sys.dm_os_wait_stats jelentésben szerepelnek wait_type értékkel PAGELATCH_*.

• Nem pufferes (nem BUF) retesz: a pufferkészlet lapjaitól eltérő memóriabeli struktúrák konzisztenciájának garantálására szolgál. A nem pufferzárakra való várakozásokat a rendszer a következőként wait_typeLATCH_*jelenti: .

• I/O-retesz: a pufferzárak egy részhalmaza, amely garantálja a pufferzárakkal védett struktúrák konzisztenciáját, ha ezek a struktúrák I/O-művelettel történő betöltést igényelnek a pufferkészletbe. Az IO-reteszek megakadályozzák, hogy egy másik szál ugyanazt a lapot betöltse a pufferkészletbe egy nem kompatibilis reteszszel. A wait_type társítva egy PAGEIOLATCH_*-val.

  Megjegyzés:

  Ha jelentős PAGEIOLATCH várakozásokat lát, az azt jelenti, hogy az SQL Server az I/O-alrendszerre vár. Bár bizonyos mértékű PAGEIOLATCH várakozás várható és normális működésre van szükség, ha az átlagos PAGEIOLATCH várakozási idő következetesen 10 ezredmásodperc felett van, meg kell vizsgálnia, hogy miért van nyomás alatt az I/O alrendszer.

Ha a sys.dm_os_wait_stats DMV vizsgálatakor nem puffer zárak jelennek meg, meg kell vizsgálni a sys.dm_os_latch_stats annak érdekében, hogy részletes lebontást kapjunk a nem puffer zárak összesített várakozási adatáról. Az összes pufferzár-várakozás a BUFFER reteszosztály alá van besorolva, a fennmaradóak pedig a nem pufferzárak besorolására szolgálnak.

Az SQL Server zárolási ütközések tünetei és okai

Egy forgalmas, magas egyidejűségű rendszerben normális jelenség, hogy az SQL Serveren található, gyakran hozzáfért és reteszekkel vagy más vezérlési mechanizmusokkal védett struktúrákon versenyhelyzet lép fel. Problémásnak számít, ha az oldal reteszének beszerzéséhez kapcsolódó versengés és várakozási idő elegendő az erőforrás (CPU) kihasználtságának csökkentéséhez, ami akadályozza az átviteli sebességet.

Példa reteszes versengésre

Az alábbi ábrán a kék vonal az SQL Server átviteli sebességét jelöli, a másodpercenkénti tranzakciók alapján mérve; a fekete vonal az átlagos lapzáridőt jelöli. Ebben az esetben minden tranzakció egy műveletet hajt végre egy fürtözött indexbe, sorrendben növekvő kezdőértékkel, például egy bigint típusú adatoszlop feltöltésekor. Mivel a CPU-k száma 32-re nőtt, nyilvánvaló, hogy a teljes átviteli sebesség csökkent, és az oldalzároló várakozási ideje körülbelül 48 ezredmásodpercre nőtt, amint azt a fekete vonal is bizonyítja. Ez az inverz kapcsolat az átviteli sebesség és a lapzáras várakozási idő között gyakori eset, amely könnyen diagnosztizálható.

A teljesítmény, amikor a retesz versengés fel van oldva

Az alábbi ábrán látható, hogy az SQL Server már nem szűk keresztmetszetű a laptartó várakozásoknál, és az átviteli sebesség a másodpercenkénti tranzakciók száma alapján 300%-val nőtt. Ezt a cikk későbbi részében ismertetett hash-particionálás számított oszloppal technikával valósították meg. Ez a teljesítménybeli javulás a nagy számú maggal és magas szintű egyidejűséggel rendelkező rendszerekre irányul.

A reteszelési versengést befolyásoló tényezők

Az OLTP-környezetekben a teljesítményt akadályozó reteszes versengést általában az alábbi tényezők egy vagy több tényezőjével kapcsolatos magas egyidejűség okozza:

Tényező	Részletek
Az SQL Server által használt logikai CPU-k nagy száma	A reteszes versengés bármely többmagos rendszeren előfordulhat. Az SQLCAT-ben a túlzott reteszelő versengés, amely az alkalmazások elfogadható szinten túli teljesítményét befolyásolja, leggyakrabban a 16+ CPU-maggal rendelkező rendszereken figyelhető meg, és növekedhet, mivel több mag érhető el.
Sématervezési és hozzáférési minták	A B-fa mélysége, a fürtözött és nem fürtözött indexek kialakítása, a sorok mérete és sűrűsége oldalanként, valamint a hozzáférési minták (olvasási/írási/törlési tevékenységek) olyan tényezők, amelyek hozzájárulhatnak a túlzott oldalelzárási versengéshez.
Magas fokú egyidejűség az alkalmazás szintjén	A túlzott oldalzárlat-versengés általában az alkalmazásszintről érkező magas szintű egyidejű kérésekkel együtt fordul elő. Vannak bizonyos programozási eljárások, amelyek nagy számú kérést is bevezethetnek egy adott oldalhoz.
AZ SQL Server-adatbázisok által használt logikai fájlok elrendezése	A logikai fájlelrendezés hatással lehet az oldalkapcsolási versengés szintjére, amelyet olyan allokációs struktúrák okoznak, mint az oldalszabad terület (PFS), a globális allokációs térkép (GAM), a megosztott globális allokációs térkép (SGAM) és az indexallokációs térkép (IAM). További információkért lásd: TempDB felügyelete és hibaelhárítása: Foglalási szűk keresztmetszet.
Az I/O alrendszer teljesítménye	A jelentős PAGEIOLATCH várakozások azt jelzik, hogy az SQL Server az I/O-alrendszeren várakozik.

Az SQL Server reteszmegosztási ütközésének diagnosztizálása

Ez a szakasz az SQL Server reteszes versengés diagnosztizálásához nyújt információt annak megállapításához, hogy az problémás-e a környezet számára.

Eszközök és módszerek a reteszütközés diagnosztizálásához

A reteszelési versengés diagnosztizálására használt eszközök a következők:

• Teljesítményfigyelő az SQL Server processzorkihasználtságának és várakozási idejének monitorozásához, valamint annak megállapításához, hogy van-e kapcsolat a processzorkihasználtság és a reteszelő várakozási idő között.

• Az SQL Server DMV-jei, amelyek a problémát okozó retesz típusának és az érintett erőforrásnak a meghatározására használhatók.

• Bizonyos esetekben az SQL Server-folyamat memóriaképeit Windows hibakeresési eszközökkel kell beszerezni és elemezni.

Megjegyzés:

Ez a speciális hibaelhárítási szint általában csak akkor szükséges, ha a nem pufferelt reteszelés hibaelhárítása történik. Érdemes lehet a Microsoft terméktámogatási szolgálatát igénybe venni az ilyen típusú speciális hibaelhárításhoz.

A reteszes versengés diagnosztizálási technikai folyamata az alábbi lépésekben foglalható össze:

1. Állapítsa meg, hogy van-e olyan versengés, amely reteszproblémákkal kapcsolatos lehet.

2. Az érintett retesz és erőforrások típusának meghatározásához használja az SQL Server Retesz versengési szkriptjei függelékben megadott DMV-nézeteket.

3. Enyhítse a versengést a különböző táblaminták reteszelési versengésének kezelése című részben leírt technikák egyikével.

A reteszes versengés mutatói

Ahogy korábban említettem, a reteszelés csak akkor problémás, ha a lapzárlatok beszerzéséhez kapcsolódó versengés és várakozási idő megakadályozza, hogy az átviteli sebesség növekedjen, amikor a CPU-erőforrások elérhetők. A versengés elfogadható mennyiségének meghatározásához holisztikus megközelítésre van szükség, amely figyelembe veszi a teljesítmény- és átviteli sebességre vonatkozó követelményeket, valamint a rendelkezésre álló I/O- és CPU-erőforrásokat. Ez a szakasz végigvezeti a reteszelési versengés számítási feladatokra gyakorolt hatásának meghatározásán az alábbiak szerint:

1. Mérje meg a teljes várakozási időt egy reprezentatív teszt során.
2. Rangsorolja őket sorrendben.
3. Határozza meg a rácsokhoz kapcsolódó várakozási idők arányát.

A DMV-ből kumulatív várakozási sys.dm_os_wait_stats információk érhetők el. A zárversengés leggyakoribb típusa a pufferzár-versengés, amely a reteszek várakozási idejének növekedésével wait_typePAGELATCH_*figyelhető meg. A nem pufferzárak a LATCH* várakozási típus szerint vannak csoportosítva. Az alábbi ábrán látható, hogy először kumulatívan kell megvizsgálnia a rendszer várakozásait a sys.dm_os_wait_stats DMV használatával a puffer vagy a nem pufferzárak által okozott teljes várakozási idő százalékos arányának meghatározásához. Ha nem pufferzárakkal kapcsolatban merül fel probléma, akkor a sys.dm_os_latch_stats DMV-t is meg kell vizsgálni.

Az alábbi ábra a DMV-k által sys.dm_os_wait_statssys.dm_os_latch_stats visszaadott információk közötti kapcsolatot ismerteti.

További információ a sys.dm_os_wait_stats DMV-ről: sys.dm_os_wait_stats az SQL Server súgójában.

További információ a sys.dm_os_latch_stats DMV-ről: sys.dm_os_latch_stats az SQL Server súgójában.

A reteszelési várakozási idő alábbi mértékei azt jelzik, hogy a túlzott reteszelés hatással van az alkalmazás teljesítményére:

• Az átlagos oldalzár várakozási idő folyamatosan növekszik az átviteli sebességgel: Ha az átlagos oldalzár várakozási ideje folyamatosan növekszik az átviteli sebességgel, és ha az átlagos pufferzár várakozási idő is meghaladja a várt lemezes reakcióidőt, akkor a DMV használatával vizsgálja meg a jelenlegi várakozó feladatokat. Az átlagok félrevezetőek lehetnek, ha elkülönítve elemzik, ezért fontos, hogy a számítási feladatok jellemzőinek megismerése érdekében a rendszer élőben legyen vizsgálva. Különösen ellenőrizze, hogy vannak-e nagy várakozások PAGELATCH_EX és/vagy PAGELATCH_SH bármelyik oldalon megjelenő kérések esetében. Az alábbi lépéseket követve diagnosztizálhatja az oldalzárak átlagos várakozási idejének növelését az átviteli sebességgel:

  • Az aktuális várakozási feladatok megtekintéséhez és az átlagos reteszes várakozási idő méréséhez használja a Query sys.dm_os_waiting_tasks rendezése munkamenet-azonosító szerint vagy a várakozások kiszámítása egy időszak alatt mintaszkripteket.

  • A lekérdezéspufferleírók mintaszkripttel határozhatja meg azokat az objektumokat, amelyek reteszelési versengést okoznak annak az indexnek és mögöttes táblának a meghatározásához, amelyen a versengés történik.

  • Mérje meg az átlagos oldalzárlat-várakozási időt az MSSQL teljesítményfigyelő számlálójával%InstanceName%\Várakozási statisztikák\Lapelzárási várakozások\Átlagos várakozási idő vagy a sys.dm_os_wait_stats DMV futtatásával.

  Megjegyzés:

  Egy adott várakozási típus átlagos várakozási idejének kiszámításához (visszaadottként sys.dm_os_wait_statswt_:type) oszd el a teljes várakozási időt (visszaadottként wait_time_ms) a várakozási feladatok számával (visszaadva így waiting_tasks_count).

• A reteszes várakozási típusokkal töltött teljes várakozási idő százalékos aránya a csúcsterhelés során: Ha az átlagos reteszelési várakozási idő a teljes várakozási idő százalékos arányában növekszik az alkalmazás terhelésének megfelelően, akkor a reteszelési versengés hatással lehet a teljesítményre, és meg kell vizsgálni.

  Oldalzárlat és nem oldalzárlat várakozási idejét mérje az SQL Server, Wait Statistics objektum teljesítményszámlálóival. Ezután hasonlítsa össze ezeknek a teljesítményszámlálóknak az értékeit a processzorral, az I/O-val, a memóriával és a hálózati átviteli sebességgel társított teljesítményszámlálókkal. A tranzakciók/mp és a kötegkérések/mp például az erőforrás-kihasználtság két jó mértéke.

  Megjegyzés:

  Az egyes várakozási típusokhoz tartozó relatív várakozási idő nem szerepel a sys.dm_os_wait_stats DMV-ben, mert ez a DMW az SQL Server példányának legutóbbi elindítása óta eltelt várakozási időt méri, vagy a kumulatív várakozási statisztikákat a rendszer visszaállította a használatával DBCC SQLPERF. Az egyes várakozási típusokhoz tartozó relatív várakozási idő kiszámításához készítsen pillanatképet sys.dm_os_wait_stats a csúcsterhelés előtt, a csúcsterhelés után, majd számítsa ki a különbséget. Erre a célra a Várakozások számítása időszakon keresztül példaszkript használható.

  Csak tesztkörnyezet esetén törölje a sys.dm_os_wait_stats DMV-t a következő paranccsal:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Egy hasonló parancs futtatható a sys.dm_os_latch_stats DMV törléséhez:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Az átviteli sebesség nem nő, és bizonyos esetekben csökken, ahogy az alkalmazás terhelése növekszik, és az SQL Server számára elérhető CPU-k száma nő: Ezt a Reteszes versengés példában szemléltetjük.

• A CPU-kihasználtság nem nő az alkalmazás számítási feladatainak növekedésével: Ha a rendszer processzorhasználata nem növekszik az alkalmazás átviteli sebességének növekedésével egyidejűleg, ez azt jelzi, hogy az SQL Server valamire vár, és a reteszes versengés tünete.

A kiváltó okok elemzése. Még ha az előző feltételek mindegyike igaz is, akkor is lehetséges, hogy a teljesítményproblémák kiváltó oka máshol található. Valójában a legtöbb esetben az optimálisnál rosszabb processzorhasználatot más típusú várakozások okozzák, mint például a zárolások blokkolása, az I/O-val kapcsolatos várakozások vagy a hálózattal kapcsolatos problémák. Ökölszabályként mindig a legjobb megoldás az erőforrás-várakozás feloldása, amely a teljes várakozási idő legnagyobb arányát képviseli, mielőtt részletesebb elemzést folytatna.

Az aktuális várakozási pufferzárak elemzése

A pufferzár-versengés a reteszek várakozási idejének megnövekedéseként wait_type jelenik meg a DMV-ben vagy PAGELATCH_*PAGEIOLATCH_* a DMV-ben sys.dm_os_wait_stats látható módon. A rendszer valós idejű megtekintéséhez futtassa a következő lekérdezést egy rendszeren, hogy csatlakoztassa az sys.dm_os_wait_stats, sys.dm_exec_sessions és sys.dm_exec_requests DMV-ket. Az eredmények a kiszolgálón futó munkamenetek aktuális várakozási típusának meghatározására használhatók.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

A lekérdezés által közzétett statisztikák leírása a következő:

Statisztika	Leírás
session_id	A tevékenységhez társított munkamenet azonosítója.
wait_type	Az SQL Server által a motorban rögzített várakozás típusa, amely megakadályozza az aktuális kérés végrehajtását.
last_wait_type	Ha ez a kérés korábban le lett tiltva, ez az oszlop az utolsó várakozás típusát adja vissza. Nem lehet null értékű.
wait_duration_ms	Ezredmásodpercben megadott várakozási idő az SQL Server-példány elindítása vagy a halmozott várakozási statisztikák visszaállítása óta eltelt várakozási típusra.
blocking_session_id	A kérést blokkoló munkamenet azonosítója.
blocking_exec_context_id	A tevékenységhez társított végrehajtási környezet azonosítója.
resource_description	Az resource_description oszlopban a várt lap pontos formátuma látható: <database_id>:<file_id>:<page_id>

Az alábbi lekérdezés az összes nem pufferelt rács adatait adja vissza:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

A lekérdezés által közzétett statisztikák leírása a következő:

Statisztika	Leírás
latch_class	Az SQL Server által a motorban rögzített reteszelés típusa, amely megakadályozza az aktuális kérés végrehajtását.
waiting_requests_count	Várakozások száma ebben az osztályban az SQL Server újraindítása óta. Ez a számláló a reteszes várakozás kezdetekor növekszik.
wait_time_ms	Az adott retesztípusra való várakozás teljes ideje ezredmásodpercben.
max_wait_time_ms	Maximális idő ezredmásodpercben bármely kérés esetén, amely erre a retesz típusra várakozik.

A DMV által visszaadott értékek kumulatívak az adatbázismotor legutóbbi újraindítása vagy a DMV alaphelyzetbe állítása óta. A sqlserver_start_time oszlopával keresse meg az adatbázismotor utolsó indítási idejét. Egy olyan rendszeren, amely már régóta fut, ez azt jelenti, hogy néhány statisztika, mint például max_wait_time_ms, ritkán hasznos. A következő paranccsal állíthatja alaphelyzetbe a DMV várakozási statisztikáit:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

az SQL Server reteszelési ütemezési forgatókönyvei

Az alábbi forgatókönyvek esetében megfigyelték, hogy túlzott reteszes versengést okozhatnak.

Utolsó vagy záró oldal beszúrásának ütközése

Az OLTP gyakori gyakorlata egy fürtözött index létrehozása identitás- vagy dátumoszlopon. Ez segít fenntartani az index megfelelő fizikai rendszerét, ami nagyban segíthet az olvasások és az indexbe történő írások teljesítményében. Ez a sématerv véletlenül azonban reteszes versengéshez vezethet. Ez a probléma leggyakrabban nagy táblákban, kis sorokkal fordul elő, és az olyan indexekbe történő beszúráskor jelentkezik, amelyek tartalmaznak egy sorban növekvő kezdőkulcs-oszlopot, például egy egész szám vagy dátum-idő kulcsot. Ebben a forgatókönyvben az alkalmazás ritkán végez frissítéseket vagy töröl, ez alól kivételt képeznek az archiválási műveletek.

Az alábbi példában az első szál és a második szál egy olyan rekord beszúrását szeretné végrehajtani, amely a 299. oldalon lesz tárolva. Logikai zárolás szempontjából nincs probléma, mivel sorszintű zárolások vannak használatban, és az ugyanazon az oldalon lévő mindkét rekord kizárólagos zárolásai egyszerre tárolhatók. A fizikai memória integritásának biztosítása érdekében azonban egyszerre csak egy szál rendelkezhet kizárólagos reteszelővel, így az oldalhoz való hozzáférés szerializálva van, hogy megakadályozza a memória elveszett frissítéseit. Ebben az esetben az 1. szál megszerzi a kizárólagos reteszt, míg a 2. szál várakozik, és ez a várakozás regisztrálódik ennél az erőforrásnál a várakozási statisztikákban. A wait_type DMV-ben ez a sys.dm_os_waiting_tasks értéken jelenik meg.

Ezt a versengést gyakran "Last Page Insert" (Utolsó oldal beszúrása) versengésnek is nevezik, mert a B-fa jobb szélső szélén fordul elő, ahogyan az az alábbi ábrán látható:

Az ilyen típusú reteszelés a következőképpen magyarázható. Amikor új sort szúr be egy indexbe, az SQL Server a következő algoritmussal hajtja végre a módosítást:

1. Az új rekord tárolásához keresse meg a megfelelő lapot a B-fán.

2. Zárja be a lapot PAGELATCH_EX, nehogy mások módosítsák, és szerezze be a megosztott rácsokat (PAGELATCH_SH) az összes nem levéloldalon.

   Megjegyzés:

   Bizonyos esetekben az SQL Engine megköveteli, hogy EX zárszerkezeteket is be kell szerezni a nem levél B-fa oldalakon. Ha például oldalfelosztás történik, a közvetlenül érintett oldalakat kizárólag a ()reteszekkel kell elzárni.PAGELATCH_EX

3. Rögzítsen egy naplóbejegyzést arról, hogy a sort módosították.

4. Adja hozzá a sort a laphoz, és jelölje meg a lapot piszkosként.

5. Bontsa ki az összes lapot.

Ha a táblázatindex egy egymás után növekvő kulcson alapul, minden új beszúrás ugyanarra a lapra kerül a B-fa végén, amíg meg nem telik az oldal. Magas egyidejűségi forgatókönyvek esetén ez versengést okozhat a B-fa jobb szélén, és fürtözött és nem fürtözött indexeken fordulhat elő. Az ilyen típusú versengés által érintett táblák elsősorban INSERT lekérdezéseket fogadnak, és a problémás indexek lapjai általában viszonylag sűrűek (például egy sor mérete ~165 bájt (beleértve a sorterhelést is) egyenlő oldalanként ~49 sorral). Ebben a beszúrásokkal teli példában várakozásokra számítunk PAGELATCH_EX/PAGELATCH_SH , és ez egy tipikus megfigyelés. Az oldalzár-várakozások és a faoldal-zár-várakozások vizsgálatához használja a sys.dm_db_index_operational_stats DMV-t.

Az alábbi táblázat az ilyen típusú reteszelés során megfigyelt főbb tényezőket foglalja össze:

Tényező	Tipikus megfigyelések
Az SQL Server által használt logikai processzorok	Az ilyen típusú reteszelés főleg 16+ processzormagos rendszereken és leggyakrabban 32+ PROCESSZORmagos rendszereken fordul elő.
Sématervezési és hozzáférési minták	A tranzakciós adatokhoz egy tábla indexének első oszlopaként egymást követő módon növekvő identitásértéket használ. Az index növekvő elsődleges kulccsal rendelkezik, és magas beszúrási aránnyal bír. Az indexnek legalább egy egymás után növekvő oszlopértéke van. Általában kis sorméret, oldalanként több sortal.
Megfigyelt várakozási típus	Számos szál verseng ugyanazon erőforrásért, amelyek kizárólagos (EX) vagy megosztott (SH) reteszelési várakozási időt tapasztalnak az azonos resource_description esetében a sys.dm_os_waiting_tasks DMV-ben, hasonlóan ahhoz, amit a Query sys.dm_os_waiting_tasks a várakozási időtartam szerint rendezve ad vissza.
Megfontolandó tervezési tényezők	Fontolja meg az indexoszlopok sorrendjének módosítását a nem szekvenciális indexcsökkentési stratégiában leírtak szerint, ha garantálni tudja, hogy a beszúrások egyenletesen oszlanak el a B-fán. Ha a Hash partíciós enyhítési stratégiát használja, az megfosztja a lehetőséget a particionálás más célokra történő használatának, mint például a csúszóablak-archiválás. A Hash-partíció csökkentési stratégia használata partíció megszüntetési problémákhoz vezethet az alkalmazás által használt SELECT lekérdezések esetében.

Nem fürtözött indexszel és szórványos beszúrásokkal rendelkező kis táblák reteszelési ütközése (sorbanállási tábla)

Ez a forgatókönyv általában akkor jelenik meg, ha egy SQL-táblát ideiglenes üzenetsorként használnak (például aszinkron üzenetkezelő rendszerben).

Ebben a forgatókönyvben a kizárólagos (EX) és a megosztott (SH) lezárási versengés a következő feltételek mellett fordulhat elő:

• A beszúrási, kijelölési, frissítési vagy törlési műveletek nagy egyidejűség mellett történnek.
• A sor mérete viszonylag kicsi (sűrű oldalakhoz vezet).
• A táblázat sorainak száma viszonylag kicsi; egy sekély B-fához vezet, amelyet két vagy három indexmélység határoz meg.

Megjegyzés:

Még a nagyobb mélységű B-fák is szembesülhetnek versengéssel az ilyen típusú hozzáférési mintánál, ha az adatmanipulációs nyelv (DML) gyakorisága és a rendszer egyidejűsége elég magas. A zárolási versengés szintje kifejezettebbé válhat az egyidejűség növekedésével, ha 16 vagy több processzormag érhető el a rendszer számára.

A reteszelés akkor is előfordulhat, ha a hozzáférés véletlenszerű a B-fán, például ha egy nem szekvenciális oszlop a fürtözött index vezető kulcsa. Az alábbi képernyőkép egy olyan rendszerről származik, amely ilyen típusú reteszelési versengést tapasztal. Ebben a példában a versengés oka a lapok sűrűsége, amelyet a kis sorméret és a viszonylag sekély B-fa okoz. Az egyidejűség növekedésével a lapok reteszelési versengése akkor is előfordul, ha a beszúrások véletlenszerűek a B-fán, mivel a GUID volt az index első oszlopa.

Az alábbi képernyőképen a várakozások mind a pufferadatoldalakon, mind a pfS-oldalak szabad területén történnek. Még akkor is, ha az adatfájlok száma megnőtt, a retesz versengés elterjedt a puffer adatoldalakon.

Az alábbi táblázat az ilyen típusú reteszelés során megfigyelt főbb tényezőket foglalja össze:

Tényező	Tipikus megfigyelések
Az SQL Server által használt logikai processzorok	A reteszes versengés elsősorban a 16+ processzormaggal rendelkező számítógépeken fordul elő.
Sématervezési és hozzáférési minták	Nagy gyakoriságú beszúrási/kiválasztási/frissítési/törlési hozzáférési minták kis táblákhoz. Sekély B-fa (két vagy három indexmélység). Kis sorméret (sok rekord oldalanként).
Az egyidejűség szintje	A reteszes versengés csak az alkalmazásszintről érkező, egyidejű kérések magas szintje alatt történik.
Megfigyelt várakozási típus	Figyelje meg a várakozásokat a puffer (PAGELATCH_EX és PAGELATCH_SH) és a nem puffer zárolás ACCESS_METHODS_HOBT_VIRTUAL_ROOT esetén a gyökéreloszlások miatt. Továbbá a PFS-oldalakon is PAGELATCH_UP várakozik. További információért a nem pufferelt reteszek várakozásairól, tekintse meg a SQL Server súgóban található sys.dm_os_latch_stats részt.

A sekély B-fa és a véletlenszerű beszúrások kombinációja az indexben hajlamos arra, hogy oldalhasadásokat okoz a B-fában. Az oldalfelosztás végrehajtásához az SQL Servernek minden szinten meg kell szereznie a megosztott (SH) rácsokat, majd kizárólagos (EX) rácsokat kell beszereznie a B-fában lévő lapokhoz, amelyek részt vesznek az oldalfelosztásokban. Amikor az egyidejűség magas, és az adatok folyamatosan beillesztésre és törlésre kerülnek, a B-fa gyökérhasadásai is előfordulhatnak. Ebben az esetben előfordulhat, hogy más beszúrásoknak várniuk kell a B-fán beszerzett nem pufferzárakra. Ez a DMV-ben ACCESS_METHODS_HOBT_VIRTUAL_ROOT megfigyelt retesztípuson nagy számú várakozásként sys.dm_os_latch_stats nyilvánul meg.

A következő szkript módosítható az érintett tábla indexeihez tartozó B-fa mélységének meghatározásához.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Reteszelési versengés a PFS oldalak szabad területén

A PFS jelentése Page Free Space, az SQL Server minden adatbázisfájlban 8088 oldalonként egy PFS-lapot foglal le (kezdve a következővel PageID = 1). A PFS-lap minden bájtja rögzíti az információkat, beleértve azt is, hogy mennyi szabad terület van az oldalon, ha van lefoglalva, vagy sem, és hogy az oldal tárolja-e a szellemrekordokat. A PFS-lap információkat tartalmaz a kiosztásra rendelkezésre álló lapokról, ha egy beszúrási vagy frissítési művelet új lapot igényel. A PFS-lapot több forgatókönyvben is frissíteni kell, beleértve a foglalások vagy felszabadítások előfordulását is. Mivel a PFS-lap védelméhez frissítési (UP) retesz használata szükséges, a PFS-oldalakon a reteszes versengés akkor fordulhat elő, ha viszonylag kevés adatfájl található egy fájlcsoportban és sok processzormaggal. A probléma megoldásának egyszerű módja a fájlcsoportonkénti fájlok számának növelése.

Figyelmeztetés

A fájlcsoportonkénti fájlok számának növelése hátrányosan befolyásolhatja bizonyos terhelések teljesítményét, például a sok nagy rendezési művelettel rendelkező betöltéseket, amelyek memóriát ürítenek a lemezre.

Ha sok PAGELATCH_UP várakozás történik a PFS- vagy SGAM-lapoknál tempdb, hajtsa végre az alábbi lépéseket a szűk keresztmetszet kiküszöbölése érdekében:

1. Adjon hozzá adatfájlokat tempdb , hogy a tempdb-adatfájlok száma megegyezik a kiszolgáló processzormagjainak számával.

2. Engedélyezze az SQL Server nyomkövetési jelző 1118-at.

A rendszeroldalakon való ütközés által okozott foglalási szűk keresztmetszetekről a a foglalás szűk keresztmetszete című blogbejegyzésben talál további információt.

Táblaértékű függvények és reteszütközés a tempdb-ben

A foglalási versengésen kívül más tényezők is okozhatnak reteszes versengést tempdb a lekérdezéseken belül, például a nagy TVF-használat.

A különböző táblázatminták reteszes versengésének kezelése

A következő szakaszok olyan technikákat mutatnak be, amelyek a teljesítményproblémák kezelésére vagy megkerülésére használhatók, amelyek a túlzott reteszelésből adódnak.

Nem szekvenciális vezető indexkulcs használata

A reteszelési versengés kezelésének egyik módszere, ha a szekvenciális indexkulcsot nem szekvenciális indexkulccsal cseréli le, így egyenletesen elosztja a beszúrásokat egy indextartományon belül.

Ez általában úgy történik, hogy az indexben van egy vezető oszlop, amely a számítási feladatot arányosan osztja el. Itt néhány lehetőség közül választhat:

Lehetőség: A tábla oszlopának használata az indexkulcstartomány értékeinek elosztásához

Értékelje a munkaterhelést egy olyan természetes érték alapján, amely a beszúrások elosztására használható a kulcstartományon belül. Vegyük például azt az ATM-banki forgatókönyvet, ahol ATM_ID jó választás lehet a beszúrások tranzakciós táblába való elosztására a kivonásokhoz, mivel egy ügyfél egyszerre csak egy ATM-et használhat. Hasonlóképpen, egy értékesítési pontrendszerben, talán Checkout_ID vagy egy áruházazonosító lenne egy természetes érték, amely a beszúrások kulcstartományon belüli elosztására használható. Ehhez a technikához létre kell hoznia egy összetett indexkulcsot, amelynek első kulcsoszlopa vagy az azonosított oszlop értéke, vagy az érték kivonata egy vagy több további oszlopmal kombinálva egyediséget biztosít. A legtöbb esetben az érték kivonata működik a legjobban, mert a túl sok különböző érték rossz fizikai elrendezést eredményez. Egy értékesítési pontrendszerben például létrehozhat egy kivonatot az áruházazonosítóból, amely valamilyen modulo, amely igazodik a processzormagok számához. Ez a technika viszonylag kis számú tartományt eredményezne a táblázatban, azonban elegendő lenne a beszúrások oly módon történő elosztása, hogy elkerülje a reteszes versengést. Az alábbi kép ezt a technikát szemlélteti.

Fontos

Ez a minta ellentmond a hagyományos indexelési ajánlott eljárásoknak. Bár ez a technika biztosítja a beszúrások egységes eloszlását a B-fán, szükség lehet egy sémamódosításra az alkalmazás szintjén is. Emellett ez a minta negatívan befolyásolhatja a fürtözött indexet használó tartományvizsgálatokat igénylő lekérdezések teljesítményét. A számítási feladatok mintáinak elemzése szükséges annak megállapításához, hogy ez a kialakítási megközelítés megfelelően működik-e. Ezt a mintát akkor érdemes implementálni, ha hajlandó feláldozni bizonyos mértékű szekvenciális vizsgálat teljesítményét a beszúrási teljesítmény és a skálázhatóság érdekében.

Ez a minta egy teljesítménylabor-foglalkozás során lett implementálva, és megoldotta a 32 fizikai CPU-maggal rendelkező rendszeren a reteszek közti versengést. A táblát a tranzakció végén lévő záró egyenleg tárolására használták; minden üzleti tranzakció egyetlen beszúrást hajtott végre a táblában.

Eredeti tábladefiníció

Az eredeti tábladefiníció használatakor a pk_table1 fürtözött indexnél túlzott retesz versengést figyeltek meg.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Megjegyzés:

A tábladefiníció objektumnevei módosultak az eredeti értéküktől.

Átrendezett indexdefiníció

Az index UserID kulcsoszlopainak átrendezése az elsődleges kulcs első oszlopaként szinte véletlenszerűen elosztotta a beszúrásokat az oldalak között. Az eredményként kapott eloszlás nem volt 100% véletlenszerű, mivel nem minden felhasználó egyidejűleg van online, de az eloszlás elég véletlenszerű volt ahhoz, hogy enyhítse a túlzott retesz versengést. Az indexdefiníció átrendezésének egyik hátránya, hogy az adott táblára vonatkozó SELECT lekérdezéseket úgy kell módosítani, hogy az egyenlőségi predikátumok esetén mind UserID és TransactionID-t használhassanak.

Fontos

Győződjön meg arról, hogy a változtatásokat alaposan teszteli egy tesztkörnyezetben, mielőtt éles környezetben futtatná.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Kivonatérték használata első oszlopként az elsődleges kulcsban

A következő tábladefinícióval olyan modulo hozható létre, amely a processzorok számához igazodik, HashValue és az egymás után növekvő érték TransactionID használatával jön létre, hogy egységes eloszlást biztosítson a B-fa között:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Lehetőség: Guid azonosító használata az index első kulcsoszlopaként

Ha nincs természetes elválasztó, akkor a GUID oszlop az index vezető kulcsoszlopaként használható a beszúrások egységes eloszlásának biztosításához. Amikor az indexkulcs részeként a GUID az első oszlopként van használva, ez a megközelítés lehetővé teheti más funkciók particionálását, ugyanakkor a technika több oldalhasadást, gyenge fizikai elrendezést és alacsony oldalsűrűséget is okozhat, amik potenciális hátrányok.

Megjegyzés:

A GUID-k indexek vezető kulcsoszlopaként való használata erősen vitatott téma. A módszer előnyeinek és hátrányainak részletes megvitatása a cikk hatókörén kívül esik.

Hash particionálás használata számított oszloppal

Az SQL Serveren belüli táblaparticionálással csökkenthető a túlzott láncolási ütközés. Particionált táblán történő számított oszloppal való hash particionálási séma létrehozása általában a következő lépésekkel valósítható meg:

1. Hozzon létre egy új fájlcsoportot, vagy használjon egy meglévő fájlcsoportot a partíciók tárolásához.

2. Ha új fájlcsoportot használ, egyenlően egyensúlyozza ki az egyes fájlokat a LUN-tal szemben, ügyelve az optimális elrendezés használatára. Ha a hozzáférési minta nagy mennyiségű beszúrást tartalmaz, mindenképpen hozza létre ugyanazt a számú fájlt, mint a fizikai processzormagok az SQL Server-számítógépen.

3. CREATE PARTITION FUNCTION A parancs használatával a táblákat X partíciókra particionálhatja, ahol az X az SQL Server-számítógépen található fizikai CPU-magok száma. (legalább 32 partíció)

   Megjegyzés:

   A partíciók számának 1:1-es igazítása a processzormagok számához nem mindig szükséges. Ez sok esetben kisebb érték lehet, mint a processzormagok száma. Ha több partíciót szeretne létrehozni, az nagyobb többletterhelést okozhat az olyan lekérdezések esetében, amelyeknek az összes partícióban kell keresniük, és ezekben az esetekben kevesebb partíció segíthet. A 64 és 128 logikai CPU-rendszereken valós ügyfél-számítási feladatokkal végzett SQLCAT-tesztelés során 32 partíció elegendő volt a túlzott reteszelés feloldásához és a skálázási célok eléréséhez. Végső soron a partíciók ideális számát teszteléssel kell meghatározni.

4. Használja a CREATE PARTITION SCHEME parancsot:

   • A partíciófüggvény kötése a fájlcsoportokhoz.
   • Adjon hozzá egy tinyint vagy smallint típusú hash oszlopot a táblához.
   • Számítsa ki a megfelelő kivonateloszlást. Például használja a HASHBYTES modullal vagy BINARY_CHECKSUM-vel.

A következő példaszkript testre szabható a megvalósításhoz:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Ez a szkript használható egy olyan tábla hash particionálására, amely utolsó oldal/záró oldal beszúrási ütközés miatt problémákat tapasztal. Ez a technika úgy helyezi át a versengést az utolsó oldalról, hogy particionálja a táblázatot, és elosztja a beszúrásokat a táblázatpartíciók között egy kivonatérték modulus művelettel.

Mit csinál a hash particionálás egy számított oszloppal?

Ahogy az alábbi ábra is mutatja, ez a technika az utolsó oldalról áthelyezi a versengést a kivonatoló függvény indexének újraépítésével és az SQL Server-számítógépen található fizikai PROCESSZORmagokkal azonos számú partíció létrehozásával. A beszúrások továbbra is a logikai tartomány végére kerülnek (sorrendben növekvő érték), de a hashérték modulus művelete biztosítja, hogy a beszúrások elosztásra kerüljenek a különböző B-fák között, ami enyhíti a szűk keresztmetszetet. Ezt a következő diagramok szemléltetik:

Kompromisszumok kivonatparticionálás használatakor

Bár a kivonat particionálása kiküszöbölheti a beszúrások ütközését, számos kompromisszumot kell figyelembe venni, amikor eldönti, hogy használja-e ezt a technikát.

• A legtöbb esetben a lekérdezéseket módosítani kell, hogy belefoglalják a kivonatpartíciót a predikátumba, és olyan lekérdezési tervhez vezessenek, amely nem biztosít partíciókivételt a lekérdezések kiadásakor. Az alábbi képernyőképen egy rossz terv látható, amely nem szünteti meg a partíciót a kivonatparticionálás implementálása után.

  

• Kiküszöböli a partíciók eltávolításának lehetőségét bizonyos más lekérdezéseken, például a tartományalapú jelentéseken.

• Ha egy hash partícionált táblát egy másik táblához kapcsol, a partíciók megszüntetésének érdekében a második táblát ugyanarra a kulcsra kell hash partícionálni, és a hash kulcsnak az illesztési feltételek részét kell képeznie.

• A hashparticionálás megakadályozza a particionálás használatát más kezelési funkciókhoz, például a csúszóablak-archiváláshoz és a partícióváltás funkcióhoz.

A kivonatparticionálás hatékony stratégia a túlzott reteszelési versengés mérséklése érdekében, mivel növeli a rendszer általános átviteli sebességét a beszúrások versengésének enyhítésével. Mivel bizonyos kompromisszumok is fennállnak, előfordulhat, hogy egyes hozzáférési minták esetében nem ez az optimális megoldás.

A zárolás ütközés kezelésére használt technikák összefoglalása

Az alábbi két szakasz összefoglalja a túlzott reteszelő versengés kezelésére használható technikákat:

Nem szekvenciális kulcs/index

Előnyök:

• Lehetővé teszi más particionálási funkciók használatát, például adatok archiválását csúsztatásos ablaksémával és partíciókapcsoló funkcióval.

Hátrányai:

• A kulcs/index kiválasztásakor jelentkező lehetséges kihívások, hogy a beszúrások egyenletes eloszlása mindig "elég közel" legyen.
• A GUID vezető oszlopként használható az egységes eloszlás biztosítására azzal a kikötéssel, hogy az túlzott oldaleloszlási műveleteket eredményezhet.
• A B-faszerkezet véletlenszerű beszúrásai túl sok oldalelosztó műveletet eredményezhetnek, és nem levél típusú oldalakon láncolati ütközést okozhatnak.

Hash particionálás számított oszloppal

Előnyök:

• A beszúrások átlátszóak.

Hátrányai:

• A particionálás nem használható a kívánt felügyeleti funkciókhoz, például az adatok partícióváltási beállítások használatával történő archiválásához.
• Partícióelhárítási problémákat okozhat a lekérdezések esetében, beleértve az egyéni és a tartományalapú kiválasztást/frissítést, valamint az összekapcsolást végző lekérdezéseket.
• A megőrzött számított oszlop hozzáadása offline művelet.

Jótanács

További technikákért lásd a blogbejegyzést PAGELATCH_EX várakozások és nehéz beszúrások.

Útmutató: Retesz versengés diagnosztizálása

Az alábbi útmutató az SQL Server-reteszkezés diagnosztizálása és a különböző táblaminták reteszének kezelése című cikkben leírt eszközöket és technikákat mutatja be egy valós forgatókönyvben előforduló probléma megoldásához. Ez a forgatókönyv egy ügyfélmegbízást ír le, amely egy értékesítési rendszer terheléstesztjének elvégzésére irányul, és amely körülbelül 8000 üzletet szimulált, amelyek tranzakciókat hajtottak végre egy 8 foglalatos, 32 fizikai magos rendszerű, 256 GB memóriával rendelkező SQL Server-alkalmazáson.

Az alábbi ábra az értékesítési pontrendszer teszteléséhez használt hardvert mutatja be:

Tünet: Gyakori lécek

Ebben az esetben nagy várakozásokat figyeltünk meg a PAGELATCH_EX esetén, ahol az általunk meghatározott magas szint jellemzően átlagosan több mint 1 ms. Ebben az esetben következetesen megfigyeltük a 20 ms-ot meghaladó várakozásokat.

Miután megállapítottuk, hogy a reteszzel kapcsolatos ütközés problémás volt, ezután megpróbáltuk meghatározni, hogy mi okozza ezt az ütközést.

A reteszelési versengést okozó objektum elkülönítése

A következő szkript a resource_description oszlopot használja annak elkülönítésére, hogy melyik index okozta a PAGELATCH_EX versengést:

Megjegyzés:

A szkript által visszaadott resource_description oszlop az erőforrás leírását adja meg a <DatabaseID,FileID,PageID> formátumban, ahol a DatabaseID társított adatbázis neve a DatabaseID érték DB_NAME() függvénynek való átadásával határozható meg.

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Ahogy az itt látható, a versengés a táblában LATCHTEST és az index neve CIX_LATCHTEST. A jegyzetnevek módosultak a számítási feladat anonimizálásához.

A fejlettebb szkriptekért, amelyek ismételten lekérdeznek és ideiglenes táblát használnak a konfigurálható időszak során felmerülő teljes várakozási idő meghatározására, lásd a függelékben a Lekérdezéspuffer-leírók: Objektumok Meghatározása Reteszelési Versengés Okán szakaszt.

Alternatív technika a reteszelést okozó objektum elkülönítésére

Néha nem praktikus a lekérdezés sys.dm_os_buffer_descriptors. Ahogy a rendszer memóriája és a pufferkészlet számára elérhető memória növekszik, úgy nő a DMV futtatásához szükséges idő is. A 256 GB-os rendszereken akár 10 perc vagy több időt is igénybe vehet a DMV futtatása. Rendelkezésre áll egy alternatív technika, amely az alábbiak szerint körvonalazódik, és egy másik számítási feladattal van szemléltetve, amelyet a laborban futtattunk:

1. Az aktuális várakozó feladatok lekérdezése a Függelék szkript Query sys.dm_os_waiting_tasks felhasználásával, Várakozási időtartam szerint rendezve.

2. Azonosítsa azt a kulcslapot, ahol konvoj figyelhető meg, ami akkor fordul elő, ha több szál verseng ugyanazon az oldalon. Ebben a példában a beszúrást végrehajtó szálak a B-fában lévő záró oldalon versengnek, és várják meg, amíg el nem kapnak egy EX reteszet. Ezt jelzi a resource_description az első lekérdezésben, esetünkben 8:1:111305.

3. Engedélyezze a 3604 nyomkövetési jelzőt, amely a következő szintaxissal DBCC PAGE teszi elérhetővé a lap további információit, cserélje le a zárójelben lévő értékre a resource_description keresztül kapott értéket:

   A konzol kimenetének engedélyezéséhez engedélyezze a nyomkövetési jelző 3604-et:

   DBCC TRACEON (3604);
   

   Vizsgálja meg a lap részleteit:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Vizsgálja meg a DBCC kimenetét. A mi esetünkben 78623323egy társított metaadat-objektumazonosítónak kell lennie.

   

5. Most futtathatjuk a következő parancsot a versengést okozó objektum nevének meghatározásához, amely a várt módon történik LATCHTEST.

   Megjegyzés:

   Győződjön meg arról, hogy a megfelelő adatbázis-környezetben van, ellenkező esetben a lekérdezés visszaadja NULL.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Összegzés és eredmények

A fenti technikával sikerült meggyőződnünk arról, hogy a versengés egy fürtözött indexen történt, amely egymás után növekvő kulcsértéket tartalmaz a táblán, amely messze a legtöbb beszúrást kapta. Ez a fajta versengés nem ritka az olyan indexek esetében, amely egymás után növekvő kulcsértékkel rendelkezik, például datetime, identity vagy alkalmazás által létrehozott TransactionID.

A probléma megoldásához kivonatparticionálást használtunk egy számított oszlopban , és 690% teljesítménybeli javulást észleltünk. Az alábbi táblázat összefoglalja az alkalmazás teljesítményét a kivonatparticionálás számítási oszlopokkal való implementálása előtt és után. A CPU-kihasználtság mértéke a várakozásnak megfelelően növekszik az átviteli sebességgel arányosan, miután eltávolították a retesz-versengés szűk keresztmetszetét.

Mérték	Hash-particionálás előtt	Hash-particionálás után
Üzleti tranzakciók/szekciók	36	249
Átlagos lapretesz várakozási idő	36 ezredmásodperc	0,6 ezredmásodperc
Reteszes várakozások/másodperc	9,562	2,873
SQL Processzoridő	24%	78%
SQL Batch-kérelmek másodpercenként	12,368	47,045

Ahogy az előző táblázatból is látható, a túlzott oldalzárolás miatti teljesítményproblémák helyes azonosítása és megoldása pozitív hatással lehet az alkalmazás általános teljesítményére.

Függelék: Alternatív technika

A túlzott oldalelzárási versengés elkerülésének egyik lehetséges stratégiája a sorok karakteroszlopokkal való kitöltése annak biztosítása érdekében, hogy minden sor teljes oldalt használjon. Ez a stratégia akkor alkalmazható, ha a teljes adatméret kicsi, és kezelni kell a következő tényezők kombinációjából adódó EX oldalzár foglaltságot:

• Kis sorméret
• Sekély B-fa
• Hozzáférési minta nagy véletlenszerű beszúrási, kiválasztási, frissítési és törlési műveletekkel
• Kis táblák, például ideiglenes várólistatáblák

Ha a sorokat úgy párnázod, hogy egy teljes oldalt foglaljanak el, az SQL-nek több lapot kell lefoglalnia, így több lap válik elérhetővé a beszúrásokhoz, és csökken az oldalelzárási versengés.

Sorok kiegészítése annak érdekében, hogy minden egyes sor teljes oldalt foglaljon el.

Az alábbihoz hasonló szkripttel sorokat lehet beszedni egy teljes oldal elfoglalásához:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Megjegyzés:

Használja a lehető legkisebb karaktert, amely biztosítja, hogy minden oldalra csak egy sor jusson, így csökkentve a padding érték miatti extra CPU igényeket és a sor naplózásához szükséges extra helyet. Egy nagy teljesítményű rendszerben minden byte számít.

Ezt a technikát a teljesség céljából magyarázzák; a gyakorlatban az SQLCAT csak egy kis táblán használta ezt, amelyben egyetlen teljesítmény-előjegyzésben 10 000 sor található. Ez a technika korlátozott alkalmazással rendelkezik, mivel növeli az SQL Server memóriaterhelését a nagyméretű táblák esetében, és nem pufferelt reteszelést eredményezhet a nem levéloldalakon. A többlet memóriaterhelés jelentős korlátozó tényező lehet a technika alkalmazása szempontjából. A modern kiszolgálókon rendelkezésre álló memóriamennyiség miatt az OLTP számítási feladatokhoz használt munkakészlet nagy része általában a memóriában van. Ha az adatkészlet olyan méretűre nő, hogy már nem fér el a memóriában, jelentős teljesítménycsökkenés következik be. Ezért ez a technika csak kis táblákra alkalmazható. Ezt a technikát az SQLCAT nem használja olyan helyzetekben, mint például az utolsó oldal/záró oldal beszúrása versengés nagy táblák esetében.

Fontos

Ennek a stratégiának a alkalmazása nagy számú várakozást okozhat a ACCESS_METHODS_HOBT_VIRTUAL_ROOT retesztípuson, mivel ez a stratégia a B-fa nem leveles szintjein nagy számú oldaleloszlást eredményezhet. Ha ez történik, az SQL Servernek minden szinten meg kell szereznie a megosztott (SH) rácsokat, majd kizárólagos (EX) rácsokat kell használnia a B-fa azon oldalain, ahol az oldalak felosztása lehetséges. Ellenőrizze a sys.dm_os_latch_stats DMV-ben, hogy a sorok kitöltése után sok várakozás van-e a ACCESS_METHODS_HOBT_VIRTUAL_ROOT retesztípuson.

Függelék: SQL Server lakatkollíziós szkriptek

Ez a szakasz olyan szkripteket tartalmaz, amelyek segítenek a reteszelési versengési problémák diagnosztizálásában és elhárításában.

Lekérdezés sys.dm_os_waiting_tasks munkamenet-azonosító szerint rendezve

A következő példaszkript lekérdezi sys.dm_os_waiting_tasks-t, és visszaadja a munkamenet-azonosító szerint rendezett reteszes várakozásokat:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

A sys.dm_os_waiting_tasks lekérdezése várakozási időtartam szerint rendezve.

A következő példaszkript lekérdezi a sys.dm_os_waiting_tasks-t, és a reteszes várakozásokat a várakozás időtartama szerinti sorrendben adja vissza.

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Várakozások kiszámítása egy adott időszakra vonatkozóan

Az alábbi szkript kiszámítja és eredményként visszaadja a reteszvárakozásokat egy meghatározott időszakon keresztül.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Lekérdezéspufferleírók a reteszelési versengést okozó objektumok meghatározásához

Az alábbi szkript lekérdezi a pufferleírókat annak meghatározásához, hogy mely objektumok vannak társítva a leghosszabb reteszeléses várakozási időhöz.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Kivonatoló particionálási szkript

Ennek a szkriptnek a használatát a Hash particionálás használata számítási oszlopmal című cikkben ismertetjük, és az implementáció szempontjából testre kell szabni.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Kapcsolódó tartalom

• teljesítményfigyelő és hangoló eszközök