Spinlock-versengés diagnosztizálása és megoldása az SQL Serveren

Ez a cikk részletes információkat nyújt a spinlock-versengéssel kapcsolatos problémák azonosításáról és megoldásáról a nagy egyidejűségi rendszereken futó SQL Server-alkalmazásokban.

Megjegyzés:

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. Eredetileg a Microsoft SQL Server Customer Advisory Team (SQLCAT) csapata tette közzé.

Háttér

A múltban az árucikk Windows Server számítógépek csak egy vagy két mikroprocesszort/CPU-chipet használtak fel, és a processzorokat csak egyetlen processzorral vagy "maggal" tervezték. A számítógép feldolgozási kapacitásának növelését a processzorok gyorsabb használatával sikerült elérni, ami nagyrészt a tranzisztor sűrűségének fejlődésével tette lehetővé. A "Moore törvénye" után a tranzisztor sűrűsége vagy az integrált áramkörbe helyezhető tranzisztorok száma kétévente folyamatosan megduplázódott az első általános célú egylapka cpu 1971-es fejlesztése óta. Az utóbbi években a számítógép-feldolgozási kapacitás gyorsabb processzorokkal való növelésének hagyományos megközelítését több processzorral rendelkező számítógépek létrehozásával egészítették ki. Ebben az írásban az Intel Nehalem CPU-architektúrája processzoronként legfeljebb nyolc magot foglal el, amely nyolc szoftvercsatornás rendszerben való használat esetén 128 logikai processzorra duplázható egyidejű többszálú (SMT) technológiával. Intel processzorokon az SMT neve Hyper-Threading. Az x86-kompatibilis számítógépeken a logikai processzorok számának növekedésével párhuzamosan az egyidejűséggel kapcsolatos problémák növekednek, ahogy a logikai processzorok versenyeznek az erőforrásokért. Ez az útmutató bemutatja, hogyan azonosíthatja és oldhatja meg az SQL Server-alkalmazások nagy egyidejűségi rendszereken és bizonyos számítási feladatokon való futtatásakor tapasztalt egyes erőforrás-versengési problémákat.

Ebben a szakaszban az SQLCAT csapatának a spinlock-versengési problémák diagnosztizálásával és megoldásával kapcsolatos tanulságait elemezzük. A Spinlock-versengés az egyidejűség egyik típusa, amelyet a nagy léptékű rendszerek valós ügyfél-számítási feladataiban észleltek.

A spinlock-versengés tünetei és okai

Ez a szakasz azt ismerteti, hogyan diagnosztizálhatja a spinlock-versengéssel kapcsolatos problémákat, ami káros az SQL Server OLTP-alkalmazásai teljesítményére. A Spinlock diagnosztizálását és hibaelhárítását speciális témának kell tekinteni, amelyhez a hibakeresési eszközök és a Windows belső elemeinek ismerete szükséges.

A spinlockok egyszerű szinkronizálási primitívek, amelyek az adatstruktúrákhoz való hozzáférés védelmére szolgálnak. A spinlockok nem egyediek az SQL Serverben. Az operációs rendszer akkor használja őket, ha egy adott adatstruktúrához csak rövid időre van szükség. Ha egy spinlockot megszerezni próbáló szál nem tud hozzáférést szerezni, a szál időszakosan ellenőrzi, hogy az erőforrás elérhető-e, ahelyett, hogy azonnal átadná az irányítást. Bizonyos idő elteltével a spinlockon várakozó szál hozamot eredményez, mielőtt megszerezheti az erőforrást. A hozam lehetővé teszi az ugyanazon a CPU-n futó más szálak végrehajtását. Ezt a viselkedést nevezik visszalépésnek, és a cikk későbbi részében részletesebben tárgyaljuk.

