建立高效能 SQL Server 執行個體

這個教學課程說明如何建立 Compute Engine VM 執行個體，並確保執行個體採用的 SQL Server 具備最佳效能。本教學課程將逐步說明如何建立執行個體，然後在Google Cloud上設定 SQL Server，以獲得最佳效能。您可以使用多個設定選項來調整系統效能。

本教學課程使用 SQL Server Standard Edition 2022，因此本指南中介紹的設定選項不一定適用於所有使用者，而且並非所有選項都能為每個工作負載帶來顯著的效能提升。

目標

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

費用

本教學課程使用 Google Cloud的計費元件，包括：

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

Pricing Calculator 可根據您預計的使用量來估算費用。提供的連結會顯示本教學課程所用產品的費用估算值，每小時可能超過 4 美元，每月則超過 3,000 美元。

初次使用 Google Cloud 的使用者可能符合免費試用期資格。

事前準備

登入 Google Cloud 帳戶。如果您是 Google Cloud新手，歡迎 建立帳戶，親自體驗產品的實際應用成效。新客戶還能獲得價值 $300 美元的免費抵免額，能用於執行、測試及部署工作負載。

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

建立含有磁碟的 Compute Engine VM

如要建立高效能 SQL Server 執行個體，請先建立含有 SQL Server 和兩個永久磁碟磁碟區的 VM 執行個體。

Persistent Disk 注意事項

如要為 VM 選取永久磁碟區類型，請考量下列事項：

• 本機 SSD 磁碟可為 tempdb 和 Windows 分頁檔提供高效能位置。

  使用本機 SSD 磁碟時，請注意以下重要事項。 如果您從 Windows 關機執行個體，或使用 API 重設執行個體，系統就會移除本機 SSD 磁碟。這項操作會導致執行個體無法啟動。 如要讓機器再次運作，您需要卸載永久磁碟、使用這些磁碟建立新的執行個體，然後定義新的本機 SSD 磁碟。開機之後，您可能也需要為新的磁碟設定格式並重新開機。因此，除非您已準備好重建執行個體，否則不應將重要資料永久儲存在本機 SSD 磁碟，也不應關閉執行個體電源。

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

  Persistent Disk 的效能是根據 CPU 數量和磁碟大小計算而得。使用 32 個 vCPU 和 1 TB 磁碟時，效能最高可達每秒 40,000 次讀取作業 (ops) 和 30,000 次寫入作業 (ops)。讀取和寫入的總持續處理量分別為每秒 800 MB 和每秒 400 MB。這些指標代表附加至虛擬機器的所有永續磁碟磁碟區總和，包括 C:\ 磁碟機。為確保效能一致，請建立本機 SSD 磁碟，並卸載分頁檔、tempdb、暫存資料和備份檔所需的所有 IOPS。

如要進一步瞭解磁碟效能，請參閱「設定磁碟以符合效能需求」。

建立含磁碟的 Compute Engine VM

如要建立預先安裝 Windows Server 2022 的 SQL Server 2022 Standard VM，請按照下列步驟操作：

1. 前往 Google Cloud 控制台的「建立執行個體」頁面。

   前往「建立執行個體」

2. 在「Name」(名稱) 中輸入 ms-sql-server。

3. 在「機器設定」部分，選取「一般用途」，然後執行下列操作：

   1. 在「系列」清單中，按一下「N2」。
   2. 在「機型」清單中，按一下「n2-highmem-16 (16 個 vCPU，128 GB 記憶體)」。

4. 在「Boot disk」(開機磁碟) 部分，按一下「Change」(變更)，然後執行下列操作：

   1. 在「Public images」(公開映像檔) 分頁中，按一下「Operating system」(作業系統) 清單，然後選取「SQL Server on Windows Server」(Windows Server 上的 SQL Server)。
   2. 在「版本」清單中，按一下「SQL Server 2022 Standard on Windows Server 2022 Datacenter」。
   3. 在「Boot disk type」(開機磁碟類型) 清單中，按一下「Standard persistent disk」(標準永久磁碟)。
   4. 在「Size (GB)」(大小 (GB)) 欄位中，將開機磁碟大小設為 50 GB。
   5. 如要儲存開機磁碟設定，請按一下「選取」。

