建立高效能 SQL Server 執行個體

這個教學課程說明如何建立 Compute Engine VM 執行個體，並確保執行個體採用的 SQL Server 具備最佳效能。這個教學課程會逐步引導您建立執行個體及設定 SQL Server，以便在Google Cloud中享有最佳效能。您可以使用多個設定選項來調整系統效能。

這個教學課程使用的是 SQL Server Standard Edition 2022，因此文中提及的各個設定選項並不一定適用於所有使用者，而且某些設定選項可能無法顯著提升系統處理各項工作負載的效能。

建立含有磁碟的 Compute Engine VM

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

永久磁碟考量事項

如要為 VM 選取永久磁碟區類型，請參閱下列注意事項：

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

    使用本機 SSD 磁碟時，請留意以下幾個重要注意事項。在您使用 Windows 關閉執行個體或透過 API 重設執行個體時，系統會移除本機 SSD 磁碟，並導致執行個體無法開機。如要讓機器恢復運作，您必須卸離永久磁碟，並新建具備永久磁碟的執行個體，然後再定義新的本機 SSD 磁碟。開機之後，您可能也需要為新的磁碟設定格式並重新開機。因此，除非您已準備好重新建立執行個體，否則不建議您在本機 SSD 磁碟中永久儲存重要資料或關閉執行個體。

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

    永久磁碟效能會依據使用的 CPU 數量和磁碟大小而調整。使用 32 個 vCPU 和 1 TB 磁碟時，效能最高可達每秒 40,000 次讀取作業和 30,000 次寫入作業。讀取和寫入的總處理量分別為每秒 800 MB 和每秒 400 MB，這些測量結果代表附加至虛擬機器的所有永久磁碟區總和，包括 C:\ 磁碟。為確保效能一致，請建立本機 SSD 磁碟，並處理分頁檔案、tempdb、暫存資料和備份所需的所有 IOPS。

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

建立含磁碟的 Compute Engine VM

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

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

    前往「建立執行個體」

  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. 在「版本」清單中，按一下「Windows Server 2022 Datacenter 上的 SQL Server 2022 Standard」。
    3. 在「Boot disk type」(開機磁碟類型) 清單中，按一下「Standard persistent disk」(標準永久磁碟)。
    4. 在「Size (GB)」(大小 (GB)) 欄位中，將開機磁碟大小設為 50 GB。
    5. 如要儲存開機磁碟設定，請按一下「Select」(選取)。

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

    1. 展開「磁碟」部分。

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

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

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

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

  6. 如要建立 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. 使用遠端桌面通訊協定連線至執行個體。

設定磁碟區

建立及格式化磁碟區：

  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. 在「Assign Drive Letter or Path」(指派磁碟機代號或路徑) 步驟中，選擇磁碟機代號 P:，然後按一下「Next」(下一步)繼續操作。

      5. 在「Format Volume」(格式化磁碟區) 步驟中，將「Allocation unit size」(配置單位大小) 變更為 8192，然後在「Volume label」(磁碟區標籤) 旁輸入「pagefile」。按一下「下一步」繼續操作。

        新增磁碟區精靈

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

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

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

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

        新增條紋磁碟

      4. 在「Assign Drive Letter or Path」(指派磁碟機代號或路徑) 步驟中，選擇磁碟機代號 P:，然後按一下「Next」(下一步)繼續操作。

      5. 在「Format Volume」(格式化磁碟區) 步驟中，將「Allocation unit size」(配置單位大小) 變更為 8192，然後在「Volume label」(磁碟區標籤) 旁輸入「pagefile」。按一下「下一步」繼續操作。

        新增磁碟區精靈

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

  5. 重複上述步驟，為 SSD 磁碟建立「新簡單磁碟區」，並變更以下三項設定：

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

    • 將「配置單位大小」設為 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 檔案。

移動主要設定資料庫檔案並重新啟動系統之後，您必須將系統設為指向模型和 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 的效能降低。將群組政策權限「Lock Pages in Memory」授予 SQL Server 服務帳戶即可避免系統移動這類資訊。

步驟如下：

  1. 在「Start」(開始) 選單中搜尋「Edit Group Policy」(編輯群組政策) ，以便開啟主控台。
  2. 依序展開「本機電腦原則」「電腦設定」「Windows 設定」「安全性設定」「本機原則」「使用者權限指派」。
  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 中執行 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 的效能，同時降低永久磁碟區的 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

在預設情況下，這項設定的數值會相當大，但我們會建議您將其設為以下計算結果：可用實體 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 最佳做法指南。