Este, în general, mai rapid să selectați într-un tabel temporar decât să selectați într-un tabel real?

scrierea în tempdb este mai rapidă decât o tabelă reală care nu este în tempdb

Este adevărat. Există două îmbunătățiri IO în TempDb.

Scrierile într-un tabel dintr-o bază de date de utilizator trebuie să aibă înregistrările jurnalului transferate pe disc la comitere, sau dacă o inserție minim înregistrată (cum ar fi SELECT … INTO), trebuie ca paginile bazei de date să fie transferate pe disc la comitere. Modul în care funcționează înregistrarea minimă într-o bază de date de utilizator este că paginile bazei de date sunt scrise direct pe disc. Până la finalizarea SELECT … INTO, noile pagini au fost scrise toate în fișierele fizice.

TempDb scrie că nu trebuie spălat pe disc la comitere, deoarece TempDb nu este recuperat niciodată după un eșec. Deci, pur și simplu nu sunt. Modificările dvs. generează înregistrări de jurnal, dar tamponul de jurnal este transferat pe disc numai când este plin, nu pentru fiecare comitere.

Și de la SQL Server 2014 , inserțiile minim înregistrate în TempDb nu sunt întotdeauna scrise pe disc. Dacă încărcați un mic, tabelul temporar de scurtă durată nu poate fi scris niciodată pe disc. Jurnalul va conține câteva înregistrări despre alocările de pagini și intrările de metadate pentru tabel, dar asta este.

EG rulează următoarele lot în tempdb, o bază de date completă de recuperare și o bază de date simplă de recuperare pentru a vedea diferențele.

drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs 

și veți vedea ceva de genul:

Pentru recuperare simplă:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE 

pentru recuperare completă:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL 

și pentru tempdb:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE 

Uneori pentru tempdb veți vedea bufferul jurnalului spălat:

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE 

Comentarii

• Există un caz în care inserția inițială este mai rapidă, dar revine pentru a vă mușca mai târziu. Această demonstrație arată o interogare care aduce date în memoria tampon care durează mult mai mult, deoarece scriitorul leneș este ocupat să scrie pe disc pagini tempdb murdare pentru o masă temporară care nu mai există youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Da. Există ' o potențială îmbunătățire viitoare pentru a elimina spălarea paginilor Buffer Pool care nu sunt alocate niciunui obiect. Dar încărcarea unui tabel mare de temperatură va conduce întotdeauna la IO, fie direct, fie indirect, reducând memoria disponibilă pentru cache.

La fel ca și scrie pe tempdb, adesea nu toate IO-urile de disc / rețea care se lovesc, așa cum este extins în răspunsul David Browne , în funcție de IO configurația este posibil să descoperiți că, chiar și atunci când datele sunt suficient de mari pentru a putea fi spulate pe disc, acestea sunt încă mai rapide decât selectarea într-un tabel „normal”:

• TempDB poate fi diferit unități, deci au propria lățime de bandă IO. Acest lucru este semnificativ mai ales în cazul discurilor rotative, mai degrabă decât SSD-urilor. Citirea din și scrierea în aceeași bază de date (sau într-o altă bază de date pe aceleași unități) va implica mai multe mișcări ale capului adăugați mai multă latență IO și potențial reduceți lățimea de bandă IO eficientă. Copierea datelor între baze de date pe diferite unități / tablouri nu va avea aceeași latență suplimentară.

• TempDB poate fi chiar fa suport steril decât stocarea principală. Poate pe unitățile locale unde stocarea principală este în rețea sau SSD-urile NVMe unde magazinul principal este pe unitățile tradiționale.

Ambele diferențe pot fi, de asemenea, văzute în interiorul aceleiași baza de date dacă utilizați mai multe grupuri de fișiere pentru a răspândi părți ale datelor între diferite unități / tablouri.

Opusul poate fi valabil și dacă aveți mai multe baze de date care sunt utilizate în mod activ. Deoarece TempDB este o resursă partajată, acesta și unitățile / rețeaua care îl găzduiesc ar putea fi sub o încărcare mai mare decât fișierele de date pentru orice DB individual.