对《SQL Server中tempdb的management》的一些更正和补充
对《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全局临时表，插入数据，修改数据，删除数据，建立聚集索引，删除聚集索引，建立非聚集索引，删除非聚集索引
查看事务日志文件里有没有相关的操作记录
测试脚本
插入记录
1 use tempdb
2 go
3 ---------------
4 -- SETUP
5 ---------------
6 drop table ##t_tempdblog
7 go
8
9
10 create table ##t_tempdblog (c1 int, c2 char(1000))
11 go
12
13
14 declare @i int
15 select @i = 0
16 while (@i < 10)
17 begin
18 insert into ##t_tempdblog values
19 (@i, replicate (cast ('a' as varchar), 1000))
20 select @i = @i+1
21 end
22
23
24 select top 10 operation,context,
25 [log record fixed length],
26 [log record length],
27 [AllocUnitName]
28 from fn_dblog(null, null)
29 where allocunitname='dbo.##t_tempdblog'
30 order by [Log Record Length] DESC
这个表每行记录应该有char（1000）+4=1004字节
我们看一下事务日志记录
插入记录到全局临时表并不会记录事务日志
如果将上面那个脚本在用户数据库上执行，输入的日志记录会是下面的样子
用户数据库里的表名是 t_tempdblog
用户数据库会记录插入记录的事务日志信息
修改记录
1 UPDATE ##t_tempdblog
2 SET c2 = REPLICATE(CAST ('b' AS VARCHAR), 1000)
3
4 SELECT TOP 10
5 operation ,
6 context ,
7 [log record fixed length] ,
8 [log record length] ,
9 [AllocUnitName]
10 FROM fn_dblog(NULL, NULL)
11 WHERE allocunitname = 'dbo.##t_tempdblog'
12 ORDER BY [Log Record Length] DESC
修改记录会记录到事务日志文件
创建聚集索引
1 CREATE CLUSTERED INDEX cix_##t_tempdblog ON [##t_tempdblog](c1)
2 GO
3 SELECT TOP 10
4 operation ,
5 context ,
6 [log record fixed length] ,
7 [log record length] ,
8 [AllocUnitName]
9 FROM fn_dblog(NULL, NULL)
10 WHERE allocunitname = 'dbo.##t_tempdblog'
11 ORDER BY [Log Record Length] DESC
建立聚集索引之后连日志都没有了
drop掉聚集索引之后又能看到事务日志了
1 DROP INDEX cix_##t_tempdblog ON ##t_tempdblog
2 GO
3 --drop 掉聚集索引之后又能看到日志了
4 SELECT TOP 10
5 operation ,
6 context ,
7 [log record fixed length] ,
8 [log record length] ,
9 [AllocUnitName]
10 FROM fn_dblog(NULL, NULL)
11 WHERE allocunitname = 'dbo.##t_tempdblog'
12 ORDER BY [Log Record Length] DESC
建立非聚集索引
1 CREATE INDEX ix_##t_tempdblog ON [##t_tempdblog](c1)
2 GO
3 SELECT TOP 10
4 operation ,
5 context ,
6 [log record fixed length] ,
7 [log record length] ,
8 [AllocUnitName]
9 FROM fn_dblog(NULL, NULL)
10 WHERE allocunitname = 'dbo.##t_tempdblog'
11 ORDER BY [Log Record Length] DESC
事务日志又消失了
建立聚集索引和非聚集索引的时候，全局临时表里是有数据的，大家不要以为我删除了表数据
删除非聚集索引
1 DROP INDEX ix_##t_tempdblog ON ##t_tempdblog
2 GO
3 --drop 掉聚集索引之后又能看到日志了
4 SELECT TOP 10
5 operation ,
6 context ,
7 [log record fixed length] ,
8 [log record length] ,
9 [AllocUnitName]
10 FROM fn_dblog(NULL, NULL)
11 WHERE allocunitname = 'dbo.##t_tempdblog'
12 ORDER BY [Log Record Length] DESC
还是没有事务日志记录
删除表数据
1 DELETE ##t_tempdblog
2 GO
3
4 SELECT TOP 10
5 operation ,
6 context ,
7 [log record fixed length] ,
8 [log record length] ,
9 [AllocUnitName]
10 FROM fn_dblog(NULL, NULL)
11 WHERE allocunitname = 'dbo.##t_tempdblog'
12 ORDER BY [Log Record Length] DESC
局部临时表
插入数据
1 drop table #t_tempdblog
2 go
3
4
5 create table #t_tempdblog (c1 int, c2 char(1000))
6 go
7
8
9 declare @i int
10 select @i = 0
11 while (@i < 10)
12 begin
13 insert into #t_tempdblog values
14 (@i, replicate (cast ('a' as varchar), 1000))
15 select @i = @i+1
16 end
17
18
19 select top 10 operation,context,
20 [log record fixed length],
21 [log record length],
22 [AllocUnitName]
23 from fn_dblog(null, null)
24 where allocunitname='dbo.#t_tempdblog'
25 order by [Log Record Length] DESC
修改数据
1 UPDATE #t_tempdblog
2 SET c2 = REPLICATE(CAST ('b' AS VARCHAR), 1000)
3
4 SELECT TOP 10
5 operation ,
6 context ,
7 [log record fixed length] ,
8 [log record length] ,
9 [AllocUnitName]
10 FROM fn_dblog(NULL, NULL)
11 WHERE allocunitname = 'dbo.#t_tempdblog'
12 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了