建立高效能 SQL Server 執行個體

本教學課程說明如何建立效能優異且執行 SQL Server 的 Google Compute Engine VM 執行個體,並逐步指導您建立執行個體及設定 SQL Server,讓 Google Cloud Platform 的效能保持在最佳狀態。下文將介紹多種設定選項,方便您自行調整系統效能。

本教學課程使用的版本為 SQL Server Standard Edition 2014,因此並非所有設定選項均適用於每一位使用者,而且某些設定選項可能無法為工作負載提升明顯效能。

目標

  • 設定 Compute Engine 執行個體和磁碟。
  • 設定 Windows 作業系統。
  • 設定 SQL Server。

費用

本教學課程使用的 Cloud Platform 可計費元件包括:

  • Google Compute Engine 高記憶體使用率執行個體
  • Compute Engine SSD 永久磁碟儲存空間
  • Compute Engine 本機 SSD 磁碟儲存空間
  • SQL Server Standard 預先設定映像檔

Pricing Calculator 可以根據您預計的使用量來產生預估費用。點選以上連結即可查看本教學課程中所用產品的預估費用,每小時可能超過 4 美元,每個月則可能超過 3000 美元。 初次使用 Cloud Platform 的使用者可能符合申請免費試用的資格。

事前準備

  1. 登入您的 Google 帳戶。

    如果您沒有帳戶,請申請新帳戶

  2. 選取或建立 Google Cloud Platform 專案。

    前往「Manage resources」(管理資源) 頁面

  3. 請確認您已啟用 Google Cloud Platform 專案的計費功能。

    瞭解如何啟用計費功能

  4. 如果您的本機並非使用 Windows,請安裝第三方 RDP 用戶端 (例如 FusionLabs 提供的 Chrome RDP)。

建立 Compute Engine 執行個體和磁碟

