ebben a cikkben megvitatjuk azokat a bevált gyakorlatokat, amelyeket be kell tartani a TempDB adatbázis egészséges állapotban tartása és az adatbázis váratlan növekedésének megakadályozása érdekében, azon eljárás mellett, amelyet követni lehet ennek a váratlan növekedésnek az észlelésére, miután bekövetkezett.
TempDB áttekintés
a TempDB adatbázis az egyik legfontosabb SQL Server rendszeradatbázis, amelyet ideiglenes felhasználói objektumok, például a felhasználó által definiált vagy táblázatértékű függvény végrehajtásából, ideiglenesen tárolt eljárásokból, táblázatváltozókból vagy indexekből visszaadott ideiglenes táblák tárolására használnak.
a felhasználói objektumokon kívül a TempDB az SQL Server adatbázismotor által a különböző belső műveletek, például köztes rendezési, spooling, aggregate vagy kurzor műveletek során létrehozott belső objektumok tárolására szolgál.
a TempDB rendszeradatbázis a sorok verzióinak tárolására is szolgál, hogy támogassa azokat a funkciókat, amelyek megkövetelik a táblázatsorokon végrehajtott módosítások nyomon követését, például a tranzakciók pillanatkép elkülönítési szintjét, az online Index újraépítését vagy a több aktív eredményhalmaz funkciót.
a TempDB adatbázis minden egyes SQL Server-szolgáltatás újraindításakor törlődik, majd újra létrejön, kezdve az adatbázis új tiszta másolatával. Ennek alapján az ezen az adatbázison tárolt összes kifogásolt felhasználó és belső adatbázis automatikusan törlődik, amikor az SQL Server szolgáltatás újraindul, vagy amikor a létrehozott objektumok munkamenete megszakad. Ezért a biztonsági mentési és visszaállítási műveletek nem érhetők el a TempDB számára.
miért TempDB
a TempDB rendszeradatbázis fontos szerepet játszik az SQL Server teljesítményhangolási folyamatában. Mivel gyorsítótár-tárolóként használják különböző típusú felhasználói adatbázis-objektumok tárolására, valamint a rendszer belső objektumainak tárolására az SQL Server Database Engine-hez kapcsolódó folyamatok felgyorsítása érdekében.
mivel a TempDB rendszeradatbázis fontos szerepet játszik az SQL Server példány általános teljesítményének javításában, nagyon fontos a kezdeti méret, az automatikus növekedés és a helymeghatározás bevált gyakorlatainak alkalmazása a TempDB adatbázis adatain és naplófájljain. Ezeknek a bevált gyakorlatoknak az alkalmazása az SQL Server példány munkaterhelésének megfelelő módon megakadályozza a TempDB adatbázis adatainak és naplófájljainak nagyon gyakori bővítését, figyelembe véve, hogy a fájl kibővítési folyamata drága folyamat, amelyben az SQL Server Engine extra helyet kér az operációs rendszertől, amely nullázást végez ezen a téren, mielőtt az SQL Server Engine-hez rendelné. El lehet képzelni, hogy mennyi idő és erőforrás szükséges ehhez az elosztási folyamathoz.
ha már alkalmazza az összes bevált gyakorlatot, és váratlan növekedési művelet történt, felgyorsíthatja a kiosztási folyamatot az adatbázis azonnali fájl inicializálási beállításával, amelyben az operációs rendszer megbízik az SQL Server szolgáltatásban, és lefoglalja a szükséges helyet anélkül, hogy az idő-és erőforrás-pazarló műveletet végrehajtaná a lefoglalt területen.
bevált gyakorlatok
számos TempDB rendszeradatbázis-konfigurációs bevált gyakorlat ajánlott annak érdekében, hogy a megosztott erőforrás a lehető legjobb teljesítményt nyújtsa, és megvédje az SQL Server-példányt a TempDB-adatbázis adatainak vagy naplófájljainak váratlan növekedésétől, amelyek kitölthetik az alapul szolgáló lemezterületet.
ezek a konfigurációk a következők:
- a TempDB adatfájlok számának meg kell egyeznie a logikai processzorok számával, legfeljebb nyolc fájl, azon a gépen, ahol az SQL Server példány telepítve van. Ha az adott gépen a logikai processzorok száma nagyobb, mint nyolc, állítsa az adatfájlok számát nyolcra, azzal a képességgel, hogy 4-szeresével meghosszabbíthatja azt, ha TempDB-vita merül fel
- győződjön meg arról, hogy a TempDB-fájlok azonos méretűek ugyanazon a fájlcsoporton belül, hogy a lehető legmagasabb fokú párhuzamos műveletek hatékonyságát biztosítsák, az arányos kitöltési mechanizmus miatt, amelyet az adatfájlok belsejében lévő adatok írására használnak. További információért olvassa el az SQL Server arányos kitöltési algoritmusát
- konfigurálja a TempDB adatbázis adatait és naplófájljait ésszerű kezdeti méretekkel és az ezekre a fájlokra írandó várható adatmennyiségnek megfelelő automatikus növekedésű mennyiségekkel, valamint az SQL Server példányon végrehajtott különböző munkaterhelésekkel, beleértve a felhasználói tevékenységeket és az indexkarbantartási műveleteket, annak érdekében, hogy elkerülje a gyakori kis növekményeket, amelyek befolyásolják az Általános példány teljesítményét
- keresse meg a TempDB fájlokat egy gyors I/O alrendszeren annak érdekében, hogy elkerülje a TempDB fájlok I / O szűk keresztmetszetek
- keresse meg a TempDB-t egy külön meghajtón, távol a felhasználói adatbázisoktól és más rendszeradatbázisoktól. Ily módon a TempDB-n végrehajtott nehéz olvasási és írási műveletek nem befolyásolják vagy befolyásolják a felhasználói adatbázisok munkaterhelését, javítva az SQL Server teljes teljesítményét
a TempDB teljesítményének új fejlesztéseiről további információt a TempDB adatbázis-dokumentumban talál.
hol alkalmazható
az SQL Server 2016-os verziója előtt a TempDB méretkiosztása az SQL Server példány telepítése után, az adatbázis tulajdonságai oldalon végezhető el. Ahol szabályozhatja az aktuális SQL Server-példánynak megfelelő adatbázis-adatfájlok számát, az egyes adatbázis-adatok és naplófájlok kezdeti méretét, az Autogrowth beállítás megfelelő értékét, valamint az adatbázis-adatok és naplófájlok helyét.
a módosítások végrehajtásához bontsa ki az adatbázisok csomópontot az SQL Server Management Studio alkalmazásban, bontsa ki a Rendszeradatbázisok csomópontot, majd kattintson a jobb gombbal a tempdb adatbázisra, és válassza a Tulajdonságok lehetőséget az alábbiak szerint:
a megnyitott Database Properties ablakban böngésszen a fájlok oldalon, és ellenőrizze vagy módosítsa az alábbi adatbázis-adatok és naplófájlok beállításait:
az SQL Server 2016 verziójától kezdve a telepítővarázsló által már javasolt aktuális kiszolgálóerőforrásokhoz illeszkedő legjobb gyakorlatokkal együtt az SQL Server installation Installation folyamat során ellenőrizhető és végrehajtható az SQL Server installation Wizard segítségével az adatbázismotor konfigurációi | TempDB alatt, az alábbiak szerint:
a TempDB növekedésének nyomon követése
a TemDB adatbázisfájlok növekedésének és a TempDB erőforrásokat fogyasztó lekérdezéseknek a nyomon követése a TempDB fájlokhoz társított Teljesítményfigyelő számlálókkal és az SQL Profiler eszközzel végezhető el, amely egy adott adatbázis-munkaterhelési típust figyel.
a TempDB adatbázisfájlok növekedésének nyomon követésére szolgáló adatbázis-adminisztrátor barátságosabb módja a következő dinamikus kezelési nézetek lekérdezése:
- sys.dm_db_file_space_usage, amely visszaadja az adatbázis minden egyes fájljának területhasználati adatait anélkül, hogy megmutatná, melyik munkamenet vagy feladat használta fel azt a helyet
- sys.dm_db_session_space_usage, amely visszaadja az egyes munkamenetenként lefoglalt és kiosztott oldalak számát
- sys.dm_db_task_space_usage, amely visszaadja az egyes feladatokra kiosztott és kiosztott oldalak számát
vegye figyelembe, hogy a sys által visszaadott eredmény.dm_db_session_space_usage DMV összefoglalta a sys által visszaadott eredményt.dm_db_task_space_usage DMV ha az összes feladat sikeresen befejeződött, annak a ténynek köszönhető, hogy több kapcsolódó feladatok ugyanazt a lekérdezést hajtjuk végre ugyanazon a munkameneten. Ezen DMV-k további előnye, hogy megmutatja a TempDB erőforrásokat fogyasztó objektum típusát, és ha ez az objektum felhasználói objektum, például ideiglenes vagy változó tábla vagy belső objektum, például indexkarbantartás vagy rendezési művelet. Tehát az egyes objektumok által felhasznált oldalak száma alapján megadhatja, hogy a TempDB-t egy belső vagy felhasználói objektum használja-e, és optimalizálhatja az adott fogyasztót.
sys. dm_db_file_space_usage
a sys.dm_db_file_space_usage DMV a TempDB adatbázisfájlokban használt lemezterület figyelésére használható. Az alábbi lekérdezések mutatják a fel nem osztott helyet, a sorok verzióinak tárolására használt helyet, a belső objektumok által felhasznált helyet, végül a felhasználói objektumok által felhasznált helyet, az alábbiak szerint:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT (SUM(unallocated_extent_page_count)*1.0/128) mint TempDB_FreeSpaceAmount_InMB
innen: sys.dm_db_file_space_usage;
SELECT (SUM(version_store_reserved_page_count)*1.0/128) TEMPDB_VERSIONSTORESPACEAMOUNT_INMB
from Sys.Dm_db_file_space_usage;
select (sum(internal_object_reserved_page_count)*1.0/128) as TempDB_InternalObjSpaceAmount_Inmb
FROM sys.dm_db_file_space_usage;
SELECT (SUM(user_object_reserved_page_count)*1.0/128) AS TempDB_UserObjSpaceAmount_InMB
innen: sys. dm_db_file_space_usage;
|
a helyi SQL Server példányból visszaadott eredményből látható, hogy a TempDB fájlokban 60 MB nem használható, a belső objektumokhoz csak 0,5 MB, a felhasználói objektumokhoz pedig 1,5 MB használható, az alábbiak szerint:
sys.dm_db_task_space_usage és sys.dm_db_session_space_usage
sys.dm_db_task_space_usage és sys.dm_db_session_space a DMV-k az egyes feladatok vagy munkamenetek által lefoglalt és kiosztott oldalak számának ellenőrzésére szolgálnak a tempdb adatbázisban. Ily módon láthatja, hogy melyik felhasználó vagy egy belső objektum fogyasztja a TempDB helyet.
például az alábbi eredmény a rendszer lekérdezéséből származik.dm_db_task_space_usage DMV a TempDB adatbázis alatt, ahol visszaadja az egyes felhasználókhoz vagy belső feladatokhoz lefoglalt és kiosztott oldalak számát, az alábbiak szerint:
de a DBA nem igényel sok részletet a TempDB erőforrásokat fogyasztó lekérdezés nyomon követéséhez. Tehát össze kell foglalnunk az egyes munkamenetek előző eredményét az alábbi lekérdezés segítségével:
|
1
2
3
4
5
6
7
8
|
select session_id,
SUM(internal_objects_alloc_page_count) mint NumOfPagesAllocatedInTempDBforInternaltask,
SUM(internal_objects_dealloc_page_count) mint NumOfPagesDellocatedInTempDBforInternaltask,
SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) mint numofpagesallocatedintempdbforusertask,
Sum(User_objects_dealloc_page_count) mint numofpagesdellocatedintempdbforusertask
FROM sys. dm_db_task_space_usage
csoportosítás session_id szerint
Rendezés NumOfPagesAllocatedInTempDBforInternaltask DESC, NumOfPagesAllocatedInTempDBforUsertask DESC szerint
|
az összesített oldalak fogyasztási eredménye minden munkamenetenként az alábbi lesz:
ugyanaz az eredmény adható vissza a rendszer lekérdezésével.dm_db_session_space_usage DMW, amely összefoglalja az ugyanazon munkamenetben futó összes feladat oldalelosztását és deallocációját, az alábbiak szerint:
hajtsunk végre néhány műveletet a TempDB adatbázisban mind belső, mind felhasználói objektumokkal az alábbiak szerint:
ezután hajtsa végre az alábbi konszolidált lekérdezést, amely visszaadja a belső és felhasználói objektumok által lefoglalt és kiosztott oldalak számát, és megjeleníti mind a befejezett műveleteket (a sys használatával.dm_db_session_space_usage DMV) és a befejezetlen műveletek (a sys.dm_db_task_space_usage DMV használatával):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
válassza a SpacePerTask lehetőséget.session_id,
SpacePerSession.belső_objects_alloc_page_count
+ összeg (SpacePerTask.internal_objects_alloc_page_count) mint NumOfPagesAllocatedInTempDBforInternaltask,
SpacePerSession.belső_objects_dealloc_page_count
+ összeg (SpacePerTask.internal_objects_dealloc_page_count) mint NumOfPagesDellocatedInTempDBforInternaltask,
SpacePerSession.User_objects_alloc_page_count
+ összeg (SpacePerTask.User_objects_alloc_page_count) mint NumOfPagesAllocatedInTempDBforUsertask,
SpacePerSession.User_objects_dealloc_page_count
+ összeg (SpacePerTask.User_objects_dealloc_page_count) mint NumOfPagesDellocatedInTempDBforUsertask
innen: sys.dm_db_session_space_usage mint SpacePerSession
belső csatlakozás sys.dm_db_task_space_usage mint SpacePerTask a SpacePerSession-en.session_id = SpacePerTask.session_id
csoportosítás SpacePerTask szerint.session_id, űrutazás.belső_objects_alloc_page_count, Térpersession.belső_objects_dealloc_page_count, Térpersession.User_objects_alloc_page_count, SpacePerSession.User_objects_dealloc_page_count
rendezés a Lapokszámaintempdbforinternaltask DESC, NumOfPagesAllocatedInTempDBforUsertask DESC
|
az előző műveletek eredménye pedig tükrözi a lekérdezés kimenetét, bemutatva az ideiglenes felhasználói tábla létrehozásával és az index-újraépítési folyamat során az adatok rendezésével lefoglalt és kiosztott oldalak számát, az alábbiak szerint:
a TempDB erőforrásait használó lekérdezés szövegének megszerzéséhez használhatja az SQL fejlesztési képességeit az ys.dm_db_task_space_usage DMV eredményének összekapcsolásával más DMV-kkel, például a sys.dm_exec_requests és a sys.dm_exec_sessions a sys.dm_exec_sql_text dinamikus menedzsment funkcióval, ahol megjeleníti azokat a lekérdezéseket, amelyek most a tempdb helyet fogyasztják, mint ebben a lekérdezésben.
az eredmény hasonló lesz az alábbiakhoz:
- szerző
- Legutóbbi hozzászólások
Microsoft Certified Solution Expert Az adatkezelés és elemzés területén, Microsoft Certified Solution Associate az SQL Database Administration and Development területén, Azure Developer Associate és Microsoft Certified Trainer.
emellett számos blogban közreműködik SQL tippjeivel.
Ahmad Yaseen összes hozzászólásának megtekintése
- Azure Data Factory Interjú Kérdések és válaszok-február 11, 2021
- az Azure Data Factory figyelése-január 15, 2021
- Forrásvezérlés használata az Azure Data Factory – ban-január 12, 2021