Sql Server tempdb原理-緩衝機制解析實踐

標籤：des   cWeb   style   blog   http   color   os   使用   io   

Tempdb就像Sqlserver的臨時倉庫,各式各樣的對象,資料在裡面進行頻繁計算,操作.大量的操作使得tempdb可能面臨很大壓力,tempdb中緩衝的設計就是為了緩解這些壓力.這次就為大家介紹下tempdb的緩衝機制.

 

在介紹緩衝機制前,先簡單瞭解一下TempDB對象

一般我們把tempdb對象分為兩種類型使用者物件和內部對象.使用者物件指通過顯式T-sql來創造的對象(如暫存資料表),內部對象指通過隱式T-sql建立的對象(Worktables)

注:在引入版本控制後,也可以此單獨分類(DMV sys.dm_db_file_space_usage中單獨列出)

使用者物件

暫存資料表

表變數(包含資料表值函式傳回值及表值參數)

暫存預存程序

使用者自訂對象

使用者線上(Online)建立索引空間

內部對象

Sorts(排序溢出)

Worktables(checkdb,遊標,Merge joins,假離線,並行查詢交換溢出,LOB對象等)

Workfiles(hash join 溢出)

Version store(版本行控制)

查看對象使用方式

當tempdb資料檔案很大或者有異常時,我們可以查看相應的使用方式.如何查看,沄劍的Blog中如何查看某個查詢用了多少TempDB空間有詳細指令碼.

Tempdb緩衝機制

Tempdb中的眾多個物件緩衝機制不盡相同,其中一些操作(如Sort)採用內部的機制,對使用者是不可控的,這裡我們主要介紹常用對象暫存資料表/表變數(也是經常引發問題)的緩衝機制.

暫存資料表緩衝機制(#t)

只有使用預存程序,觸發器,Functions才能緩衝

以plan cache的形式緩衝一個IAM頁和一個DatePage頁

禁止Create後使用DDL操作

禁止命名約束

可以看出暫存資料表的緩衝是以proc執行計畫緩衝的形式實現的.所以batch,動態sql是無法緩衝的.值得注意的是既然是執行計畫緩衝,我們就不能在proc中加WITH RECOMPILE關鍵字.

注意:緩衝對象時局部暫存資料表,不包括全域暫存資料表.

我們通過一個簡單的執行個體來分析下緩衝實現.

首先我們來看下一般batch的操作(非緩衝)執行兩次,觀察日誌情況1-1

batch create code

use tempdbgocheckpointgocreate table #t(id int)insert into #t select 1drop table #tselect Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

 

                                                                                 圖1-1

實際上我們可以看出第一次和第二次執行的日誌記錄情況是相同的.

再來看下proc方式

proc code

use tempdbgocheckpointgocreate proc p_tstcacheascreate table #t(id int)insert into #t select 1exec p_tstcache------第一次執行後觀察日誌記錄1-2select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)checkpointgoexec p_tstcache------第二次執行後觀察日誌記錄資訊1-3select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

 

 

                                            圖1-2

 

                                                圖1-3

 

 

可以看到當預存程序第二次執行時使用了緩衝,日誌記錄數明顯減少.使用完成後繼續緩衝。

緩衝的益處

我們通過一個簡單的壓力測試來看下緩衝的效果.

我們使用sqlquerystress開100的threads分別執行1000次看下batch,proc,proc中create後ddl的效果.(感興趣的朋友可以觀察相應的計數器 temp tables creation rate) 圖1-4

注意:預先設定好tempdb資料記錄檔大小,避免因為檔案增長帶來的測試偏差.

Code 1 batch

create table #t(id int)
Drop table #t

Code2 proc

Create proc p_tstcacheAscreate table #t(id int)

Code 3 proc ddl after create script

create proc p_tstcache_ddlascreate table #t(id int)Create index ix_id on #t(id) ----ddl after create

 

                                                   圖1-4

 

可以看到因為緩衝機制,在一些應用頻繁建立暫存資料表的執行個體中我們可以通過proc中完成暫存資料表的構建從而緩解競爭.但應注意proc 暫存資料表cache的限制.

關於表變數.

表變數的緩衝機制與暫存資料表相同(注: 表值參數不支援緩衝)

表變數是不能建立索引的,但可以有個預設約束

表變數沒有統計資訊

表變數不支援事務

關於Proc中顯式drop暫存資料表.

微軟聲稱proc中顯式drop暫存資料表並不受create後DDL的影響,但在現實生產環境中的情況,顯式drop還是有一定影響的.況且proc執行完成後字自動緩衝處理,沒必要顯式drop.

關於暫存資料表/表變數緩衝應用

通過上面的執行個體我們可以看到,如果要利用proc緩衝cache是有不少限制的.現實生產環境中我們有可能使用到暫存資料表(表變數)需要建立索引以提高查詢效率.這時就需要我們來權衡.實際上高並發查詢中使用資料量較大的暫存資料表此時我們可以在建立暫存資料表的指令碼中一併完成索引的建立.但高並發的大暫存資料表下的壓力會是建立過程嗎?

結語:有時候DBA的工作的確是手藝活,需要不停的打磨權衡.在業務穩定的情形下如果我們無法調整硬體環境,就需要我們打造業務所需的合理平衡.

Sql Server tempdb原理-緩衝機制解析實踐