sql server 臨時表(上) Tempdb概述

一.概述

　　在sql server里臨時表存儲在TempDB庫中，TempDB是一個系統資料庫，它只有Simple恢復模式，也是最小日誌記錄操作。主要用於存放局部臨時表，全局臨時表，表變數，都是基於臨時特征，每次伺服器或服務重啟後，都會按照Model庫的配置重新創建TempDB庫。在sql server 2012中TempDB表可以配置在故障轉移中。在TempDB庫中存放三類對象包括：用戶對象, 內部對象, 行版本存儲。TempDB庫只有一個文件組，就是primary文件組，增加其它文件組會提示報錯。在許可權方面，所有用戶預設都具有訪問TempDB庫許可權。

　　1.1 Tempdb 的物理屬性

　　　　下表列出了 tempdb 數據和日誌文件的初始配置值。 對於不同版本的 SQL Server，這些文件的大小可能略有不同。

　　1.2 使用Tempdb的作用

　　　　(1) 通過tempdb庫可以緩存臨時表和表變數，用於減少數據表的查詢次數以及鎖問題。緩存允許刪除和創建臨時對象的操作能快速地執行，並減少頁分配的爭用問題。

　　　　(2) 分配頁閂鎖協議得到改善。 從而減少使用的 UP（更新）閂鎖數。

　　　　(3) 減少了 tempdb 的日誌開銷。 從而減少了 tempdb 日誌文件上的磁碟 I/O 帶寬消耗。

　　　　 (4) 分配混合的頁中的演算法tempdb得到了改進。

　　1.3 使用tempdb的限制, 不能對 tempdb 資料庫執行以下操作：

       (1)添加文件組。

       (2)備份或還原資料庫。

       (3)更改排序規則。 預設排序規則為伺服器排序規則。

       (4)更改資料庫所有者。 tempdb 的所有者是 sa。

       (5)創建資料庫快照。

       (6)刪除資料庫。

       (7)從資料庫中刪除 guest 用戶。

       (8)啟用變更數據捕獲。

       (9)參與資料庫鏡像。

       (10)刪除主文件組、主數據文件或日誌文件。

       (11)重命名資料庫或主文件組。

       (12)運行 DBCC CHECKALLOC。

       (13)運行 DBCC CHECKCATALOG。

       (14)將資料庫設置為 OFFLINE。

二. TempDB存儲的三類對象

　　　　在SQL 2005以後，引入了一張新的管理視圖：sys.dm_db_file_space_usage。通過查詢這張視圖，能瞭解tempdb的空間使用情況，能知道tempdb的空間是被哪一塊對象使用掉的，是用戶對象(user_object_reserved_page_count欄位)，還是系統對象（internal_object_reserved_page_count欄位），還是版本存儲區（version_store_reserved_page_count欄位。在瞭解這個dmv視圖之前，先瞭解tempdb庫的三類對象。

　　2.1 用戶臨時對象(user_object_reserved_page_count)

　　　　由用戶會話顯示創建的對象，可以在系統表目錄中找到，用戶臨時對象主要包括以下內容：

 (a) 創建臨時表和索引，不過這些表在重啟後清空。

 (b) 全局臨時表以 ##開頭的表。適用於所有會話操作該表信息。需要顯示的刪除或重啟服務才能清除表。

 (c) 局部臨時表以 #開頭的表。適用於當前會話操作該表信息。會話結束清除表。

 (d) 表變數, 以@開頭。

下麵是簡單示例，演示局部臨時表，臨時表索引，全局臨時表，表變數，腳本如下：

-- 創建局部臨時表
CREATE TABLE #TempTable(id INT,NAME VARCHAR(10))
-- 根據已有表,創建臨時表
SELECT * INTO #TempTable2 FROM tablename

--創建局部臨時表, 帶有聚集索引
CREATE TABLE #tempWithCLUSTERED([SID] INT PRIMARY KEY CLUSTERED, model VARCHAR(50))

-- 創建全局臨時表
CREATE TABLE ##TempTable3(id INT,NAME VARCHAR(10))
-- 根據已有表創建全局臨時表
SELECT * INTO ##TempTable4 FROM dbo.ACT_AnswerTab

-- 刪除全局臨時表
DROP TABLE ##TempTable4

-- 創建表變數
DECLARE  @tablevar  TABLE (id int,NAME varchar(10))

　　下麵簡單介紹一下，臨時對象中表變數與臨時表區別

　　　　(1)統計信息

　　　　　　表變數沒有統計信息，統計信息要麼為0，要麼為1。sql server查詢優化器只會把表變數當作裡面只有1條數據或沒有數據的表對待，腳本演示如下所示：

-- 創建表變數示例
DECLARE  @tablevar  TABLE (id int,NAME varchar(10))
INSERT INTO @tablevar(id,name)
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values
SELECT * FROM @tablevar

　　　　(2) 索引

　　　　　　表變數使用索引，只能是主鍵(可以是聚集和非聚集索引)或者唯一約束，需要預先定義好，定義後不能再添加索引了，註意：即使是有索引，也沒有統計信息。表變數使用聚集索引如下所示：

DECLARE  @tablevar  TABLE (id INT  PRIMARY KEY CLUSTERED,NAME varchar(10))
INSERT INTO @tablevar(id,name)
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values
SELECT * FROM @tablevar WHERE id=1

　　　　 (3) 架構修改

　　　　　　架構修改可以出現在臨時表上，但不會出現在表變數上面，修改會產生重編譯，非預期的重編譯不是好事。下麵是臨時表架構修改，將name欄位長改為50, 腳本如下：

ALTER TABLE #TempTable ALTER COLUMN name NVARCHAR (50) NULL 
USE tempdb
go
sp_help #TempTable

　　　　(4) 對比表格

 　　　　總結: 建議:小於100行使用表變數。數據都不是存入於記憶體中，而是存放在TempDB表中，通過sys.dm_db_session_space_usage這個dmv可查看空間占用情況。後面監控時再說這dmv。

　　2.2 內部對象（internal_object_reserved_page_count）

　　　　內部對象是根據需要 由 SQL Server 資料庫引擎創建的，用於處理 SQL Server 語句。主要是查詢過程中存儲臨時數據的對象，這些臨時數據的對象可以在語句的作用域中自動創建和刪除。 內部對象主要包括以下內容：

　　(a) 游標

　　(b) 哈希(Hash)聯接或哈希聚合操作的查詢

　　(c) 某些 GROUP BY、ORDER BY 或 UNION 查詢的中間排序結果.

　　在內部對象中比較常見的是Worktable, 這個臨時表是自動生成，自動銷毀，主要用於游標，假離線，臨時大對象的數據類型(LOB)存儲，這個內部臨時表用於返回查詢的中間結果，監視分析Worktable可以通過打開statistics io 選項，執行sql語句再查看，要註意的是：高效的查詢下很少出現這種現象。

　　2.3 版本存儲（version_store_reserved_page_count)

　　　　版本存儲區主要用來支持Snapshot事務隔離級別，以及SQL 2005以後推出的一些其他提高資料庫併發度的新功能。這類併發模式會借用TempDB來存放修改前的版本數據，第一行數據被修改前，都會在TempDB中創建一個相同的行，並加上14bytes的長度，包含: (1)修改前的事務序列號(XSN)長度為6 bytes (2) TempDB的行標識符(RID)長度為8bytes。版本存儲用於存儲行版本、MARS、聯機索引、觸發器、基於快照的隔離級別。如果開啟了樂觀併發模式(已提交讀快照和快照二種隔離級別)，可能造成Tempdb庫的非預期增長， 需要對Tempdb庫進行監控。