我以为我曾经读过某个地方，写tempdb的速度比不在tempdb中的实际表要快。这是真的吗？
我想我还记得它说过有关tempdb和将数据存储在内存中的一些特别之处吗？

#1 楼


写入tempdb的速度比不使用tempdb的实际表要快。


这是事实。 TempDb中有两个IO增强功能。

写入用户数据库中的表时，必须在提交时将其日志记录刷新到磁盘，或者如果记录最少的插入（如SELECT ... INTO），则必须将数据库页面刷新提交磁盘。用户数据库中最少日志记录的工作方式是将数据库页面直接写入磁盘。到SELECT ... INTO完成时，新页面已全部写入物理文件。

TempDb写入不必在提交时刷新到磁盘，因为TempDb永远不会恢复。失败。所以他们根本不是。您所做的更改将生成日志记录，但是日志缓冲区仅在已满时才刷新到磁盘，而不是每次提交都刷新到磁盘。

从SQL Server 2014开始，TempDb中记录最少的插入内容也不总是写入磁盘。如果您加载一个短暂的临时表，则可能根本不会将其写入磁盘。该日志将包含一些有关表的页面分配和元数据条目的记录，仅此而已。

EG在tempdb中运行以下批处理，完整的恢复数据库和简单的恢复数据库以查看差异。

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


，您会看到类似的内容：

为了简单恢复：

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


进行完全恢复：

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


tempdb：

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


有时tempdb，您将看到刷新的日志缓冲区：

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


评论

在某些情况下，初始插入会更快，但是稍后会再次咬住您。此演示演示了一个查询，该查询将数据带入缓冲区高速缓存，这需要花费更长的时间，因为懒惰的编写器正忙于写入已不再存在的temp表的tempdb磁盘youtube.com/watch?v=X60ipwYv1Ms&feature=youtu.be

–马丁·史密斯
20 Jan 24 '10：30


是。将来有潜在的增强功能，可以消除未分配给任何对象的刷新缓冲池页面。但是加载大型临时表将始终直接或通过减少可用于缓存的内存来间接驱动IO。

– David Browne-微软
20年1月26日在17:21

#2 楼

正如David Browne的回答所扩展的那样，写入tempdb时通常不会每次都击中磁盘/网络IO，根据您的IO配置，您可能会发现，即使数据足够大以致必须将其后台处理到磁盘，它仍会更快而不是选择进入“正常”表：

TempDB可能位于不同的驱动器上，因此具有自己的IO带宽。这对于旋转驱动器而不是SSD尤其重要。读取和写入相同的数据库（或相同驱动器上的另一个数据库）将涉及更多的磁头移动，这将增加更多的IO延迟，并有可能限制您的有效IO带宽。在不同驱动器/阵列上的数据库之间复制数据不会有相同的额外延迟。
TempDB甚至可以在比主存储更快的介质上使用。也许在主存储位于网络上的本地驱动器上，或主存储在传统驱动器上的NVMe SSD上。将数据的不同部分分散在不同的驱动器/阵列之间。

如果您有多个正在使用的数据库，则情况相反。由于TempDB是共享资源，因此它以及承载它的驱动器/网络的负载可能比任何单个DB的数据文件都要大。