對《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

頁面 CHECKSUM:

在資料庫頁面從被寫入磁盤到被SQL Server讀取的這段時間内，有可能由于外界原因發生損壞。

比如I/O裝置的損壞，驅動的損壞或者由于電源不穩沒有寫完整。Checksum機制使SQL Server可以檢測到這些損壞。

需要注意的是，Checksum機制隻能使我們确定是不是I/O子系統引起頁面損壞，但是不能自動修複它們。

作為管理者，我們可以利用這些資訊來辨識出并更換損壞的I/O裝置，

也可以利用重建索引或者修複損壞檔案等方法修複已發生的頁面損壞。

計算checksum的算法是複雜的，因為會涉及到多個頁面的讀寫，增加CPU的負荷，可能會影響系統的吞吐量

系統吞吐量、CPU負荷這個本人覺得可以忽略不計，因為使用者資料庫預設都是開啟了checksum頁面驗證的

是以本人覺得還是有必要開啟checksum頁面驗證

頁面checksum的條件：在資料庫頁面從被寫入磁盤到被SQL Server讀取的這段時間内，資料頁一定要寫入到磁盤，

如果資料頁面沒有寫入到磁盤，那麼開啟checksum也就沒有什麼用處

在tempdb裡，無論是資料頁還是索引頁還是版本存儲區裡的頁面都有可能寫入磁盤

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

版本存儲區也和資料頁面索引頁面一樣由8k大小的頁組成。這些頁存在緩沖池中，可以在TempDB面臨記憶體壓力時被寫入磁盤

如果是這樣，那麼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位元組

int占用4個位元組

char [ ( n ) ]

固定長度，非 Unicode 字元資料，長度為 n 個位元組。n 的取值範圍為 1 至 8,000，存儲大小是 n 個位元組。char 的 SQL 2003 同義詞為 character。

我們看一下事務日志記錄

插入記錄到全局臨時表并不會記錄事務日志

如果将上面那個腳本在使用者資料庫上執行，輸入的日志記錄會是下面的樣子

使用者資料庫裡的表名是 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      

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      

View Code

删除索引，建立非聚集索引也是一樣，沒有事務日志記錄

删除資料

1 DELETE  #t_tempdblog
 2 
 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

觸發器：觸發器對被删除和被插入的行進行操作，這是原表的 DML 操作的一部分。SQL Server2005之前，觸發器通過周遊撤消/重做日志邏輯構造這些行。

因為 SQL Server 需要周遊的舊的記錄，這會導緻磁盤頭來回移動，進而損害的日志所在磁盤的 IO 吞吐量。

通常事務復原并不常見，日志所在磁盤一般都是有順序地寫入。如果應用程式大量使用觸發器就有可能導緻日志磁盤上的 IO 瓶頸。

自SQL Server 2005開始，觸發器改為使用行版本。于是被删除的和被插入的行将使用行版本，這些行的句柄将附加到該事務中。

使用行版本避免了SQL Server周遊日志，進而減小了日志磁盤壓力，但同時行版本增加了TempDB的使用量。 

線上索引：索引可以線上建立，但建立的同時行的資料可能會發生改變。SQL Server使用行版本保證建立索引參考的資料不被改變。

MARS：MARS全稱為Multiple Active Result Sets(多個活動結果集)。SQL Server通過行版本實作它。這是一個較為複雜的功能。

概括的說就是因為MARS允許同一事務内的多個語句在同一時間處于活動狀态，SQL Server 就必須使用行版本控制來提供語句級别的一緻性。

快照隔離級别和使用行版本控制的已送出讀隔離級别：行版本是實作這些新隔離級别的基礎。SQL Server 建立已修改的行版本，

使這些隔離級别下運作的事務不會讀到髒資料。

DBCC CHECKDB：生成資料庫的快照，再對資料庫進行checkdb檢查

還有一些功能用到tempdb的，例如版本存儲區，這裡就一一詳細列出來了

本人覺得這些功能都非常重要的，承載的資料也很重要

在SQLSERVER2012裡面，tempdb的資料庫選項有些不一樣，可能從SQLSERVER2008開始就不一樣，不過由于本人

沒有安裝SQLSERVER2008，先不說SQLSERVER2008，先看一下SQLSERVER2012

SQLSERVER2012裡model資料庫和tempdb資料庫選項的比較

可以看出SQLSERVER2012已經将tempdb的頁面驗證改為checksum了

如有不對的地方，歡迎大家拍磚o(∩_∩)o