前面文章針對CPU、記憶體、磁碟、語句、等待講述了SQL SERVER的一些基本的問題診斷與調優方式。為了方便閱讀給出導讀文章鏈接方便閱讀：

SQL SERVER全面優化-------Expert for SQL Server 診斷系列

　　　　這篇我們來說說TempDB，這個系統資料庫如何進行優化，怎麼樣平衡他的使用。

　　　　首先簡單介紹一下TempDB：Tempdb是SQL Server里的一個重要的系統資料庫。並且每個實例中只有一個TempDB，也就是當你在一個實例下創建了100個資料庫，這100個資料庫也只能用這一個TempDB。是不是感覺到了他的壓力會很大？還沒完呢！許多用戶的操作，都有可能使用到它。最常見的當然是用戶使用臨時表或者表變數。其他可能性有，用戶使用trigger，Snapshot Isolation Level，某些複雜的查詢，以及DBCC CHECKDB等。聽起來這是要爆炸的節奏呀！他不會爆炸，這麼說只是想你提高對他的關註性，很多系統性能問題就出在他身上！

　　　　一如既往還是用一個例子說明: 語句相當於“車”，硬體相當於 “路” ，等待相當於 “紅綠燈”，那麼TempDB 相當於什麼呢？ “服務區停車場”

--------------博客地址---------------------------------------------------------------------------------------

Expert 診斷優化系列 http://www.cnblogs.com/double-K/

廢話不多說，直接開整-----------------------------------------------------------------------------------------

• TempDB壓力從哪來？

　　　　當資料庫創建一張新表的時候，SQL Server要為這張表分配存儲頁面，同時SQL Server也要修改SGAM, PFS, 和GAM頁面，把已經分配出去的頁面標誌成已使用。所以每創建一張新表，SGAM, PFS, 和GAM這些系統頁面都會有修改動作。這種行為對一般的用戶資料庫不會有問題，因為正常的應用不會折騰著不停地建表、刪表。但是tempdb就不同了。如果一個存儲過程使用了臨時表，而這個存儲過程被併發用戶廣泛使用，那很自然地就會有很多併發用戶在tempdb里同時創建表，做完了以後又刪除表。這樣，在一個時間點，會有很多任務要修改SGAM, PFS, 或GAM頁面。但是為了維護物理的一致性，對於同一個頁面，SQL Server在一個時間點同時只允許一個用戶修改它。所以對於tempdb，如果同時有很多很多人要在同一個數據文件里分配空間，那這個數據文件的SGAM, PFS, 或GAM頁面，就有可能成為系統瓶頸。大家只能一個一個做，併發度上不去。

　　　　這就好像你進停車場要登記交費一樣！一個一個來不要急~

　　　　直接上例子: 

　　　　等待資源為 ： “2：1：3” 這是什麼意思？ ID 為 2 的資料庫（TempDB）的 1號文件 的 頁碼為3的頁（SGAM頁面）！

　　　　這裡關於系統頁不過多的介紹，想詳細瞭解的朋友請參見 ：  SQL Server中的GAM頁和SGAM頁

　　　　我創建個臨時表跟系統頁還有關係？

　　　　下麵也用一個例子說明 ： 

　　　　創建臨時表的時候會對系統表中進行插入和更新，而刪除臨時表逆向過程會刪除或更新系統表！

use [AdventureWorks2012]
GO
checkpoint
go
create table #t
(
id int
)
drop table #t


use tempdb
go
select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

　　　　所以當你併發過高且頻繁創建刪除臨時表的時候就會造成大量的爭用。

• TempDB問題簡單處理

　　　　上面描述的看起來好像需要對SQL SERVER掌握的很深，才能處理這個問題。其實很簡單 ，只需要你做一件事情就可以搞定TempDB的大部分問題！那就是把TempDB設置成多個來分攤這個壓力。

　　　　“服務區停車場” 可以設置多個收費口來避免擁堵和排隊！

　　　　分成多個文件

　　　　作為一般規則，如果邏輯處理器數小於或等於 8，使用和邏輯處理器相同數量的數據文件。如果邏輯處理器數大於 8 時，使用 8 個數據文件，然後如果仍然存在爭用，增加數據文件數4 的倍數（最多的邏輯處理器數）直到爭用降低到可接受的程度或對工作負荷/代碼進行更改。

　　　　在網上流傳的各種TempDB 配置文檔中，都描述的是使用邏輯處理器相同數量的數據文件。一般情況下是沒什麼問題，但是有一點需要註意：如果程式中有記憶體不足蔓延到tempDB的情況，或頻繁的使用數據量大的臨時數據Worktables 等，性能反而會下降，因為你的文件被分成多個，但數據寫入的時候就需要輪循（round-robin），簡單理解這樣會有一定的時間損失，且讀取的時候隨機IO 也會多消耗IO資源和時間。有興趣的朋友可以詳見 ：

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

　　　　這裡說的看官們好像也不知道我該使用幾個了...對於系統最佳實踐，非常精細化的優化時可能才需要考慮上面的問題，對於一般系統TempDB一般可以配置成8 或16 個Temp文件就足夠了，如果還有大量爭取就繼續增加（一般情況不要超過你的邏輯CPU數量）。

　　　　文件大小、增長率要相同

　　　這裡需要註意一個小細節，你所分配的文件必須大小一致，如果設置自動增長那麼增長率要相同。

