对《SQL Server中tempdb的management》的一些更正和补充

前几天看了这篇文章：SQL Server中tempdb的management

发现里面有些内容不是很准确

文章中说到：

TempDB和其他用户数据库一样以Model数据库为模板创建，并且可以创建和查询表格。

与其他用户数据库不同的是，TempDB在每次SQL Server启动的时候都会被重新创建。

我打开SQLSERVER2005，对比了一下model数据库和tempdb数据库，发现还是有一些不同

初始大小和自动增长、跨数据库所有权链接已启用这里不讨论

跨数据库所有权链接：http://www.cnblogs.com/chenxizhang/archive/2009/04/16/1436887.html

页面验证里面，model数据库页面验证用的是checksum，但是tempdb没有页面验证

tempdb不使用页面验证究竟好还是不好？？？

讨论这个问题之前，这里要先了解一下checksum的功能

以下摘抄自：http://blogs.msdn.com/b/apgcdsd/archive/2012/04/09/sql-2005-checksum.aspx

系统吞吐量、CPU负荷这个本人觉得可以忽略不计，因为用户数据库默认都是开启了checksum页面验证的

所以本人觉得还是有必要开启checksum页面验证

页面checksum的条件：在数据库页面从被写入磁盘到被SQL Server读取的这段时间内，数据页一定要写入到磁盘，

如果数据页面没有写入到磁盘，那么开启checksum也就没有什么用处

在tempdb里，无论是数据页还是索引页还是版本存储区里的页面都有可能写入磁盘

版本存储区的资料：http://blogs.msdn.com/b/apgcdsd/archive/2012/03/30/sql-server-tempdb-version-store.aspx

如果是这样，那么SQLSERVER应该要开启tempdb数据库的checksum页面验证才对啊

很可惜，本人暂时还没有在网上找到相关解释

不过通过下面实验，本人猜测了一些SQLSERVER团队的意图

这些实验在文章SQL Server中tempdb的management里已经给出了，本人只是做一些补充

---

实验

以下实验的实验环境：Windows7，SQLSERVER2005个人开发者版

全局临时表

通过创建##t_tempdblog全局临时表，插入数据，修改数据，删除数据，建立聚集索引，删除聚集索引，建立非聚集索引，删除非聚集索引

查看事务日志文件里有没有相关的操作记录

测试脚本

插入记录

 use tempdb

 go

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

 -- SETUP

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

 drop table ##t_tempdblog

 go

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

 go

 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

 select top 10 operation,context,

       [log record fixed length],

       [log record length],

       [AllocUnitName]

 from fn_dblog(null, null)

 where allocunitname='dbo.##t_tempdblog'

 order by [Log Record Length] DESC

这个表每行记录应该有char（1000）+4=1004字节

我们看一下事务日志记录

插入记录到全局临时表并不会记录事务日志

如果将上面那个脚本在用户数据库上执行，输入的日志记录会是下面的样子

用户数据库里的表名是 t_tempdblog

用户数据库会记录插入记录的事务日志信息

修改记录

 UPDATE  ##t_tempdblog

 SET     c2 = REPLICATE(CAST ('b' AS VARCHAR), 1000)

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

修改记录会记录到事务日志文件

创建聚集索引

 CREATE CLUSTERED INDEX cix_##t_tempdblog ON [##t_tempdblog](c1)

 GO

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

建立聚集索引之后连日志都没有了

drop掉聚集索引之后又能看到事务日志了

 DROP INDEX cix_##t_tempdblog ON ##t_tempdblog

 GO

 --drop 掉聚集索引之后又能看到日志了

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

建立非聚集索引

 CREATE  INDEX ix_##t_tempdblog ON [##t_tempdblog](c1)

 GO

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

事务日志又消失了

建立聚集索引和非聚集索引的时候，全局临时表里是有数据的，大家不要以为我删除了表数据

删除非聚集索引

 DROP INDEX ix_##t_tempdblog ON ##t_tempdblog

 GO

 --drop 掉聚集索引之后又能看到日志了

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

还是没有事务日志记录

删除表数据

 DELETE  ##t_tempdblog

 GO

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.##t_tempdblog'

 ORDER BY [Log Record Length] DESC

---

局部临时表

插入数据

 drop table #t_tempdblog

 go

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

 go

 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

 select top 10 operation,context,

       [log record fixed length],

       [log record length],

       [AllocUnitName]

 from fn_dblog(null, null)

 where allocunitname='dbo.#t_tempdblog'

 order by [Log Record Length] DESC

修改数据

 UPDATE  #t_tempdblog

 SET     c2 = REPLICATE(CAST ('b' AS VARCHAR), 1000)

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.#t_tempdblog'

 ORDER BY [Log Record Length] DESC

创建聚集索引

 CREATE CLUSTERED INDEX cix_#t_tempdblog ON [#t_tempdblog](c1)

 GO

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.#t_tempdblog'

 ORDER BY [Log Record Length] DESC

删除索引，建立非聚集索引也是一样，没有事务日志记录

删除数据

 DELETE  #t_tempdblog

 SELECT TOP 10

         operation ,

         context ,

         [log record fixed length] ,

         [log record length] ,

         [AllocUnitName]

 FROM    fn_dblog(NULL, NULL)

 WHERE   allocunitname = 'dbo.#t_tempdblog'

 ORDER BY [Log Record Length] DESC

---

猜测原因

从上面的实验可以看到，SQLSERVER不是对所有的操作都写入事务日志，局部临时表完全没有日志记录

可以猜测：SQLSERVER团队认为tempdb里的数据不是那么重要，所以不设置页面验证为checksum验证

但是根据这篇文章：http://blogs.msdn.com/b/apgcdsd/archive/2012/03/19/sql-server-tempdb-tempdb.aspx

还有一些功能用到tempdb的，例如版本存储区，这里就一一详细列出来了

本人觉得这些功能都非常重要的，承载的数据也很重要

在SQLSERVER2012里面，tempdb的数据库选项有些不一样，可能从SQLSERVER2008开始就不一样，不过由于本人

没有安装SQLSERVER2008，先不说SQLSERVER2008，先看一下SQLSERVER2012

---

SQLSERVER2012里model数据库和tempdb数据库选项的比较

可以看出SQLSERVER2012已经将tempdb的页面验证改为checksum了

如有不对的地方，欢迎大家拍砖o(∩_∩)o