Redgate Hub

în ultimii ani, am lucrat pentru a menține și optimiza un mediu de baze de date și m-am confruntat cu tot felul de probleme, dar un punct de durere comun în aproape toate mediile SQL Server este tempdb.

utilizarea tempdb în SQL Server este bine cunoscută și nu vreau să explic aceeași listă de probleme și recomandări deja acoperite foarte bine în multe articole de pe internet. Iată câteva dintre problemele cunoscute pe care le-ați putea google cu ușurință pentru a obține mai multe informații despre:

  • obiect allocation contention on metadate pages used to manage space on tempdb data file
  • metadate contention for pages that apartment to system objects used to track the temporary object ‘ s metadata (btw, acest lucru este îmbunătățit pe SQL Server 2019)
  • probleme cu coduri care nu sunt optimizate în beneficiul cache tempdb de metadate de sistem
  • scurgeri de hash și felul

în acest articol, nu aș vrea să mă concentrez pe acele lucruri comune, dar în alte probleme interesante pe care le-am găsit în timp ce îmi sprijineam clienții.

unele probleme tempdb sunt foarte probabil să aibă

următoarea este o listă de câteva probleme tempdb sunt foarte probabil să aibă în mediul SQL Server, care nu pot să știu despre.

  • în versiunile anterioare SQL Server 2014, eager writer încetinește inserțiile și pierde I/o atunci când nu este necesară o operație de spălare.
  • pe SQL Server 2014 (backported la SQL Server 2012 SP1+CU10 sau SP2+CU1), scriitorul dornic a fost relaxat pentru a nu spăla pentru tempdb; aceasta a rezolvat o problemă și a provocat alta.
  • punct de control Indirect (timp de recuperare țintă) activat în mod implicit pe tempdb pentru SQL Server 2016. Ideea a fost de a curăța paginile murdare create de schimbarea în sql2014, dar nu face cu adevărat o treabă bună de fixare, deoarece nu curăță paginile murdare alocate operațiunilor minim logate.

Eager Writer și tempdb

una dintre multele îmbunătățiri de performanță care au venit cu SQL Server 2014 este că nu spală paginile murdare create într-o operație minim logat pe tempdb. Acest lucru vă oferă avantajul de a avea inserții mai rapide (în comparație cu versiunile anterioare), dar a cauzat o altă problemă, deoarece acele pagini alocate pot dura mult timp pentru a fi eliminate din cache-ul de date buffer pool. Înainte de a discuta problema, uita-te rapid la beneficiul și apoi să înțeleagă unele concepte importante de culoare pagini murdare pe tempdb.

utilizați următoarea interogare care introduce aproximativ 520 MB de date într-un tabel temp:

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