　　　　TempDB磁碟劃分

　　  大多數情況下，TempDB的文件不需要拆分磁碟，在同一個磁碟即可，如果壓力大可以選擇放置在一個單獨的磁碟中，這樣不會與其他文件（如數據讀寫）發生磁碟資源競爭。

　　　　如果出現TempDB 讀取響應時間高的情況，請考慮，TempDB的磁碟相關優化。

• TempDB壓力診斷

　　等待類型診斷

　　TempDB的爭用壓力在等待篇中已經簡單介紹，等待的表現為 pagelatch_類等待，等待的資源是 “2： X ：X ”

　　計數器診斷

　　計數器中我們主要看以下幾個計數器：

1. Workfiles Created/sec 
2. Worktables Created/sec 
3. Active Temp Tables　　
4. Temp Tables Creation Rate
5. Temp Tables For Destruction　　　

　　這裡的標準各不相同就不細說了。

　　通過對象分佈診斷

　　　　TempDB中對象可分為三種：

• 顯式創建的用戶對象

　　這些對象由用戶顯式創建。存在於用戶會話的作用域中，也可位於創建對象所用的常式（存儲過程、觸發器或用戶定義函數）的作用域中。

　　包括：表和索引（系統的，或用戶定義的）、臨時表和索引（全局的，或局部的）、表變數、表值函數中返回的表。

• 資料庫引擎創建的內部對象

　　這些內部對象由資料庫引擎根據需要而創建，用於處理SQL Server語句。可以在語句的作用域中創建和刪除。每個內部對象至少使用9個頁面：1個IAM頁，1個連續8頁的區。

　　包括：用於游標或假離線操作以及臨時大型對象（LOB）存儲的工作表；用於HASH連接或HASH聚合操作的工作表；用於創建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中間排序結果，或者某些GROUP BY、ORDER BY或UNION查詢的中間排序結果。

• 版本存儲區

　　版本存儲區是數據頁的集合，它包含支持使用行版本控制的功能所需的數據行，主要用來支持快照事務隔離級別，以及一些其它提高資料庫併發性能的新功能。主要分為2類：公用版本存儲區、聯機索引生成版本存儲區。

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

　　腳本奉上 ：

SELECT 'tempdb' AS DB,GETDATE() AS TIME,
SUM (user_object_reserved_page_count)*8 as [用戶對象(kb)], ----如臨時表的使用
SUM (internal_object_reserved_page_count)*8 as [內部對象(kb)], -----如連接hash 使用的空間
SUM (version_store_reserved_page_count)*8  as [紀錄版本空間(kb)],
SUM (unallocated_extent_page_count)*8 as [可用空間(kb)],
SUM (mixed_extent_page_count)*8 as [mixedextent(kb)]
FROM sys.dm_db_file_space_usage

　　　高能預警：如果用戶對象分配空間持續使用很大，基本可以說明你的程式代碼中過度依賴TempDb 過併發高的存儲過程中有大量的臨時表使用。如果內部對象持續很高，說明你的程式中有很多語句寫法可以優化（如排序、hash join溢出，游標等等）

• TempDB和語句調優

　　　　語句調優篇提到語句中使用臨時表或表變等會減少語句的複雜度，提升語句的效率，是常用的三板斧之一，但這裡的需要一個平衡。如果對語句過度使用會造成文中提到的TempDB壓力。那麼怎麼樣平衡呢？下麵給出幾點建議：

1. 切記不要過度使用！臨時表的使用主要有兩個場景，拆分語句降低複雜性。另一個是緩存中間結果避免重覆操作。
2. 減少使用臨時表鎖系統表的時間！”select 欄位 into #臨時表 from“ 如果語句執行時間過長這將是災難，儘量選用先創建，後插入的做法。
3. 臨時表也是有緩存的，查找哪些對象沒有被緩存，為什麼發生這樣的情況！參見 ：Sql Server tempdb原理-緩存機制解析實踐

• SQL SERVER 2016版本小福利

　　2016 已經發佈了 在2016中做瞭如下改動：

　　2016之前，很多人使用1117和1118跟蹤標記來定義SQL Server在資料庫里如何分配頁，新版本中已經不需要啦！

　　　高能預警： 2016中預設的TempDB 文件數量也和本文講述的TempDB配置個數相符合哦~~~~

--------------博客地址---------------------------------------------------------------------------------------

Expert 診斷優化系列 http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

　　總結：TempDB經過添加多個文件，基本可以避免成為瓶頸。

　　　　　TempDB添加的文件一定要大小一致，增長率一致，否則不會起到效果。

　　　　　使用臨時表等對語句優化是常用手段，但一定要保持一個平衡，切勿過度使用。

　　　　　 通過語句優化一樣能降低TempDB壓力，如檢查執行計劃，是否有一些計劃創建了大量的臨時對象、假離線、排序或者工作表。對此，你需要把一些臨時對象清理掉。比如，在列中創建用於order by的索引可以考慮移除排序。

　　　　　TempDB的文件分配是優化的常規配置。

 ----------------------------------------------------------------------------------------------------

註：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文鏈接！
若您覺得這篇文章還不錯請點擊下右下角的推薦，非常感謝！

　　引用高大俠的一句話 ：“拒絕SQL Server背鍋，從我做起!”

系列文章導讀請關註 ：  SQL SERVER全面優化-------Expert for SQL Server 診斷系列