Tempdb--TempDB Basic

1. TempDB只能运行在Simple Recovery Model下

2. 由于TempDB不需要Recovery,因此在TempDB中发生的操作不需要REDO,因此在日志记录上有别于其他数据库。

3. 不是所有在Tempdb中的object都被记录日志。

4. Improved cahcing for tempory objects, SQL Server chaches 1 IAM Page and 1 data page with the associated query plan.First, if the same plan is exected multiple times, the tmeporary table needs to be created once there by reducing ddl contention and reduceing allocation contention.(如果存储过程中包含创建临时表,那么临时表只会在存储过程编译的时候被创建),Temp objects are only cached when none of the following conditions is violated:

1. Data definition languaged(DDL) statements that affects the table are not run after the temp table has been created, such as create index or create statistics statements.

2. named constraints are not created.

3. Temp objects is not created by using dynamic SQL, such as:sp_executesql

系统对一些临时对象进行优化以减少重复创建和删除临时对象造成的Allocation Bottleneck

 

 

 

Internal object: created internally by sql server to execute user command, like the temprory result set(worktable) which created by hash join.

The operation on internal objects are not logged because internal objects are created/destoryed in the scope of a single statement.

内部对象有系统创建和清理,对内部对象的操作不记录入日志。


 

原文地址:https://www.cnblogs.com/TeyGao/p/3519608.html