Ik dacht dat ik ooit ergens had gelezen dat schrijven naar tempdb sneller is dan een echte tabel die niet in tempdb staat. Is dit waar in welke hoedanigheid dan ook? Ik dacht dat ik me herinner dat het iets speciaals zei over tempdb en het opslaan van de gegevens in het geheugen?
Antwoord
schrijven naar tempdb is sneller dan een werkelijke tabel die niet in tempdb staat
Het is waar. Er zijn twee IO-verbeteringen in TempDb.
Bij schrijft naar een tabel in een gebruikersdatabase moeten de logboekrecords bij vastleggen naar schijf worden gespoeld, of als een minimaal gelogde insert (zoals SELECT … INTO), moeten de databasepaginas naar schijf worden gespoeld bij vastleggen. De manier waarop de minimale logboekregistratie in een gebruikersdatabase werkt, is dat de databasepaginas rechtstreeks naar schijf worden geschreven. Tegen de tijd dat SELECT … INTO is voltooid, zijn de nieuwe paginas allemaal naar de fysieke bestanden geschreven.
TempDb-schrijfbewerkingen hoeven niet naar de schijf te worden doorgespoeld bij vastlegging, aangezien TempDb nooit wordt hersteld na een fout. Dus ze zijn gewoon niet “t. Uw wijzigingen genereren logboekrecords, maar de logbuffer wordt alleen naar de schijf gespoeld als deze vol is, niet voor elke commit.
En sinds SQL Server 2014 worden de minimaal gelogde inserts in TempDb ook niet altijd naar de schijf geschreven. Als je een kleine, kortstondige tijdelijke tabel wordt het misschien nooit naar schijf geschreven. Het logboek zal een paar records bevatten over de paginatoewijzingen en metadata-items voor de tabel, maar dat is het.
EG voer het volgende uit batch in tempdb, een volledige hersteldatabase en een eenvoudige hersteldatabase om de verschillen te zien.
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 en je “ziet zoiets als:
Voor eenvoudig herstel:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE voor volledig herstel:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL en voor tempdb:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE Soms zie je voor tempdb “dat de logbuffer leeg is:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE Reacties
- Er is een geval waarin de eerste invoeging sneller is, maar het komt later terug om je te bijten. Deze demo toont een query die gegevens in de buffercache brengt die veel langer duurt omdat de luie schrijver bezig is met schrijven naar tempdb-paginas op de schijf die vuil zijn voor een tijdelijke tabel die niet meer bestaat youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
- Ja. Er is ' een mogelijke toekomstige verbetering om het wissen van Buffer Pool-paginas die niet aan een object zijn toegewezen, te elimineren. Maar het laden van een grote tijdelijke tabel zal altijd IO stimuleren, hetzij direct, hetzij indirect door het geheugen dat beschikbaar is voor caching te verminderen.
Answer
Schrijft niet alleen naar tempdb, maar ook niet naar elke harde schijf / netwerk-IO, zoals uitgebreid in David Browne” s antwoord , afhankelijk van je IO configuratie kan het zijn dat zelfs wanneer de gegevens groot genoeg zijn om naar schijf te worden gespoold, het nog steeds sneller is dan selecteren in een “normale” tabel:
-
TempDB kan op een andere schijven, dus hebben hun eigen IO-bandbreedte. Dit is vooral significant bij draaiende schijven in plaats van SSDs. Het lezen van en schrijven naar dezelfde database (of een andere database op dezelfde schijven) vereist meer hoofdbewegingen, wat voeg meer IO-latentie toe en beperk mogelijk uw effectieve IO-bandbreedte. Het kopiëren van gegevens tussen databases op verschillende schijven / arrays heeft niet dezelfde extra latentie.
-
TempDB kan zelfs op fa ster media dan uw hoofdopslag. Misschien op lokale schijven waar de hoofdopslag zich op het netwerk bevindt, of NVMe SSDs waar de hoofdopslag zich op traditionele schijven bevindt.
Beide verschillen kunnen ook binnen dezelfde database als u meerdere bestandsgroepen gebruikt om delen van de gegevens over verschillende stations / arrays te verdelen.
Het tegenovergestelde kan ook waar zijn als u meerdere databases actief in gebruik heeft. Aangezien TempDB een gedeelde bron is, kan het, en de schijven / het netwerk waarop het wordt gehost, zwaarder worden belast dan de gegevensbestanden voor een individuele DB.