Az SQL Server spinlockokkal védi a belső adatstruktúrákhoz való hozzáférést. A motoron belül spinlockokat használnak bizonyos adatstruktúrákhoz való hozzáférés szerializálására a rácsokhoz hasonlóan. A retesz és a spinlock közötti fő különbség az, hogy a spinlockok forognak (ciklust hajtanak végre) egy adatszerkezet rendelkezésre állásának ellenőrzésére, miközben egy szál megpróbál hozzáférést szerezni egy retesz által védett struktúrához, azonnal visszalép, ha az erőforrás nem érhető el. A megszakításhoz egy szál környezetváltása szükséges a processzorról, hogy egy másik szál végrehajthassa. Ez egy viszonylag költséges művelet, és a rövid ideig fenntartott erőforrások esetében összességében hatékonyabb, ha lehetővé teszi, hogy egy szál rendszeres időközönként ellenőrizze az erőforrás elérhetőségét.

Az SQL Server 2022-ben bevezetett adatbázismotor (16.x) belső módosítása hatékonyabbá teszi a spinlockokat.

Hibajelenségek

Minden forgalmas magas egyidejűségi rendszeren normális, hogy aktív versengés jelenik meg a gyakran használt, spinlockokkal védett struktúrákon. Ez a használat csak akkor tekinthető problémásnak, ha a versengés jelentős processzorterhelést vezet be. A Spinlock-statisztikákat az sys.dm_os_spinlock_stats SQL Server dinamikus felügyeleti nézete (DMV) teszi közzé. Ez a lekérdezés például a következő kimenetet eredményezi:

Megjegyzés:

A DMV által visszaadott információk értelmezéséről a cikk későbbi részében olvashat bővebben.

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

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

oszlop	Leírás
Ütközések	Ez az érték minden alkalommal növekszik, amikor egy szál nem fér hozzá egy spinlock által védett erőforráshoz.
Pörgések	Ez az érték minden alkalommal növekszik, amikor egy szál ciklust hajt végre, miközben arra vár, hogy a spinlock elérhetővé váljon. Ez annak a munkamennyiségnek a mértéke, amelyet egy szál végez, miközben erőforrást próbál beszerezni.
Forgások_ütközésenként	A pörgetések aránya ütközésenként.
Alvási idő	Visszalépési eseményekhez kapcsolódó; nem releváns a cikkben ismertetett technikákra.
Visszalépések	Akkor fordul elő, ha egy "forgó" szál, amely megpróbál hozzáférni egy tárolt erőforráshoz, megállapította, hogy engedélyeznie kell az ugyanazon a CPU-n lévő többi szál végrehajtását.

E vita céljából a különösen érdekes statisztikák az ütközések, pörgetések és visszalépési események száma, amelyek egy adott időszakban fordulnak elő, amikor a rendszer nagy terhelés alatt áll. Amikor egy szál megpróbál hozzáférni egy spinlock által védett erőforráshoz, ütközés történik. Ütközés esetén az ütközések száma növekszik, és a szál egy hurokban kezd el pörögni, és rendszeresen ellenőrzi, hogy elérhető-e az erőforrás. Minden alkalommal, amikor a szál pörög (hurkok), a pörgetések száma növekszik.

Az ütközésenkénti pörgetések mértéke annak a pörgetésnek a mértéke, amely akkor fordul elő, amikor a spinlockot egy szál tartja, és megmutatja, hogy hány pörgetés történik, amíg a szálak a spinlockot tartják. Például, az alacsony ütközésenkénti pörgetésszám és a magas ütközésszám azt jelenti, hogy kis mennyiségű pörgetés történik a spinlock használata során, és sok szál verseng érte. A nagy mennyiségű pörgetés azt jelenti, hogy a spinlock kódban eltöltött idő viszonylag hosszú ideig tart (vagyis a kód nagy számú bejegyzésen megy keresztül egy kivonatgyűjtőben). A versengés növekedésével (ezáltal az ütközések számának növelésével) a forgások száma is nő.

