《SQL Server企業級平臺管理實踐》讀書筆記——SQL Server中關于系統庫Tempdb總結

文章出處

Tempdb系統數據庫是一個全局資源，可供連接到SQL Server實例的所有用戶使用。

存儲的內容項：

1、用戶對象

用戶對象由用戶顯示創建。這些對象可以位于用戶會話的作用域中，也可以位于創建對象所用例程的作用域中。例程可以是存儲過程、觸發器或用戶自定義函數。用戶對象可以是一下項內容之一：

用戶定義的表和索引
系統表和索引
全局臨時表和索引
table變量
表值函數中返回的表

2、內部對象

內部對象是根據需要由SQL Server數據庫引擎創建的，用戶處理SQL Server語句。內部對象可以在語句的作用域中創建和刪除。內部對象可以是下列項之一：

用于游標或假脫機操作以及臨時大型對象（LOB）存儲的工作表
用于哈希連接或哈希聚合操作的工作文件
用于創建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中間排序結果，或者某些GROUP BY、ORDER BY或UNION查詢的中間排序結果。
每個對象至少使用9頁：一個IAM頁，一個8頁的區。

3、版本存儲區

版本存儲區是數據頁的集合，它包含支持使用行版本控制的功能所需的數據行。主要用來支持快照（Snaphot）事務隔離級別，以及SQL Server2005推出的一些其它提高并法度的新功能。在SQL Server2005中，有兩個版本存儲區：公用版本存儲區和索引生產版本區。包含以下內容：

由使用快照隔離界別或已提交讀隔離界別（基于行版本控制）的數據庫中的數據修改事務生成的行版本
由數據修改事務為實現聯機索引操作、多個活動的結果集（MARS)以及AFTER觸發器等功能而生成行版本

在SQL Server2005以后，tempdb需要的磁盤空間會多于早期版本。因此當升級到SQL Server2005或SQL Server2008后，管理員提供的tempdb磁盤空間可能得比以前要大一些，用以容納當前生產工作負荷并滿足使用tempdb的SQL Server功能的額外空間要求。

Tempdb空間使用跟蹤

tempdb空間使用的一大特點，是只有一部分對象，比如：臨時表、table 變量等，可以用sys.allcoation_units和sys.partitions這樣的管理視圖來管理。所以用sp_spaceused的結果和真實情況會有很大差異。

提示：tempdb的空間使用是不能用sp_spaceused來跟蹤的。

DBCC SHOWFILESTATS還是可以反映數據庫文件使用的整體情況的。

在2005版本以后引入了一個新的視圖：sys.dm_db_file_space_usage;這張視圖能反映出tempdb在如下幾個大類的空間里使用分布：

select * from sys.dm_db_file_space_usage

其中：

database_id：數據庫ID

file_id：文件ID,file_id映射到sys.dm_io_virtual_file_stats中的file_id,并且映射到sys.sysfile中的fileid

unallocated_extent_page_count：文件未分配區中的總頁數，不包含已分配區中的未使用頁

version_store_reserved_page_count：為版本存儲分配的統一區中的總頁數

user_object_reserved_page_count：為從統一區為數據庫中的用戶對象分配的總頁數。計數中包括已分配區中未使用的頁。可以使用sys.allocation_units目錄視圖中的total_pages列來返回用戶對象每個分配單元保留的頁計數

internale_object_reserved_page_count：從統一區為文件中的內部對象分配的總頁數。計數包括已分配區中未使用的頁

mixed_extent_page_count：文件的已分配混合區中的已分配和未分配總頁數。混合區包含分配給不同對象的頁。此計數器包含文件中的所有的IAM頁

通過這個表的監視，就能知道temdb的空間被那個一塊對象使用掉了，是用戶對象（user_object_reserved_page_count)，還是系統對象（internal_object_reserved_page_count）,還是版本存儲區對象（version_store_reserved_page_count）。

tempdb在生產環境中的設置的初始大小其實是一個經驗值，也就是說沒有一個好的固定值，但是我們可以根據工作負荷來確定該值的大小值：

1、設置tempdb的自動增長

2、模擬各個單獨的查詢或工作任務，同時監視tempdb空間使用

