Analysis and examples of the log mechanism of tempdb in SQL Server, sqlservertempdb

Analysis and examples of the log mechanism of tempdb in SQL Server, sqlservertempdb

Test Cases

We create similar objects t1 and # t1 in the user database (testpage) and tempdb respectively, and create non-temporary tables in tempdb, then execute the corresponding insert script (used to generate logs) and record the execution time to compare the description of tempdb "fast"

Code

User Database testpage

use testpagegocreate table t1(id int identity(1,1) not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

Tempdb

use tempdbgocreate table #t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into #t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

Non-temporary table execution in tempdb

use tempdbgocreate table t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

As shown in Figure 1-1, the execution of one-minute scripts in a common table only requires 22s for tempdb, while that of a common table only requires 27s for tempdb, which is much better than that of a common table.

If you are interested, you can also observe the running status of Log-related performance technicians during execution, for example, Log Bytes Flusged \ sec)

Figure 1-1

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

Actually, it is not the magic of tempdb, but the Log Mechanism of tempdb is quite different from that of other databases.

Tempdb Log Mechanism

Tempdb Simple recovery mode (no restoration operation is required after restart)

Tempdb Uses minimal logs

Tempdb is not affected by system CheckPoint (system checkpoint does not involve tempdb, but execution in tempdb is flushed to the disk)

Before flushing the data page to the disk, Tempdb does not need to flushed the log into the disk (the transaction commit log does not need to be flushed into the disk)

Reasons for "quick"

As you can see, the system checkpoint itself bypasses tempdb. During execution of tempdb, no logs need to be written to the disk first. this minimizes log records (I will state this feature later), which greatly relieves the disk IO bottleneck and makes tempdb much faster than other databases.

Note: although the system checkpoint will bypass tempdb, executing the checkpoint in tempdb still takes effect. You should only use the checkpoint in the test environment and use it with caution in the formal environment!

In the above example, we can see that no matter what the table type is, the speed in tempdb will be greatly improved, but the execution time of common tables is slightly longer than that of temporary tables, this is because the log record information of a common table is slightly higher than that of a temporary table.

Tempdb minimization log

In the heap table, the update log of the update operation does not need to be recorded.

Let's look at it through a simple example.

USE [tempdb] GOcreate table # nclst (id int identity (8000) primary key nonclustered, --- heaptablestr1 char (); create table # clst (id int identity) primary key, ------ clusteredstr1 char (8000); checkpoint ----- use with caution in the production environment! Dbcc shrinkfile (N 'templog', 0, TRUNCATEONLY) GOinsert into # nclst (str1) select 'A' select [Current LSN], Operation, CONTEXT, [Log Record Length] from fn_dblog (null, null) where AllocUnitId is not nullcheckpoint ----- use it with caution in the production environment! Dbcc shrinkfile (N 'templog', 0, TRUNCATEONLY) GOinsert into # clst (str1) select 'A' select [Current LSN], Operation, CONTEXT, [Log Record Length] from fn_dblog (null, null) where AllocUnitId is not null

As shown in figure 1-2, the detailed information of # ncls. str1 in Insert is not recorded in the heap table, and the corresponding information is recorded in the clustered table.

Figure 1-2

Why does Tempdb need logs?

Since tempdb is re-created every time it is restarted, we do not need to redo the logs, but rollback may be required during the running process, which is also the reason for the existence of tempdb logs.

Tempdb does not support Redo, but must support rollback ).

About tempdb rollback.

In Tempdb, if there is not enough space in the log file for application rollback, the entire instance will be down!

Tempdb best practices-Log

A. Do not use checkpoints in tempdb (the consumption is huge, leading to system performance degradation)

B. do not enable too long transactions in tempdb (logs cannot be truncated, resulting in excessive logs. If the rollback fails, the server goes down)

C generally, the process of intermediate table matching is created in tempdb (fast creation speed depends on the actual situation .)

D The speed of using heap tables in tempdb is good (depending on the actual situation)

How to clear SQL Server tempdb data

Method for shrinking Tempdb
This method requires you to restart SQL Server.

1. Stop SQL Server. Open a command prompt and enter the following command to start SQL Server:
Sqlservr-c-f
The-c and-f parameters enable SQL Server to start in the minimum configuration mode. Set the tempdb size of the data file to 1 MB, and the tempdb size of the log file to 0.5 MB.
Note: If you use SQL Server to name an instance, you must switch to the appropriate folder (Program Files/Microsoft SQL Server/MSSQL $ instance name/Binn ), enable-s (-s % instance_name % ).
2. Connect to SQL Server with the query analyzer and run the following Transact-SQL command: ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
-- Desired target size for the data file

Alter database tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
-- Desired target size for the log file

3. Press Ctrl-C in the Command Prompt window to stop SQL Server, restart SQL Server as a service, and then verify the size of the Tempdb. mdf and Templog. ldf files.
The limitation of this method is that it can only operate on the default tempdb logical files tempdev and templog. If you add other files to tempdb, You can contract them after restarting SQL Server as a service. All tempdb files will be re-created during startup; therefore, they are empty and can be deleted. To delete other files in tempdb, use the alter database command with the remove file option.

How to clear tempdb logs in SQL server 2005

1. service enhancements; 2. database Engine enhancements; 3: database access interface enhancements; 4: Analysis Services enhancements; 5: Integration Services enhancements; 6: full-text search enhancements; 7: enhanced Notification Services; 8: enhanced replication; 9: enhanced Reporting Services; 10: enhanced tools and tools used; great