A visszalépések a pörgetésekhez hasonlóan tekinthetők. A túlzott cpu-pazarlás elkerülése érdekében a spinlockok nem pörögnek határozatlan ideig, amíg nem férnek hozzá egy tárolt erőforráshoz. Annak biztosítása érdekében, hogy a spinlock ne használja túlzottan a CPU-erőforrásokat, a spinlockok visszaállnak, vagy leállítják a pörgetést, és "alvó" üzemmódba lépnek. A spinlockok visszalépnek, függetlenül attól, hogy valaha is megszerezték-e a célzott erőforrás tulajdonjogát. Ez azért történik, hogy más szálakat ütemezhessenek a CPU-ra abban a reményben, hogy ez hatékonyabb munkát tesz lehetővé. A motor alapértelmezett működése az, hogy először egy állandó időintervallumig forog, mielőtt késleltetést hajtana végre. A spinlock megszerzéséhez folyamatosan fenn kell tartani a gyorsítótár egyidejűségének állapotát, ami a CPU teljesítményigényes művelet a pörgés CPU-költségeihez képest. Ezért a spinlock beszerzésére tett kísérleteket takarékosan hajtják végre, és nem hajtják végre minden alkalommal, amikor egy szál pörög. Az SQL Serverben bizonyos spinlocktípusokat (például LOCK_HASH) javítottak a spinlock beszerzésére tett kísérletek közötti exponenciálisan növekvő intervallum használatával (egy bizonyos korlátig), ami gyakran csökkenti a cpu teljesítményére gyakorolt hatást.

Az alábbi ábra a spinlock algoritmus fogalmi nézetét mutatja be:

Tipikus forgatókönyvek

A Spinlock-versengés számos olyan okból fordulhat elő, amelyek nem kapcsolódnak az adatbázis-tervezési döntésekhez. Mivel a spinlockok kaput nyitnak a belső adatstruktúrákhoz, a spinlock-versengés nem ugyanúgy tapasztalható, mint a pufferzár-akadály, amelyre közvetlenül hatással vannak a sématervezési döntések és az adathozzáférési minták.

A spinlocknál fellépő versengéssel elsősorban a nagy számú pörgetés és az azonos spinlockot megszerezni kísérlő számos szál miatt jelentkező magas CPU-felhasználás társítható. Ez általában a 24 és több processzormaggal rendelkező rendszereken figyelhető meg, és leggyakrabban a 32-nél több processzormaggal rendelkező rendszereken. Ahogy korábban említettük, a spinlockok bizonyos szintű versengése normális a nagy egyidejűségű, jelentős terhelésű OLTP-rendszerek esetében, és gyakran nagy számú pörgetést (milliárd/billió) jelentenek a sys.dm_os_spinlock_stats DMV-ből olyan rendszereken, amelyek hosszú ideje futnak. Ha egy adott spinlocktípus esetében nagy számú pörgetést észlel, az nem elegendő információ annak megállapításához, hogy negatív hatással van-e a számítási feladatok teljesítményére.

A következő tünetek kombinációja spinlock-versengést jelezhet. Ha mindezek a feltételek teljesülnek, végezze el a spinlock-versengés lehetséges problémáinak további vizsgálatát.

• Egy adott spinlock típus esetében nagy számú pörgetés és hátralépés figyelhető meg.

• A rendszer nagy processzorkihasználtságot vagy kiugró processzorhasználatot tapasztal. A nagy processzorhasználati forgatókönyvekben magas jelvárás jelenik SOS_SCHEDULER_YIELD meg (ezt a DMV sys.dm_os_wait_statsjelenti).

• A rendszer magas egyidejűséget tapasztal.

• A processzorhasználat és a pörgetések az átviteli sebességhez képest aránytalanul növekednek.

Az egyik könnyen diagnosztizálható jelenség az átviteli sebesség és a processzorhasználat jelentős eltérése. Számos OLTP-számítási feladatnak van kapcsolata a (rendszeren lévő felhasználók átviteli sebessége/ száma) és a processzorhasználat között. A processzorhasználat és az átviteli sebesség jelentős eltérésével együtt megfigyelt magas pörgetések utalhatnak a processzorterhelést bevezető spinlock-versengésre. Fontos megjegyezni, hogy gyakran látható ez a fajta eltérés a rendszereken, amikor bizonyos lekérdezések idővel drágábbá válnak. Hasonló tüneteket okozhatnak például azok a lekérdezések, amelyeket olyan adathalmazok ellen adnak ki, amelyek több logikai olvasást végeznek az idő múlásával.

Fontos

Az ilyen típusú problémák hibaelhárítása során fontos kizárni a magas processzorhasználat egyéb gyakori okait.

