Azure 虛擬機器中的 SQL Server 效能最佳做法Performance best practices for SQL Server in Azure Virtual Machines

OverviewOverview

本主題提供將「Microsoft Azure 虛擬機器」中的 SQL Server 效能最佳化的最佳做法。This topic provides best practices for optimizing SQL Server performance in Microsoft Azure Virtual Machine. 在 Azure 虛擬機器中執行 SQL Server 時,我們建議您繼續使用相同的資料庫效能微調選項,這些選項適用於內部部署伺服器環境中的 SQL Server。While running SQL Server in Azure Virtual Machines, we recommend that you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environment. 不過,公用雲端中關聯式資料庫的效能優劣取決於許多因素,例如虛擬機器的大小和資料磁碟的組態。However, the performance of a relational database in a public cloud depends on many factors such as the size of a virtual machine, and the configuration of the data disks.

建立 SQL Server 映像時,請考慮在 Azure 入口網站中佈建 VMWhen creating SQL Server images, consider provisioning your VMs in the Azure portal. 使用 Resource Manager 在入口網站中佈建的 SQL Server VM 會實作所有這些最佳作法,包括儲存體設定。SQL Server VMs provisioned in the Portal with Resource Manager implement all these best practices, including the storage configuration.

本文的主題為如何讓 Azure VM 上的 SQL Server 達到最佳 效能。This article is focused on getting the best performance for SQL Server on Azure VMs. 如果您的工作負載需求較低,可能就不需要採用下列每一項最佳化條件。If your workload is less demanding, you might not require every optimization listed below. 評估以下建議時,請考慮您的效能需求和工作負載模式。Consider your performance needs and workload patterns as you evaluate these recommendations.

注意

Azure 建立和處理資源的部署模型有二種:Resource Manager 和傳統Azure has two different deployment models for creating and working with resources: Resource Manager and classic. 本文將說明如何使用這兩個模型,但 Microsoft 建議大多數新的部署請使用資源管理員模型。This article covers using both models, but Microsoft recommends that most new deployments use the Resource Manager model.

快速檢查清單Quick check list

下列快速檢查清單能協助您讓 Azure 虛擬機器上的 SQL Server 達到最佳效能:The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

領域Area 最佳化Optimizations
VM 大小VM size SQL Enterprise Edition 的 DS3 或更高版本。DS3 or higher for SQL Enterprise edition.

SQL Standard 和 Web Edition 的 DS2 或更高版本。DS2 or higher for SQL Standard and Web editions.
儲存體Storage 使用進階儲存體Use Premium Storage. 標準儲存體只建議用於開發/測試。Standard storage is only recommended for dev/test.

儲存體帳戶和 SQL Server VM 置於同一個區域。Keep the storage account and SQL Server VM in the same region.

停用儲存體帳戶上的 Azure 異地備援儲存體 (異地複寫)。Disable Azure geo-redundant storage (geo-replication) on the storage account.
磁碟Disks 使用至少 2 個 P30 磁碟 (1 個用於儲存記錄檔案,另 1 個用於儲存資料檔案和存放 TempDB)。Use a minimum of 2 P30 disks (1 for log files; 1 for data files and TempDB).

避免使用作業系統或暫存磁碟作為資料儲存體或進行記錄。Avoid using operating system or temporary disks for database storage or logging.

啟用裝載資料檔案和 TempDB 的磁碟上的 [讀取快取] 功能。Enable read caching on the disk(s) hosting the data files and TempDB.

不要啟用裝載記錄檔案的磁碟上的 [快取] 功能。Do not enable caching on disk(s) hosting the log file.

重要事項︰變更 Azure VM 磁碟的快取設定時,停止 SQL Server 服務。Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk.

分割多個 Azure 資料磁碟,以提高 IO 輸送量。Stripe multiple Azure data disks to get increased IO throughput.

以文件上記載的配置大小格式化。Format with documented allocation sizes.
I/OI/O 啟用 [資料庫頁面壓縮] 功能 。Enable database page compression.

針對資料檔案,啟用 [立即檔案初始化] 功能。Enable instant file initialization for data files.

限制資料庫上的 [自動成長] 功能,或停用。Limit or disable autogrow on the database.

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

將所有的資料庫 (包括系統資料庫) 移到資料磁碟。Move all databases to data disks, including system databases.

將 SQL Server 的錯誤記錄檔和追蹤檔案目錄移至資料磁碟。Move SQL Server error log and trace file directories to data disks.