5. 展開「Advanced options」(進階選項) 區段，然後執行下列操作：

   1. 展開「磁碟」部分。

   2. 如要建立本機磁碟，請按一下「新增本機 SSD」，然後執行下列操作：

      1. 在「介面」清單中，選取符合系統效能需求的通訊協定。
      2. 在「Disk capacity」(磁碟容量) 清單中，選取支援 tempdb 檔案預期大小的磁碟容量。
      3. 如要完成建立這個磁碟，請按一下「儲存」。

   3. 如要建立其他磁碟，請按一下「新增磁碟」。

      1. 「名稱」欄位請勿變更。
      2. 在「Disk source type」(磁碟來源類型) 清單中，選取「Blank disk」(空白磁碟)。
      3. 在「Disk type」(磁碟類型) 清單中，選取「SSD persistent disk」(SSD 永久磁碟)。
      4. 在「大小」欄位中，輸入可容納資料庫大小的磁碟大小。
      5. 按一下「Save」(儲存)，即可完成第二個磁碟的建立作業。

6. 按一下「Create」(建立)，即可建立 VM。

設定 Windows

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

連線至執行個體

1. 前往 Google Cloud 控制台的「VM instances」(VM 執行個體) 頁面。

   前往 VM 執行個體

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

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

4. 指定使用者名稱。

5. 按一下 [Set] (設定)，為這個 Windows 執行個體產生新的密碼。

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

7. 使用 RDP 連線至執行個體。

設定磁碟區

建立及格式化磁碟區：

1. 在「開始」選單中搜尋並開啟「電腦管理」。
2. 在「儲存空間」部分下方，選取「磁碟管理」。
3. 系統提示初始化磁碟時，請接受預設選項，然後按一下「確定」。

4. 為本機 SSD 磁碟建立分割區：

   如要找出本機 SSD 磁碟，請在磁碟上按一下滑鼠右鍵，然後選取「內容」。 如果是 SCSI 介面，本機 SSD 磁碟的屬性名稱為 Google EphemeralDisk；如果是 NVMe 介面，則為 nvme_card。本機 SSD 磁碟和永久 SSD 都會標示為具有 Unallocated 分割區。

   1. 如果 VM 只包含 1 個本機 SSD 磁碟，請按照下列步驟操作：

      1. 在磁碟機清單下方，對 374.98 GB 的本機 SSD 磁碟按一下滑鼠右鍵，然後選取「新增簡單磁碟區」。
      2. 在「歡迎」畫面中，按一下「下一步」，啟動磁碟區精靈。
      3. 在「Specify Volume Size」(指定磁碟區大小) 步驟中，將磁碟區大小保留為預設值，然後按一下「Next」(下一步)繼續操作。
      4. 在「指派磁碟機代號或路徑」步驟中，選擇「P:」做為磁碟機代號，然後按一下「下一步」繼續操作。

      5. 在「格式化磁碟區」步驟中，將「配置單元大小」變更為 8192，並在「磁碟區標籤」中輸入「pagefile」。按一下「下一步」繼續操作。

         

      6. 按一下「完成」，完成磁碟區精靈。

   2. 如果 VM 包含多個本機 SSD 磁碟機，請按照下列步驟操作：

      1. 在磁碟機清單下方，對第一個 374.98 GB 的本機 SSD 磁碟按一下滑鼠右鍵，然後選取「新增帶區磁碟區」。
      2. 在「歡迎」畫面中，按一下「下一步」，啟動磁碟區精靈。

      3. 在「選取磁碟」步驟中，將所有大小為 383,982 MB 的可用磁碟新增至「已選取」部分。按一下「下一步」繼續操作。

         

      4. 在「指派磁碟機代號或路徑」步驟中，選擇「P:」做為磁碟機代號，然後按一下「下一步」繼續操作。

      5. 在「格式化磁碟區」步驟中，將「配置單元大小」變更為 8192，並在「磁碟區標籤」中輸入「pagefile」。按一下「下一步」繼續操作。

         

      6. 按一下「完成」，完成磁碟區精靈。

