可將 Azure Stack Hub 中的效能最佳化的 SQL Server 最佳做法SQL server best practices to optimize performance in Azure Stack Hub

本文提供 SQL Server 最佳做法,將 Microsoft Azure Stack Hub 虛擬機器 (VM) 中的 SQL Server 最佳化並提升效能。This article provides SQL server best practices to optimize SQL Server and improve performance in Microsoft Azure Stack Hub virtual machines (VMs). 在 Azure Stack Hub VM 中執行 SQL Server 時,所使用的資料庫效能微調選項,應與內部部署伺服器環境中的 SQL Server 所適用的選項相同。When running SQL Server in Azure Stack Hub VMs, use the same database performance-tuning options applicable to SQL Server in an on-premises server environment. Azure Stack Hub 雲端中關聯式資料庫的效能取決於許多因素,包括 VM 的系列大小和資料磁碟的組態。The performance of a relational database in an Azure Stack Hub cloud depends on many factors, including family size of a VM and the configuration of the data disks.

建立 SQL Server 映像時,請考慮在 Azure Stack Hub 入口網站中佈建 VMWhen creating SQL Server images, consider provisioning your VMs in the Azure Stack Hub portal. 從 Azure Stack Hub 系統管理員入口網站中的 [Marketplace 管理] 下載 SQL IaaS 擴充功能,然後下載您所選擇的 SQL Server VM 映像。Download the SQL IaaS Extension from Marketplace Management in the Azure Stack Hub administrator portal and download your choice of SQL Server VM images. 其中包括 SQL Server 2016 SP1、SQL Server 2016 SP2 和 SQL Server 2017。These include SQL Server 2016 SP1, SQL Server 2016 SP2, and SQL Server 2017.

注意

雖然本文說明的是如何使用全域 Azure 入口網站來佈建 SQL Server VM,文中的指導方針同樣適用於 Azure Stack Hub,但有如下差異:SSD 無法作為作業系統磁碟,且儲存體設定有些微差異。While the article describes how to provision a SQL Server VM using the global Azure portal, the guidance also applies to Azure Stack Hub with the following differences: SSD isn't available for the operating system disk and there are minor differences in storage configuration.

在 VM 映像中,針對 SQL Server,您只能使用自備授權 (BYOL)。In the VM images, for SQL Server, you can only use bring-your-own-license (BYOL). 針對 Windows Server,預設的授權模型為隨用隨付 (PAYG)。For Windows Server, the default license model is pay-as-you-go (PAYG). 如需 VM 中 Windows Server 授權模型的詳細資訊,請參閱 Azure Stack Hub Marketplace 中的 Windows Server 常見問題一文。For detailed information of Windows Server license model in VM, refer the article Windows Server in Azure Stack Hub Marketplace FAQ.

本文著重於如何在 Azure Stack Hub VM 上獲得「最佳的」 SQL Server 效能。Getting the best performance for SQL Server on Azure Stack Hub VMs is the focus of this article. 如果工作負載需求不高,則不一定要遵循每個最佳化建議。If your workload is less demanding, you might not require every recommended optimization. 評估以下建議時,請考慮您的效能需求和工作負載模式。Consider your performance needs and workload patterns as you evaluate these recommendations.

注意

如需 Azure VM 的 SQL Server 效能指導方針,請參閱本文For performance guidance for SQL Server in Azure VMs, refer to this article.

SQL Server 最佳做法的檢查清單Checklist for SQL server best practices

下列檢查清單是為了讓您在 Azure Stack Hub VM 上獲得最佳的 SQL Server 效能:The following checklist is for optimal performance of SQL Server on Azure Stack Hub VMs:

區域Area 最佳化Optimizations
VM 大小VM size DS3 或更高版本 (若為 SQL Server Enterprise Edition)。DS3 or higher for SQL Server Enterprise edition.

DS2 或更高版本 (若為 SQL Server Standard Edition 和 Web Edition)。DS2 or higher for SQL Server Standard edition and Web edition.
儲存體Storage 使用支援進階儲存體的 VM 系列。Use a VM family that supports Premium storage.
磁碟Disks 使用至少兩個資料磁碟 (一個供記錄檔使用,一個供資料檔案和 TempDB 使用),並根據容量需求選擇磁碟大小。Use a minimum of two data disks (one for log files and one for data file and TempDB), and choose the disk size based on your capacity needs. 在 SQL Server 安裝期間,將預設資料檔案位置設定為這些磁碟。Set the default data file locations to these disks during the SQL Server install.

