Jeg troede, jeg engang læste et eller andet sted, at det at skrive til tempdb er hurtigere end en faktisk tabel, der ikke er i tempdb. Er dette sandt i en hvilken som helst egenskab? Jeg troede, jeg husker, at det sagde noget specielt om tempdb og lagring af data i hukommelsen?
Svar
at skrive til tempdb er hurtigere end en faktisk tabel, der ikke er i tempdb
Det er sandt. Der er to IO-forbedringer i TempDb.
Skriver til en tabel i en brugerdatabase skal have deres logposter skyllet til disken ved forpligtelse, eller hvis en minimalt logget indsats (som SELECT … INTO) skal databasesiderne skylles til disk Den måde, hvorpå den minimale logning fungerer i en brugerdatabase, er, at databasesiderne skrives direkte til disken. Når SELECT … INTO er færdig, er de nye sider alle skrevet til de fysiske filer.
TempDb skriver behøver ikke at blive skyllet til disk on commit, da TempDb aldrig gendannes efter en fejl. Så de er simpelthen ikke. Dine ændringer genererer logposter, men logbufferen skylles kun til disken, når den er fuld, ikke for hver forpligtelse.
Og siden SQL Server 2014 er de minimalt loggede indsatser i TempDb ikke altid skrevet til disken. Hvis du indlæser en lille, kortvarig temp-tabel, det kan måske slet ikke skrives til disken. Loggen vil have et par poster om sidetildelinger og metadataindgange for tabellen, men det er det.
EG kør følgende batch i tempdb, en fuld gendannelsesdatabase og en simpel gendannelsesdatabase for at se forskellene.
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 og du vil se noget som:
Til enkel gendannelse:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE for fuld gendannelse:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL og til tempdb:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE Nogle gange for tempdb vil du se logbufferen tømmes:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE Kommentarer
- Der er et tilfælde, hvor den første indsats er hurtigere, men det kommer tilbage for at bide dig senere. Denne demo viser en forespørgsel, der bringer data ind i buffercachen, der tager meget længere tid, da den dovne forfatter er optaget af at skrive til disk tempdb-sider, der er beskidte for en temp-tabel, der ikke findes længere youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
- Ja. Der er ' en potentiel fremtidig forbedring for at eliminere skylning af bufferpoolsider, der ikke er tildelt noget objekt. Men at indlæse en stor temp-tabel vil altid køre IO, enten direkte eller indirekte ved at reducere den tilgængelige hukommelse til caching.
Svar
Ud over at skrive til tempdb ofte ikke alle rammer disk / netværk IO, som det er udvidet i David Brownes svar , afhængigt af din IO konfiguration kan du opleve, at selv når dataene er store nok til at skulle spoles til disk, er det stadig hurtigere end at vælge en “normal” tabel:
-
TempDB kan være på forskellige drev, så har sin egen IO-båndbredde. Dette er især vigtigt med spindedrev snarere end SSDer. Læsning fra og skrivning til den samme database (eller en anden database på de samme drev) vil involvere flere hovedbevægelser, som tilføj mere IO-latenstid og potentielt reducer din effektive IO-båndbredde. Kopiering af data mellem databaser på forskellige drev / arrays har ikke den samme ekstra latens.
-
TempDB kan endda være på fa ster medie end dit hovedlager. Måske på lokale drev, hvor hovedlagring er på netværket, eller NVMe SSDer, hvor hovedbutikken er på traditionelle drev.
Begge disse forskelle kan også ses inden i det samme database, hvis du bruger flere filgrupper til at sprede dele af dataene mellem forskellige drev / arrays.
Det modsatte kan også være tilfældet, hvis du har flere databaser, der er aktivt i brug. Da TempDB er en delt ressource, er den og de drev / netværk, der er vært for den, muligvis belastet mere end datafilerne for enhver enkelt DB.