tässä artikkelissa keskustelemme parhaista käytännöistä, joita tulisi noudattaa, jotta TempDB-tietokanta pysyisi terveessä tilassa ja estäisi tietokannan odottamattoman kasvun, sen lisäksi, että menettely, jota voidaan noudattaa havaita tämä odottamaton kasvu kerran tapahtunut.
TempDB overview
TempDB-tietokanta on yksi tärkeimmistä SQL Server-järjestelmän tietokannoista, johon tallennetaan väliaikaisia käyttäjäobjekteja, kuten käyttäjän määrittelemiä väliaikaisia taulukoita tai palautettuja taulukkoarvostettuja funktioiden suorituksia, väliaikaisia tallennettuja menettelyjä, taulukkomuuttujia tai indeksejä.
käyttäjäobjektien lisäksi TempDB: tä käytetään SQL Server-tietokantamoottorin luomien sisäisten objektien tallentamiseen erilaisten sisäisten operaatioiden aikana, kuten välilajittelu -, juonittelu -, kooste-tai kohdistinoperaatioiden aikana.
TempDB-järjestelmätietokantaa käytetään myös riviversioiden tallentamiseen, jotta voidaan tukea ominaisuuksia, jotka edellyttävät taulukkoriveillä tehtävien muutosten seuraamista, kuten tapahtumien snapshot isolation-taso, Online Index rebuilds tai Multiple Active Result Sets-ominaisuus.
TempDB-tietokanta pudotetaan ja luodaan uudelleen aina, kun SQL Server-palvelu käynnistetään uudelleen, aloittaen uudesta puhtaasta kopiosta tietokannasta. Tämän tosiasian perusteella kaikki tähän tietokantaan tallennetut käyttäjän ja sisäisen tietokannan objektit poistetaan automaattisesti, kun SQL Server-palvelu käynnistetään uudelleen tai kun istunto, jossa nämä objektit luodaan, katkaistaan. Siksi varmuuskopiointi-ja palautustoiminnot eivät ole käytettävissä TempDB: ssä.
miksi TempDB
TempDB-järjestelmätietokannalla on tärkeä rooli SQL Serverin suorituskyvyn viritysprosessissa. Koska sitä käytetään välimuistitallennuksena erilaisten käyttäjien tietokantaobjektien tallentamiseen ja järjestelmän sisäisten objektien tallentamiseen SQL Server-tietokannan moottoriin liittyvien prosessien nopeuttamiseksi.
koska TempDB-järjestelmätietokanta on tärkeässä roolissa SQL Server-instanssin yleisen suorituskyvyn parantamisessa, on erittäin tärkeää soveltaa tempdb-tietokannan tietojen ja lokitiedostojen alkuperäistä kokoa, automaattista kasvua ja sijaintia koskevia parhaita käytäntöjä. Soveltamalla näitä parhaita käytäntöjä tavalla, joka sopii SQL Server esimerkiksi työmäärä estää laajentamalla TempDB tietokannan tietoja ja lokitiedostoja hyvin usein, ottaen huomioon, että tiedosto laajenee prosessi on kallis prosessi, jossa SQL Server Engine pyytää ylimääräistä tilaa käyttöjärjestelmästä, joka suorittaa nollaamalla että tila ennen sen jakamista SQL Server Engine. Voitte kuvitella, kuinka paljon aikaa ja resursseja tähän jakoprosessiin tarvitaan.
jos sovellat jo kaikkia parhaita käytäntöjä ja odottamaton kasvuoperaatio on tapahtunut, voit nopeuttaa allokointiprosessia käyttämällä tietokannan pikatiedostojen alustusasetusta, jossa käyttöjärjestelmä luottaa SQL Server-palveluun ja jakaa tarvittavan tilan suorittamatta aikaa ja resursseja tuhlaavaa toimintoa varatulle tilalle.
parhaat käytännöt
on olemassa useita TempDB-järjestelmän tietokantakokoonpanon parhaita käytäntöjä, joita suositellaan parhaan suorituskyvyn saamiseksi kyseisestä jaetusta resurssista ja SQL Server-esiintymän suojaamiseksi TempDB-tietokannan tietojen tai lokitiedostojen odottamattomalta kasvulta, joka saattaa täyttää taustalla olevan levytilan.
näitä kokoonpanoja ovat:
- TempDB-datatiedostojen määrän tulisi vastata loogisten prosessorien määrää, jopa kahdeksaa tiedostoa, koneessa, johon SQL Server-instanssi on asennettu. Jos loogisten prosessorien määrä kyseisessä koneessa on suurempi kuin kahdeksan, aseta datatiedostojen määrä kahdeksaan, ja mahdollisuus laajentaa sitä kerrannaisella 4, Jos sinulla on TempDB-väite kysymys
- varmista, että TempDB-tiedostot ovat samankokoisia samassa filegroupissa, jotta saadaan mahdollisimman suuri rinnakkaisten operaatioiden tehokkuus, johtuen suhteellisesta täytön kirjoitusmekanismista, jota käytetään tietojen kirjoittamiseen datatiedostojen sisällä. Lisätietoja, tarkista ymmärrystä SQL Serverin suhteellinen täyttöalgoritmi
- Määritä TempDB-tietokannan tiedot ja lokitiedostot kohtuullisella alkukoolla ja automaattisella kasvumäärällä, joka sopii odotettuun datamäärään, joka näihin tiedostoihin kirjoitetaan, sekä erityyppiset työmäärät, jotka suoritetaan SQL Server-instanssissa, mukaan lukien käyttäjän toiminnot ja indeksin ylläpitotoimet, jotta vältetään toistuvat pienet lisäykset, jotka vaikuttavat kokonaisesityksen suorituskykyyn
- paikantaa TempDB-tiedostot nopealla I / O-alijärjestelmällä, jotta vältetään I / O pullonkaulat
- paikantaa TempDB erilliselle levyasemalle, pois käyttäjien tietokannoista ja muista järjestelmätietokannoista. Näin tempdb: ssä suoritetut raskaat luku-ja kirjoitusoperaatiot eivät vaikuta käyttäjätietokantojen työmäärään, mikä parantaa SQL Server-instanssin yleistä suorituskykyä
jos haluat lisätietoja TempDB-suorituskyvyn uusista parannuksista, tarkista TempDB-tietokanta-asiakirja.
jos sovelletaan
ennen SQL Server 2016-versiota, TempDB-kokojako voidaan suorittaa SQL Server-instanssin asennuksen jälkeen tietokannan ominaisuudet-sivulta. Jossa voit hallita tietokantatiedostojen määrää, jotka sopivat nykyiseen SQL Server-instanssiin, kunkin tietokannan tietojen ja lokitiedoston alkuperäistä kokoa, autogrowth-vaihtoehdon oikeaa arvoa ja tietokannan tietojen ja lokitiedostojen sijaintia.
jos haluat tehdä nämä muutokset, laajenna SQL Server Management Studion Tietokantasolmu, laajenna Järjestelmätietokantasolmu sitten hiiren kakkospainikkeella tempdb-tietokantaa ja valitse Ominaisuudet-asetus kuten alla:
avatusta tietokannan ominaisuusikkunasta Selaa tiedostot-sivua ja tarkista tai muuta alla olevia tietokannan tietoja ja lokitiedostoja koskevia asetuksia:
alkaen SQL Server 2016-versiosta kaikki nämä muutokset ja ohjatun asennuksen ehdottamat parhaat käytännöt, jotka sopivat nykyisiin palvelinresursseihin, voidaan tarkistaa ja suorittaa SQL Server-instanssin asennusprosessin aikana käyttäen Ohjattua SQL Server-asennusprosessia tietokantamoottorin kokoonpanoissa / TempDB-kohdassa, kuten alla on esitetty:
Track TempDB growth
Temdb-tietokantatiedostojen kasvun ja TempDB-resursseja kuluttavien kyselyjen seuranta voidaan suorittaa TempDB-tiedostoihin liittyvillä suorituskyvyn Valvontalaskureilla ja SQL Profiler-työkalulla, joka kuuntelee tiettyä tietokantatyyppiä.
tietokannan ylläpitäjän ystävällisempi tapa seurata TempDB-tietokantatiedostojen kasvua on kysyä seuraavia dynaamisia Hallintanäkymiä:
- SYS.dm_db_file_space_usage, joka palauttaa kunkin tietokannan tiedoston tilankäyttötiedot osoittamatta, mikä istunto tai tehtävä kulutti kyseisen tilan
- SYS.dm_db_session_space_usage, joka palauttaa varattujen ja varattujen sivujen määrän kutakin istuntoa kohti
- sys.dm_db_task_space_usage, joka palauttaa jaettujen sivujen määrän kutakin tehtävää kohti
huomaa, että järjestelmän palauttama tulos.dm_db_session_space_usage DMV tiivisti järjestelmän palauttaman tuloksen.dm_db_task_space_usage DMV kun kaikki tehtävät on suoritettu onnistuneesti, koska useita samaan kyselyyn liittyviä tehtäviä suoritetaan saman istunnon aikana. Toinen näiden DMV: iden etu on se, että se näyttää tempdb-resursseja kuluttavan objektin tyypin ja onko tämä objekti käyttäjäobjekti, kuten väliaikainen tai muuttuva taulukko tai sisäinen objekti, kuten indeksin ylläpito tai lajittelutoiminto. Niin, joka perustuu sivujen määrä kulutetaan kunkin objektin, voit määrittää, jos TempDB kulutetaan sisäinen tai käyttäjäobjektin ja optimoida, että kuluttaja.
Sys.dm_db_file_space_usage
Sys.dm_db_file_space_usage DMV: n avulla voidaan seurata tempdb-tietokantatiedostojen levytilaa. Alla olevat kyselyt näyttävät kohdistamattoman tilan, riviversioiden tallentamiseen käytetyn tilan, sisäisten objektien kuluttaman tilan ja lopuksi käyttäjäobjektien kuluttaman tilan seuraavasti:
|
1
2
3
4
5
6
7
8
9
10
11
|
valitse (SUM(unallocated_extent_page_count)*1.0/128) AS TempDB_FreeSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
SELECT (SUM(version_store_reserved_page_count)*1.0/128) AS 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
FROM sys.dm_db_file_space_usage;
|
tuloksesta, joka on palautettu paikallisesta SQL Server-esiintymästä, näet, että TempDB-tiedostoissa on 60 MB käyttämättä, vain 0.5 MB käytetään sisäisille objekteille ja 1.5 MB käytetään käyttäjäobjekteille, kuten alla on esitetty:
sys.dm_db_task_space_usage and sys.dm_db_session_space_usage
sys.dm_db_task_space_usage and sys.dm_db_session_space_usage tai istunto tempdb-tietokannassa. Tällä tavalla, voit nähdä, mikä käyttäjä tai sisäinen objekti, joka kuluttaa TempDB tilaa.
esimerkiksi alla oleva tulos palautuu sys: n kyselystä.Dm_db_task_space_usage DMV tempdb-tietokannassa, johon se palauttaa kullekin käyttäjälle tai sisäiselle tehtävälle varattujen ja varattujen sivujen määrän seuraavasti:
, mutta DBA ei vaadi montaa yksityiskohtaa aloittaakseen tempdb-resursseja kuluttavan kyselyn seurannan. Joten, meidän täytyy tiivistää edellinen tulos kunkin istunnon käyttäen kyselyn alla:
|
1
2
3
4
5
6
7
8
|
valitse session_id,
SUM(internal_objects_alloc_page_count) AS Numbofpagesallocatedintempdb Forinternaltask,
SUM(internal_objects_dealloc_page_count) AS Numbofpagesdellocatedintempdb Forinternaltask,
SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) as NUMBOFPAGESALLOCATEDINTEMPDB for userertask,
Sum(User_objects_dealloc_page_count) as NUMBOFPAGESDELLOCATEDINTEMPDB for userertask
FROM sys. dm_db_task_space_usage
GROUP BY session_id
ORDER BY Numbofpagesallocatedintempdb Forinternaltask DESC, Numbofpagesallocatedintempdb for Usertask desc
|
ja tiivistetyt sivut kulutustulos kutakin istuntoa kohti on kuten alla:
sama tulos voidaan palauttaa kyselemällä sys.dm_db_session_space_usage DMW, joka tiivistää sivujen jakamisen ja jakamisen kaikille saman istunnon aikana suoritettaville tehtäville, kuten alla esitetään:
Tehkäämme joitakin toimia TempDB-tietokannassa käyttäen sekä sisäisiä että käyttäjäobjekteja kuten alla:
suorita sitten alla oleva konsolidoitu kysely, joka palauttaa myönnettyjen ja deallocoitujen sivujen määrän sisäisten ja käyttäjäobjektien mukaan ja näyttää molemmat suoritetut toiminnot (käyttäen sys: ää.dm_db_session_space_usage DMV) ja keskeneräiset toiminnot (käyttäen sys.dm_db_task_space_usage DMV):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
valitse SpacePerTask.session_id,
SpacePerSession.internal_objects_alloc_page_count
+ SUM (SpacePerTask.internal_objects_alloc_page_count) Numbofpagesallocatedintempdb Forinternaltask,
SpacePerSession.internal_objects_dealloc_page_count
+ SUM (SpacePerTask.internal_objects_dealloc_page_count) nimellä Numbofpagesdellocatedintempdb Forinternaltask,
SpacePerSession.User_objects_alloc_page_count
+ SUM (SpacePerTask.User_objects_alloc_page_count) Numbofpagesallocatedintempdb for Usertask,
SpacePerSession.User_objects_dealloc_page_count
+ SUM (SpacePerTask.User_objects_dealloc_page_count) as Numbofpagesdellocatedintempdb for Usertask
FROM sys.dm_db_session_space_usage AS SpacePerSession
INNER JOIN sys.dm_db_task_space_usage AS SpacePerTask on SpacePerSession.session_id = SpacePerTask.session_id
ryhmä SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_objects_alloc_page_count, SpacePerSession.User_objects_dealloc_page_count
ORDER BY Numbofpagesallocatedintempdbforinternaltask DESC, Numbofpagesallocatedintempdbfor Userertask DESC
|
ja edellisen toiminnan tulos heijastaa kyselyn ulostulo, näyttää Sivumäärä varattu ja deallocated luomalla käyttäjän väliaikainen taulukko ja lajittelu tiedot aikana indeksin uudelleenrakennusprosessin, kuten alla:
saadaksesi TempDB-resursseja kuluttavan kyselyn tekstin voit käyttää SQL-kehitystaitojasi liittämällä ys.dm_db_task_space_usage DMV: n tuloksen muiden DMV: iden kanssa, kuten sys.dm_exec_requests ja sys.dm_exec_sessions kanssa SYS.dm_exec_sql_text dynaaminen hallintatoiminto, jossa se näyttää kyselyt, jotka kuluttavat tempdb Tilaa nyt, kuten tässä kyselyssä.
tulos on samanlainen kuin alla:
- tekijä
- tuoreita viestejä
hän on Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate ja Microsoft Certified Trainer.
hän on myös monissa blogeissa mukana SQL-vinkeillään.
Näytä kaikki viestit käyttäjältä Ahmad Yaseen
- Azure Data Factory Haastattelu Kysymyksiä ja vastauksia – helmikuu 11, 2021
- Miten seurata Azure Data Factory-tammikuu 15, 2021
- käyttämällä Source Control Azure Data Factory-tammikuu 12, 2021