設定預設備份和資料庫檔案位置。Setup default backup and database file locations.

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

套用 SQL Server 效能修正程式。Apply SQL Server performance fixes.
特定功能Feature specific 直接備份至 Blob 儲存體。Back up directly to blob storage.

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

VM 大小指引VM size guidance

對於需要高效能的應用程式,建議您採用下列 虛擬機器大小For performance sensitive applications, it’s recommended that you use the following virtual machines sizes:

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

儲存體指引Storage guidance

DS 系列 (以及 DSv2 系列和 GS 系列) VM 支援 進階儲存體DS-series (along with DSv2-series and GS-series) VMs support Premium Storage. 針對所有生產環境工作負載,建議使用進階儲存體。Premium Storage is recommended for all production workloads.

警告

標準儲存體具有不同的延遲和頻寬,並建議僅用於開發/測試工作負載。Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads. 生產工作負載應該使用進階儲存體。Production workloads should use Premium Storage.

此外,我們建議您在存放 SQL Server 虛擬機器的同一個資料中心內建立 Azure 儲存體帳戶,以減少傳輸延遲的狀況。In addition, we recommend that you create your Azure storage account in the same data center as your SQL Server virtual machines to reduce transfer delays. 建立儲存體帳戶時,請停用 [異地複寫] 功能,因為跨多個磁碟時無法保證寫入順序一致。When creating a storage account, disable geo-replication as consistent write order across multiple disks is not guaranteed. 請考慮另一個方法,在兩個 Azure 資料中心之間設定 SQL Server 災害復原技術。Instead, consider configuring a SQL Server disaster recovery technology between two Azure data centers. 如需詳細資訊,請參閱 Azure 虛擬機器中的 SQL Server 高可用性和災害復原For more information, see High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines.

磁碟指引Disks guidance

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

  • OS 磁碟:建立「Azure 虛擬機器」時,平台會至少將一個磁碟 (標示為 C 磁碟機) 連接至 VM 作為您的作業系統磁碟。OS disk: When you create an Azure Virtual Machine, the platform will attach 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 虛擬機器」包含另一個稱為暫存磁碟的磁碟 (標示為 D: 磁碟機)。Temporary disk: Azure Virtual Machines 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.
  • 資料磁碟:您也可以將其他磁碟連接至虛擬機器作為資料磁碟,這些磁碟將會以分頁 Blob 形式儲存於儲存體中。Data disks: You can also attach additional disks to your virtual machine as data disks, and these will be 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.

作業系統磁碟上的預設快取原則是 讀取/寫入Default caching policy on the operating system disk is Read/Write. 對於需要高效能的應用程式,我們建議您使用資料磁碟取代作業系統磁碟。For performance sensitive applications, we recommend that you use data disks instead of the operating system disk. 請參閱下面的<資料磁碟>一節。See the section on Data Disks below.

暫存磁碟Temporary disk

標示為 D: 磁碟機的暫存磁碟機不會保存至 Azure blob 儲存體。The temporary storage drive, labeled as the D: drive, is not persisted to Azure blob storage. 請勿將使用者資料庫檔案或使用者交易記錄檔儲存在 D: 磁碟機。Do not store your user database files or user transaction log files on the D: drive.

D 系列、Dv2 系列和 G 系列 VM 的暫存磁碟機皆為 SSD 式。For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. 如果您的工作負載大量使用 TempDB (例如用於暫存物件或複雜的聯結),將 TempDB 儲存在 D 磁碟機可能會產生較高的 TempDB 輸送量和較低的 TempDB 延遲。If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.

對於支援「進階儲存體」的 VM (DS 系列、DSv2 系列與 GS 系列),建議您將 TempDB 儲存在支援「進階儲存體」且啟用讀取快取的磁碟上。For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. 這項建議有一個例外,如果 TempDB 使用方式是密集寫入,您可以將 TempDB 儲存在本機的 D 磁碟機 (在這些機器大小上也是 SSD 型) 上以達到更高的效能。There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.

