Tempdb database details

I,TempdbIntroduction

TempdbYesSqlserverThe system database is alwaysSqlserverIs used to store temporary objects. Easy to understandTempdbYesSqlserverThe sketchboard. ApplicationProgramAnd DatabasesTempdbAs a temporary data storage area. All users of an instance share oneTempdb. Obviously, this design is not very good. When the databases of multiple applications are deployed on the same server, the applications are shared.TempdbIf the developer does not pay attentionTempdbThese databases affect each other and affect applications.

II, TempdbFeatures

1,TempdbAny data in does not persist after the system is restarted. Because every timeSqlserverWill be re-created at startupTempdb. This feature is explained.TempdbRecovery is not required.

2,TempdbAlways set to"Simple"Recovery mode. An error will be reported when you try to modify it. That is to say, the transaction log records of committed transactions are marked as reused after each checkpoint.

3,TempdbOnly oneFilegroupYou cannot add more file groups.

4,TempdbIt is used to store three types of objects: user objects, internal objects, and version storage areas.

3., TempdbStorage objects

1,User objects: local temporary tables, global temporary tables, and table variables are stored inTempdb. You don't need to talk about local temporary tables and global temporary tables. Whether table variables existTempdbMany people have misunderstandings, especially on the Internet.ArticleIt's just a mistake. Table variables are created in memory at a faster speed than temporary tables. We can useSqlserver2005Dynamic management view introducedSYS. dm_db_session_space_usageTo view the existence of table variables.

In yourSqlserver2005Or2008Run the following script:

Select * From SYS. dm_db_session_space_usage

Where database_id = db_id ('tempdb') and session_id> 50

 

Declare @ t table (id int)

Insert into @ T values (2)

 

Select * From SYS. dm_db_session_space_usage

Where database_id = db_id ('tempdb') and session_id> 50

The result is as follows:

 

 

We can see that the table variable causesTempdbA page is allocated, So table variables are not created in memory.

2,Internal object

Internal objects are mainlySqlserverThe user temporarily stores data objects during query processing. Such as sorting,Spool, Hash join,Common table expressions query, create or regenerate indexes, Table value functionsAnd cursor

You can useSYS. dm_db_session_space_usageIn the internal_object_alloc_page_count column, you can view the number of pages allocated to each session for internal objects.

3,Version storage Zone

Sqlserver2008Many functions require maintenance of multiple versions of rows. The version storage area is used to store different versions of indexes and data rows. The following functions use a version storage zone: triggers, Snapshot isolation, and read/submit Snapshot isolation,

Online Index operations and multi-activity result sets.

You can useSYS. dm_tran_version_storeView All content in the version storage area. This dynamic management view consumes a lot of resources.

Version storage Overhead:

The overhead of row version control is 14 fields per line, including a transaction serial number and a row identifier. :

XsnUsed to concatenate multiple versions of the same row6Bytes,RIDUsed for locatingTempdbRow version in8Bytes.

This14Bytes overhead does not reduce the maximum number of bytes per line8060Byte limit. If the data page is full, create an additional14Bytes may cause page split, which may affect disk space requirements.

Store only

Row version information is written to two append storage zones. Index reconstruction has its own version storage zone, and all other operations have a public storage zone. To improve scalabilityCPUThe scheduler has its own page in the version storage area to store rows.

Some people may think that it is impossible to always append an object. Do not delete it. Of course,SqlserverThere is a dedicated thread in the background every minute fromTempdbTo delete the old row version.

To be continued...