[SQL Server][安裝]Tempdb自動組態(SQL Server 2016)

2017-11-05

3739
0
SQLInstall

Tempdb在資料庫執行時有著神聖的特殊任務，但也因為只此一家別無分號，為了避免Tempdb Allocation Contention(爭用)，裝完SQL Server後，我們會再填單請DBA大人幫忙設定多個Tempdb資料庫檔案，現在SQL Server 2016、SQL Server 2017更方便了，SQL安裝精靈到了"資料庫引擎組態"步驟時，精靈會自動偵測硬體的CPU核心數，我們只需要配置好初始與成長大小。

最近想幫早期上線(SQL Server 2008R2)的客戶增加檔案個數，筆記過程。

高並行交易系統

高並行的線上交易系統如果在交易中密集使用temp table(#)或是table variable(@)，很快很快，我們會先遇到tempdb資源的爭用(tempdb allocation contention)，原因在於使用tempdb建立與刪除資料時需要在tempdb的資料庫檔案中的PFS、GAM及SGAM分頁上註記:

PFS分頁會記錄每一個資料Page的用途與空間 :1Byte
GAM分頁則會記錄每一個資料Extent(8 Page)是否有人使用 :1bit
SGAM分頁則會紀錄每一個資料Extent是否還有剩餘空間 :1 bit

確認是否出現tempdb資源爭用

想確認是否有遇到tempdb資源爭用:

推薦Rico大推薦使用的擴充事件或是sqlserver.latch_suspend_end事件來監控，如果發現PAGELATCH_*比例很高或是監控的tempdb頻繁出現latch suspend : Bingo!
透過dmv線上觀察等待資源wait resource，因為tempdb的db id是2，第一個初始分頁2:1:1 代表PFS，第二個初始分頁就是2:1:2 代表GAM，2:1:3則代表SGAM，如果發現wait resource出現頻繁的等待tempdb PFS的狀況(2:1:1): Bingo!

如果SQL版本已經是SQL 2016以後的版本，Good!

SQL安裝精靈預設就會切多個tempdb file，也可以避免掉MIXED_PAGE_ALLOCATION。

模擬線上交易(#Temp table)-單一tempdb檔案

使用SQLQueryStress工具模擬線上交易頻繁使用temp table的指令，執行20條執行緒100次

指令內容(還不能挖太小，要讓他經常要使用到新的分頁，所以設定CHAR 2000)

CREATE TABLE #TX
(C1 INT IDENTITY,C2 CHAR(2000))

DECLARE @I INT = 0
WHILE @I < 100 
BEGIN
INSERT INTO #TX
    VALUES ('1')
 SET @I = @I + 1
END

#TABLE 對照組，共執行了34秒，平均每次0.3027秒

模擬線上交易(@table variable)-單一tempdb檔案

使用SQLQueryStress工具模擬線上交易頻繁使用table變數的指令，執行20條執行緒100次

DECLARE @TX TABLE
(
  C1 INT IDENTITY,C2 CHAR(2000)
)
DECLARE @I INT = 0
WHILE @I < 100 
BEGIN
INSERT INTO @TX
    VALUES ('1')
 SET @I = @I + 1
END

@TABLE 對照組

監控等待資源 (2:1:*)

在temp table與table variable的情境下，透過Dmv在執行過程中都有發現PAGELATCH_*，Wait Page 2:1:*

偵測Tempdb檔案等待資源語法

WITH CTE_WaitPage
AS
(SELECT
		req.session_id
		,req.database_id
		,req.blocking_session_id
		,req.status
		,req.command
		,req.wait_type
		,AVG(req.wait_time) AS [wait_time]
		,req.wait_resource
		,CONVERT(INT,(RIGHT(req.wait_resource, LEN(req.wait_resource) - CHARINDEX(':', req.wait_resource, 3)))) AS page_id

	FROM sys.dm_exec_requests AS req
	  JOIN sys.dm_exec_sessions AS s
		ON req.session_id = s.session_id

	WHERE req.wait_type IS NOT NULL
	AND s.is_user_process = 1

	GROUP BY GROUPING SETS ((req.session_id, req.status, req.command,
	req.database_id, req.blocking_session_id,
	req.wait_type, req.wait_time, req.wait_resource), ()))
SELECT
	session_id
	,status
	,command
	,DB_NAME(database_id) AS dbname
	,blocking_session_id
	,wait_type
	,wait_time
	,wait_resource
	,page_id
	,(CASE
		WHEN page_id = 1 OR
			page_id % 8088 = 0 THEN 'PFS_PAGE'
		WHEN page_id = 2 OR
			page_id % 511232 = 0 THEN 'GAM_PAGE'
		WHEN page_id = 3 OR
			(page_id - 1) % 511232 = 0 THEN 'SGAM_PAGE'
		ELSE 'Others'
	END) page_type
FROM CTE_WaitPage
WHERE session_id IS NOT NULL

增加Tempdb檔案個數然後挖好挖滿

首先先確認目前tempdb的配置

USE tempdb 
SELECT file_id as [檔案序號],
           name as [檔案名稱],
           size/128. as [資料檔案大小(MB)],
           FILEPROPERTY(name, 'SpaceUsed') as [已使用的資料頁],
           physical_name as [實體路徑]
FROM sys.database_files;

好的! 接著我們按照微軟technet的建議將tempdb個數增加至8個(測試機有2顆CPU共16 core)，並且一次挖好挖滿。

將 tempdb 的復原模型設定為 SIMPLE。此模型會自動收回記錄空間，以保持較小的空間需求。

允許 tempdb 檔案依需要自動成長。這可讓檔案成長到磁碟已滿為止。註: 如果實際執行環境無法容忍自動成長作業期間發生應用程式逾時的可能性，則預先配置空間來容許預期的工作負載。

將檔案成長增量設成合理的大小，可避免 tempdb 資料庫檔案每次成長量的值太小。如果檔案的成長比寫入 tempdb 的資料量少太多，那麼 tempdb 可能必須經常擴大。這樣會影響效能

您可將檔案大小設定為夠大的值來容納環境中的典型工作負載，藉此為所有 tempdb 檔案預先配置空間。這防止 tempdb 擴充過於頻繁而影響效能。tempdb 資料庫應該設為自動成長，但這應該用來增加非計畫中例外狀況的磁碟空間。

建立最大化磁碟頻寬所需的最多檔案。使用多個檔案可減少 tempdb 儲存體爭用並明顯產生更好的延展性。不過，不要建立太多檔案，因為這樣會降低效能及增加管理負擔。一般來說，請為伺服器上的每一個 CPU 建立一個資料檔 (來負責任何相似性遮罩設定)，然後依需要向上或向下調整檔案數。請注意，雙核心 CPU 被視為兩個 CPU。

使每一個資料檔大小相同；這可達到最佳比例填入效能。

將 tempdb 資料庫放在快速的 I/O 子系統上。如果有許多直接連接的磁碟，請使用磁碟條狀配置。

將 tempdb 資料庫放在不同於使用者資料庫所使用的磁碟上。

習慣用伺服器總核心數和數字4的最小公倍數計算，但有一篇kb2154845講到，如果邏輯處理器數目大於 8，使用 8 為資料檔案，除非持續爭用，才使用（最多的邏輯處理器數目）的 4 的倍數直到爭用會減少到可接受的層級，我們這次就用8。

新增Tempdb檔案指令:

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp02', FILENAME = N'T:\SQL\temp02.ndf' , SIZE = 2GB , FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp03', FILENAME = N'T:\SQL\temp03.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp04', FILENAME = N'T:\SQL\temp04.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp05', FILENAME = N'T:\SQL\temp05.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp06', FILENAME = N'T:\SQL\temp06.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp07', FILENAME = N'T:\SQL\temp07.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp08', FILENAME = N'T:\SQL\temp08.ndf' , SIZE = 2GB, FILEGROWTH = 1GB)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2GB,FILEGROWTH = 1GB)
GO

重新啟動SQL Server

因為是SQL 2008版本，打開組態管理員(sqlservermanager10.msc)重新啟動SQL Instance

挖好挖滿

重新模擬線上交易-8個tempdb檔案

#Table實驗組共執行了19秒(比剛剛快15秒)，平均每次0.12秒(比剛剛快0.18秒)，進步超多!

@table實驗組共執行了16秒(比剛剛快13秒)，平均每次0.12秒(比剛剛快0.16秒)

監控剛剛的dmv也沒再發現等待tempdb的情形(每0.5秒監控一次)。美好!

小結:

在頻繁使用#temp table或是@table variable情境下，增加tempdb檔案個數會有很大的效能提升。

tempdb檔案數	情境	單筆時間(秒)	整體時間(秒)
1	大量線上交易使用 #temp table	0.30	34
1	大量線上交易使用 @table variable	0.27	29
8	大量線上交易使用 #temp table	0.12	19
8	大量線上交易使用 @table variable	0.11	16