建立高效能的 SQL Server 執行個體

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

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

目標

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

費用

這個教學課程使用的 Google Cloud Platform 計費元件包括:

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

您可以使用 Pricing Calculator 依據預估用量估算費用。按一下連結即可查看這個教學課程中所用產品的預估費用,每小時費用可能超過 $4 美元,每個月則有可能超過 $3,000 美元。新的 Google Cloud Platform 使用者可能符合免費試用的資格。

事前準備

  1. 登入您的 Google 帳戶。

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

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

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

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

    瞭解如何啟用計費功能

  4. 如果您的本機電腦並非使用 Windows,請安裝第三方遠端桌面協定用戶端,例如 Fusion Labs 提供的 Chrome RDP

建立 Compute Engine 執行個體和磁碟

建立具備 SQL Server 和兩個永久磁碟的 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:\ 磁碟)。因此,我們會建議您建立本機 SSD 來處理分頁檔案、tempdb、暫存資料和備份所需的所有 IOPS。

如要進一步瞭解磁碟效能,請參閱區塊儲存空間效能一文。

建立 Compute Engine 執行個體

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

  1. 在 Google Cloud Platform Console 中,前往「VM Instances」(VM 執行個體) 頁面。

    前往「VM Instances」(VM 執行個體) 頁面

  2. 按一下 [Create instance] (建立執行個體) 按鈕。

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

  4. 將「Machine configuration」(機器設定) 設為 [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. 在「Size (GB)」(大小 (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 Console 的「VM Instances」(VM 執行個體) 頁面。

    前往「VM Instances」(VM 執行個體) 頁面

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

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

  4. 指定使用者名稱。

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

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

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

    • 如果您已安裝 Fusion Labs 推出的 Chrome RDP,請按一下執行個體詳細資料頁面頂端的 [RDP] (遠端桌面協定) 按鈕。
    • 如果使用的是其他 RDP 用戶端 (例如 Windows 遠端桌面連線),請點選 [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 永久磁碟重複執行前述步驟,並變更以下三項設定:

  • 選擇 [D:] 這個磁碟機代號。
  • 將「Allocation unit size」(配置單位大小) 變更為「32k」。

    儘管 Microsoft 會建議您將 SQL Server 資料和記錄檔磁碟的格式設為 64k,但 GCP 中的永久磁碟技術與 32k 較為相符。這項變更也會減少計入永久磁碟 I/O 上限的磁碟作業次數。

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

修正 Failed to mount path - Invalid Parameter 錯誤

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

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

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

  5. 選擇 [D:] 這個磁碟機代號。

  6. 按一下 [OK] (確定)。

移動 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. 連續點選三次 [OK] (確定),退出進階系統屬性。

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

變更電源設定檔

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

  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,然後按一下 [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. 如要使用「報表伺服器」功能,請一併移動「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] (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

在預設情況下,這項設定的數值會相當大,但我們會建議您將其設為以下計算結果:可用實體 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 Edition 提供的功能,請參閱 SQL Server 最佳做法指南

清除所用資源

完成 SQL Server 教學課程之後,您可以清除在 GCP 中建立的資源,這樣這些資源就不會占用配額,您日後也無須為其付費。下列各節將說明如何刪除或停用這些資源。

刪除專案

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

如要刪除專案:

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

    前往「Projects」(專案) 頁面

  2. 在專案清單中,找到您要刪除的專案並按一下「刪除」圖示 delete
  3. 在對話方塊中輸入專案 ID,按一下 [Shut down] (關閉) 即可刪除專案。

刪除執行個體

如何刪除 Compute Engine 執行個體:

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

    前往 VM 執行個體頁面

  2. 找到您要刪除的執行個體,然後按一下旁邊的核取方塊。
  3. 按一下頁面頂端的「刪除」圖示 delete,即可刪除執行個體。

刪除永久磁碟

如何刪除永久磁碟:

  1. 前往 GCP Console 的「Disks」(磁碟) 頁面。

    前往「Disks」(磁碟) 頁面

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

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

後續步驟

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

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

這個網頁
Compute Engine