避免使用作業系統或暫存磁碟作為資料儲存體或進行記錄。Avoid using operating system or temporary disks for database storage or logging.
使用「儲存空間」,分割多個 Azure 資料磁碟以提高 IO 輸送量。Stripe multiple Azure data disks to get increased IO throughput using Storage Spaces.

以文件上記載的配置大小格式化。Format with documented allocation sizes.
I/OI/O 針對資料檔案,啟用 [立即檔案初始化] 功能。Enable instant file initialization for data files.

將資料庫的自動成長限制在合理且固定的小規模增量 (64 MB - 256 MB)。Limit autogrow on the databases with reasonably small fixed increments (64 MB-256 MB).

停用資料庫上的 [自動壓縮] 功能。Disable autoshrink on the database.

將預設的備份和資料庫檔案位置設定在資料磁碟上,而非在作業系統磁碟上。Set up default backup and database file locations on data disks, not the operating system disk.

啟用鎖定的頁面。Enable locked pages.

套用 SQL Server Service Pack 和累積更新。Apply SQL Server service packs and cumulative updates.
功能專屬Feature-specific 直接備份到 Blob 儲存體 (如果所使用的 SQL Server 版本可支援)。Back up directly to blob storage (if supported by the SQL Server version in use).

如需有關「如何」 和「為何」 進行這些最佳化的詳細資訊,請檢閱下列各節提供的詳細資料與指導方針。For more information on how and why to make these optimizations, review the details and guidance provided in the following sections.

VM 大小指引VM size guidance

對於需要高效能的應用程式,建議您採用下列 VM 大小For performance-sensitive applications, the following VM sizes are recommended:

  • SQL Server Enterprise Edition: DS3 或更高版本SQL Server Enterprise edition: DS3 or higher

  • SQL Server Standard Edition 和 Web Edition: DS2 或更高版本SQL Server Standard edition and Web edition: DS2 or higher

在使用 Azure Stack Hub 時,DS 和 DS_v2 VM 系列的效能沒有差異。With Azure Stack Hub, there's no performance difference between the DS and DS_v2 VM family series.

儲存體指引Storage guidance

Azure Stack Hub 中的 DS 系列 (以及 DSv2 系列) VM 可提供最大的作業系統磁碟和資料磁碟輸送量 (IOPS)。DS-series (along with DSv2-series) VMs in Azure Stack Hub provide the maximum operating system disk and data disk throughput (IOPS). 無論所選磁碟的大小或類型為何,DS 或 DSv2 系列中的 VM 都可為作業系統磁碟提供最高 1,000 的 IOPS,並為每個資料磁碟提供最高 2,300 的 IOPS。A VM from the DS or DSv2 series provides up to 1,000 IOPS for the operating system disk and up to 2,300 IOPS per data disk, no matter the type or size of the chosen disk.

資料磁碟輸送量僅取決於 VM 系列。Data disk throughput is determined uniquely based on the VM family series. 您可以參閱本文,來找出每個 VM 系列的資料磁碟輸送量。You can refer to this article to identify the data disk throughput per VM family series.

注意

對於生產工作負載,請選取 DS 系列或 DSv2 系列的 VM,以對作業系統磁碟和資料磁碟提供所能實現的最大 IOPS。For production workloads, select a DS-series or DSv2-series VM to provide the maximum possible IOPS on the operating system disk and data disks.

在 Azure Stack Hub 中建立儲存體帳戶時,由於 Azure Stack Hub 未提供異地複寫功能,因此異地複寫選項不會有作用。When creating a storage account in Azure Stack Hub, the geo-replication option has no effect because this capability isn't available in Azure Stack Hub.

磁碟指引Disks guidance

Azure Stack Hub VM 上有三種主要的磁碟類型︰There are three main disk types on an Azure Stack Hub VM:

  • 作業系統磁碟: 建立 Azure Stack Hub VM 時,平台會至少將一個磁碟 (標示為 C 磁碟機) 連接至 VM 作為您的作業系統磁碟。Operating system disk: When you create an Azure Stack Hub VM, the platform attaches at least one disk (labeled as the C drive) to the VM for your operating system disk. 此磁碟是以分頁 Blob 的形式儲存於儲存體的 VHD。This disk is a VHD stored as a page blob in storage.

  • 暫存磁碟: Azure Stack Hub VM 包含另一個稱為暫存磁碟的磁碟 (標示為 D 磁碟機)。Temporary disk: Azure Stack Hub VMs contain another disk called the temporary disk (labeled as the D drive). 此磁碟位於可用於塗銷空間的節點上。This is a disk on the node that can be used for scratch space.

  • 資料磁碟: 您可以將其他磁碟連結至 VM 作為資料磁碟,這些磁碟將會以分頁 Blob 形式儲存於儲存體中。Data disks: You can attach additional disks to your VM as data disks, and these disks are stored in storage as page blobs.