utilizați tempdb
GO
dacă OBJECT_ID (‘#TabTest1’) nu este nul
DROP TABLE # TabTest1;
creați TABLE # TabTest1(Col1 CHAR(500));
introduceți în # TabTest1
selectați top 1000000
CONVERT(CHAR(500), A.name) ca Col1
de la maestru.dbo.sysobjects A,
maestru.dbo.sysobjects B,
maestru.dbo.sysobjects C,
maestru.dbo.sysobjects D
opțiune (MAXDOP 1);
merge

când l-am rulat în instanțele mele locale cu ambele tempdb configurate cu aceleași detalii (același disc, dimensiuni inițiale, steaguri de urmărire etc.), Văd următoarele rezultate:

  • pe SQL Server 2008 R2 (10.50.6220.0) a durat 32 de secunde pentru a rula.
  • pe SQL Server 2017 (14.0.3238.1) a durat 1 secundă pentru a rula.

după cum puteți vedea, diferența este foarte mare; acest lucru este destul de mult din cauza următoarele:

1
2
3
4
5
6
7
8
9
10

selectați Page_Status = caz
când is_modified = 1 apoi ‘Dirty’
altfel ‘Clean’
END,
DBName = DB_NAME(database_id),
Pages = COUNT(1)
din sys.dm_os_buffer_descriptors
unde database_id = db_id(‘tempdb’)
grup de database_id,
is_modified

SQL Server 2008R2

SQL Server 2017

după cum puteți vedea, verificarea DMV sys.dm_os_buffer_descriptors arată că majoritatea paginilor alocate pe SQL Server 2017 sunt murdare, iar pe SQL Server 2008r2 procesul eager writer a avut nevoie de timp pentru a curăța și scrie paginile pe datele tempdb dosar. De aici vine diferența de timp. Pe SQL 2008R2, comanda trebuie să aștepte culoarea, iar pe sql2017 nu.

ce face Checkpoint pe tempdb?

înainte de a merge mai departe, iată o recapitulare rapidă a unor concepte importante pe care trebuie să le cunoașteți pentru a înțelege problemele pe care le voi menționa în acest articol.

când introduceți un rând într-un tabel, SQL Server nu scrie imediat în fișierul de date (mdf). Are un control pentru a-l scrie pe jurnal (ldf) și pentru a păstra rândul într-o pagină în memorie. Paginile care sunt în memorie și nu au fost scrise fizic în fișierul de date se numesc pagini murdare. O pagină murdară este scrisă în fișierul de date de unul dintre cele trei procese: checkpoint, Lazy writer sau eager writer. Acestea sunt elementele de bază ale scrierii I/O pe SQL Server și trebuie să o înțelegeți pentru a putea depana și explica probleme complexe legate de tempdb și întregul motor SQL.

rândurile pot exista numai în fișierul jurnal și în paginile murdare din memorie. Dacă se întâmplă un accident neașteptat și SQL Server se oprește, odată ce este din nou, SQL engine trebuie să refacă orice tranzacție care a fost scris în fișierul jurnal de tranzacții și se aplică aceste modificări la fișierele de date (mdf). Toată chestia asta nu are sens pentru tempdb, nu? Deoarece tempdb este recreat pe SQL Server start, nu este nevoie de tempdb pentru a recupera tranzacțiile din fișierul jurnal în fișierul de date, prin urmare, nu este nevoie pentru a spăla pagini murdare tempdb pe disc. De fapt, acesta este motivul pentru care SQL Server nu (există excepții, când jurnalul atinge 70% din spațiu, punct de control manual…) face puncte de control automate pentru tempdb.

înțelegerea de ce schimbarea pe SQL2014 a cauzat o altă problemă

s-ar putea să vă întrebați, având în vedere cazul în care ne uităm la, de ce acest lucru punct de control întreg este important? Ei bine, permiteți-mi să răspund la această întrebare cu o altă întrebare.

folosind interogarea inserarea datelor pe # TabTest1 ca un eșantion, ia în considerare faptul că pe SQL Server 2014+, Pagini murdare nu va fi spălată de scriitor dornici; ar Acest comportament cauza o altă problemă? Cu alte cuvinte, care ar fi impactul de a avea multe pagini murdare pe tempdb?

Ei bine, problema este că ai putea avea un scenariu în cazul în care tempdb pagini murdare sunt folosind o mulțime de memorie de piscina tampon prețioase. Dacă considerați că există scenarii cu utilizarea intensă a tabelelor temporare, acest lucru se poate transforma rapid în presiune de memorie. Principala problemă a acestei presiuni de memorie este că nu va exista un punct de control automat pentru a spăla acele pagini murdare, ceea ce înseamnă că scriitorul leneș va fi cel care va spăla acele pagini. După ce spală paginile, atunci va elibera alte tampoane disponibile prin eliminarea paginilor utilizate rar din memoria cache tampon.

după cum a menționat Bob Dorr în postarea sa pe blog despre îmbunătățire, „paginile utilizate în aceste operațiuni sunt marcate atât de leneș scriitor va favoriza scrierea lor la tempdb”. Ceea ce spune aici este că aceste pagini vor fi scrise în fișierul de date tempdb de către lazy writer. Până acum, totul e bine. Cu toate acestea, dacă există vreo presiune de memorie, lazy writer va spăla acele pagini și le va pune în Lista gratuită și o va face pentru toate paginile murdare. Aceasta include pagini care au fost alocate într-o sesiune care nu mai sunt active. Să presupunem că aveți o sesiune care a creat un tabel temporar de 10 GB. După ce ați terminat, puteți renunța la tabel și puteți închide sesiunea, dar paginile alocate vor fi în continuare acolo în memorie. Corect? Toate acele 10GB urât de pagini murdare ale unui tabel care nu mai există. De îndată ce aveți presiune de memorie și trebuie să găsiți spațiu pe memoria cache tampon pentru a pune unele date în memorie, lazy writer va pierde timpul pentru a spăla acele pagini murdare inutile care nu vor fi folosite niciodată. În opinia mea, conceptul corect ar trebui să fie că, odată ce masa este abandonată sau sesiunea este terminată, lazy writer ar trebui să poată pune aceste pagini pe Lista gratuită pentru a evita utilizarea prețiosului cache buffer pool și declanșarea operațiunilor inutile de scriere i/O pe fișierul de date tempdb.

OK, Fabiano, nu înțeleg, poți să-mi arăți o mostră despre unde ar putea fi o problemă? Da, sigur. Stai cu mine.

acum pentru a pregăti scenariul pentru a simula problema. Mai întâi, setați instanța (puteți utiliza orice versiune egală sau mai mare decât SQL Server 2014, folosesc un SQL Server 2017) pentru a utiliza doar 8 GB de memorie:

1
2
3
4
5
6
7

utilizați master
merge
— Set MaxServerMemory la 8GB
EXEC SYS.sp_configurează memoria serverului N ‘ Max (MB)’, N ‘8192’
GO
reconfigurează cu suprascriere
GO

apoi, creați un tabel de 5 GB pentru a rula testele. Pentru acest demo, folosesc baza de date AdventureWorks2017:

1
2
3
4
5
6
7
8
9
10

utilizați AdventureWorks2017
GO
DROP TABLE dacă există AdventureWorks2017.Vânzări.SalesOrderDetailBig
du-te
selectați top 50000000 a.*
în AdventureWorks2017.Vânzări.SalesOrderDetailBig
din AdventureWorks2017.Vânzări.SalesOrderDetail a,
AdventureWorks2017.Vânzări.SalesOrderDetail B
opțiune (MAXDOP 1)
merge

acum, creați o procedură care va insera 5 GB de date într-un tabel temporar folosind o comandă INSERT + SELECT.

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

utilizați AdventureWorks2017
GO
dacă OBJECT_ID (‘st_1’) nu este nul
DROP PROC ST_1;
mergeți
creați PROC st_1
ca
dacă OBJECT_ID(‘tempdb.dbo.# TabTest1’) nu este NULL
DROP TABLE #TabTest1;
creare TABLE #TabTest1
(
Col1 CHAR(500)
);
introduceți în # TabTest1
selectați top 10000000
CONVERT(CHAR(500), A.name) ca Col1
de la maestru.dbo.sysobjects A,
maestru.dbo.sysobjects B,
maestru.dbo.sysobjects C,
maestru.dbo.SYSOBJECTS D
opțiune (MAXDOP 1);
merge

acum, înainte de a rula st_1 pentru a popula tabelul temporar, asigurați-vă că îl rulați folosind o memorie cache rece:

1
2
3
4
5

— checkpoint Manual pe tempdb pentru a spăla paginile și începe cu un env rece.
utilizați tempdb
GO
punct de control;DBCC DROPCLEANBUFFERS(); DBCC FREEPROCCACHE ()
merge

acum, deschideți o nouă sesiune și executați procedura st_1 pentru a aloca câteva pagini pe tempdb. După ce ați terminat, puteți închide sesiunea; știu că tabelul temporar este distrus după executarea sp, dar doar în cazul în care credeți că nu este, mergeți mai departe și închideți sesiunea.

1
2
3
4

— ar trebui să dureze aproximativ 15 secunde, atâta timp cât are memorie pentru a păstra
— toate 5GB de date inserate
Exec AdventureWorks2017. dbo. st_1
merge

în acest moment, dacă te uiți la sys.dm_os_buffer_descriptors, veți vedea că aproximativ 666k (număr înfricoșător) de pagini nou alocate sunt murdare:

1
2
3
4
5
6
7
8
9
10
11

selectați Page_Status = caz
când is_modified = 1 apoi ‘Dirty’
altfel ‘Clean’
END,
DBName = DB_NAME(database_id),
Pages = COUNT(1)
din sys.dm_os_buffer_descriptors
unde database_id = db_id (‘tempdb’)
grup de database_id,
is_modified
merge

acum, aici este partea distractivă. Dacă încercați să citiți tabelul de 5 GB (vânzări.SalesOrderDetailBig) creat pe AdventureWorks2017 DB, ce se întâmplă? Amintiți-vă, este o masă temporară de 5 GB. Aproape toate aceste pagini sunt în memorie și murdare. Dacă încercați să citiți încă 5 GB, nu va fi suficientă memorie disponibilă (memoria maximă a serverului este setată la 8 GB), iar lazy writer va trebui să ruleze și să pună câteva pagini pe Lista gratuită. Înainte de a face asta, va spăla toate acele pagini murdare. În timp ce o face, interogarea dvs. va fi adormită și poate dura mult timp pentru a rula.

1
2
3

selectați COUNT (*) din AdventureWorks2017.Vânzări.Salesorderdetailmare
opțiune (MAXDOP 1)
merge

în timp ce interogarea se execută, aceasta este ceea ce puteți vedea:

cum era de așteptat, lazy scrie / sec performance counter arată o mulțime de activitate:

interogarea este în așteptare pe SLEEP_TASK, și există mai multe în așteptare i/o Cereri.

1
2
3
4
5

selectați * din sys.dm_os_waiting_tasks
unde session_id = 51
GO
selectați * din sys. dm_io_pending_io_requests
GO

punctul meu este că interogarea privind vânzările.SalesOrderDetailBig durează 3 minute pentru a rula și, de cele mai multe ori, așteaptă lazy writer să spele unele dintre paginile tempdb murdare și să pună pagini pe Lista gratuită. Dacă lazy writer nu a trebuit să spele acele pagini, interogarea ar trebui să poată rula în doar câteva secunde.

dar așteptați, SQL 2016+ checkpoint indirect pe tempdb fix această problemă

pe SQL Server 2016 checkpoint indirect, o caracteristică remarcabilă introdusă inițial pe SQL Server 2012, este activată în mod implicit pe baza de date sistem model și setat la 60 de secunde. Deoarece baza de date tempdb este derivată din model în timpul pornirii, moștenește această proprietate și are punct de control indirect activat în mod implicit. (Notă: TF3468 poate fi folosit pentru a dezactiva acest lucru)

dacă nu ați auzit despre punctul de control indirect, următoarea este o explicație rapidă a acestuia din acest document MS:

„algoritmul de control convențional sau automat presupune un interval de timp fix pentru a recupera fiecare înregistrare jurnal fără a ține cont de numărul de pagini murdare de o tranzacție de bază de date. În termeni simpli, atunci când (numărul de înregistrări jurnal * interval de timp fix pentru a recupera o înregistrare jurnal) depășește intervalul de recuperare stabilit la nivel de instanță, punctul de control automat este declanșat în baza de date. Deoarece punctul de control automat nu ține cont de paginile murdare din fiecare tranzacție (un influencer important al timpului de recuperare), face ca timpul de recuperare să fie mai puțin previzibil. Cu checkpoint indirect, motorul menține liste de pagini murdare partiționate pe bază de date pentru a urmări numărul de pagini murdare din piscina tampon pentru acea bază de date din fiecare tranzacție. În cazul în care scriitorul de recuperare de fundal de votare managerul de pagini murdare detectează starea, în cazul în care numărul de pagini murdare depășește pragul de pagini murdare (calculat prin euristică), scriitorul de recuperare ar declanșa culoare de pagini murdare pentru a asigura recuperarea bazei de date ar finaliza în cadrul target_recovery_time set”

așa cum am menționat mai înainte, nu există nici o nevoie de recuperare pentru tempdb așa cum este recreat pe SQL service start, astfel încât punctele de control indirecte pe tempdb nu poate face prea mult sens dintr-o perspectivă de recuperare. Cu toate acestea, caracteristica checkpoint indirect este încă importantă pentru a vă asigura că paginile murdare din tempdb nu continuă să elimine paginile buffer pool din volumul de lucru al bazei de date a utilizatorilor, deoarece nu există un punct de control automat pentru a spăla paginile pentru tempdb.

unul dintre motivele pentru a permite Puncte de control indirecte pe tempdb este de a ajuta scenarii cu pagini tempdb murdare ia cache-ul de date buffer pool și cauza presiune pe scriitor leneș în timp ce bufeuri pagini murdare, scenariul exact analizăm.

dacă checkpoint indirect ar fi spălat anterior paginile murdare pe tempdb, o interogare nu ar trebui să aștepte (SLEEP_TASK) pentru ca lazy writer să o spele.

dar, stai, nu fi fericit încă; există o mică problemă cu punctele de control indirecte pe tempdb. Orice operațiune „în vrac” neînregistrată care se califică pentru o „scriere dornică” în tempdb nu este un candidat care să fie spălat de scriitorul de recuperare (firul intern care rulează punctul de control indirect).

aceasta ridică o întrebare importantă: ce operațiune de încărcare a datelor este înregistrată minim pe tempdb? Acest lucru este important de știut, deoarece operațiunile înregistrate minim pe tempdb nu vor fi spălate de punctul de control indirect. Următoarea listă poate fi utilizată pentru a vă ajuta să înțelegeți ce operațiuni de încărcare pe tempdb vor fi înregistrate minim și care nu.

comandă

este minim logat?

introduceți în # tmp + selectați

da, deoarece tabelele temporare locale sunt private pentru sesiunea de creare, nu este nevoie să utilizați TABLOCK pentru a obține o operație ML.

introduceți în #TMP cu (TABLOCK) + selectați

Da. Rețineți că TABLOCK este necesar pentru a obține + selectați un operator de inserare care rulează în paralel pe SQL2016+.

selectați în #TMP

Da. Deoarece modelul de recuperare tempdb DB este întotdeauna setat la simplu, un SELECT + INTO va fi ML.

selectați în tempdb.dbo.TMP

Da.

introduceți în tempdb.dbo.TMP cu (TABLOCK) + selectați

Da. Deoarece acesta este un tabel de utilizator obișnuit care poate fi accesat de orice sesiune, este nevoie ca un TABLOCK să fie ML. Același lucru este valabil și pentru un tabel temporar global ( # # ).

introduceți în tempdb.dbo.TMP + selectați

nu. Deci, singura modalitate de a nu se califica pentru o operațiune ML pe tempdb este de a utiliza un regulat sau un tabel temporar global (##) și nu specificați TABLOCK.

după cum puteți vedea, aproape toate comenzile de inserare pe care le puteți utiliza pe tempdb vor fi înregistrate minim, iar paginile nu vor fi spălate de punctul de control indirect.

rezumat

depanarea Tempdb poate fi foarte complexă. Este nevoie să înțelegeți multe componente ale motorului SQL Server. Dacă aveți nevoie de un răspuns la întrebarea „De ce”, asigurați-vă că ați luat ceva timp pentru a studia SQL Server interne.

comportamentul actual al SQL Server este doar „modul în care funcționează astăzi.”Mă aștept sincer să se îmbunătățească în acest domeniu, deoarece nu este rar pentru mine să văd un mediu în care tempdb este baza de date de top 1 privind utilizarea cache-ului de date. Nu vreau ca mediile mele să aibă tabelele mele prețioase de producții care fac citiri fizice, deoarece cineva a rulat un raport care a creat un tabel temporar de 20 GB sau, într-un scenariu cel mai rău caz, să aștepte ca scriitorul leneș să spele unele pagini murdare „nu vor mai fi folosite niciodată”. Ar fi bine să aveți ceva de genul unei setări „max buffer pool usage” pe tempdb. Tu ce crezi?

Lasă un răspuns

Adresa ta de email nu va fi publicată.