前面文章針對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 ”
計數器診斷
計數器中我們主要看以下幾個計數器：
- Workfiles Created/sec
- Worktables Created/sec
- Active Temp Tables
- Temp Tables Creation Rate
- 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壓力。那麼怎麼樣平衡呢？下面給出幾點建議：
- 切記不要過度使用！臨時表的使用主要有兩個場景，拆分語句降低複雜性。另一個是緩存中間結果避免重複操作。
- 減少使用臨時表鎖系統表的時間！”select 字段 into #臨時表 from“ 如果語句執行時間過長這将是災難，盡量選用先建立，後插入的做法。
- 臨時表也是有緩存的，查找哪些對象沒有被緩存，為什麼發生這樣的情況！參見 ： Sql Server tempdb原理-緩存機制解析實踐
SQL SERVER 2016版本小福利
2016 已經釋出了 在2016中做了如下改動：
2016 建立資料庫時會檢測CPU個數來建立tempdb，但是初始大小為8M,64M增長。
2016 tempdb使用預設為統一區，在以前的SQL Server版本裡，臨時表的資料頁總配置設定在所謂的混合區（Mixed Extends），它大小是64kb在多個資料庫對象（像表和索引）間共享。這個方法是可以減少在
SGAM（共享全局配置設定映射（Shared Global Allocation Map）頁，管理混合區）頁上的
闩鎖競争問題（Latch Contention problem）。
2016之前，很多人使用1117和1118跟蹤标記來定義SQL Server在資料庫裡如何配置設定頁，新版本中已經不需要啦！
高能預警： 2016中預設的TempDB 檔案數量也和本文講述的TempDB配置個數相符合哦~~~~
-----------------------------------------------------------------------------------------------------
總結：TempDB經過添加多個檔案，基本可以避免成為瓶頸。
TempDB添加的檔案一定要大小一緻，增長率一緻，否則不會起到效果。
使用臨時表等對語句優化是常用手段，但一定要保持一個平衡，切勿過度使用。
通過語句優化一樣能降低TempDB壓力，如檢查執行計劃，是否有一些計劃建立了大量的臨時對象、假脫機、排序或者工作表。對此，你需要把一些臨時對象清理掉。比如，在列中建立用于order by的索引可以考慮移除排序。
TempDB的檔案配置設定是優化的正常配置。
----------------------------------------------------------------------------------------------------
注：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，非常感謝！
引用高大俠的一句話 ：“拒絕SQL Server背鍋，從我做起!”
系列文章導讀請關注 ：