文章出處

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

View Code

猜測原因

從上面的實驗可以看到，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檢查