Redgate Hub

viime vuosina, olen työskennellyt ylläpitää ja optimoida tietokantaympäristön, ja olen kohdannut kaikenlaisia ongelmia, mutta yksi kipupiste yhteinen lähes kaikissa SQL Server ympäristöissä on tempdb.

tempdb: n käyttö SQL Server-palvelussa on hyvin tiedossa, enkä halua selittää samaa luetteloa asioista ja suosituksista, joita on käsitelty jo hyvin monissa artikkeleissa ympäri Internetiä. Tässä muutamia tunnettuja kysymyksiä voit helposti google saada lisätietoja:

  • Object allocation väite metatietosivuilla, joita käytetään tilan hallintaan tempdb-tiedostossa
  • Metadata väite sivuista, jotka kuuluvat väliaikaisen objektin metadatan seuraamiseen käytettäviin järjestelmäobjekteihin (btw, tämä on parannettu SQL Server 2019: ssä)
  • ongelmat, joiden koodeja ei ole optimoitu tempdb cache of system metadata
  • hajautus ja lajittelut

tässä jutussa en haluaisi keskittyä niihin yhteisiin asioihin, mutta muihin kiinnostaviin ongelmiin löysin asiakkaita tukiessani.

jotkut tempdb-ongelmat, joita sinulla on hyvin todennäköisesti

Seuraavassa on luettelo muutamista tempdb-ongelmista, joita sinulla on hyvin todennäköisesti SQL Server-ympäristössäsi, joista et ehkä tiedä.

  • SQL Server 2014: ää edeltävissä versioissa eager writer hidastaa inserttejä ja tuhlaa I/O: ta, kun huuhteluoperaatiota ei tarvita.
  • SQL Server 2014: ssä (backported to SQL Server 2012 SP1+CU10 tai SP2+CU1) innokas kirjoittaja oli rento olla huuhtelematta tempdb: tä; tämä korjasi yhden ongelman ja aiheutti toisen.
  • epäsuora tarkistuspiste (target recovery time) käytössä oletuksena tempdb: ssä SQL Server 2016: lle. Ideana oli siivota sql2014: n muutoksen luomat likaiset sivut, mutta se ei todellakaan tee hyvää työtä sen korjaamisessa, koska se ei siivoa likaisia sivuja, jotka on jaettu minimaalisesti kirjatuille toiminnoille.

Eager Writer and tempdb

yksi monista SQL Server 2014: n mukana tulleista suoritusparannuksista on se, että se ei huuhtele likaisia sivuja, jotka on luotu minimaalisesti kirjatussa operaatiossa tempdb: ssä. Tämä antaa sinulle hyötyä ottaa nopeammin (verrattuna aikaisempiin versioihin) insertit, mutta se aiheutti toisen ongelman, koska niille varattu sivut voivat kestää paljon aikaa poistetaan puskurin pool data cache. Ennen kuin keskustelemme ongelmasta, nopeasti tarkastella hyötyä ja sitten ymmärtää joitakin tärkeitä käsitteitä huuhtele likainen sivuja tempdb.

käytä seuraavaa kyselyä, joka lisää noin 520MB dataa temp-taulukkoon:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