資料磁碟Data disks

  • 將資料磁碟用於資料檔和記錄檔:至少使用 2 個進階儲存體 P30 磁碟,一個磁碟包含記錄檔,另一個則包含資料和 TempDB 檔案。Use data disks for data and log files: At a minimum, use 2 Premium Storage P30 disks where one disk contains the log file(s) and the other contains the data and TempDB file(s). 每個進階儲存體磁碟會根據其大小提供數個 IOPS 和頻寬 (MB/s),如下列文章所述:針對磁碟使用進階儲存體Each Premium Storage disk provides a number of IOPs and bandwidth (MB/s) depending on its size, as described in the following article: Using Premium Storage for Disks.

  • 磁碟等量分割︰如需更多的輸送量,您可以新增其他資料磁碟,並使用「磁碟等量分割」。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 to analyze the number of IOPS and bandwidth required for your log file(s), and for your data and TempDB file(s). 請注意,不同的 VM 大小在支援的 IOPS 和頻寬數目上有不同的限制,請參閱每個 VM 大小的 IOPS 相關表格。Notice that different VM sizes have different limits on the number of IOPs and bandwidth supported, see the tables on IOPS per VM size. 請使用下列指引:Use the following guidelines:

    • 若為 Windows 8/Windows Server 2012 以上版本,請遵循下列指導方針使用儲存空間For Windows 8/Windows Server 2012 or later, use Storage Spaces with the following guidelines:

      1. 將 OLTP 工作負載的間隔 (等量磁碟區大小) 設為 64 KB (65536 位元組),資料倉儲的工作負載則設為 256 KB (262144 位元組),以避免分割對齊錯誤影響效能。Set the interleave (stripe size) to 64 KB (65536 bytes) for OLTP workloads and 256 KB (262144 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 個以上的磁碟 (不是伺服器管理員 UI) 時,使用 PowerShell。Use PowerShell when configuring more than 8 disks (not Server Manager UI).

      例如,下列 PowerShell 會建立新的儲存體集區,其間隔大小為 64 KB,且資料行數目為 2︰For example, the following PowerShell creates a new storage pool with the interleave size 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 
      
    • 對於 Windows 2008 R2 之前的版本,可以使用動態磁碟 (OS 分割的磁碟區),且等量磁碟區的大小一律為 64 KB。For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB. 請注意,Windows 8/Windows Server 2012 已不再提供此選項。Note that this option is deprecated as of Windows 8/Windows Server 2012. 如需相關資訊,請參閱 虛擬磁碟服務正轉換為 Windows 存放管理 API中的支援聲明。For information, see the support statement at Virtual Disk Service is transitioning to Windows Storage Management API.

    • 如果您的工作負載不需大量記錄及,且不需專屬於 IOP,您可以只設定一個儲存體集區。If your workload is not log intensive and does not need dedicated IOPs, you can configure just one storage pool. 否則,請建立兩個儲存體集區,一個用於儲存記錄檔案,另一個用於儲存資料檔案和存放 TempDB。Otherwise, create two storage pools, one for the log file(s) and another storage pool for the data file(s) and TempDB. 請根據您預期的負載量,決定與每個儲存體集區相關聯的磁碟數量。Determine the number of disks associated with each storage pool based on your load expectations. 請注意,各 VM 大小所允許連接的資料磁碟數量皆不同。Keep in mind that different VM sizes allow different numbers of attached data disks. 如需相關資訊,請參閱虛擬機器的大小For more information, see Sizes for Virtual Machines.

    • 如果您不是使用「進階儲存體」(開發/測試案例),建議您新增您 VM 大小 所支援的最大數目資料磁碟,並使用「磁碟等量分割」。If you are not using Premium Storage (dev/test scenarios), the recommendation is to add the maximum number of data disks supported by your VM size and use Disk Striping.

  • 快取原則:針對「進階儲存體」資料磁碟,請只在裝載資料檔和 TempDB 的資料磁碟上啟用讀取快取。Caching policy: For Premium Storage data disks, enable read caching on the data disks hosting your data files and TempDB only. 如果您並非使用進階儲存體,請勿啟用任何資料磁碟上的任何快取功能。If you are not using Premium Storage, do not enable any caching on any data disks. 如需有關設定磁碟快取功能的指示,請參閱下列主題。For instructions on configuring disk caching, see the following topics. 對於傳統 (ASM) 部署模型,請參閱:Set-azureosdiskSet-azuredatadiskFor the classic (ASM) deployment model see: Set-AzureOSDisk and Set-AzureDataDisk. 對於 Azure Resource Manager 部署模型,請參閱:Set-AzureRMOSDiskSet-AzureRMVMDataDiskFor the Azure Resource Manager deployment model see: Set-AzureRMOSDisk and Set-AzureRMVMDataDisk.

    警告

    變更 Azure VM 磁碟的快取設定時,請停止 SQL Server 服務,以避免任何發生資料庫損毀的可能性。Stop the SQL Server service when changing the cache setting of Azure VM disks to avoid the possibility of any database corruption.

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

  • 磁碟管理最佳做法︰移除資料磁碟或變更其快取類型時,請於變更期間停止 SQL Server 服務。Disk management best practices: When removing a data disk or changing its cache type, stop the SQL Server service during the change. 當 OS 磁碟上的快取設定發生變更時,Azure 會停止 VM、變更快取類型,然後重新啟動 VM。When the caching settings are changed on the OS disk, Azure stops the VM, changes the cache type, and restarts the VM. 當資料磁碟的快取設定發生變更時,系統不會停止 VM,但在變更期間會從 VM 卸離資料磁碟,然後再重新連接。When the cache settings of a data disk are changed, the VM is not stopped, but the data disk is detached from the VM during the change and then reattached.

    警告

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

I/O 指引I/O guidance

  • 平行處理應用程式和要求時,進階儲存體可以達到最佳效能。The best results with Premium Storage are achieved when you parallelize your application and requests. 進階儲存體是專為 IO 佇列深度大於 1 的案例所設計,所以您會發現單一執行緒的序列要求 (即使其為儲存密集型) 的效能只有微幅提升,或沒有提升。Premium Storage is designed for scenarios where the IO queue depth is greater than 1, so you will see little or no performance gains for single-threaded serial requests (even if they are storage intensive). 舉例來說,這可能會影響效能分析工具 (如 SQLIO) 的單一執行緒測試結果。For example, this could impact the single-threaded test results of performance analysis tools, such as SQLIO.

  • 請考慮使用 資料庫頁面壓縮 功能,其有助於改善 I/O 密集型工作負載的效能。Consider using database page compression as it can help improve performance of I/O intensive workloads. 不過,資料壓縮可能會增加資料庫伺服器的 CPU 使用量。However, the data compression might increase the CPU consumption on the database server.

  • 請考慮啟用 [立即檔案初始化] 功能,以減少配置初始檔案所需的時間。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 are 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 is not 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 considered to be merely a contingency for unexpected growth. 請勿每天使用「自動成長」功能,管理資料和記錄成長。Do not 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.

  • 將所有的資料庫 (包括系統資料庫) 移到資料磁碟。Move all databases to data disks, including system databases. 如需詳細資訊,請參閱 移動系統資料庫For more information, see Move System Databases.

  • 將 SQL Server 的錯誤記錄檔和追蹤檔案目錄移至資料磁碟。Move SQL Server error log and trace file directories to data disks. 在 SQL Server 組態管理員中,以滑鼠右鍵按一下您的 SQL Server 執行個體並選取內容,即可完成。This can be done in SQL Server Configuration Manager by right-clicking your SQL Server instance and selecting properties. 您可以在 [啟動參數] 索引標籤中變更錯誤記錄和追蹤檔案設定。若要指定「傾印目錄」,則是在 [進階] 索引標籤中指定。下列螢幕擷取畫面顯示錯誤記錄啟動參數的位置。The error log and trace file settings can be changed in the Startup Parameters tab. The Dump Directory is specified in the Advanced tab. The following screenshot shows where to look for the error log startup parameter.

    SQL ErrorLog 螢幕擷取畫面

  • 設定預設備份和資料庫檔案位置。Setup default backup and database file locations. 請使用本主題中的建議,並在 [伺服器屬性] 視窗中進行變更。Use the recommendations in this topic, 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 demonstrates where to make these changes.

    SQL 資料記錄和備份檔案

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

  • 如果您執行的是 SQL Server 2012,請安裝 Service Pack 1 累計更新 10。If you are running SQL Server 2012, install Service Pack 1 Cumulative Update 10. 此更新包含一個修正程式,可修正在 SQL Server 2012 中執行 select into 暫存資料表陳述式時,I/O 效能不佳的狀況。This update contains the fix for poor performance on I/O when you execute select into temporary table statement in SQL Server 2012. 如需相關資訊,請參閱此 知識庫文章For information, see this knowledge base article.

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

特定功能指引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 can help you to achieve better performance:

後續步驟Next Steps

如需安全性的最佳作法,請參閱 Azure 虛擬機器中的 SQL Server 安全性考量For security best practices, see Security Considerations for SQL Server in Azure Virtual Machines.

請檢閱 Azure 虛擬機器上的 SQL Server 概觀中的其他「SQL Server 虛擬機器」主題。Review other SQL Server Virtual Machine topics at SQL Server on Azure Virtual Machines Overview.