建立具備 SQL Server 和 2 個永久磁碟的 Compute Engine。

  • 本機 SSD 可以為 tempdb 和 Windows 分頁檔提供高效能的儲存位置。

    使用本機 SSD 時,請留意一些重要注意事項。當您透過 Windows 關閉執行個體或透過 API 重設執行個體時,系統將會移除本機 SSD,這項操作將會導致執行個體無法開機。如要讓機器恢復運作,您必須卸離永久磁碟,並且使用永久磁碟建立新的執行個體,然後再定義新的本機 SSD。開機之後,您可能必須格式化新磁碟並重新開機。因此,除非您已準備重新建置執行個體,否則我們建議您不要在本機 SSD 上永久儲存重要資料或關閉執行個體。

  • SSD 永久磁碟可以為資料庫檔案提供高效能的儲存空間。

    SSD 永久磁碟效能會依據使用的 CPU 數量和磁碟大小而調整。在 32 個 vCPU 和 1 TB 磁碟的機器上,最高效能為每秒 40,000 次讀取作業和 30,000 次寫入作業,讀取和寫入總處理量則分別為每秒 800 MB 和每秒 400 MB。這些數值代表連結至虛擬機器的所有永久磁碟的總和 (包括 C:\` drive.)。 This is why you should create a local SSD to offload all the IOPS needed for the paging file,tempdb、暫存資料和備份需要的所有 IOPS。

如要進一步瞭解磁碟效能,請參閱最佳化永久磁碟和本機 SSD 的效能

建立 Compute Engine 執行個體

建立已在 Windows Server 2012 上預先安裝 SQL Server 2014 Standard 的 VM。

  1. 在 Google Cloud Platform 主控台中,前往「VM 執行個體」頁面。

    前往「VM 執行個體」頁面

  2. 點選 [建立執行個體] 按鈕。

  3. 將執行個體命名為「ms-sql-server」。

  4. 將「Machine type」(機器類型) 設為 [16 vCPUs 104 GB, n1-highmem-16] (16 個 vCPU、104 GB、n1-highmem-16)

  5. 在「Boot disk」(開機磁碟) 部分中,按一下 [Change] (變更) 來開始設定開機磁碟。

  6. 在「Application images」(應用程式映像檔) 分頁中,選擇 [SQL Server 2014 Standard on Windows Server 2012 R2]

  7. 在「Boot disk type」(開機磁碟類型) 部分中,選取 [Standard persistent disk] (標準永久磁碟)

  8. 在「大小 (GB)」區段中,將開機磁碟大小設為 50 GB。

  9. 按一下 [Select] (選取)。

  10. 按一下 [Management, security, disks, networking, sole tenancy] (管理、安全性、磁碟、網路、單獨租用)

  11. 按一下 [Disks] (磁碟)。

  12. 在「Additional disks」(其他磁碟) 底下,按一下 [Add new disk] (增加新磁碟) 來建立其他磁碟。

  13. 請勿變更「Name」(名稱) 欄位。

  14. 在「Type」(類型) 下方選取 [Local SSD scratch disk (maximum 8)] (本機 SSD 暫存磁碟 (最多 8 個))

  15. 按一下 [Done] (完成) 以完成這個磁碟的建立程序。

  16. 在「Additional disks」(其他磁碟) 底下,按一下 [Add new disk] (增加新磁碟) 來建立第二個磁碟。

  17. 請勿變更「Name」(名稱) 欄位。

  18. 在「Type」(類型) 下方選取 [SSD persistent disk] (SSD 永久磁碟)

  19. 在「Source type」(來源類型) 下方選取 [Blank disk] (空白磁碟)

  20. 按一下 [Done] (完成) 以完成次要磁碟的建立程序。

  21. 按一下 [Create] (建立) 以建立執行個體。

設定 Windows

現在您已成功設定執行 SQL Server 的執行個體,接下來請連線至執行個體並設定 Windows 作業系統。設定完成後,接下來的章節會指導您設定 SQL Server。

連線至執行個體

  1. 前往 GCP 主控台的「VM 執行個體」頁面。

    前往「VM 執行個體」頁面

  2. 在「Name」(名稱) 資料欄中,按一下執行個體的名稱 ms-sql-server

  3. 在執行個體詳細資料頁面的最上方,按一下 [Set Windows Password] (設定 Windows 密碼) 按鈕。

  4. 指定使用者名稱。

  5. 點選 [設定],為 Windows 執行個體產生新密碼。

  6. 請記下這組使用者名稱和密碼,以便登錄執行個體。

  7. 透過遠端桌面協定連線至您的執行個體:

    • 如果您已安裝 FusionLabs 提供的 Chrome RDP,請按一下執行個體詳細資料頁面最上方的 [RDP] (遠端桌面協定) 按鈕。
    • 如果使用的是其他 RDP 用戶端 (例如 Windows 遠端桌面連線),請點選 [RDP] (遠端桌面協定) 按鈕旁的溢位選單來下載 RDP 檔案,然後透過用戶端開啟 RDP 檔案。

設定磁碟區

建立及格式化磁碟區:

  1. 在「Start」(開始) 功能表中搜尋並開啟「Server Manager」(伺服器管理員)。
  2. 選取 [File and Storage Services] (檔案和存放服務),然後選取 [Disks] (磁碟)

    本機 SSD 的名稱為 Google EphemeralDisk,但本機 SSD 和永久 SSD 的磁碟分區皆標記為 Unknown

    尋找 Google EphemeralDisk 項目

  3. 在名稱為「Google Ephemeral Disk」的 375 GB 本機 SSD 磁碟上按一下滑鼠右鍵,然後選取 [New Volume] (新增磁碟區)

  4. 繼續使用預設值,然後選擇 [P:] 做為磁碟機標籤,將其設為用於存放分頁檔的磁碟。

  5. 在檔案系統設定的步驟中,將「Allocation unit size」(配置單位大小) 變更為 8192,然後在「Volume label」(磁碟區標籤) 旁輸入「pagefile」。

    新增磁碟區精靈

  6. 對第二個 SSD 永久磁碟重複執行前述的相同步驟,並且變更以下 3 項設定:

  • 選擇 [D:] 做為磁碟機代號。
  • 將「配置單位大小」 變更為「32k」。

    雖然 Microsoft 建議將 SQL Server 資料和記錄磁碟格式化為 64k,但 GCP 內的永久磁碟技術與 32k 的對齊效果更好。這項變更同時也會降低計入永久磁碟 IO 上限的磁碟作業次數。

  • 在「Volume label」(磁碟區標籤) 旁輸入「sqldata」。

修正「Failed to mount path - Invalid Parameter」錯誤

如果發生這項錯誤,請按照下列步驟操作:

  1. 點選 [關閉]
  2. 點選右上角的重新整理磁碟圖示。
  3. 按一下清單中的 500 GB 永久磁碟。
  4. 在「Volumes」(磁碟區) 面板中的磁碟區上按一下滑鼠右鍵,然後選擇 [Manage Drive Letter and Access Paths] (管理磁碟機代號和存取路徑)

    管理磁碟機代號和存取路徑

  5. 選擇 [D:] 做為磁碟機代號。

  6. 點選 [確定]

移動 Windows 分頁檔案

現在您已成功建立並掛接新磁碟區,接著請將 Windows 分頁檔案移至本機 SSD,進而釋放永久磁碟 IOPS,並改善虛擬記憶體的存取時間。

  1. 在「Start」(開始) 選單中搜尋「View advanced system settings」(檢視進階系統設定),然後開啟對話方塊。
  2. 按一下 [Advanced] (進階) 分頁標籤,然後按一下「Performance」(效能) 部分中的 [Settings] (設定)
  3. 在「Virtual memory」(虛擬記憶體) 部分中,按一下 [Change] (變更) 按鈕。
  4. 取消勾選 [Automatically manage paging file size for all drives] (自動管理所有磁碟的分頁檔大小)。目前「C:」磁碟上應該已有分頁檔,因此您必須自行移動分頁檔。
  5. 點選 [C:],然後按一下 [No paging file] (沒有分頁檔) 圓形按鈕。
  6. 按一下 [Set] (設定) 按鈕。
  7. 如要建立新分頁檔,請點選 [P:] 磁碟,然後按一下 [System managed size] (系統管理大小) 圓形按鈕。
  8. 按一下 [Set] (設定) 按鈕。
  9. 連續點選 3 次 [OK] (確定),結束「進階系統內容」。

    歡迎參考 Microsoft 支援服務所發佈的其他虛擬記憶體相關提示

變更電源設定檔

將電源設定檔從「High-Performance」變更為「Balanced」。

  1. 在「Start」(開始) 選單中搜尋「Choose a Power Plan」(選擇電源計劃),然後開啟點源選項。
  2. 選取 [High Performance] (高效能) 圓形按鈕。
  3. 結束對話方塊。

設定 SQL Server

您可以利用 SQL Server Management Studio 執行大多數的管理工作。安裝 SQL Server 2014 預先設定映像檔會一併安裝 Management Studio,但如果您使用的是 SQL Server 2016 映像檔,就必須手動下載並安裝 Management Studio。安裝完成後,請執行 Management Studio,然後點選 [連線] 以連線至預設資料庫。

移動資料和記錄檔

SQL Server 預先設定映像檔會在 C:\` drive, including the system databases. 磁碟安裝包括系統資料庫在內的所有內容。In order to optimize your setup, move those files to the new移至您建立的「D:」磁碟,並且在「D:」磁碟上建立所有新資料庫。由於您使用的是 SSD 永久磁碟,因此您不需將資料檔案和記錄檔分別儲存在不同的磁碟分區中。

您可以透過以下 2 種方式將已安裝的內容移動到次要磁碟:使用安裝程式或手動移動檔案。

使用安裝程式

如要使用安裝程式,請執行 c:\setup.exe,然後在次要磁碟上指定新的安裝路徑。

手動移動檔案

如何移動系統資料庫並將 SQL Server 設為將資料和記錄檔儲存在相同磁碟區中:

  1. 建立名稱為「D:\SQLData」的新資料夾。
  2. 開啟命令提示字元視窗。
  3. 輸入下列指令,將完整權限授予「NT Service\MSSQLSERVER」:

    icacls D:\SQLData /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  4. 使用 Management Studio 並按照相關指南移動系統資料庫及變更新資料庫的預設檔案位置

  5. 如要使用「Report Server」(報表伺服器) 功能,請一併移動 ReportServerReportServerTempDB 檔案。

移動主要檔案並重新啟動後,您必須將系統設為指向模型和 MSDB 資料庫的新位置。以下是可在 Management Studio 中執行的協助程式指令碼:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

執行以上指令之後:

  1. 使用 services.msc 嵌入式管理單元來停止 SQL Server 資料庫服務。
  2. 使用 Windows 檔案總管將 C:\` drive where the 磁碟中的實體檔案移動至 Master 資料庫 (位於 D:database was located to the 目錄)。
  3. 啟動 SQL Server 資料庫服務。

設定系統權限

移動系統資料庫之後,您必須修改部分其他設定。請先將相關權限授予為了執行 SQL Server 程序而建立的 Windows 使用者帳戶「NT Service\MSSQLSERVER」。

授予「Lock Pages in Memory」權限

Lock Pages in Memory」群組原則權限可避免 Windows 將實體記憶體中的分頁移動至虛擬記憶體。為了讓實體記憶體維持自由運用及妥善整理的狀態,Windows 會嘗試將較早建立、極少修改的分頁移動至磁碟上的虛擬記憶體分頁檔中。

SQL Server 會將重要資訊儲存在記憶體中 (例如資料表結構、執行計劃和快取查詢),因此系統會選擇將極少修改的部分這類資訊移動至分頁檔中,但這麼做可能會導致 SQL Server 的效能下降。將「Lock Pages in Memory」權限授予 SQL Server 服務帳戶即可避免移動這類資訊。

步驟如下:

  1. 在「Start 」(開始) 選單中搜尋「Edit Group Policy」(編輯群組原則) 來開啟主控台。
  2. 依序展開 [Local Computer Policy] (本機電腦原則) > [** Computer Configuration**] (電腦設定) > [Windows Settings] (Windows 設定) > [Local Policies] (本機原則) > [User Rights Assignment] (使用者權限指派)。
  3. 搜尋「Lock pages in memory」(鎖定記憶體中的分頁)
  4. 點選 [新增使用者或群組]
  5. 搜尋「NT Service\MSSQLSERVER」。
  6. 如果看到多個名稱,請按兩下「MSSQLSERVER」名稱。
  7. 連續按兩次 [**OK**] (確定)。
  8. 保持開啟「Group Policy Editor」(群組原則編輯器) 主控台。

鎖定分頁

授予「Perform volume maintenance tasks」權限

根據預設,當應用程式向 Windows 要求部分磁碟空間時,作業系統會先尋找大小適中的磁碟空間區塊,然後清空整個磁碟區塊中的資料,再將其分配給應用程式。由於 SQL Server 的優勢在於增加檔案及填滿磁碟空間,因此這種行為無法達到最佳效能。

有一個單獨 API (通常稱為「檔案立即初始化」) 可用於為應用程式分配磁碟空間,但這項設定僅適用於資料檔案。我們會在之後說明增加記錄檔案的章節中介紹這項設定。如要使用「檔案立即初始化」功能,執行 SQL Server 程序的服務帳戶必須具備另一項「Perform volume maintenance tasks」群組原則權限。

  1. 在「Group Policy Editor」(群組原則編輯器) 中,搜尋「Perform volume maintenance tasks」(執行磁碟區維護工作」)。
  2. 新增「NT Service\MSSQLSERVER」帳戶 (如上一節所述)。
  3. 重新啟動 SQL Server 程序即可啟用這兩項設定。

設定 TempDB

這項功能會為每個 CPU 建立一個 TempDB 檔案,原本是用於最佳化 SQL Server CPU 使用量的最佳做法。但由於 CPU 數量會隨著時間而增加,因此遵循這個做法可能會導致效能下降。建議您建立 4 個 TempDB 檔案;評估系統效能時,在極少數的情況下,您可能必須將 TempDB 檔案數量提高到最多 8 個。

您可以透過 SQL Server Management Studio 執行 T-SQL 指令碼,將 TempDB 檔案移動至位於「P:」磁碟的資料夾中。

  1. 建立目錄 p:\tempdb
  2. 將完整安全性存取權授予「NT Service\MSSQLSERVER」使用者帳戶。

    icacls p:\tempdb /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  3. 透過 SQL Server Management Studio 執行以下指令碼,移動 TempDB 資料檔案和記錄檔:

    USE Master
    GO
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
    GO
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
    GO
    
  4. 重新啟動 SQL Server。

  5. 執行以下指令碼,修改檔案大小並為新的 TempDB 建立三個額外的資料檔案。

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    GO
    

    如果使用的是 SQL Server 2016,那麼在您執行上述步驟之後,您必須另外移除 3 個 TempDB 檔案:

    ALTER DATABASE [tempdb] REMOVE FILE temp2;
    ALTER DATABASE [tempdb] REMOVE FILE temp3;
    ALTER DATABASE [tempdb] REMOVE FILE temp4;
    
  6. 再次重新啟動 SQL Server。

  7. 從「C:」磁碟的原始位置中刪除 modelMSDBmastertempdb 檔案。

您已成功將 TempDB 檔案移至本機 SSD 磁碟分區。如前文所述,這麼做可能會帶來一些風險,但如果這些檔案因任何原因而遺失,SQL Server 仍然會重新建立 TempDB 檔案。移動 TempDB 可以提高本機 SSD 的效能,同時降低永久磁碟的 IOPS 使用量。

設定「max degree of parallelism

預設情況下,「max degree of parallelism」的建議設定會與伺服器上的 CPU 數量保持一致。不過,如果在 16 或 32 個平行區塊中執行查詢並合併結果,執行速度可能會比在單一程序中來得慢。如果使用 16 或 32 核心的執行個體,您可以執行以下 T-SQL 指令將「max degree of parallelism」值設為 8:

USE Master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

設定「max server memory

預設情況下,這項設定值是一個相當大的數字,但我們建議您將其設為可用實體記憶體減去作業系統和系統負擔所佔用記憶體 (可能有數 GB 之多) 後的數字 (單位為 MB)。以下的 T-SQL 範例會將「max server memory」調整為 100 GB。您可以根據執行個體狀況調整這個值的大小。

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

完成設定

請再次重新啟動執行個體,確認所有新設定均已生效。您的 SQL Server 系統現已設定完成,您可以著手準備建立自己的資料庫,並開始測試特定工作負載。如要進一步瞭解操作活動、其他效能注意事項以及 Enterprise Edition 提供的功能,請參閱 SQL Server 最佳做法指南。

清理

完成 PHP 教學課程後,您可以清除在 Google Cloud Platform 建立的資源,這樣日後就不需再為這些資源付費。下列各節將說明如何刪除或停用這些資源。

刪除專案

如要避免被收費,最簡單的方法就是刪除您在教學課程中建立的專案。

如何刪除專案:

  1. 前往 GCP 主控台的「Projects」(專案) 頁面。

    前往專案頁面

  2. 在專案清單中選取要刪除的專案,然後按一下 [Delete] (刪除)
  3. 在對話方塊中輸入專案 ID,按一下 [Shut down] (關閉) 即可刪除專案。

刪除執行個體

刪除 Compute Engine 執行個體:

刪除永久磁碟

如何刪除永久磁碟:

  1. 前往 GCP 主控台的「磁碟」頁面。

    前往磁碟頁面

  2. 找出您要刪除的磁碟,然後選取磁碟名稱旁的核取方塊。

  3. 按一下頁面頂端的 [Delete] (刪除) 按鈕。

後續步驟

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Compute Engine