Még ha az előző feltételek mindegyike igaz is, akkor is lehetséges, hogy a magas processzorhasználat kiváltó oka máshol található. Valójában az esetek túlnyomó többségében a megnövekedett processzorhasználat a spinlock-versengéstől eltérő okokból történik.

A megnövekedett processzorhasználat leggyakoribb okai közé tartoznak a következők:

• Azok a lekérdezések, amelyek az alapul szolgáló adatok növekedése miatt idővel drágulnak, további logikai olvasást igényelnek a memória-rezidens adatokból.
• A lekérdezéstervek módosítása, amely az optimálisnál rosszabb végrehajtást eredményez.

Példák

Az alábbi példában közel lineáris kapcsolat áll fenn a processzorhasználat és az átviteli sebesség között a másodpercenkénti tranzakciók alapján mérve. Itt általában némi eltérés tapasztalható, mivel a számítási feladatok növekedésével járó többletterhelések merülnek fel. Ahogy az itt látható, ez az eltérés jelentőssé válik. Az átviteli sebesség is jelentősen csökken, miután a processzorhasználat eléri a 100%-t.

A pörgetések számának 3 perces időközönkénti mérése azt mutatja, hogy a pörgetések növekedése inkább exponenciális, mint lineáris, ami arra utal, hogy a spinlock-versengés problémás lehet.

Ahogy korábban említettem, a spinlockok a leggyakoribbak a nagy terhelés alatt álló nagy egyidejűségi rendszereken.

A problémára hajlamos forgatókönyvek közé tartoznak a következők:

• Az objektumok nevének teljes minősítésének sikertelensége által okozott névmegoldási problémák. További információ: A fordítási zárolások által okozott SQL Server-blokkolás leírása. Ezt a konkrét problémát részletesebben ebben a cikkben ismertetjük.

• A zároláskezelő zárolási kivonatgyűjtőinek versengése olyan számítási feladatok esetén, amelyek gyakran érik el ugyanazt a zárolást (például megosztott zárolás egy gyakran olvasható sorban). Ez a fajta versengés egy bizonyos típusú spinlockként LOCK_HASH jelenik meg. Egy konkrét esetben azt találtuk, hogy ez a probléma egy tesztkörnyezet helytelenül modellezett hozzáférési mintáinak eredményeként jelentkezett. Ebben a környezetben a vártnál több szál fért hozzá folyamatosan ugyanahhoz a sorhoz a helytelenül konfigurált tesztparaméterek miatt.

• Magas DTC-tranzakciók, ha az MSDTC tranzakciókoordinátorai között nagy a késés. Ezt a konkrét problémát részletesen dokumentálja az SQLCAT blog bejegyzése A DTC-vel kapcsolatos várakozások feloldása és a DTC méretezhetőségének finomhangolása.

A spinlock versengésének diagnosztizálása

Ez a szakasz az SQL Server spinlock-versengés diagnosztizálásához nyújt információt. A spinlock-versengés diagnosztizálásához használt elsődleges eszközök a következők:

Eszköz	Használd
Teljesítményfigyelő	Keresse meg a magas cpu-feltételeket vagy az átviteli sebesség és a processzorhasználat közötti eltérést.
Spinlock-statisztikák	A sys.dm_os_spinlock_stats DMV lekérdezésével magas számú pörgetést és visszalépési eseményt kereshet különböző időszakok során.
Várakozási statisztikák	Az SQL Server 2025 -től kezdve (17.x) lekérdezheti a sys.dm_os_wait_stats és sys.dm_exec_session_wait_stats DMV-ket a SPINLOCK_EXT várakozási típus használatával. A nyomkövetési jelző 8134-et igényel. További információ: SPINLOCK_EXT.
BŐVÍTETT SQL Server-események	A nagy számú pörgetést tapasztaló spinlockok hívásveremeinek nyomon követésére szolgál.
Memóriaképek	Bizonyos esetekben az SQL Server folyamat memóriaképei és a Windows hibakeresési eszközei kapcsolódnak egymáshoz. Általában ez az elemzési szint akkor történik, amikor a Microsoft támogatási csapatai részt vesznek.