下列各節說明使用這些不同磁碟的建議。The following sections describe recommendations for using these different disks.

作業系統磁碟Operating system disk

作業系統磁碟是指可開機,並掛接為執行的作業系統版本,且標示為 C 磁碟機的 VHD。An operating system disk is a VHD that you can boot and mount as a running version of an operating system and is labeled as C drive.

暫存磁碟Temporary disk

標示為 D 磁碟機的暫存磁碟機不會保留下來。The temporary storage drive, labeled as the D drive, isn't persistent. 請勿在 D 磁碟機上儲存您不想失去的資料。Don't store any data you're unwilling to lose on the D drive. 這包括使用者資料庫檔案和使用者交易記錄檔。This includes your user database files and user transaction log files.

由於每個資料磁碟最多可提供高達 2,300 的 IOPS,因此建議您將 TempDB 儲存在資料磁碟上。We recommend storing TempDB on a data disk as each data disk provides a maximum of up to 2,300 IOPS per data disk.

資料磁碟Data disks

  • 對資料和記錄檔使用資料磁碟。Use data disks for data and log files. 如果您未使用磁碟等量分割,請使用支援進階儲存體之 VM 中的兩個資料磁碟,一個磁碟包含記錄檔,另一個則包含資料和 TempDB 檔案。If you're not using disk striping, use two data disks from a VM that supports Premium storage, where one disk contains the log files and the other contains the data and TempDB files. 每個資料磁碟都會提供數個 IOPS (取決於 VM 系列),如 Azure Stack Hub 中支援的 VM 大小一文所述。Each data disk provides a number of IOPS depending on the VM family, as described in VM sizes supported in Azure Stack Hub. 如果您使用磁碟等量分割技術 (例如「儲存空間」),請將所有資料和記錄檔放置在相同磁碟機 (包括 TempDB)。If you're using a disk-striping technique, such as Storage Spaces, place all data and log files on the same drive (including TempDB). 此設定會讓您有最大的 IOPS 數目供 SQL Server 取用 (不論哪個檔案在哪個特定時間有需要)。This configuration gives you the maximum number of IOPS available for SQL Server to consume, no matter which file needs them at any particular time.

注意