5. 重複上述步驟，為 SSD 磁碟建立「New Simple Volume」，並進行下列三項變更：

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

   • 將「Allocation unit size」(分配單元大小) 設為 64k。

     如要進一步瞭解如何選取配置單元大小，請參閱「SQL Server 執行個體最佳做法」。

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

移動 Windows 分頁檔案

現在已建立並掛接新磁碟區，請將 Windows 分頁檔移至本機 SSD 磁碟，這樣就能釋出永久磁碟 IOPS，並縮短虛擬記憶體的存取時間。

1. 在「Start」(開始) 選單中搜尋「View advanced system settings」(檢視進階系統設定)，然後開啟對話方塊。
2. 按一下 [Advanced] (進階) 分頁標籤，然後按一下「Performance」(效能) 部分中的 [Settings] (設定)。
3. 在「Virtual memory」(虛擬記憶體) 部分中，按一下 [Change] (變更) 按鈕。
4. 取消勾選「為所有磁碟機自動管理分頁檔大小」核取方塊。C:\ 磁碟中應已建立分頁檔案，因此您必須自行移動分頁檔案。
5. 依序點選 [C:] 和 [No paging file] (沒有分頁檔案) 圓形按鈕。
6. 按一下 [Set] (設定) 按鈕。
7. 如要建立新分頁檔，請點選 [P:] 磁碟，然後按一下 [System managed size] (系統管理大小) 圓形按鈕。
8. 按一下 [Set] (設定) 按鈕。

9. 連續點選三次 [OK] (確定)，退出進階系統屬性。

   Microsoft 支援團隊已發布虛擬記憶體設定的其他提示。

變更電源設定檔

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

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

設定 SQL Server

您可以使用 SQL Server Management Studio 執行大部分的管理工作。SQL Server 的預先設定映像檔已安裝 Management Studio。啟動 Management Studio，然後按一下「Connect」(連線)，連線至預設資料庫。

移動資料和記錄檔

SQL Server 預先設定的映像檔會在 C:\ 磁碟中安裝包括系統資料庫在內的所有內容。如要最佳化設定，請將這些檔案移至新建立的 D:\ 雲端硬碟。此外，請記得在D:\磁碟機上建立所有新資料庫。由於您使用的是 SSD，因此不需要將資料檔案和記錄檔儲存在不同的磁碟分割區。

您可以透過以下兩個方式將已安裝的內容移至次要磁碟：使用安裝程式或手動移動檔案。

使用安裝程式

如要使用安裝程式，請執行 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」(報表伺服器) 功能，請一併移動 ReportServer 和 ReportServerTempDB 檔案。

重要事項： 請勿修改 tempdb 檔案，因為您必須先變更部分權限設定，然後將資料庫移至本機 SSD 磁碟區。

移動主要設定資料庫檔案並重新啟動後，您需要設定系統，指向模型和 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 檔案總管找到 master 資料庫所在的 C:\ 磁碟，並將當中的實體檔案移至 D:\SQLData 目錄。
3. 啟動 SQL Server 資料庫服務。

設定系統權限

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

授予 Lock Pages in Memory 權限

「Lock Pages in Memory」群組政策權限可以避免 Windows 將實體記憶體中的分頁移至虛擬記憶體。為了讓實體記憶體保持隨時可用和妥善整理的狀態，Windows 會嘗試將較早建立、極少修改的分頁移至磁碟中的虛擬記憶體分頁檔案。

SQL Server 會將資料表結構、執行計畫和快取查詢等重要資訊儲存在記憶體中，因此系統會選擇將極少修改的部分這類資訊移動至分頁檔中，不過這麼做可能會導致 SQL Server 的效能降低。為 SQL Server 的服務帳戶授予群組政策 Lock Pages in Memory 權限，即可避免發生這種交換作業。

