SQLShack

în acest articol, vom discuta despre cele mai bune practici care ar trebui urmate pentru a menține baza de date TempDB într-o stare sănătoasă și pentru a preveni orice creștere neașteptată a bazei de date, pe lângă procedura care poate fi urmată pentru a detecta această creștere neașteptată odată ce a avut loc.

prezentare generală TempDB

baza de date TempDB este una dintre cele mai importante baze de date de sistem SQL Server, care este utilizată pentru a stoca obiecte temporare ale utilizatorului, cum ar fi tabelele temporare definite de utilizator sau returnate din execuția funcției evaluate de tabel, procedurile stocate temporar, variabilele tabelului sau indexurile.

în plus față de obiectele utilizator, TempDB va fi utilizat pentru a stoca obiecte interne care sunt create de motorul bazei de date SQL Server în timpul diferitelor operațiuni interne, cum ar fi sortarea intermediară, spooling, agregate sau operațiuni cursor.

baza de date a sistemului TempDB este utilizată, de asemenea, pentru a stoca versiunile de rânduri pentru a sprijini caracteristicile care necesită urmărirea modificărilor efectuate pe rândurile de tabel, cum ar fi nivelul de izolare instantaneu al tranzacțiilor, reconstruirea indexului Online sau caracteristica Seturi de rezultate Active multiple.

baza de date TempDB va fi abandonată și recreată din nou de fiecare dată când serviciul SQL Server este repornit, începând cu o nouă copie curată a bazei de date. Pe baza acestui fapt, toate utilizator și baza de date internă obiectat care sunt stocate pe această bază de date va fi abandonat automat atunci când serviciul SQL Server este repornit sau atunci când sesiunea în cazul în care aceste obiecte create este deconectat. Prin urmare, operațiunile de backup și restaurare nu sunt disponibile pentru TempDB.

de ce TempDB

baza de date a sistemului TempDB joacă un rol important în procesul de reglare a performanței SQL Server. Deoarece este folosit ca stocare cache pentru a stoca diferite tipuri de obiecte de bază de date de utilizator și pentru a stoca obiectele interne ale sistemului pentru a accelera procesele legate de motorul bazei de date SQL Server.

datorită acestui rol vital pe care îl joacă baza de date a sistemului TempDB în îmbunătățirea performanței generale a instanței SQL Server, este foarte important să aplicați cele mai bune practici de dimensiune inițială, creștere automată și locație pe datele bazei de date TempDB și fișierele jurnal. Aplicarea acestor bune practici într-un mod care se potrivește cu volumul de lucru al instanței SQL Server va împiedica extinderea foarte frecventă a datelor bazei de date TempDB și a fișierelor jurnal, luând în considerare faptul că procesul de extindere a fișierului este un proces costisitor în care SQL Server Engine va solicita spațiu suplimentar de la sistemul de operare, care va efectua reducerea la zero a spațiului respectiv înainte de a-l aloca SQL Server Engine. Vă puteți imagina timpul și resursele necesare pentru acest proces de alocare.

dacă aplicați deja toate cele mai bune practici și a avut loc o operație de creștere neașteptată, puteți accelera procesul de alocare utilizând opțiunea de inițializare instantanee a fișierului bazei de date, în care sistemul de operare va avea încredere în serviciul SQL Server și va aloca spațiul necesar fără a efectua operația de pierdere a timpului și a resurselor pe spațiul alocat.

cele mai bune practici

există o serie de cele mai bune practici de configurare a bazei de date a sistemului TempDB care sunt recomandate pentru a obține cea mai bună performanță din acea resursă partajată și pentru a vă proteja instanța SQL Server de orice creștere neașteptată a datelor bazei de date TempDB sau a fișierelor jurnal care pot umple spațiul de disc subiacent.

aceste configurații includ:

