建立高效能 SQL Server 執行個體

這個教學課程說明如何建立 Compute Engine VM 執行個體,並確保執行個體採用的 SQL Server 具備最佳效能。本教學課程將引導您建立執行個體,然後設定 SQL Server,以便在Cloud de Confiance by S3NS上發揮最佳效能。您可以使用多個設定選項來調整系統效能。

本教學課程使用 SQL Server Standard Edition 2022,因此本指南介紹的設定選項不一定適用於所有人,也並非所有選項都能為每項工作負載帶來顯著的效能優勢。

目標

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

費用

本教學課程使用 Cloud de Confiance by S3NS的計費元件,包括:

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

Pricing Calculator 可根據您預計的使用量來估算費用。點選提供的連結,即可查看本教學課程所用產品的預估費用,每小時可能超過 4 美元,每月可能超過 3,000 美元。

事前準備

  1. In the Cloud de Confiance console, on the project selector page, select or create a Cloud de Confiance 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.

    Go to project selector

  2. Verify that billing is enabled for your Cloud de Confiance project.

建立 Compute Engine VM 和磁碟

如要建立高效能 SQL Server 執行個體,您必須先建立具備 SQL Server 和兩個 Persistent Disk 磁碟區的 VM 執行個體。

Persistent Disk 考量

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

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

    使用本機 SSD 磁碟時,請留意幾項重要事項。從 Windows 關閉執行個體,或使用 API 重設執行個體時,本機 SSD 磁碟會遭到移除。這項操作會導致執行個體無法啟動。如要再次執行機器,您必須卸載永久磁碟、使用這些磁碟建立新的執行個體,然後定義新的本機 SSD 磁碟。開機之後,您可能也需要為新的磁碟設定格式並重新開機。因此,除非您準備好重建執行個體,否則不應在本機 SSD 磁碟上永久儲存重要資料,也不應將執行個體關機。

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

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

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

建立 Compute Engine VM 和磁碟

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

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

    前往「Create an instance」(建立執行個體)

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

  3. 在「Machine configuration」(機器設定) 部分,選取「General Purpose」(一般用途),然後執行下列操作:

    1. 在「系列」清單中,按一下「N2」。
    2. 在「Machine type」(機型) 清單中,按一下「n2-highmem-16 (16vCPU, 128 GB memory)」(n2-highmem-16 (16 個 vCPU,128 GB 記憶體))。
  4. 在「Boot disk」(開機磁碟) 區段中,按一下「Change」(變更),然後執行下列操作:

    1. 在「Public images」(公開映像檔) 分頁中,按一下「Operating system」(作業系統) 清單,然後選取「SQL Server on Windows Server」。
    2. 在「Version」(版本) 清單中,按一下「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. 展開「Disks」(磁碟) 區段。
    2. 如要建立本機磁碟,請按一下「Add Local SSD」(新增本機 SSD),然後執行下列操作:

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

      1. 「Name」欄位保持不變。
      2. 在「Disk source type」(磁碟來源類型) 清單中,選取「Blank disk」(空白磁碟)。
      3. 在「Disk type」(磁碟類型) 清單中,選取「SSD persistent disk」(SSD 永久磁碟)。
      4. 在「Size」(大小) 欄位中,輸入可容納資料庫大小的磁碟大小。
      5. 按一下「儲存」,完成建立第二個磁碟。
  6. 按一下「Create」(建立),即可建立 VM。

設定 Windows

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

連線至執行個體

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

    前往 VM 執行個體

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

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

  4. 指定使用者名稱。

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

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

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

設定磁碟區

建立及格式化磁碟區:

  1. 在「開始」選單中搜尋「電腦管理」,然後開啟該應用程式。
  2. 在「儲存空間」部分下方,選取「磁碟管理」。
  3. 系統提示您初始化磁碟時,請接受預設選項,然後按一下「OK」(確定)。
  4. 為本機 SSD 磁碟建立分區:

    如要找出本機 SSD 磁碟,請在磁碟上按一下滑鼠右鍵,然後選取「Properties」。本機 SSD 磁碟屬性名稱會是 Google EphemeralDisk (SCSI 介面) 或 nvme_card (NVMe 介面)。本機 SSD 磁碟和永久 SSD 都會標示為具有 Unallocated 分區。

    1. 如果 VM 只有 1 個本機 SSD 磁碟機,請按照下列步驟操作:

      1. 在磁碟機清單下方,對 374.98 GB 本機 SSD 磁碟按一下滑鼠右鍵,然後選取「New Simple Volume」(新增簡單磁碟區)。
      2. 在歡迎畫面中,按一下「Next」(下一步),啟動磁碟區精靈。
      3. 在「Specify Volume Size」(指定磁碟區大小) 步驟中,將磁碟區大小保留為預設值,然後按一下「Next」(下一步) 繼續操作。
      4. 在「Assign Drive Letter or Path」(指派磁碟機代號或路徑) 步驟中,選擇「P:」做為磁碟機代號,然後按一下「Next」(下一步) 繼續操作。
      5. 在「Format Volume」(格式化磁碟區) 步驟中,將「Allocation unit size」(配置單位大小) 變更為 8192,並在「Volume label」(磁碟區標籤) 中輸入「pagefile」。請點選「Next」(下一步) 繼續操作。

        新增磁碟區精靈

      6. 按一下「完成」,結束磁碟區精靈。

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

      1. 在磁碟機清單下方,對第一個 374.98 GB 本機 SSD 磁碟按一下滑鼠右鍵,然後選取「New Striped Volume」(新增等量磁碟區)。
      2. 在歡迎畫面中,按一下「Next」(下一步),啟動磁碟區精靈。
      3. 在「Select Disks」(選取磁碟) 步驟中,將所有大小為 383,982 MB 的可用磁碟新增至「Selected」(已選取) 區段。點選「Next」(下一步) 繼續操作。

        新增條帶磁碟

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

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

        新增磁碟區精靈

      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 檔案。

移動主要設定資料庫檔案並重新啟動後,您必須設定系統,將模型和 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. 依序展開 「Local Computer Policy」> 「Computer Configuration」> 「Windows Settings」>「Security Settings」> 「Local Policies」> 「User Rights Assignment」。
  3. 搜尋並按兩下「鎖定記憶體中的分頁」。
  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 的效能,並減少 Persistent Disk 磁碟區使用的 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 最佳做法指南。

清除所用資源

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

刪除專案

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

刪除專案的方法如下:

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

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

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

刪除執行個體

如要刪除 Compute Engine 執行個體:

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

    前往 VM 執行個體

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

刪除 Persistent Disk 磁碟區

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

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

    前往「Disks」(磁碟) 頁面

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

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

後續步驟