SQL Server中tempdb的management

Article
03/11/2012

Ø 资料来自：

https://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/

https://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx

我们推出了一系列的关于TempDB基础，监控和问题调试的博文，也建议大家阅读有开发和测试部门给出的白皮书https://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Ø TempDB基础

TempDB和其他用户数据库一样以Model数据库为模板创建，并且可以创建和查询表格。与其他用户数据库不同的是，TempDB在每次SQL Server启动的时候都会被重新创建。这就意味着：

l TempDB不需要恢复。因此它不必使用全备份或者大容量日志记录恢复模型。事实上，TempDB是配置成简单恢复模型并且不能改变的。

l 虽然TempDB不需要被恢复，但不是说TempDB上的操作没有日志记录。SQL Server中每个操作都是在显式或隐式（每个语句都是一个独立的事务）的事务中完成，这个事务也包含了在TempDB对象上的操作。如果更新全局临时表中的行然后回滚事务，是无法撤销此更改的，除非我们记录了更新之前的值。不过，也没有必要记录更新后的值，因为只有数据库恢复过程中需要重做事务时这个值才有用。像我之前提到的一样，TempDB是不需要恢复的，于是没有理由记录更新后的值。再举一个例子让我们看看插入操作。在一个全局临时表中的插入一行，实际值不会被记录。因为SQL Server不需要这个行值来完成撤消操作，仅仅需要适当地设置页面中的偏移量，或者这个插入造成页面被分配或者取消分配。总结起来，我们只需要记录可能被撤销的信息而不需要记录可能被重做的信息。这一点关于日志记录文件的变化是由SQLServer 2005开始的。更复杂的情况是，并不是TempDB中所有对象都被日志文件记录。后文会详细叙述。现在我们来看一个例子说明这一点

use tempdb

---------------

-- SETUP

---------------

drop table ##t_tempdblog

-- create a global temp table. Notice, each row is

-- greater than > 1000. I did this so that we can easily

-- identify if the row is indeed logged.

create table ##t_tempdblog (c1 int, c2 char(1000))

-- load 10 rows into this table

declare @i int

select @i = 0

while (@i < 10)

begin

insert into ##t_tempdblog values

(@i, replicate (cast ('a' as varchar), 1000))

select @i = @i+1

end

--Now run the following command to see the log records

select top 10 operation,context,

[log record fixed length],

[log record length]

from fn_dblog(null, null)

where allocunitname='dbo.##t_tempdblog'

order by [Log Record Length] Desc

这是上述查询的输出。您将注意到的所有日志记录都 < 100 字节，显然插入的行没有被记录。

operation context log record fixed length log record length

-----------------------------------------------------------------------

LOP_MODIFY_ROW LCX_IAM 62 88

LOP_FORMAT_PAGE LCX_HEAP 80 84

LOP_FORMAT_PAGE LCX_IAM 80 84

LOP_FORMAT_PAGE LCX_HEAP 80 84

LOP_MODIFY_ROW LCX_PFS 62 80

LOP_INSERT_ROWS LCX_HEAP 62 72

于是，这就证明了TempDB的日志和其他用户数据库不同。将上面同样的例子操作应用于用户数据库，日志记录会成为这个样子：

operation context log record fixed length log record length

----------------------------------------------------------------------

LOP_INSERT_ROWS LCX_HEAP 62 1104

LOP_INSERT_ROWS LCX_HEAP 62 1101

LOP_INSERT_ROWS LCX_HEAP 62 1104

这就证明了，撤销确实会被日志记录。实验时做了以下操作:

update ##t_tempdblog set c2 =

replicate (cast ('b' as varchar), 1000)

之后，做这样的查询：

select top 10 operation,context,

[log record fixed length], [log record length]

from fn_dblog(null, null)

where allocunitname='dbo.##t_tempdblog'

order by [Log Record Length] Desc

以下是输出内容，你会发现日志的长度超过1000字节，事实上列C2更新之前的值被记录了。

operation context log record fixed length log record length

-----------------------------------------------------------------------

LOP_MODIFY_ROW LCX_HEAP 62 1072

更多内容将在下一篇博文中讨论。

By 微软亚太区全球技术支持中心数据库技术支持组

对话微软原厂技术专家，手把手教会你 SQL Server性能调优: https://support.microsoft.com/gp/anxin_training2/zh-cn

给SQL Server来个全套体检，有病治病，无病强身：https://support.microsoft.com/gp/anxin_guide6/zh-cn

SQL Server技术支持大全，根治纠结您N久的疑难杂症：https://support.microsoft.com/gp/anxin_sql/zh-cn

SQL Server中tempdb的management

Additional resources