當您在入口網站中佈建 SQL Server VM 時,您可以選擇編輯儲存體組態。When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. 根據您的設定,Azure Stack Hub 會設定一或多個磁碟。Depending on your configuration, Azure Stack Hub configures one or more disks. 多個磁碟會合併成單一儲存體集區。Multiple disks are combined into a single storage pool. 資料和記錄檔皆在此設定中。Both the data and log files reside together in this configuration.

  • 磁碟等量分割︰ 如需更多的輸送量,您可以新增其他資料磁碟,並使用磁碟等量分割。Disk striping: For more throughput, you can add additional data disks and use disk striping. 若要判斷所需的資料磁碟數目,請分析記錄檔以及資料和 TempDB 檔案所需的 IOPS 數目。To determine the number of data disks you need, analyze the number of IOPS required for your log files and for your data and TempDB files. 請注意,IOPS 的限制是以每個資料磁碟為基礎的 (根據 VM 系列),而不是以 VM 大小為基礎。Notice that IOPS limits are per data disk based on the VM series family, and not based on the VM size. 不過,網路頻寬限制則是以 VM 大小為基礎。Network bandwidth limits, however, are based on the VM size. 如需詳細資訊,請參閱 Azure Stack Hub 中的 VM 大小上的資料表。See the tables on VM sizes in Azure Stack Hub for more detail. 請使用下列指引:Use the following guidelines:

    • 若為 Windows Server 2012 或更新版本,請使用儲存空間與下列指導方針:For Windows Server 2012 or later, use Storage Spaces with the following guidelines:

      1. 將線上交易處理 (OLTP) 工作負載的間隔 (等量磁碟區大小) 設為 64 KB (65,536 位元組),資料倉儲的工作負載則設為 256 KB (262,144 位元組),以避免分割對齊錯誤影響效能。Set the interleave (stripe size) to 64 KB (65,536 bytes) for online transaction processing (OLTP) workloads and 256 KB (262,144 bytes) for data warehousing workloads to avoid performance impact due to partition misalignment. 必須使用 PowerShell 來設定。This must be set with PowerShell.

      2. 設定資料行數目 = 實體磁碟數量。Set column count = number of physical disks. 設定超過 8 個磁碟時請使用 PowerShell (而非伺服器管理員 UI)。Use PowerShell when configuring more than eight disks (not Server Manager UI).

        例如,下列 PowerShell 會建立新的儲存體集區,其間隔大小設定為 64 KB,且資料行數目設定為 2︰For example, the following PowerShell creates a new storage pool with the interleave size set to 64 KB and the number of columns to 2:

        $PoolCount = Get-PhysicalDisk -CanPool $True
        $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"}
        
        New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" -PhysicalDisks $PhysicalDisks | New-VirtualDisk -FriendlyName "DataFiles" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple -UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" -AllocationUnitSize 65536 -Confirm:$false
        
  • 請根據您預期的負載量,決定與您的儲存體集區相關聯的磁碟數量。Determine the number of disks associated with your storage pool based on your load expectations. 請注意,各 VM 大小所允許連接的資料磁碟數量皆不同。Keep in mind that different VM sizes allow different numbers of attached data disks. 如需詳細資訊,請參閱 Azure Stack Hub 中支援的 VM 大小For more information, see VM sizes supported in Azure Stack Hub.

  • 若要獲得資料磁碟所能實現的最大 IOPS,建議您新增 VM 大小所支援的最大資料磁碟數目,並使用磁碟等量分割。To get the maximum possible IOPS for data disks, the recommendation is to add the maximum number of data disks supported by your VM size and to use disk striping.

  • NTFS 配置單位大小: 格式化資料磁碟時,建議您針對資料/記錄檔案和 TempDB,採用 64 KB 的配置單位大小。NTFS allocation unit size: When formatting the data disk, we recommend you use a 64-KB allocation unit size for data and log files as well as TempDB.

  • 磁碟管理實務: 移除資料磁碟時,請於變更期間停止 SQL Server 服務。Disk management practices: When removing a data disk, stop the SQL Server service during the change. 此外,請勿變更磁碟的快取設定,這麼做並不會改善效能。Also, don't change cache settings on the disks as it doesn't provide any performance improvements.

警告

如果在這些作業進行期間無法停止 SQL Server,可能會造成資料庫損毀。Failure to stop the SQL Service during these operations can cause database corruption.

I/O 指引I/O guidance

  • 請考慮啟用 [立即檔案初始化] 功能,以減少配置初始檔案所需的時間。Consider enabling instant file initialization to reduce the time that is required for initial file allocation. 若要發揮「立即檔案初始化」的優點,請將 SE_MANAGE_VOLUME_NAME 授與 SQL Server (MSSQLSERVER) 服務帳戶,並將該帳戶加入[執行磁碟區維護工作] 安全性原則。To take advantage of instant file initialization, you grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. 如果您使用的是 Azure 的 SQL Server 平台映像,預設服務帳戶 (NT Service\MSSQLSERVER) 不會加入 [執行磁碟區維護工作] 安全性原則。If you're using a SQL Server platform image for Azure, the default service account (NT Service\MSSQLSERVER) isn't added to the Perform Volume Maintenance Tasks security policy. 也就是說,SQL Server Azure 平台映像未啟用 [立即檔案初始化] 功能。In other words, instant file initialization isn't enabled in a SQL Server Azure platform image. 將 SQL Server 服務帳戶加入 [執行磁碟區維護工作] 安全性原則之後,請重新啟動 SQL Server 服務。After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service. 使用此功能時,可能有安全性考量。There could be security considerations for using this feature. 如需詳細資訊,請參閱 資料庫檔案初始化For more information, see Database File Initialization.

  • 自動成長 是在發生非預期成長時的應急方案。Autogrow is a contingency for unexpected growth. 請勿每天使用「自動成長」功能,管理資料和記錄成長。Don't manage your data and log growth on a day-to-day basis with autogrow. 若已使用自動成長功能,請透過 [大小] 參數預先放大檔案。If autogrow is used, pre-grow the file using the Size switch.

  • 請確定已停用「自動壓縮」 ,以避免不必要的額外負荷,而對效能造成負面影響。Make sure autoshrink is disabled to avoid unnecessary overhead that can negatively affect performance.

  • 設定預設備份和資料庫檔案位置。Setup default backup and database file locations. 請使用本文中的建議,並在 [伺服器屬性] 視窗中進行變更。Use the recommendations in this article and make the changes in the Server properties window. 如需指示,請參閱 檢視或變更資料及記錄檔的預設位置 (SQL Server Management Studio)For instructions, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio). 下列螢幕擷取畫面顯示可供進行這些變更的位置:The following screenshot shows where to make these changes:

    檢視或變更預設位置

  • 起用鎖定的頁面,以減少 IO 和任何分頁活動。Enable locked pages to reduce IO and any paging activities. 如需詳細資訊,請參閱 啟用鎖定記憶體分頁選項 (Windows)For more information, see Enable the Lock Pages in Memory Option (Windows).

  • 將資料檔案傳輸至 Azure Stack Hub,或從 Azure Stack 往外傳輸時,請考慮將資料檔案壓縮。Consider compressing any data files when transferring in/out of Azure Stack Hub, including backups.

