在Microsoft Azure Virtual Machines中使用SSD來存放TempDB和啟用緩衝集區擴充

在Microsoft Azure Virtual Machines中使用SSD來存放TempDB和啟用緩衝集區擴充

說明

日前Microsoft Azure推出D系列的Virtual Machines(以下簡稱Azure VM)讓較要求CPU、磁碟效能以及記憶體的應用程式,有更適合的Azure VM層次可以使用,D系列尤其強調磁碟效能,全系列採SSD來存放虛擬硬碟(VHD),但雲端服務的計費模式採用多少算多少,若您用到D系列的Azure VM可以享受更高的效能,當然也必須支付更高的費用,詳細價目資訊請見下表以及參考資料一節。

image

image

除了應用程式外,SQL Server的TempDB也非常適合放在SSD中運行來提升效能,若您的SQL Server需要使用到大量的暫存資料表、資料表變數及維護線上索引作業,當您把SQL Server部署在Azure VM中,就非常適合將TempDB做適當的設定,進而善用SSD儲存體的優點。再者,若您使用的是SQL Server 2014,那麼不能錯過使用緩衝集區擴充(Buffer Pool Extensions)這個新功能,透過這個功能將SSD儲存體做為記憶體的延伸,進而提升SQL Server的I/O輸送量(throughput),有關緩衝集區擴充的介紹,請見參考資料一節。

眾所皆知,Azure VM的D磁碟是用來存放暫存資料,不保證存放在D磁碟的資料的可用性,官方也強調重要資料以及需要保存的資料,應避免放在D磁碟,而SQL Server TempDB存放的是暫存資料及物件,會在每次SQL Server重新啟動時自動重建,因此不需擔心資料遺失的問題;另緩衝集區擴充只存放乾淨的頁面(Clean Pages),有被異動過的資料不會存放在緩衝集區擴充之中,因此也不用擔心資料遺失的問題。

SSD 緩衝集區延伸模組架構

因此,當您使用D系列Azure VM,可以利用下列步驟來將TempDB和緩衝集區擴充放在VM的D磁碟,透過SSD儲存體所帶來的高I/O輸送量(throughput)來提升SQL Server的效能,詳細步驟請見下一節。

建立TempDB和緩衝集區擴充資料夾

請在D磁碟中建立存放TempDB資料檔和交易記錄檔以及緩衝集區擴充檔案所需的資料夾,或是您也可以建立一個資料夾就好,同時存放TempDB和緩衝集區擴充檔案。

本文示範將TempDB放在D:\TempDB資料夾,緩衝集區擴充的檔案放在D:\BPE資料夾(如下圖)。

image

修改TempDB的存放位置

為了讓TempDB的資料庫檔案和交易記錄檔由預設安裝路徑改放到D:\TempDB,必須執行下列的T-SQL指令碼來搬移TempDB。


GO

ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\TempDB\tempdb.mdf') 
GO

ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\TempDB\templog.ldf') 
GO

啟用緩衝集區擴充

一般建議實體記憶體大小 (max_server_memory)與緩衝集區擴充大小比率為 1:16 以下,建議 1:4 到 1:8 的範圍內的比率可能會比較適當,若您知道可以指派給SQL Server的最大記憶體大小是多少,可以使用下列T-SQL指令碼來查詢:


GO

RECONFIGURE
GO

EXEC sp_configure 'max server memory'
GO

接著執行下列的T-SQL來啟用緩衝集區擴充,並將緩衝集區擴充設定為10GB並且存放在D:\BPE資料夾。


SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\BPE\ExtensionFile.BPE' , SIZE = 10GB)

修改SQL Server資料庫引擎和SQL Server Agent的服務啟動模式

為了避免存放在D磁碟的TempDB以及緩衝集區擴充資料夾被刪掉,需要做點手腳,先將SQL Server資料庫引擎和SQL Server Agent的服務啟動模式設為手動,請從SQL Server Configuration Manager將上述兩個服務的Start Mode設為Manual。

image

image

建立啟動SQL Server資料庫引擎和SQL Server Agent所需的PowerShell

因為我們已經將TempDB搬移到D:\TempDB資料夾,並且將緩衝集區擴充的檔案放在D:\BPE資料夾,因此在啟動服務前必須先檢查該資料夾是否存在,若不存在則需在啟動服務前先行建立,請輸入下列PowerShell指令碼後,將檔案儲存為C:\StartSQLServices.ps1。

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"

$TempDBFolder="D:\SQLTEMP"
if (!(test-path -path $TempDBFolder)) {
    New-Item -ItemType directory -Path $TempDBFolder
}

$BPEFolder="D:\BPE"
if (!(test-path -path $BPEFolder)) {
    New-Item -ItemType directory -Path $BPEFolder
}

Start-Service $SQLService
Start-Service $SQLAgentService

再來必須修改執行PowerShell的安全性設定,將執行政策設定為RemoteSigned,以使得從網路下載的PowerShell檔案要求要簽署以確保發行者是安全的發行者,而本機的PowerShell檔案可以忽略簽署。

Set-ExecutionPolicy RemoteSigned

image

建立工作排程執行PowerShell

開啟Administrative Tools,點選Task Scheduler來建立工作排程。

image

點選右側的Create Basic Task來建立基本工作,輸入工作名稱Start SQL Services。

image

在Trigger步驟中選擇When the computer starts,使得排定工作會在電腦啟動時被觸發。

image

由於我們要執行的是一段事先準備好的PowerShell指令碼,因此在Action步驟需選擇Start a program。

image

於Program/script欄位輸入【PowerShell.exe】,以及add arguments欄位輸入【-file "C:\StartSQLServices.ps1"】(在此檔案名稱必須要用雙引號包起來,若您使用單引號可能會發生執行工作時回傳0xfffd0000的錯誤)。

image

最後您應該會看到如下圖的結果。

image

工作排程建立後,請將Security options調整為Run whether user is logged on or not,並指派適當的使用者帳戶。

image

測試工作排程

由於我們已經事先將SQL Server資料庫引擎和SQL Server Agent設為手動啟動,接著在電腦啟動時透過工作排程來檢查TempDB以及緩衝擴充集區所需的資料夾,並執行PowerShell來啟動SQL Server資料庫引擎和SQL Server Agent服務。若前面的步驟都設定正確,在工作排程執行歷程中可以看到執行成功的紀錄(如下圖)。

image

以及從SQL Server Configuration Manager也可以看到相關的服務已經被順利啟動。

image

 

參考資料

緩衝集區擴充

tempdb 資料庫

Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions

虛擬機器定價

New D-Series of Azure VMs with 60% Faster CPUs, More Memory and Local SSD Disks

管理磁碟及映像