SQL Server tempdb principle-Log Mechanism parsing practice

I used to interview a DBA, "Why is tempdb faster than other databases?" So that more than 95% of the candidates have a blank face. tempdb, as an important feature of SQL Server, has long been familiar and unfamiliar to it. Familiarity is that we are always in use, unfamiliar may be very few people pay attention to its operating mechanism. This time I will introduce you to the log mechanism of tempdb.

Test Cases

We create a similar object T1 in the user database (testpage), tempdb, #t1, create a non-temporal table in tempdb, and then execute the appropriate insert script (to generate the log) and record the execution time to compare the tempdb "fast"

Code

User Database Testpage

 UseTestpageGoCreate TableT1 (IDint Identity(1,1) not NULL, str1Char(8000))Declare @tDateTime2=sysutcdatetime ()Declare @i intSet @i=1 while(@i<100000)beginInsert  intoT1Select @i,'AA'Select @i=@i+1EndSelect [Extime]=DATEDIFFS@t, Sysutcdatetime ())

Tempdb

 UsetempdbGoCreate Table#t1 (IDint  not NULL, str1Char(8000))Declare @tDateTime2=sysutcdatetime ()Declare @i intSet @i=1 while(@i<100000)beginInsert  into#t1Select @i,'AA'Select @i=@i+1EndSelect [Extime]=DATEDIFFS@t, Sysutcdatetime ())

Non-temporal tables are executed in tempdb

 UsetempdbGoCreate TableT1 (IDint  not NULL, str1Char(8000))Declare @tDateTime2=sysutcdatetime ()Declare @i intSet @i=1 while(@i<100000)beginInsert  intoT1Select @i,'AA'Select @i=@i+1EndSelect [Extime]=DATEDIFFS@t, Sysutcdatetime ())

As we can see in Figure 1-1, the execution of a one-minute script in the normal table, tempdb only needs to execute 22s. The normal table is only 27s in tempdb, which is much better than normal table execution.

Interested friends can also observe the performance of the logs related to the operation of the technical device (log Bytes flusged \sec, etc.)

Figure 1-1

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

The fact is not that Tempdb has any magic, but that the log mechanism of tempdb differs greatly from other databases.

Log mechanism for tempdb

Tempdb Simple Recovery mode (no restore operation after reboot)

Tempdb uses minimized logs

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

TEMPDB does not need to drop the disk before swiping into the data page (transaction commit log does not need to drop disk)

The reason for "fast"

You can see that the system checkpoint itself bypasses the tempdb,tempdb execution without having to log the disk first. and minimizes logging (I'll make a statement about this one). These all greatly alleviate the disk IO bottleneck, making tempdb much faster than other db.

Note: Although the system checkpoint checkpoint bypasses tempdb, it will still work for checkpoint in tempdb, and you should only use it in a test environment, with caution in a formal environment!

In the example above we can see that regardless of the type of the table, there is a significant increase in the speed in tempdb, but the execution time of the normal table is slightly longer than the temporary table, because the logging information of the normal table is more than the temporary table.

About the tempdb minimized log

The update information log for insert,update operations in the heap table (heap) does not need to be logged.

We look at it through a simple example.

 Use [tempdb]GOCreate Table#nclst (IDint Identity(1,1)Primary Key nonclustered,---heaptableStr1Char(8000));Create Table#clst (IDint Identity(1,1)Primary Key,------ClusteredStr1Char(8000));Checkpoint-----production environment with caution! DBCCShrinkfile (N'Templog',0, Truncateonly)GOInsert  into#nclst (STR1)Select 'AA'Select [Current LSN], Operation,context,[Log Record Length]  fromFn_dblog (NULL,NULL)whereAllocunitid is  not NULLCheckpoint-----production environment with caution! DBCCShrinkfile (N'Templog',0, Truncateonly)GOInsert  into#clst (STR1)Select 'AA'Select [Current LSN], Operation,context,[Log Record Length]  fromFn_dblog (NULL,NULL)whereAllocunitid is  not NULL

As you can see in Figure 1-2, the details of the #ncls.str1 in the insert are not recorded in the heap table, and the corresponding information is recorded in the aggregation table.

Figure 1-2

Why tempdb needs logs

Since tempdb is re-established every time a reboot is required, we do not need to redo the log, but we may need to rollback during the run, which is why the tempdb log exists.

TEMPDB does not support Redo (Redo) but needs to support rollback (rollback).

about tempdb rollback .

Tempdb will cause the entire instance to go down if there is not enough space in the log file to apply the rollback!

tempdb Best Practices-Logs

A do not checkpoint in tempdb (heavy consumption causes system performance to decline)

b Do not open a long transaction in tempdb (unable to truncate the log, causing the log to be too large, such as failure to roll back when the rollback is down)

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

The use of heap tables in tempdb is a good speed. (Subject to availability)