文章出處

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

我們先運行監控,然后再運行一般腳本,可以分析一下跟蹤結果。

可以看到在新建臨時表的時候,對于Tempdb的申請空間有所增加。 


文章列表




Avast logo

Avast 防毒軟體已檢查此封電子郵件的病毒。
www.avast.com


arrow
arrow
    全站熱搜

    大師兄 發表在 痞客邦 留言(0) 人氣()