SQL Server tempdb principle: Logging mechanism parsing practice

The author once interviewed the DBA, "Why is tempdb faster than any other database?" So that more than 95% of the candidates have a blank face. As an important feature of SQL Server, tempdb has all along been familiar and unfamiliar to it. Familiarity is what we use all the time, strangers may be very few people pay attention to its operating mechanism. I'll introduce you to the logging mechanism of tempdb in this case.

Test Cases

We create similar objects in the user database (testpage), tempdb, T1, #t1, create a non temp table in tempdb, then execute the corresponding insert script (to produce the log), and record the execution time for comparison to describe the tempdb "quick"

Code

User Database Testpage

Use Testpage
go
CREATE table T1
(
ID int identity (1,1) not NULL,
str1 char (8000)
)
     
declare @t datetime2=sysutcdatetime ()
declare @i int
set @i=1 while
(@i<100000)
begin
Inserts into T1 select @i, ' AA '
select @i=@i+1
end
SELECT [Extime]=datediff (S,@t,sysutcdatetime ())

Tempdb

Use tempdb
go
CREATE table #t1
(
ID int. NOT NULL,
str1 char (8000)
)
     
declare @t Datetime2=sysutcdatetime ()
declare @i int
set @i=1 while
(@i<100000)
begin
inserts into # T1 select @i, ' AA '
select @i=@i+1
end
SELECT [Extime]=datediff (S,@t,sysutcdatetime ())

Non-temporary tables executed in tempdb

Use tempdb
go
CREATE table T1
(
ID int. NOT NULL,
str1 char (8000)
)
     
declare @t datetime2= Sysutcdatetime ()
declare @i int
set @i=1
while (@i<100000)
begin
inserts into T1 select @i, ' AA '
select @i=@i+1
end
SELECT [Extime]=datediff (S,@t,sysutcdatetime ())

As we can see from figure 1-1, it takes a minute to execute a script in a normal table, and tempdb only needs to perform 22s. and the normal table in tempdb is also only 27s is much better than the normal table performance.

Interested friends can also observe the performance of log-related performances during implementation (log Bytes flusged \sec, etc.)

Figure 1-1

From this test we can see that the "tempdb is faster than any other database" mentioned in this article.