Az SQL Server Spinlock-versengés diagnosztizálásával kapcsolatos általános technikai folyamat a következő:

1. 1. lépés: Állapítsa meg, hogy van-e olyan versengés, amely spinlocktal kapcsolatos lehet.

2. 2. lépés: Rögzítse a statisztikákat sys.dm_os_spinlock_stats , hogy megtalálja azt a spinlocktípust, amelynél a legnagyobb a versengés.

3. 3. lépés: Szerezze be a hibakeresési szimbólumokat a sqlservr.exe (sqlservr.pdb) számára, és helyezze a szimbólumokat ugyanabban a könyvtárban, mint az SQL Server szolgáltatás .exe fájlja (sqlservr.exe) az SQL Server-példányhoz. A visszalépési események hívásveremeinek megtekintéséhez rendelkeznie kell a futtatott SQL Server adott verziójához tartozó szimbólumokkal. Az SQL Server szimbólumai a Microsoft Symbol Serveren érhetők el. A szimbólumok Microsoft Szimbólumkiszolgálóról való letöltéséről további információt a szimbólumokkal végzett hibakeresésben talál.

4. 4. lépés: Az SQL Server bővített eseményei segítségével nyomon követheti a spinlock típusú érdeklődési körök háttéreseményeit. A rögzítendő események a következők: spinlock_backoff és spinlock_backoff_warning.

A kiterjesztett események lehetővé teszik a háttéresemények nyomon követését és a hívásverem rögzítését azon művelet(ek) esetében, amelyek leggyakrabban megpróbálják beszerezni a spinlockot. A hívásverem elemzésével meg lehet állapítani, hogy milyen típusú művelet járul hozzá az adott spinlock versengéséhez.

Diagnosztikai útmutató

Az alábbi útmutató bemutatja, hogyan lehet az eszközöket és módszereket használni egy spinlock-versengési probléma diagnosztizálásához egy valós életbeli forgatókönyvben. Ez az útmutató egy körülbelül 6500 egyidejű felhasználót szimuláló tesztelést futtató ügyfélkapcsolaton alapul, amely egy 8 foglalatos, 64 fizikai magos, 1 TB méretű memóriával rendelkező kiszolgálón fut.

Hibajelenségek

Rendszeres kiugró cpu-értékeket figyeltek meg, amelyek közel 100%tették le a processzorkihasználtságot . Az átviteli sebesség és a processzorhasználat közötti eltérés a probléma megoldásához vezetett. A nagy cpu-kiugró érték bekövetkezésének idejére megállapították a nagy mennyiségű pörgetés mintáját, amely a magas processzorhasználat idején, adott időközönként történt.

Ez egy szélsőséges eset volt, amelyben a versengés olyan volt, hogy spinlock konvoj feltételt hozott létre. Konvoj akkor alakul ki, amikor a szálak már nem tudják folytatni a feladatok feldolgozását, hanem az összes feldolgozási erőforrást arra összpontosítják, hogy hozzáférjenek a zároláshoz. A teljesítményfigyelő naplója szemlélteti a tranzakciónapló átviteli sebessége és a processzorhasználat közötti eltérést, és végső soron a processzorhasználat nagy megugrását.

Miután lekérdezte sys.dm_os_spinlock_stats a SOS_CACHESTORE jelentős versengés meglétét, egy kiterjesztett eseményfigyelő scripttel mérték meg az érdeklődésre számottevő spinlock típusok hátralépési eseményeinek számát.

Név	Ütközések	Forog	Pörgetések ütközésenként	Hátrálások
SOS_CACHESTORE	14,752,117	942,869,471,526	63,914	67,900,620
SOS_SUSPEND_QUEUE	69,267,367	473,760,338,765	6,840	2,167,281
LOCK_HASH	5,765,761	260,885,816,584	45,247	3,739,208
MUTEX	2,802,773	9,767,503,682	3,485	350,997
SOS_SCHEDULER	1,207,007	3,692,845,572	3,060	109,746

