Correction and supplement of tempdb management in SQLServer

I have been reading this article a few days ago to correct and supplement "tempdb management in SQLServer": the management of tempdb in SQLServer has found that some of the content is not very accurate. The article said: like other user databases, TempDB uses the Model database as a template to create and query tables. Does not work with other user Databases

I have read this article for some corrections and supplements in SQL Server tempdb management a few days ago: some of the content in SQL Server's tempdb management is not very accurate as mentioned in the article: like other user databases, TempDB uses the Model database as a template to create and query tables. Does not work with other user Databases

Correction and supplement of tempdb management in SQL Server

I read this article a few days ago: tempdb management in SQL Server

Some content is not very accurate.

The article says:

Like other user databases, TempDB uses the Model database as a template to create and query tables.

Unlike other user databases, TempDB is re-created every time SQL Server is started.

I opened SQLSERVER2005 and compared the model database and tempdb database. I found that there are still some differences.

The initial size and auto-increment, cross-database ownership links are enabled and will not be discussed here

Cross-database ownership link:

In the page verification, the model database page verification uses checksum, but tempdb does not have page Verification

Is it good for tempdb not to use page verification ???

Before discussing this question, we should first understand the checksum function.

Extracted from:

Page CHECKSUM:
The database page may be damaged due to external reasons during the period from being written to the disk to being read by SQL Server.
For example, the I/O device is damaged, the driver is damaged, or the driver is not completely written due to unstable power supply. The Checksum mechanism enables SQL Server to detect these damages.
Note that the Checksum mechanism only allows us to determine whether the I/O subsystem causes page corruption, but cannot fix them automatically.
As administrators, we can use this information to identify and replace damaged I/O devices,
You can also use methods such as re-indexing or repairing damaged files to repair page corruption.

The algorithm for calculating checksum is complex, because it involves reading and writing multiple pages and increases the CPU load, which may affect the system throughput.

System throughput and CPU load are negligible because the checksum page verification is enabled by default in your database.

So I think it is necessary to enable checksum page verification.

Page checksum condition: during the time when the database page is written to the disk and read by SQL Server, the data page must be written to the disk,

If the data page is not written to the disk, enabling checksum is useless.

In tempdb, pages in both data pages, index pages, and version storage areas may be written to disks.

Version storage area information:

The version storage area and data page index page are also composed of 8 K pages. These pages exist in the buffer pool and can be written to the disk when TempDB is under memory pressure.

If so, SQLSERVER should enable the checksum page verification of the tempdb database.

Unfortunately, I have not found any explanation on the Internet.

However, through the following experiment, I guess some intentions of the SQLSERVER team

These experiments have been provided in the management of tempdb in the article SQL Server. I just want to make some supplements.

Lab

Lab environment for the following experiments: Windows 7, SQLSERVER2005 personal Developer Edition

Global temporary table

Create # t_tempdblog global temporary table, insert data, modify data, delete data, create clustered indexes, delete clustered indexes, create non-clustered indexes, and delete non-clustered indexes.

Check whether there are related operation records in the transaction log file.

Test script

Insert record

1 use tempdb # t_tempdblog (c1 int, c2 char (1000) () # t_tempdblog), 1000) operation, context, fn_dblog (null, null)