käytä tempdb: tä
mene
jos OBJECT_ID (”#TabTest1”) ei ole nolla
DROP TABLE #TabTest1;
Luo taulukko #TabTest1 (Col1 CHAR(500));
lisätään kohtaan # TabTest1
valitse TOP 1000000
Muunna (merkki(500), A.name) COL1
Masterilta.dbo.sysobjects A,
master.dbo.sysobjects B,
master.dbo.sysobjects C,
master.dbo.sysobjects D
OPTION (MAXDOP 1);
GO

kun ajoin sen minun paikallisissa tapauksissa sekä tempdb määritetty samat tiedot(sama levy, alkuperäinen koot, jäljittää liput, jne.), Näen seuraavat tulokset:

  • SQL Server 2008 R2 :ssa (10.50.6220.0) sen suorittaminen kesti 32 sekuntia.
  • SQL Server 2017: ssä (14.0.3238.1) se kesti 1 sekunnin ajan.

kuten näette, ero on valtava; tämä johtuu jokseenkin seuraavista:

1
2
3
4
5
6
7
8
9
10

valitse Page_Status = CASE
WHEN is_modified = 1 THEN ”Dirty”
ELSE ”Clean”
END,
DBName = DB_NAME(database_id),
Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID (”tempdb”)
GROUP BY database_id,
is_modified

SQL Server 2008R2

SQL Server 2017

kuten näette, DMV sys.dm_os_buffer_descriptors: n tarkistaminen osoittaa, että suurin osa SQL Server 2017: n varatuista sivuista on likaisia, ja SQL Server 2008R2: n innokas kirjoittajaprosessi vei aikaa puhdistaa ja kirjoittaa sivut tempdb-dataan tiedosto. Tästä se Aikaero tulee. SQL 2008R2: ssa komento joutuu odottamaan värisuoraa ja SQL2017: ssä ei.

mitä Checkpoint tekee tempdb: ssä?

ennen kuin siirrymme eteenpäin, tässä on nopea kertaus joistakin tärkeistä käsitteistä, jotka sinun on tiedettävä ymmärtääksesi ongelmat, jotka mainitsen tässä artikkelissa.

kun asetat rivin taulukkoon, SQL Server ei kirjoita sitä välittömästi data (mdf) – tiedostoon. Siinä on hallintalaite, joka kirjoittaa sen lokiin (ldf) ja pitää rivin sivulla muistissa. Muistissa olevia sivuja, joita ei ole fyysisesti kirjoitettu datatiedostoon, kutsutaan likaisiksi sivuiksi. Likainen sivu kirjoitetaan datatiedostoon jollakin kolmesta prosessista: checkpoint, lazy writer tai eager writer. Nämä ovat hyvin perusasiat I / O kirjallisesti SQL Server, ja sinun täytyy ymmärtää se voi vianmääritys ja selittää monimutkaisia ongelmia, jotka liittyvät tempdb ja koko SQL engine.

rivejä voi olla vain lokitiedostossa ja likaisilla sivuilla muistissa. Jos odottamaton kaatuminen tapahtuu ja SQL Server pysähtyy, kun se on palannut, SQL engine on tehtävä uudelleen kaikki tapahtumat, jotka on kirjoitettu tapahtumalokitiedostoon ja sovellettava näitä muutoksia data (mdf) tiedostoja. Tempdb: ssä ei ole järkeä. Koska tempdb luodaan uudelleen SQL Server startissa, tempdb: n ei tarvitse palauttaa tapahtumia lokitiedostosta datatiedostoon, joten likaisia tempdb-sivuja ei tarvitse huuhdella levylle. Oikeastaan, tämän vuoksi SQL Server ei (on poikkeuksia, kun loki saavuttaa 70% tilaa, manuaalinen checkpoint…) tehdä automaattinen Checkpoint tempdb.

ymmärtää, miksi SQL2014: n muutos aiheutti toisen ongelman

saatat ihmetellä, ottaen huomioon tämän tapauksen, miksi tämä koko checkpoint-juttu on tärkeä? Vastaan tähän toisella kysymyksellä.

käyttämällä kyselyn lisäämällä tietoja #TabTest1 näytteenä, ota huomioon, että SQL Server 2014+: ssa innokas kirjoittaja ei huuhdo likaisia sivuja; aiheuttaisiko tämä käyttäytyminen toisen ongelman? Toisin sanoen, mitä vaikutuksia olisi sillä, että tempdb: ssä olisi paljon likaisia sivuja?

no, ongelma on, että sinulla voisi olla skenaario, jossa tempdb: n likaiset sivut käyttävät paljon muistia arvokkaasta puskuripoolistasi. Jos ajatellaan, että on olemassa skenaarioita, joissa käytetään paljon väliaikaisia taulukoita, tämä voi nopeasti muuttua muistipaineeksi. Suurin ongelma tämän muistin paine on, että ei ole automaattista tarkastuspistettä huuhdella ne likaiset sivut, se tarkoittaa, että laiska kirjoittaja on se, joka huuhtelee ne sivut. Kun se huuhtelee sivut, se vapauttaa muut käytettävissä olevat Puskurit poistamalla harvoin käytetyt sivut puskurivälimuistista.

kuten Bob Dorr mainitsi parannusta käsittelevässä blogikirjoituksessaan, ”näissä operaatioissa käytetyt sivut on merkitty, joten laiska kirjoittaja suosii niiden kirjoittamista tempdb: hen”. Mitä hän sanoo Tässä on, että nämä sivut kirjoitetaan tempdb datatiedostoon lazy writer. Toistaiseksi kaikki hyvin. Jos kuitenkin tulee muistipaineita, laiska kirjoittaja huuhtelee nuo sivut ja laittaa ne vapaaseen listaan ja tekee sen kaikille likaisille sivuille. Tämä sisältää sivut, jotka on jaettu istuntoon, joka ei ole enää aktiivinen. Sano, että sinulla on istunto, joka loi 10GB väliaikainen pöytä. Kun olet valmis, voit pudottaa pöydän ja sulkea istunnon, mutta varatut sivut ovat silti siellä muistissa. Eikö niin? Kaikki ne inhottavat 10 Gt likaisia sivuja pöydästä, jota ei ole enää olemassa. Heti kun sinulla on muisti paine ja täytyy löytää tilaa puskurin välimuisti laittaa joitakin tietoja muistiin, laiska kirjoittaja tuhlaa aikaa huuhdella niitä hyödyttömiä likaisia sivuja, joita ei koskaan käytetä. Mielestäni oikea käsite pitäisi olla, että kun taulukko on pudotettu tai istunto on tehty, laiska kirjailija pitäisi pystyä laittamaan nämä sivut vapaaseen luetteloon, jotta vältetään käyttämästä arvokas puskuri pool välimuisti ja käynnistää tarpeettomia kirjoittaa I/O operaatioita tempdb datatiedoston.

OK, Fabiano, en tajua sitä, voitko näyttää minulle näytteen siitä, missä tämä voisi olla ongelma? Totta kai. Pysy tajuissasi.

nyt valmistellaan skenaariota ongelman simuloimiseksi. Ensimmäinen, asettaa esimerkiksi (voit käyttää mitä tahansa versiota yhtä suuri tai suurempi kuin SQL Server 2014, käytän SQL Server 2017) käyttää vain 8 Gt muistia:

1
2
3
4
5
6
7

Käytä master
GO
— Aseta MaxServerMemory 8 Gt
EXEC sys.sp_konfigure N ’ Max server memory (MB)’, n’8192′
GO
RECONFIGURE with OVERRIDE
GO

sitten, luoda 5GB taulukko suorittaa testejä. Tähän demoon käytän AdventureWorks2017-tietokantaa:

1
2
3
4
5
6
7
8
9
10

käytä AdventureWorks2017
GO
DROP TABLE jos on olemassa AdventureWorks2017.Myynti.Myyntitilausdetailbig
mene
valitse TOP 50000000 a. *
AdventureWorks2017.Myynti.SalesOrderDetailBig
alkaen AdventureWorks2017.Myynti.Myyntitilaukset a,
seikkailut 2017.Myynti.Myyntijärjestys b
OPTION (MAXDOP 1)
GO

nyt, luoda menettely, joka lisää 5GB tietoja väliaikaiseen taulukkoon käyttäen INSERT + valitse komento.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

käytä Adventureworks 2017
GO
IF OBJECT_ID (”st_1”) IS null
DROP PROC st_1;
GO
CREATE PROC st_1
AS
IF OBJECT_ID (”tempdb.dbo.#TabTest1′) ei ole nolla
DROP TABLE #TabTest1;
CREATE TABLE #TabTest1
(
Col1 CHAR(500)
);
lisätään kohtaan # TabTest1
valitse TOP 10000000
Muunna (merkki(500), A.name) COL1
Masterilta.dbo.sysobjects A,
master.dbo.sysobjects B,
master.dbo.sysobjects C,
master.dbo.sysobjects D
vaihtoehto (MAXDOP 1);
mene

nyt, ennen kuin suoritat st_1 väliaikaistaulukon kansoittamiseksi, varmista, että suoritat sen kylmän Välimuistin avulla:

1
2
3
4
5

— manuaalinen tarkistuspiste tempdb: ssä, jotta voit huuhdella sivuja ja aloittaa kylmällä env: llä.
USE tempdb
GO
CHECKPOINT;DBCC DROPCLEANBUFFERS(); DBCC FREEPROCCACHE ()
GO

nyt, Avaa uusi istunto ja suorita menettely st_1 jakaa joitakin sivuja tempdb. Kun olet valmis, voit sulkea istunnon; tiedän, että väliaikainen pöytä tuhoutuu sp: n suorittamisen jälkeen, mutta vain siinä tapauksessa, että luulet, että se ei ole, mene eteenpäin ja sulje istunto.

1
2
3
4

— pitäisi kestää noin 15 sekuntia niin kauan kuin sillä on muistia pitää
— kaikki 5GB lisättyä tietoa
EXEC AdventureWorks2017.dbo. st_1
GO

tällä hetkellä, jos katsot sys.dm_os_buffer_descriptors, näet, että noin 666k (pelottava määrä) uusia sivuja ovat likaisia:

1
2
3
4
5
6
7
8
9
10
11

valitse Page_Status = CASE
WHEN is_modified = 1 THEN ”Dirty”
ELSE ”Clean”
END,
DBName = DB_NAME(database_id),
Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID (”tempdb”)
GROUP BY database_id,
is_modified
GO

tässä on hauskin osuus. Jos yrität lukea 5GB taulukko (myynti.SalesOrderDetailBig) luotu AdventureWorks2017 DB, mitä tapahtuu? Muista, se on 5GB väliaikainen pöytä. Kaikki sivut ovat muistissa ja likaisia. Jos yrität lukea toisen 5GB, ei ole tarpeeksi muistia käytettävissä (max palvelimen muisti on asetettu 8GB), ja laiska kirjailija on ajaa ja laittaa joitakin sivuja vapaa lista. Sitä ennen se huuhtoo kaikki likaiset sivut. Kun se tekee sitä, kysely nukutetaan ja voi kestää kauan juosta.

1
2
3

valitse lukumäärä (*) osoitteesta Seikkailutyö2017.Myynti.SalesOrderDetailBig
OPTION (MAXDOP 1)
GO

kun kysely on käynnissä, tämän näet:

odotetusti, laiska kirjoittaa / sek suorituslaskuri näyttää paljon aktiivisuutta:

kysely odottaa SLEEP_TASK, ja vireillä olevia I/O-pyyntöjä on useita.

1
2
3
4
5

valitse * SYS: stä.dm_os_waiting_taskut
WHERE session_id = 51
GO
SELECT * FROM sys. dm_io_pending_io_pyynnöt
GO

tarkoitan Myyntikyselyä.SalesOrderDetailBig kestää 3 minuuttia ajaa, ja suurimman osan ajasta se odottaa laiska kirjailija huuhtele joitakin likainen tempdb sivuja ja laittaa sivuja vapaa lista. Jos laiska kirjoittaja ei tarvitse huuhdella sivuja, kyselyn pitäisi pystyä toimimaan vain muutamassa sekunnissa.

mutta odota, SQL 2016 + epäsuora tarkistuspiste tempdb: ssä korjasi tämän ongelman

SQL Server 2016: ssa epäsuora tarkistuspiste, erinomainen ominaisuus, joka alun perin otettiin käyttöön SQL Server 2012: ssa, on oletusarvoisesti käytössä mallijärjestelmän tietokannassa ja asetettu 60 sekuntiin. Koska tempdb-tietokanta on johdettu mallista käynnistyksen aikana, se perii tämän ominaisuuden ja on oletusarvoisesti epäsuora tarkistuspiste käytössä. (Huomautus: TF3468 voidaan poistaa käytöstä)

jos et ole kuullut epäsuorasta tarkastuspisteestä, Seuraavassa on nopea selitys sille tästä MS-asiakirjasta:

”tavanomainen tai automaattinen tarkistuspisteen algoritmi olettaa kiinteän aikaväli palauttaa kunkin lokitietueen ilman, että otetaan huomioon tietokantatapahtuman likaamien sivujen määrä. Yksinkertaisemmin sanottuna, kun (lokitietueiden määrä * kiinteä aikaväli lokitietueen palauttamiseen) ylittää esiintymistasolla asetetun palautusvälin, automaattinen tarkastuspiste käynnistyy tietokannassa. Koska automaattinen tarkastuspiste ei ota huomioon jokaisen tapahtuman likaisia sivuja (tärkeä toipumisajan vaikuttaja), se tekee palautumisajasta vähemmän ennustettavaa. Epäsuoralla tarkistuspisteellä Moottori ylläpitää osioituja likaisten sivujen luetteloita tietokantaa kohden seuratakseen kuhunkin tapahtumaan liittyvien puskuripoolissa olevien likaisten sivujen määrää. Kun Tausta recovery writer polling dirty page manager havaitsee ehto, jossa likaisten sivujen määrä ylittää dirty page kynnys (lasketaan heuristics), recovery writer käynnistäisi värin likaisten sivujen varmistaa tietokannan elpyminen olisi täydellinen sisällä target_recovery_time set”

kuten aiemmin mainitsin, ei ole elpymistä tarvitse tempdb koska se on uudelleen SQL service start, joten epäsuorat tarkastuspisteet tempdb ei ehkä ole paljon järkeä elpymisen näkökulmasta. Epäsuora tarkistuspiste-ominaisuus on kuitenkin edelleen tärkeä sen varmistamiseksi, että tempdb: n likaiset sivut eivät edelleenkään poista puskuripoolisivuja käyttäjätietokannan työmäärästä, koska ei ole olemassa automaattista tarkistuspistettä, joka huuhtelisi sivuja tempdb: lle.

yksi syy ottaa epäsuorat tarkastuspisteet käyttöön tempdb: ssä on auttaa skenaarioita, joissa tempdb: n sivut ovat likaisia, poistamaan puskurivaraston datavälimuistin ja aiheuttamaan painetta laiskalle kirjoittajalle, kun se huuhtelee likaisia sivuja, juuri sitä skenaariota, jota analysoimme.

jos epäsuora tarkistuspiste olisi aiemmin huuhtonut likaiset sivut tempdb: ssä, kyselyn ei tarvitsisi odottaa (SLEEP_TASK) laiskan kirjoittajan huuhtelevan sitä.

but, wait, don ’ t be happy yet;there is a small problem with indirect check points on tempdb. Mikä tahansa ei-Kirjautunut ”bulk” – toiminto, joka täyttää ”eager write” – vaatimukset tempdb: ssä, ei ole ehdokas, jonka recovery writer (sisäinen säie, joka toimii epäsuorassa tarkastuspisteessä) voi huuhdella.

tämä herättää tärkeän kysymyksen: Mikä datalataustoiminto kirjataan minimaalisesti tempdb: hen? Tämä on tärkeää tietää, koska minimaalisesti kirjattuja tempdb-toimintoja ei huuhdella epäsuoralla tarkastuspisteellä. Seuraavaa luetteloa voidaan käyttää auttamaan sinua ymmärtämään, mitkä tempdb: n lataustoiminnot kirjataan minimaalisesti ja mitkä eivät.

komento

on minimaalisesti Kirjautunut?

lisää kohtaan #TMP + valitse

kyllä, koska paikalliset väliaikaiset pöydät ovat yksityisiä luomisistunnossa, TABLOCKIA ei tarvitse käyttää ML: n leikkaukseen.

lisää kohtaan #tmp kanssa(TABLOCK) + valitse

Kyllä. Huomaa, että TABLOCK tarvitaan saada + valitse Lisää operaattori käynnissä rinnakkain sql2016+.

valitse muotoon #TMP

Kyllä. Koska tempdb DB recovery malli on aina asetettu yksinkertainen, valitse + osaksi on ML.

valitse tempdb: hen.dbo.TMP

Kyllä.

lisää tempdb: hen.dbo.TMP kanssa (TABLOCK) + valitse

Kyllä. Koska tämä on säännöllinen käyttäjä taulukko, joka voi käyttää mitä tahansa istuntoa, se vaatii TABLOCK on ML. Sama koskee maailmanlaajuista ( # # ) väliaikaista pöytää.

lisää tempdb: hen.dbo.TMP + valitse

Ei. Joten, ainoa tapa ei ole oikeutettu ML-operaatioon tempdb: ssä on käyttää tavallista tai globaalia (##) väliaikaista taulukkoa ja älä määritä TABLOCKIA.

kuten näet, suurin piirtein kaikki tempdb: ssä käyttämäsi insert-komennot kirjataan minimaalisesti, eikä epäsuora tarkistuspiste huuhdo sivuja.

Yhteenveto

Tempdb-vianmääritys voi olla hyvin monimutkaista. Se vaatii sinua ymmärtämään monia osia SQL Server engine. Jos tarvitset vastauksen ”miksi” kysymykseen, varmista, että olet ottanut jonkin aikaa opiskella SQL Server sisäosat.

SQL Serverin nykyinen käyttäytyminen on vain ” niin kuin se nykyään toimii.”Odotan sen parantuvan tällä alueella, koska ei ole harvinaista, että näen ympäristön, jossa tempdb on top 1-tietokanta datavälimuistin käytöstä. En halua minun ympäristöissä on minun arvokas productions taulukot tekee fyysinen lukee, koska joku juoksi raportin, joka loi 20GB väliaikainen pöytä, tai, pahimmassa tapauksessa odottaa laiska kirjailija huuhdella joitakin ”koskaan käytetään uudelleen” likaisia sivuja. Olisi hyvä olla jotain ”max buffer pool usage” asetus tempdb. Mitä mieltä olet?

Vastaa

Sähköpostiosoitettasi ei julkaista.