SQLServer第二章第八節(jié)TEMPDB

1、1.1.1 TempDBTempDB 是一個全局數(shù)據(jù)庫，存儲內部和用戶對象還有臨時表、 對象、在 SQLServer 操作過程中產生的存儲過程等數(shù)據(jù)。在一個 SQLServer 實例中，只有一個 TempDB。所以容易成為性能和磁盤空間瓶頸。 TempDB 可能因為空間可用程度和過量的 DDL 和 DML 操作而變得超負荷。這可能 導致不相關的應用運行緩慢甚至失敗。常見的 TempDB 問題如下：l TempDB 空間超支。l 因為 TempDB 的 I/O 瓶頸而導致查詢緩慢。 這可以查看前面的 I/O 瓶 頸章節(jié)。l 過度的 DDL 操作導致系統(tǒng)表產生瓶頸。l 資源分配爭用。在開始診斷 T

2、empDB 問題之前，先看看 TempDB 的空間是如何使用的?？梢钥偨Y為 4 部分：CategoryDescriptionUser Objects （用戶對象）由用戶會話顯式創(chuàng)建并且在系統(tǒng)目錄中被跟蹤的對象。包括：表及索引；全局臨時表（ #t1）及其索引；局部臨時表（ #t1）及其索引；會話（ session）范圍：包括會話范圍及在存儲 過程中的范圍；表變量（t1）范圍：包括會話范圍及在存儲過程中的范圍；InternalObjects（內部對象）這是語句范圍的對象， 存在和消失于 SQLServer 處理的查詢中。包括：工作文件（ hash join）；運行排序；工作表（游標、脫機（ spo

3、ol）和 LOB（大對象 數(shù)據(jù)類型）類型存儲）；從優(yōu)化角度，當工作表被刪除時，一個 IAM 也 和一個區(qū)將被保存用于新的工作表。Version Store（版本存儲）這部分用于存儲行版本、 MARS 、聯(lián)機索引、 觸發(fā)器、基于快照的隔離級別的行版本。Free Spac（e 空 余空間）TempDB 的可用空間TempDB 的總使用空間等于用戶對象（ userobjects）加上內置對象internal objects）加上版本存儲（ version store）加上可用空間?？捎每臻g等于性能計數(shù)器中 tempdb 的可用空間值。 監(jiān)控 Tempdb 空間( Monitoring

4、tempdb Space )：提早發(fā)現(xiàn)問題總比出現(xiàn)了再解決要強。你可以使用性能計數(shù)器： Free Space in tempdb（KB）來監(jiān)控 TempDB 的空間使用情況。這個計數(shù)器按 KB 來 跟蹤 TempDB。 DBA 可以使用這個指針來判斷 tempdb 是否運行在低空間環(huán) 境。但是，標識不同類別，就像簽名定義的一樣， tempdb 使用磁盤空間的情 況是非常豐富的。下面的查詢返回 tempdb 被用戶和內置對象使用情況，注 意，這僅僅適用于 tempdb：SelectNULLNULLNULLNULLSUM( user_object_reserved_page_count)* 8as

5、user_objects_kbSUM( internal_object_reserved_page_count)* 8asinternal_objects_kb ,SUM( version_store_reserved_page_count)*8 as version_store_kbSUM( unallocated_extent_page_count)* 8 asfreespace_kbFrom sys . dm_db_file_space_usageWhere database_id = 2 本機結果：user_objects_kb internal_objects_kb version_

6、store_kb freespace_kb注意這些數(shù)據(jù)是不包含混合區(qū)的計算，混合區(qū)被分配給用戶和內置對 象。 空間分配故障排查：用戶對象、內置對象和版本存儲能引起 tempdb 的空間申請，下面我們 看看如何檢查每部分的故障問題。用戶對象( User objects) :因為用戶對象不屬于任何特定會話( specific sessions)，你需要理解規(guī) 范的應用程序應該根據(jù)特定的要求創(chuàng)建和調整用戶對象。你可以通過運行 exec sp_spaceused objname= ' -<oubsjerct> 來'找到由個別用戶對象使用的 空間。比如，運行以下