A pörgetések hatásának legegyszerűbb számszerűsítése az, hogy megtekintjük, a spinlock típus(ok) esetében a legnagyobb számú pörgetés azonos 1 perces időintervallum alatt hány backoff eseményt generált sys.dm_os_spinlock_stats. Ez a módszer a legjobb a komoly versenyhelyzetet észlelésére, mert azt jelzi, ha a szálak kimerítik a spin-korlátot, miközben a spinlock megszerzésére várnak. Az alábbi szkript egy speciális technikát mutat be, amely kiterjesztett eseményeket használ a kapcsolódó háttéresemények mérésére, és azonosítja azokat a kódútvonalakat, ahol a versengés található.

Az SQL Server bővített eseményeiről további információt a Bővített események áttekintése című témakörben talál.

Szkript

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

A kimenet elemzésével láthatjuk a SOS_CACHESTORE pörgetések leggyakoribb kódútvonalainak hívásveremeit. A szkriptet több különböző alkalommal futtatták, amikor a cpu-kihasználtság magas volt, hogy ellenőrizze a visszaadott hívásveremek konzisztenciáját. A két kimenet közötti legmagasabb "slot bucket" számmal rendelkező hívásveremek (35 668 és 8506) gyakoriak. Ezek a hívásveremek helyeinek száma két nagyságrenddel nagyobb, mint a következő legmagasabb bejegyzésé. Ez a feltétel egy érdekes kód elérési útját jelzi.

Megjegyzés:

Az nem szokatlan, hogy az előző szkript által visszaadott hívásveremeket láthatjuk. Amikor a szkript 1 percig futott, megfigyeltük, hogy az 1000-es pontszámú > hívásveremek problémásak, de a 10 000-es pontszám > nagyobb valószínűséggel volt problémás, mivel magasabb pontszámról van szó.

Megjegyzés:

A következő kimenet formázását olvashatósági okokból tisztították meg.

1. kimenet

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

2. kimenet

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

Az előző példában a legérdekesebb veremek rendelkeznek a legmagasabb pontszámokkal (35 668 és 8506), amelyek valójában 1000-nél nagyobb pontszámúak.

A kérdés az lehet, hogy "mit tegyek ezekkel az információkkal"? Általában az SQL Server-motor részletes ismerete szükséges a híváshívási információk használatához, így a hibaelhárítási folyamat ekkor szürke területre kerül. Ebben a konkrét esetben a hívási veremeket tekintve láthatjuk, hogy a probléma kódútvonala a biztonsági és metaadat-keresésekhez kapcsolódik (amint azt a következő veremkeretek is jelzik CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID).

Külön-külön nehéz ezeket az információkat használni a probléma megoldásához, de ad néhány ötletet, ahol további hibaelhárításra kell összpontosítani a probléma további elkülönítéséhez.

Mivel ez a probléma a biztonsággal kapcsolatos ellenőrzéseket végző kódútvonalakkal kapcsolatosnak tűnt, úgy döntöttünk, hogy futtatunk egy tesztet, amelyben az adatbázishoz csatlakozó alkalmazásfelhasználó jogosultságokat kapott sysadmin . Bár ez a technika soha nem ajánlott éles környezetben, tesztkörnyezetünkben hasznos hibaelhárítási lépésnek bizonyult. Amikor a munkameneteket emelt szintű jogosultságokkal futtatták (sysadmin), a versengéssel kapcsolatos CPU-csúcsok eltűntek.

Opciók és kerülő megoldások

Nyilvánvaló, hogy a spinlock-versengés hibaelhárítása nem triviális feladat lehet. Nincs "egy közös legjobb megközelítés". A teljesítményproblémák hibaelhárításának és megoldásának első lépése a kiváltó ok azonosítása. A cikkben ismertetett technikák és eszközök használata az első lépés a spinlocktal kapcsolatos versengési pontok megértéséhez szükséges elemzés végrehajtásában.

Az SQL Server új verzióinak fejlesztése során a motor továbbra is javítja a méretezhetőséget a magas egyidejűségi rendszerekhez jobban optimalizált kód implementálásával. Az SQL Server számos optimalizálást vezetett be a magas egyidejűségi rendszerekhez, amelyek közül az egyik exponenciális visszalépés a leggyakoribb versengési pontokhoz. Az SQL Server 2012-től kezdődően vannak olyan fejlesztések, amelyek kifejezetten továbbfejlesztik ezt a területet azáltal, hogy exponenciális háttéralgoritmusokat alkalmaznak a motor összes spinlockjához.