步驟如下：

1. 在「Start」(開始) 選單中搜尋「Edit Group Policy」(編輯群組政策) ，以便開啟主控台。
2. 依序展開「本機電腦原則」「電腦設定」「Windows 設定」「安全性設定」「本機原則」「使用者權限指派」。
3. 搜尋並按兩下「將網頁鎖定在記憶體中」。
4. 點選 [新增使用者或群組]。
5. 搜尋「NT Service\MSSQLSERVER」。
6. 如果看到多個名稱，請按兩下「MSSQLSERVER」名稱。
7. 連續按兩次 [OK] (確定)。
8. 保持開啟「Group Policy Editor」(群組政策編輯器) 主控台。

重要注意事項：完成上述步驟並重新啟動 SQL Server 之後，您可以查看 SQL Server 記錄檔來確認設定是否已經生效。記錄檔中應該會包含類似於「Using locked pages in the memory manager」(在記憶體管理員中使用鎖定的頁面) 的項目。

授予 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 中執行 Transact-SQL (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:\ 磁碟中的原始位置刪除 model、MSDB、master 和 tempdb 檔案。

您已成功將 tempdb 個檔案移至本機 SSD 磁碟分割區。 如先前所述，這項作業會帶來一些風險，但如果因任何原因遺失，SQL Server 會重建 tempdb 檔案。移動 tempdb 可提升本機 SSD 的效能，並減少 Persistent Disk 磁碟區使用的 IOPS。

注意事項：本機 SSD 固定連接本機主機，因此不適合用來儲存永久或重要資料，因此，本機 SSD 僅適用於臨時儲存空間。在某些情況下，如果從執行個體內關閉 Windows，本機 SSD 磁碟也會遭到移除，您必須刪除並重新建立執行個體。

設定 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

在預設情況下，這項設定的數值會相當大，但我們會建議您將其設為以下計算結果：可用實體 RAM (單位為 MB) 減去作業系統和系統負擔占用的記憶體 (約為數 GB)。以下的 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 版功能，請參閱 SQL Server 最佳做法指南。

清除所用資源

完成教學課程後，您可以清除所建立的資源，這樣資源就不會繼續使用配額，也不會產生費用。下列各節將說明如何刪除或關閉這些資源。

刪除專案

如要避免付費，最簡單的方法就是刪除您為了本教學課程所建立的專案。

刪除專案的方法如下：

警告：刪除專案會造成以下影響：

• 專案中的所有內容都會遭到刪除。如果使用現有專案來進行本文中的任務，刪除專案將一併移除當中已完成的其他任務'。
• 自訂專案 ID 會消失。當您之前建立這個專案時，可能建立了想要在日後使用的自訂專案 ID。如要保留使用該專案 ID 的網址 (例如 appspot.com 網址)，請刪除在該專案中選取的資源，而不是刪除整個專案。

如果打算探索多種架構、教學課程或快速入門導覽課程，重複使用專案可避免超出專案配額限制。

1. 前往 Google Cloud 控制台的「Manage resources」(管理資源) 頁面。

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

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

刪除執行個體

如要刪除 Compute Engine 執行個體：

1. 前往 Google Cloud 控制台的「VM instances」(VM 執行個體) 頁面。

   前往 VM 執行個體

2. 勾選要刪除的執行個體核取方塊。
3. 如要刪除執行個體，請依序點選 「More actions」(更多動作) 和「Delete」(刪除)，然後按照指示操作。

刪除永久磁碟區

如要刪除永久磁碟，請按照下列步驟操作：

1. 前往 Google Cloud 控制台的「Disks」(磁碟) 頁面。

   前往「Disks」(磁碟)

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

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

後續步驟

• 針對 SQL Server 執行個體執行負載測試。
• 查看 SQL Server 最佳做法指南。
• 探索 Google Cloud 的參考架構、圖表和最佳做法。歡迎瀏覽我們的 Cloud Architecture Center。