7、腳本來列舉所有 tempdb 對象：DECLARE userobj_cursor CURSOR FORselectsys . schemas . name + '.' + sys . objects . namefrom sys . objects , sys . schemaswhere object_id > 100 andtype_desc = 'USER_TABLE' andsys . objects . schema_id = sys . schemas . schema_idgoopen userobj_cursorgodeclare name

8、 varchar ( 256 )fetch userobj_cursor into namewhile ( FETCH_STATUS = 0 )beginexec sp_spaceusedobjname = namefetch userobj_cursor into nameendclose userobj_cursor版本存儲( Version Store )：SQLServer2008 提供一個行版本框架，目前為止，以下特性被用于行版 本框架：l 觸發(fā)器l MARSl 聯(lián)機索引l 基于行版本隔離級別：需要在數(shù)據(jù)庫級別設置選項 更多信息請查看聯(lián)機叢書： RowVersioning Resou

9、rce Usage 行版本在會話過程中是共享的，創(chuàng)建者也沒有權限去回收行版本。你需 要找到并可能的情況下停止運行最久的事務來保證行版本的清除。 下面的查 詢是返回運行最久的基于行版本存儲的兩個事務：select top 2transaction_id ,transaction_sequence_num ,elapsed_time_secondsfrom sys . dm_tran_active_snapshot_database_transactionsorder by elapsed_time_seconds DESC以下是示例結果：transaction_idtransaction_seq

10、uence_numelapsed_time_seconds8609365232015625783因為第二個活動事務在一個短時期內被引用， 所以你應該把時間花在第 一個事務中。但是，沒有辦法預估多少版本空間將被停止的事務釋放。所以 建議停止多一點的事務來清空這部分的空間?？梢酝ㄟ^特定賬號來固定 tempdb 中版本存儲的空間或者靠清除，如果 可能，應該清除運行最久的快照隔離事務或者使用已提交讀快照隔離的運行 最久的查詢。可以使用以下公式大概預估行版本存儲所需空間： Sizeof version store = 2 * version store data generated per minute

11、* longestrunning time (minutes) of the transaction在所有允許使用行版本隔離級別的數(shù)據(jù)庫中， 版本存儲每分鐘產生的事 務和日志數(shù)據(jù)產生的相同。但是也有例外：在更新時的日志記錄；還有最近 插入的數(shù)據(jù)是沒有形成版本，但會被記錄日志。如果是大容量日志操作，并 且恢復模式不是完全恢復，你可以使用 Version Generation Rate 和 Version Cleanup Rate性能計數(shù)器來計算。如果 VersionCleanup Rate為 0 ，則運行久 的事務會防止行版本存儲被清空。附帶說明，在發(fā)生 tempdb 空間不足的錯 誤前， SQ

12、LServer2008 會堅持到最后，防止行版本存儲被收縮。在收縮過 程中，運行最久的事務卻沒產生任何行版本的將被標記為 “受害者 ”，并清空 這部分的空間。消息 3967 就是在錯誤日志中顯示每個受害事務的信息。一 旦事務被標記為受害者，將不能創(chuàng)建或者訪問行版本信息。消息 3966 記錄 受害事務嘗試讀取行版本時將被回滾的情況。當收縮行版本存儲成功后， tempdb 將有更多的可用空間，否則， tempdb 將耗盡。內置對象( Internal Objects )：內置對象是被每個語句創(chuàng)建或銷毀的對象，處理在前面說到的部分之 外，其他都會創(chuàng)建。如果你發(fā)現(xiàn)在 tempdb 中有一個很大的空間被

13、分配，你 就要檢查哪個會話或任務消耗了這部分空間，然后盡可能校正。SQLServer2008提供 DMVs ：sys . dm_db_session_space_usage 和 sys . dm_db_task_space_usage 來追蹤 tempdb 空間被哪些會話或者任務分配了。雖然任務是在會話環(huán) 境下運行，但是任務的空間使用在任務完成之后才被會話占用的?？梢允褂?以下查詢來找到排行前列的會話分配。 注意這些結果只包含任務已經完成的 會話：selectsession_id ,internal_objects_alloc_page_count , internal_objects_dea