• numărul fișierelor de date TempDB ar trebui să se potrivească cu numărul de procesoare logice, până la opt fișiere, pe mașina în care este instalată instanța SQL Server. Dacă numărul de procesoare logice de pe acea mașină este mai mare de opt, setați numărul de fișiere de date la opt, cu capacitatea de a-l extinde cu mai multe de 4 în cazul în care aveți o problemă de contenție TempDB
• asigurați-vă că fișierele TempDB au dimensiuni egale în cadrul aceluiași grup de fișiere pentru a oferi cel mai înalt grad posibil de eficiență a operațiunilor paralele, datorită mecanismului de scriere proporțional de umplere care este utilizat pentru a scrie datele din fișierele de date. Pentru mai multe informații, verificați înțelegerea algoritmului de umplere proporțională SQL Server
• configurați datele bazei de date TempDB și fișierele jurnal cu o dimensiune inițială rezonabilă și cantități de creștere automată care se potrivesc cantității așteptate de date care vor fi scrise pe aceste fișiere și diferitele tipuri de sarcini de lucru efectuate în instanța SQL Server, inclusiv activitățile utilizatorului și operațiunile de întreținere a indexului, pentru a evita sumele frecvente mici de creștere care afectează performanța generală a instanței
• localizați fișierele TempDB pe un subsistem I/O rapid pentru a evita orice Blocajele I /O
• localizați TempDB pe o unitate de disc separată, departe de bazele de date utilizator și alte baze de date de sistem. În acest fel, operațiunile grele de citire și scriere efectuate pe TempDB nu vor afecta sau nu vor fi afectate de volumul de lucru al bazelor de date ale utilizatorilor, îmbunătățind performanța generală a instanței SQL Server

pentru mai multe informații despre noile îmbunătățiri ale performanței TempDB, verificați documentul bazei de date TempDB.

unde se aplică

înainte de versiunea SQL Server 2016, alocarea dimensiunii TempDB poate fi efectuată după instalarea instanței SQL Server, din pagina Proprietăți bază de date. Unde puteți controla numărul de fișiere de date de bază de date care se potrivesc instanței SQL Server curent, dimensiunea inițială pentru fiecare fișier de date și jurnal de baze de date, valoarea corespunzătoare pentru opțiunea Autogrowth și locația datelor bazei de date și fișierele jurnal.

pentru a efectua aceste modificări, extindeți nodul baze de date în SQL Server Management Studio, extindeți nodul baze de date de sistem, apoi faceți clic dreapta pe baza de date tempdb și alegeți opțiunea Proprietăți ca mai jos:

din fereastra Proprietăți bază de date deschisă, răsfoiți pagina fișiere și verificați sau modificați diferitele setări date bază de date și fișiere jurnal prezentate mai jos:

pornind de la versiunea SQL Server 2016, toate aceste modificări, cu cele mai bune practici care se potrivesc resurselor curente ale serverului deja sugerate de expertul de instalare, pot fi verificate și efectuate în timpul procesului de instalare a instanței SQL Server, utilizând Expertul de instalare SQL Server, sub configurațiile motorului bazei de date / TempDB, după cum se arată mai jos:

Track TempDB growth

urmărirea creșterii fișierelor bazei de date TemDB și interogările care consumă resursele TempDB pot fi efectuate utilizând contoarele Performance Monitor asociate cu fișierele TempDB și instrumentul SQL Profiler care ascultă un anumit tip de încărcare de date.

o modalitate mai prietenoasă a administratorului bazei de date de a urmări creșterea fișierelor bazei de date TempDB este interogarea următoarelor Vizualizări dinamice de gestionare:

• SYS.dm_db_file_space_usage care returnează informațiile de utilizare a spațiului pentru fiecare fișier din Baza de date, fără a arăta ce sesiune sau activitate a consumat acel spațiu
• SYS.dm_db_session_space_usage care returnează numărul de pagini alocate și nealocate pentru fiecare sesiune
• sys.dm_db_task_space_usage care returnează numărul de pagini alocate și nealocate pentru fiecare activitate

rețineți că rezultatul returnat de SYS.dm_db_session_space_usage DMV a rezumat rezultatul returnat din sys.dm_db_task_space_usage DMV când toate sarcinile sunt finalizate cu succes, datorită faptului că, mai multe sarcini legate de aceeași interogare vor fi efectuate în cadrul aceleiași sesiuni. Un alt avantaj al acestor DMV-uri este că va arăta tipul de obiect care consumă resursele TempDB și dacă acest obiect este un obiect utilizator, cum ar fi un tabel temporar sau variabil sau un obiect intern, cum ar fi întreținerea indexului sau operațiunea de sortare. Deci, pe baza numărului de pagini consumate de fiecare obiect, puteți specifica dacă TempDB este consumat de un obiect intern sau utilizator și puteți optimiza acel consumator.