特定功能的指引Feature-specific guidance

有些部署作業可能會使用更進階的組態技術,提供額外的效能優點。Some deployments may achieve additional performance benefits using more advanced configuration techniques. 下列清單特別強調了一些可協助您達到更佳效能的 SQL Server 功能:The following list highlights some SQL Server features that may help you achieve better performance:

  • 備份至 Azure 儲存體。Back up to Azure storage. 對 Azure Stack Hub VM 中執行的 SQL Server 進行備份時,可以使用「SQL Server 備份至 URL」。When making backups for SQL Server running in Azure Stack Hub VMs, you can use SQL Server Backup to URL. 此功能從 SQL Server 2012 SP1 CU2 開始提供,建議在備份至連接的資料磁碟時使用。This feature is available starting with SQL Server 2012 SP1 CU2 and recommended for backing up to the attached data disks.

    使用 Azure 儲存體來進行備份或還原時,請遵循 SQL Server 備份至 URL 的最佳做法和疑難排解從儲存在 Microsoft Azure 中的備份進行還原中所提供的建議。When you backup or restore using Azure storage, follow the recommendations provided in SQL Server Backup to URL Best Practices and Troubleshooting and Restoring From Backups Stored in Microsoft Azure. 您也可以使用 Azure VM 中的 SQL Server 自動備份,自動執行這些備份作業。You can also automate these backups using Automated Backup for SQL Server in Azure VMs.

  • 備份至 Azure Stack Hub 儲存體。Back up to Azure Stack Hub storage. 您可以使用和「備份至 Azure 儲存體」類似的方式,來備份至 Azure Stack Hub 儲存體。You can back up to Azure Stack Hub storage in a similar fashion as with backing up to Azure Storage. 當您在 SQL Server Management Studio (SSMS) 中建立備份時,必須手動輸入設定資訊。When you create a backup inside SQL Server Management Studio (SSMS), you need to enter the configuration information manually. 您無法使用 SSMS 來建立儲存體容器或共用存取簽章。You can't use SSMS to create the storage container or the Shared Access Signature. SSMS 僅會連線至 Azure 訂用帳戶,不會連線至 Azure Stack Hub 訂用帳戶。SSMS only connects to Azure subscriptions, not Azure Stack Hub subscriptions. 相反地,您必須在 Azure Stack Hub 入口網站中或使用 PowerShell,才能建立儲存體帳戶、容器和共用存取簽章。Instead, you need to create the storage account, container, and Shared Access Signature in the Azure Stack Hub portal or with PowerShell.

    SQL Server 備份

    注意

    共用存取簽章是得自 Azure Stack Hub 入口網站的 SAS 權杖,字串中The Shared Access Signature is the SAS token from the Azure Stack Hub portal, without the leading ‘?' 沒有前置 ‘?’。in the string. 如果您從入口網站使用複製功能,則必須刪除前置 ‘?’If you use the copy function from the portal, you need to delete the leading ‘?' 才能讓權杖在 SQL Server 中生效。for the token to work within SQL Server.

    在 SQL Server 中安裝和設定了備份目的地後,您就可以備份至 Azure Stack Hub Blob 儲存體。Once you have the Backup Destination set up and configured in SQL Server, you can then back up to the Azure Stack Hub blob storage.

後續步驟Next steps

使用服務或為 Azure Stack Hub 建置應用程式Using services or building apps for Azure Stack Hub