The principle of the log mechanism of tempdb in SQL Server and example sharing _mssql

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.

The reality is not that there is any magic in tempdb, but that the logging mechanism of tempdb differs considerably from that of other databases.

The logging mechanism for tempdb

Tempdb Simple Recovery Model (no restore after reboot)

Tempdb uses minimized logs

Tempdb is not affected by system checkpoint (System checkpoint does not involve tempdb, but execution in tempdb is dropped)

TEMPDB does not need to be dropped before the data page is brushed to disk (transaction submission log does not need to be dropped)

The reason for "fast"

You can see that the system checkpoint itself bypasses tempdb,tempdb execution without having to drop the log first. and minimize logging (I'll state this later) these all greatly ease the disk IO bottleneck, making tempdb much faster than other db.

Note: Although the system checkpoint checkpoint will bypass tempdb, the human execution checkpoint in tempdb will still work, we should only test the environment to use, the formal environment for caution!

In the above example we can see that no matter what the type of table is, the speed in tempdb is much higher, but the normal table is slightly longer than the temporary table, because the logging information for the normal table is more than the temporary table.

About the tempdb minimize log

The update information log insert,update operations in the heap table (heap) is not required to be logged.

We look at it through simple examples.

Use the [tempdb] go

CREATE table #nclst
(
ID int identity (1,1) primary key nonclustered,---heaptable
str1 char (8000)
);
CREATE TABLE #clst
(
ID int identity (1,1) primary key,------clustered
str1 char (8000)
);

Checkpoint-----production environment with caution!
DBCC shrinkfile (N ' Templog ', 0, truncateonly)
Go
Insert to #nclst (str1) select ' AA '
SELECT [current Lsn],operation,context,[log record Length] 
Fn_dblog (null,null) where Allocunitid is not NULL
checkpoint-----production environment with caution!
DBCC shrinkfile (N ' Templog ', 0, truncateonly)
Go
Insert to #clst (str1) select ' AA '
SELECT [current Lsn],operation,context,[log record Length] 
Fn_dblog (null,null) where Allocunitid is not null

You can see from figure 1-2 that the details of the #ncls.str1 in the heap table are not recorded in the insert, and the information is recorded in the clustered table

Figure 1-2

Why does tempdb need a log

Since tempdb will be reset every time it restarts, we do not need to redo the log, but we may need to roll back during the run, which is why the tempdb log exists.

TEMPDB does not support Redo (Redo) but requires support for rollback (rollback).

About the tempdb rollback.

If there is not enough space in the log file in tempdb to apply a rollback, the entire instance will be down!

tempdb Best Practices-Log

A do not checkpoint in tempdb (a huge drain on system performance)

b Do not open too long transactions in tempdb (log cannot be truncated, causing log to be too large, such as when rolling back is down)

C generally requires the process of intermediate table matching to be created in tempdb (created quickly, depending on the situation).

The use of heap tables in D tempdb is a good speed. (subject to specific circumstances)