SYS.dm_db_file_space_usage

SYS.dm_db_file_space_usage DMV poate fi utilizat pentru a monitoriza spațiul pe disc utilizat în fișierele bazei de date TempDB. Interogările de mai jos arată spațiul nealocat, spațiul utilizat pentru stocarea versiunilor de rânduri, spațiul consumat de obiectele interne și, în final, spațiul consumat de obiectele utilizatorului, după cum urmează:

din rezultatul, care este returnat de la instanța mea locală SQL Server, puteți vedea că, există 60 MB neutilizate în fișierele TempDB, cu doar 0.5 MB utilizate pentru obiecte interne și 1.5 MB utilizate pentru obiectele de utilizator, așa cum se arată mai jos:

Sys.dm_db_task_space_usage și SYS.dm_db_session_space_usage

Sys.dm_db_task_space_usage și SYS.dm_db_session_space DMVs sunt utilizate pentru a verifica numărul de pagini alocate și deallocated de fiecare sarcină sau sesiune în baza de date tempdb. În acest fel, veți putea vedea ce utilizator sau un obiect intern care consumă spațiul TempDB.

de exemplu, rezultatul de mai jos este returnat din interogarea sistemului.dm_db_task_space_usage DMV în baza de date TempDB, unde va returna numărul de pagini alocate și alocate pentru fiecare utilizator sau sarcină internă, după cum urmează:

dar DBA nu va necesita ca multe detalii pentru a începe urmărirea interogării care consumă resursele TempDB. Deci, trebuie să rezumăm rezultatul anterior pentru fiecare sesiune, folosind interogarea de mai jos:

iar rezultatul consumului de pagini rezumate pentru fiecare sesiune va fi ca mai jos:

același rezultat poate fi returnat prin interogarea sistemului.dm_db_session_space_usage DMW, care rezumă alocarea și alocarea paginilor pentru toate activitățile care rulează în aceeași sesiune, așa cum se arată mai jos:

să ne efectua unele acțiuni pe baza de date TempDB folosind atât obiecte interne și de utilizator ca mai jos:

apoi executați interogarea consolidată de mai jos, care returnează numărul de pagini alocate și nealocate de obiecte interne și de utilizator și arată ambele operații finalizate (folosind sys.dm_db_session_space_usage DMV) și operațiunile nefinalizate (folosind SYS.dm_db_task_space_usage DMV):

iar rezultatul operațiunilor anterioare va reflecta ieșirea interogării, arătând numărul de pagini alocate și alocate prin crearea tabelului temporar al utilizatorului și sortarea datelor în timpul procesului de reconstrucție a indexului, așa cum se arată mai jos:

pentru a obține textul interogării care consumă resursele TempDB, puteți utiliza abilitățile de dezvoltare SQL prin alăturarea rezultatului YS.dm_db_task_space_usage DMV cu alte DMV-uri, cum ar fi SYS.dm_exec_requests și sys.dm_exec_sessions cu funcția de gestionare dinamică sys.dm_exec_sql_text, unde va afișa interogările care consumă spațiul tempdb acum, ca în această interogare.

rezultatul va fi similar cu cel de mai jos:

• autor
• Postări recente

Ahmad Yaseen este un inginer Microsoft Big Data cu cunoștințe profunde și experiență în SQL BI, SQL Server de administrare a bazelor de date și domenii de dezvoltare.
este expert Microsoft Certified Solution în managementul și analiza datelor, Microsoft Certified Solution Associate în administrarea și dezvoltarea bazelor de date SQL, Azure Developer Associate și Microsoft Certified Trainer.
de asemenea, el contribuie cu sfaturile sale SQL în multe bloguri.
Vezi toate mesajele de Ahmad Yaseen

ultimele postări de Ahmad Yaseen (vezi toate)

• Azure Data Factory Interviu Întrebări și răspunsuri-11 februarie 2021
• cum se monitorizează Azure Data Factory-15 Ianuarie 2021
• utilizarea controlului sursei în Azure Data Factory – ianuarie 12, 2021