14、lloc_page_count from sys . dm_db_session_space_usage order by internal_objects_alloc_page_countDESC可以使用下面的查詢前列會話中分配給內置對象，包含目前活動的任 務：SELECT+ task_alloc ) as+ task_dealloc ) asast1 ,) asbysession_id ) ast2 and t1 . session_id >50t1 . session_id ,( t1 . internal_objects_alloc_page_count allocated ,(

15、 t1 . internal_objects_dealloc_page_count deallocatedfrom sys . dm_db_session_space_usage( select session_id ,sum( internal_objects_alloc_page_countas task_alloc ,sum( internal_objects_dealloc_page_count task_deallocfrom sys . dm_db_task_space_usagegroup where t1 . session_id = t2 . session_id order

16、 by allocated DESC下面是示例輸出：session_id allocated deallocated525120513651160當你有一個隔離的任務或者產生大量內置對象分配的任務時， 可以使用 下面語句來發(fā)現(xiàn)這些語句和他們的詳細執(zhí)行計劃：selectt1 . session_id,t1 . request_id,t1 . task_alloc,t1 . task_dealloc ,t2 . sql_handle,t2 . statement_start_offset ,t2 . statement_end_offset ,t2 . plan_handlefrom ( Sele

17、ct session_id ,request_id ,sum( internal_objects_alloc_page_count ) as task_alloc , sum( internal_objects_dealloc_page_count ) as task_dealloc from sys . dm_db_task_space_usage group bysession_id , request_id ) as t1 , sys . dm_exec_requests ast2where t1 . session_id = t2 . session_id and( t1 . requ

18、est_id =t2 . request_id )order by t1 . task_alloc DESC示例輸出：session_id request_id task_alloc task_dealloc52 0 1024 1024sql_handle statement_start_offset0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356 statement_end_offset plan_handle-10x06000500D490961BA8C19503000000000000000000000000可以使用 sql_h

19、andle和 plan_handle列來得到語句的執(zhí)行計劃：select text from sys . dm_exec_sql_text( sql_handle )select * fromsys . dm_exec_query_plan ( plan_handle ) 注意，當你想訪問這些執(zhí)行計劃的時候可能它們不再緩存中，為了保證 執(zhí)行計劃的可用性，緩存的執(zhí)行計劃會頻繁輪詢和保存結果，以便更好地使 用。所以它可能會在后來才查到。當 SQLServer重啟時，tempdb 會初始化并增長到配置大小。 這可能導致 tempdb出現(xiàn)碎片，和引起間接開銷， 包含數(shù)據(jù)庫自動增長時申請新區(qū)而導致 的阻

20、塞，擴張 tempdb 空間。這可能導致你的工作負載增大而影響性能。建 議預先設置 tempdb 到適合的大小。過度的 DDL 和分配操作：Tempdb 爭論的兩個點為：創(chuàng)建和刪除大數(shù)據(jù)量的臨時表或者表變量會引起源數(shù)據(jù)的爭用。在 SQLServer2008中，局部臨時表和表變量只是緩存最小的源數(shù)據(jù)。但是，下 面的條件必須滿足。否則，這些臨時對象將不會被緩存：l 沒有創(chuàng)建命名約束l 作用在表上的 DDL 語句，在臨時表創(chuàng)建后沒有運行，比如 CREATE INDEX 或者 CREATE STATISTICS 語句。l 沒有使用動態(tài) SQL 創(chuàng)建的臨時對象，如 sp_executesqlN '

21、; create table #t（a int） 。 'l 在別的對象中創(chuàng)建的臨時對象，比如存儲過程、觸發(fā)器或者用戶自定 義函數(shù)、或者在臨時對象中返回用戶自定義函數(shù)、表值函數(shù)。具有代表性的是，幾乎所有的在堆中的臨時 /工作表都有這種情況。 所以， 一個增、刪、或者 drop操作都會英氣 PFS（空頁面空間 ）頁面的嚴重資源爭用。 如果大部分這些表都小于 64KB 和使用混合區(qū)來分配空間，會給 SGAM （共 享全局分配映射）頁也帶來很重的負擔。SQLServer2008緩存一個數(shù)據(jù)頁和一個 IAM 頁給均不臨時表作為最小分 配資源。工作表的緩存改進了。當一個查詢執(zhí)行時，計劃也會被緩存，

22、工作 表在多個執(zhí)行中的計劃里面被使用，但很少清空。此外，第一個工作表的 9 個頁面會被保留。因為 SGAM 和 PFS 頁發(fā)生在數(shù)據(jù)文件中固定間隔發(fā)生。所以容易找到 它們的資源描述。所以，比如 2：1：1 表示在 tempdb中的第一個 PFS 頁 （databaseid=2,fileid=1,pageid=1），2：1：3 表示第一個 SGAM 頁。 SGAM 頁在每 511232個頁面后產生一個。 PFS頁會在每 8088 個頁面后產生一個。 你可以通過這個特性去 tempdb中超找所有 PFS和SGAM 頁。任何時候一個 任務都會等待得到這些頁上的閂鎖（ latch），這些信息保存在 s

23、ys.dm_os_waiting_tasks表中。由于閂鎖等待是很短暫的，所以你可以經常 查詢這些表（大概 10 秒一次）。并且收集這些信息做后續(xù)分析。比如，你 可以使用下面面查詢去加載所有在 tempdb 頁中等待的任務到 Analysis 數(shù)據(jù) 庫的 waiting_tasks 表中：- get the current timestampdeclare now datetimeselect now = getdate （）- insert data into a table forlater analysis insert into analysis . waiting_tasks sel

24、ectsession_id wait_duration_ms resource_description now from sys . dm_os_waiting_taskswhere wait_type like 'PAGE%LATCH_%' andresource_description like '2:%'任何時候當你在表中發(fā)現(xiàn) tempdb頁中的 latch 申請，你就能分析是否基 于 PFS/SGAM 頁。如果是，意味著在 tempdb 中存在分配爭用。如果看到爭 用在 tempdb 的其他頁，并且如果你能識別這些也屬于系統(tǒng)表，意味著存在 過多的 DDL

25、 操作引起了資源爭用。在 tempdb 對象分配造成的不正常增長， 也可以監(jiān)控下面的性能計數(shù)器：1. SQL Server:Access MethodsWorkfiles Created /Sec2. SQL Server:Access MethodsWorktables Created /Sec3. SQL Server:Access MethodsMixed Page Allocations /Sec4. SQL Server:General StatisticsTemp Tables Created /Sec5. SQL Server:General StatisticsTemp Tab

26、les for destruction解決：如果 tempdb 由于過度的 DDL 操作引起資源爭用。 你可以檢查應用程序 和看看是否最小化 DDL 操作?？梢試L試以下建議：l 從 SQLServer2005 開始，臨時對象在前面所說的情況下被緩存。 但是， 如果你依然遇到重大的 DDL 爭用。你就需要查找哪些臨時對象沒有被緩存 和為什么會發(fā)生這種情況。如果這些對象發(fā)生在循環(huán)或者存儲過程里面，考 慮把它們移出存儲過程或者循環(huán)中。l 檢查執(zhí)行計劃，是否有一些計劃創(chuàng)建了大量的臨時對象、假脫機、排 序或者工作表。對此，你需要把一些臨時對象清理掉。比如，在列中創(chuàng)建用 于 order by 的索引可以考

27、慮移除排序。如果爭用是由于 SGAM/PFS 頁引起，可以通過以下方式減緩：l 增加 tempdb 數(shù)據(jù)文件，來平衡磁盤和文件的負載。理想的情況下， 應該和 CPU 個數(shù)持平。使用 TF-1118 來移除混合區(qū)的分配 tempdb 數(shù)據(jù)庫的空間使用tempdb中包含的對象：用戶對象： 是用戶顯式創(chuàng)建的， 這些對象位于用戶會話的作用域， 可以位于 創(chuàng)建對象的例程 (存儲過程、觸發(fā)器、函數(shù) )的作用域中。1. 用戶定義的表、索引2. 系統(tǒng)表、索引3. 全局臨時表、索引4. 局部臨時表、索引5. 表變量6. 表值函數(shù)中返回的表 內部對象：是根據(jù)需要由 SQL Server數(shù)據(jù)庫引擎創(chuàng)建

28、的，用于處理 SQL Server語句， 內部對象可以在語句作用域中創(chuàng)建、刪除。每個內部對象至 少需要個頁面，一個 IAM 頁，一個區(qū)包含了個頁。1. 游標、假脫機操作、臨時的大型對象 (LOB) ，存儲的工作表2. 哈希聯(lián)接、哈希聚合操作的工作文件3. 如果設置了 sort_in_tempdb 選項，那么創(chuàng)建、重新生成索引的重建排 序結果存放在 tempdb；group by、order by、 union 操作的中間結果。 版本存儲區(qū)：是數(shù)據(jù)頁的集合，包含了支持行版本控制功能的所需的數(shù)據(jù)， 主要支持快照事務隔離級別，以及一些其他的提高數(shù)據(jù)庫并發(fā)性能的新功 能。1.公用版本存儲區(qū)：在使用快照

29、隔離級別、已提交讀隔離級別的數(shù)據(jù)庫 中，由數(shù)據(jù)修改事務生成的行版本。2.聯(lián)機索引生成版本存儲區(qū)： 為了實現(xiàn)聯(lián)機索引操作而為數(shù)據(jù)修改事務 生成的行版本，多個活動結果集， after 觸發(fā)器生成的行版本。上面也提到了，由于 sys.allocation_units和 sys.partitions 視圖沒有記錄 tempdb 中的內部對象、版本存儲區(qū)所以這個視圖和 sp_spaceuse，d 不能準確反應出 tempdb的空間使用。分析 tempdb現(xiàn)有的工作負載 :1.設置tempdb的自動增長2.通過模擬單獨的查詢、工作任務，監(jiān)控 tempdb空間使用3.通過模擬執(zhí)行一些系統(tǒng)維護操作 （重新生成

30、索引 ）,監(jiān)控 tempdb空間使4.根據(jù)和中tempdb的空間使用量 ,預測總工作負荷會使用的空間 ,并針對 任務的并發(fā)度調整這個值 .5.根據(jù)得到的值 , 設置生成環(huán)境中 tempdb的初始大小 ,并開啟自動增長 . 另外 ,tempdb的文件個數(shù)和大小 ,不僅需要滿足實際使用需要 ,還要考 慮性能優(yōu)化 .監(jiān)控 tempdb的空間使用方法 :1.可以通過 SQL Trace來跟蹤,但是由于不能預期造成大量使用 tempdb語 句在什么時候運行 ,而且SQL Trance操作比較昂貴 ,如果一直開著會產生大量的跟蹤文件 , 對硬盤的負擔也比較重 ,一般不用 .2.輕量級的監(jiān)控是通過一定時間間

31、隔運行能夠監(jiān)控系統(tǒng)運行的 dbcc命 令、動態(tài)性能視圖 -函數(shù)，把結果記錄在文件中，這對于很繁忙的系統(tǒng)是不錯的選擇。 小貼士1）查詢頁分配情況Select DB_NAME( database_id ) as DB, max( FILE_ID ) as ' 文件id' , SUM ( user_object_reserved_page_countas ' 用戶對象保留的頁數(shù) 包含已分配區(qū)中的未使用頁數(shù)SUM ( internal_object_reserved_page_count 數(shù)' ,- 包含已分配區(qū)中的未使用頁數(shù)as ' 內部對象保留

32、的頁SUM ( version_store_reserved_page_countas ' 版本存儲保留的頁數(shù)SUM （ unallocated_extent_page_count- 不包含已分配區(qū)中的未使用頁數(shù)as ' 未分配的區(qū)中包含的頁數(shù)SUM( mixed_extent_page_count 未分配頁 ' - 包含 IAM 頁as文件的已分配混合區(qū)中 : 已分配頁和From sys . dm_db_file_space_usageWhere database_id = 2 group by DB_NAME( database_id )2）查詢正在運行 tempdb 空間的總體分配 ,申請空間的會話t1 .session_id ,t1 .internal_objects_alloc_page_countt1 .