A szélsőséges teljesítményt és skálázást igénylő csúcskategóriás alkalmazások tervezésekor fontolja meg, hogyan tarthatja a szükséges kódelérési utat az SQL Serveren belül a lehető legrövidebben. A rövidebb kódútvonal azt jelenti, hogy az adatbázismotor kevesebb munkát végez, és természetesen elkerüli a versengési pontokat. Számos ajánlott eljárásnak van egy mellékhatása, amely csökkenti a motor igényelte munka mennyiségét, és ezáltal optimalizálja a munka teljesítményét.

Néhány ajánlott eljárás a cikk korábbi szakaszából példákként:

• Teljes név: Az összes objektum teljes nevének megadása esetén az SQL Servernek nem kell olyan kódútvonalakat futtatnia, amelyek a nevek feloldásához szükségesek. Megfigyeltük az ütközési pontokat a SOS_CACHESTORE spinlock típussal kapcsolatban is, amikor nem használnak teljesen minősített neveket a tárolt eljárások hívásakor. A nevek teljes minősítésének sikertelensége miatt az SQL Servernek meg kell keresnie a felhasználó alapértelmezett sémáját, ami hosszabb kódútvonalat eredményez az SQL végrehajtásához.

• Paraméteres lekérdezések: Egy másik példa a paraméteres lekérdezések és a tárolt eljáráshívások használata a végrehajtási tervek létrehozásához szükséges munka csökkentése érdekében. Ez ismét rövidebb kódútvonalat eredményez a végrehajtáshoz.

• LOCK_HASH Versengés: Bizonyos zárolási szerkezetek vagy kivonatgyűjtő ütközések esetén bizonyos esetekben elkerülhetetlen a versengés. Annak ellenére, hogy az SQL Server-motor a zárolási struktúrák többségét particionálja, a zárolások lekérésekor még mindig előfordul, hogy ugyanazt a kivonatkészletet éri el. Egy alkalmazás például ugyanazt a sort egyszerre több szálon éri el (azaz referenciaadatokkal). Ezeket a problémákat olyan technikákkal lehet megközelíteni, amelyek az adatbázissémán belül skálázják fel ezeket a referenciaadatokat, vagy optimista egyidejűség-vezérlést és optimalizált zárolást használnak, ha lehetséges.

Az SQL Server számítási feladatainak finomhangolásában az első védelmi vonal mindig a szabványos hangolási eljárások (például indexelés, lekérdezésoptimalizálás, I/O-optimalizálás stb.). A szabványos hangolás mellett azonban fontos módszer a műveletek végrehajtásához szükséges kódmennyiség csökkentésére vonatkozó eljárások követése. Még ha követik is az ajánlott eljárásokat, még mindig fennáll annak az esélye, hogy a spinlock-versengés forgalmas, magas egyidejűségi rendszereken fordulhat elő. A cikkben szereplő eszközök és technikák segítségével elkülönítheti vagy kizárhatja az ilyen típusú problémákat, és meghatározhatja, hogy mikor van szükség a megfelelő Microsoft-erőforrások bevonására.

Függelék: Memóriakép rögzítésének automatizálása

A következő kiterjesztett eseményszkript hasznosnak bizonyult a memóriaképek gyűjtésének automatizálásához, amikor a spinlock-versengés jelentőssé válik. Bizonyos esetekben memóriaképekre van szükség a probléma teljes diagnosztizálásához, vagy a Microsoft csapatai kérik a részletes elemzés elvégzésére.

A következő SQL-szkripttel automatizálható a memóriaképek rögzítésének folyamata a spinlock-versengés elemzéséhez:

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Függelék: Spinlock-statisztikák rögzítése az idő függvényében

Az alábbi szkripttel megtekintheti a spinlock-statisztikákat egy adott időszakban. Minden futtatáskor visszaadja az aktuális értékek és az összegyűjtött korábbi értékek közötti különbözetet.

/* Snapshot the current spinlock 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 DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

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

Kapcsolódó tartalom

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