3、模擬執行一些系統維護操作，例如，重新生成索引，同時監視tempdb空間

4、使用前面2和3步中tempdb空間使用值來預測總的工作負荷下，會使用多少空間；并針對計劃的并發度調整此值。例如，如果一個任務會使用10GB的tempdba空間，而在生產環境里，最多可能會有4個這樣的任務同時運行，那就要至少預留40GB的空間。

5、設置tempdb在生產環境下的初始大小。同時也開始自動增長。

Temp文件個數以及大小設置，不但滿足用戶任務的需求，還要考慮到性能優化。

我們利用腳本來跟蹤一下，看看是什么樣的操作導致了SQL Server要這樣大規模的使用tempdb,然后分析一下是正常行為還是異常行為，監視SQL Server的行為當然可以利用SQL Trace來完成。可是管理員并不能預期造成大量使用tempdb的語句會在什么時刻運行。這些語句可能一周只做一次。而且開跟蹤畢竟對SQL Server來講是一個比較昂貴的事情，就算沒有性能副作用，如果一直開著SQL Trace,也會產生大量的跟蹤文件，對硬盤是個比較重的負擔。

推薦的“輕量級”的操作是以一定的時間間隔運行能夠監視系統運行狀況的DBCC命令、查詢管理視圖（DMV）以及管理函數（DMF）等，把這個結果輸出到一個文件里。

新開一個連接，我們輸入以下常規腳本：

select @@SPID
go
use AdventureWorks
go 
select GETDATE()
go 
select * into #MyOrdersDetail
from Sales.SalesOrderDetail
--創建一個臨時表
--這個操作應該會申請用戶對象頁面
go
waitfor delay '0:0:2'
select GETDATE()
go
drop table #MyOrdersDetail
--刪除一個臨時表
--這個操作用戶對象頁面數量應該會下降
go
waitfor delay '0:0:2'
select GETDATE()
go
select top 100000 * from
[Sales].[SalesOrderDetail] 
inner join [Sales].[SalesOrderHeader] 
on [Sales].[SalesOrderHeader].[SalesOrderID] =
[Sales] .[SalesOrderHeader].SalesOrderID;
--這里做了一個比較大的連接，應該會有系統對象申請
go
select GETDATE()
--join 語句做完以后系統對象頁面數目應該會下降
go

然后另開一個窗口，我們輸入監控腳本，輸出結果以文本方式存儲

use tempdb
--每隔1s運行一次，直到用戶手工終止腳本運行
while 1=1
begin
select GETDATE()
--從文件級看temp使用情況
dbcc showfilestats
--第一個會話
--返回所有做過空間申請的會話信息
select 'Tempdb' as DB,GETDATE() as Time,
sum(user_object_reserved_page_count)*8 as user_obectskb,
SUM(internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM(version_store_reserved_page_count)*8 as version_store_kb,
SUM(unallocated_extent_page_count)*8 as freespace_kb
from sys.dm_db_file_space_usage
where database_id=2

--第二個會話
--這個管理視圖能夠反映當時tempdb空間的總體分配
select t1.session_id,
t1.internal_objects_alloc_page_count,
t1.user_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count,
t1.user_objects_dealloc_page_count,
t3.*
from sys.dm_db_session_space_usage t1,
--反映每個會話累計空間申請
sys.dm_exec_sessions as t3
--每個會話信息
where 
t1.session_id=t3.session_id
and (t1.internal_objects_alloc_page_count+t1.internal_objects_dealloc_page_count+t1.user_objects_alloc_page_count+t1.user_objects_dealloc_page_count)>0

--第三會話
--返回正在運行并且做過空間申請的會話正在運行的語句
select t1.session_id,
st.text
from sys.dm_db_session_space_usage as t1,
sys.dm_exec_requests as t4
cross apply sys.dm_exec_sql_text(t4.sql_handle) as st
where t1.session_id=t4.session_id
and t1.session_id>50
and (t1.internal_objects_alloc_page_count+t1.internal_objects_dealloc_page_count+t1.user_objects_alloc_page_count+t1.user_objects_dealloc_page_count)>0

waitfor delay '0:0:1'
end