您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

Azure 虚拟机中的 SQL Server 的性能准则Performance guidelines for SQL Server in Azure Virtual Machines

概述Overview

本文提供了有关在 Microsoft Azure 虚拟机中优化 SQL Server 性能的最佳做法。This article provides guidance 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.

在 Azure 门户中预配的 SQL Server 映像遵循一般的存储配置最佳做法(有关存储配置情况的详细信息,请参阅 SQL Server VM 的存储配置)。SQL Server images provisioned in the Azure portal follow general storage configuration best practices (for more information on how storage is configured, see Storage configuration for SQL Server VMs). 在预配后,请考虑应用本文中讨论的其他优化措施。After provisioning, consider applying other optimizations discussed in this article. 根据你的工作负荷进行选择并通过测试进行验证。Base your choices on your workload and verify through testing.

提示

通常需要在针对成本优化和针对性能优化之间进行权衡。There is typically a trade-off between optimizing for costs and optimizing for performance. 本文重点介绍获得 SQL Server 在 Azure VM 上的最佳性能。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, costs, and workload patterns as you evaluate these recommendations.

快速检查列表Quick check list

下面是 SQL Server 在 Azure 虚拟机中的优化性能快速检查列表:The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

区域Area 优化Optimizations
VM 大小VM size -使用具有4个或更多 vCPU (如E4S_v3或更高版本)或DS12_v2或更高版本的 VM 大小。- Use VM sizes with 4 or more vCPU like E4S_v3 or higher, or DS12_v2 or higher.

- Es、Eas、Ds 和 Das 系列提供对 OLTP 工作负荷性能所需的 vCPU 比率的最佳内存。- Es, Eas, Ds and Das Series offers the optimum memory to vCPU ratio required for OLTP workload performance.

- M 系列提供关键任务性能所需的最高内存,非常适合用于数据仓库工作负荷。- M Series offers the highest memory to vCPU ratio required for mission critical performance and is ideal for data warehouse workloads.

-按照应用程序性能要求清单,在高峰时间收集目标工作负荷的IOPS吞吐量延迟要求,然后选择可扩展到的VM 大小工作负荷的性能要求。- Collect the target workload's IOPS, throughput and latency requirements at peak times by following the application performance requirements checklist and then select the VM Size that can scale to your workload's performance requirements.
存储Storage -若要在具有 TPC E 和 TPC_C 基准的 Azure Vm 上进行 SQL Server 性能的详细测试,请参阅博客优化 OLTP 性能- For detailed testing of SQL Server performance on Azure VMs with TPC-E and TPC_C benchmarks, refer to the blog Optimize OLTP performance.

-使用高级 ssd以获得最佳的价格/性能优势。- Use premium SSDs for the best price/performance advantages. 为数据文件配置ReadOnly 缓存,而不为日志文件配置缓存。Configure ReadOnly cache for data files and no cache for the log file.

-如果工作负荷需要的存储延迟少于1毫秒,则使用超磁盘- Use Ultra Disks if less than 1 ms storage latencies are required by the workload.

-通过监视应用程序,然后选择磁盘类型,收集 SQL Server 数据、日志和临时数据库文件的存储延迟要求。- Collect the storage latency requirements for SQL Server data, log, and Temp DB files by monitoring the application before choosing the disk type. 如果需要 < 1ms 存储延迟,则使用超磁盘,否则使用高级 SSD。If <1ms storage latencies are required, then use Ultra Disks, otherwise use premium SSD. 如果只是日志文件需要较低的延迟时间,而不是数据文件的延迟,则仅为日志文件预配所需 IOPS 和吞吐量级别的超磁盘If low latencies are only required for the log file and not for data files, then provision the Ultra Disk at required IOPS and throughput levels only for the log File.

建议将 - 高级文件共享作为 SQL Server 故障转移群集实例的共享存储。- Premium file shares are recommended as shared storage for a SQL Server failover cluster Instance. 高级文件共享不支持缓存,与高级 SSD 磁盘相比,性能有所限制。Premium file shares do not support caching, and offer limited performance compared to premium SSD disks. 为独立 SQL 实例通过高级文件共享选择高级 SSD 托管磁盘;但利用高级文件共享来实现故障转移群集实例共享存储,以简化维护和灵活的可伸缩性。Choose premium SSD-managed disks over premium file shares for standalone SQL instances; but leverage premium file shares for failover cluster instance shared storage for ease of maintenance and flexible scalability.

-标准存储只推荐用于开发和测试目的或备份文件,不应用于生产工作负荷。- Standard storage is only recommended for development and test purposes or for backup files and should not be used for production workloads.

- 将存储帐户和 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个高级 SSD 磁盘(1表示日志文件,1用于数据文件)。- Use a minimum of 2 premium SSD disks (1 for log file and 1 for data files).

-对于需要 < 1 ms IO 延迟的工作负荷,请为 M 系列启用写入加速器,并考虑对 Es 和 DS 系列使用超级 SSD 磁盘。- For workloads requiring <1 ms IO latencies, enable write accelerator for M series and consider using Ultra SSD disks for Es and DS series.

-在托管数据文件的磁盘上启用只读缓存- Enable read only caching on the disk(s) hosting the data files.

-在为 SQL Server 数据、日志和 TempDB 文件配置存储时,添加比你的工作负荷所需的额外的20% 高级 IOPS/吞吐量容量- Add additional 20% premium IOPS/throughput capacity than your workload requires when configuring storage for SQL Server data, log, and TempDB files

- 避免使用操作系统或临时磁盘进行数据库存储或日志记录。- Avoid using operating system or temporary disks for database storage or logging.

- 请勿在托管日志文件的磁盘上启用缓存。- 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 数据磁盘,以提高存储吞吐量。- Stripe multiple Azure data disks to get increased storage throughput.

- 使用规定的分配大小格式化。- Format with documented allocation sizes.

- 将 TempDB 放在本地 SSD D:\ 驱动器上,用于任务关键型 SQL Server 工作负荷(在选择正确的 VM 大小后)。- Place TempDB on the local SSD D:\ drive for mission critical SQL Server workloads (after choosing correct VM size). 如果从 Azure 门户或 Azure 快速入门模板创建 VM,并将 TEMP DB 置于本地磁盘上,则无需执行任何进一步操作;对于所有其他情况,请按照博客中的步骤使用 ssd 存储 TempDB来防止在重新启动后发生故障。If you create the VM from Azure portal or Azure quickstart templates and place Temp DB on the Local Disk then you do not need any further action; for all other cases follow the steps in the blog for Using SSDs to store TempDB to prevent failures after restarts. 如果本地驱动器的容量不足以满足临时数据库大小,请将 Temp DB 置于使用只读缓存在高级 SSD 磁盘上剥离的存储池中。If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool stripped on premium SSD disks with read-only caching.
I/OI/O - 启用数据库页面压缩。- Enable database page compression.

- 对数据文件启用即时文件初始化。- Enable instant file initialization for data files.

- 限制数据库自动增长。- Limit autogrowth of the database.

- 禁用数据库自动收缩。- Disable autoshrink of 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.

-配置默认备份和数据库文件位置。- Configure default backup and database file locations.

- 在内存中启用锁定页面- Enable locked pages in memory.

- 应用 SQL Server 性能修复程序。- Apply SQL Server performance fixes.
Feature-specificFeature-specific - 直接备份到 blob 存储。- Back up directly to blob storage.

-使用大于 12 TB 的数据库的文件快照备份- Use file snapshot backups for databases larger than 12 TB.

-使用多个 Temp DB 文件,每个核心1个文件,最多8个文件。- Use multiple Temp DB files, 1 file per core, up to 8 files.

-将操作系统的最大服务器内存设置为90% 或更高,最多为 50 GB。- Set max server memory at 90% or up to 50 GB left for the Operating System.

-启用软 NUMA。- Enable soft NUMA.

有关如何和为何进行这些优化的详细信息,请参阅以下部分提供的详细信息与指南。For more information on how and why to make these optimizations, please review the details and guidance provided in the following sections.

VM 大小指导原则VM size guidance

首先收集高峰时间工作负荷的 cpu、内存和存储吞吐量要求。Start by collecting the cpu, memory, and storage throughput requirements of the workload at peak times. \LogicalDisk\Disk Reads/Sec 和 \LogicalDisk\Disk Writes/Sec 性能计数器可用于收集读取和写入 IOPS 要求,并可使用 \LogicalDisk\Disk Bytes/Sec 计数器来收集数据、日志、和 Temp DB 文件。\LogicalDisk\Disk Reads/Sec and \LogicalDisk\Disk Writes/Sec performance counters can be used to collect read and write IOPS requirements and \LogicalDisk\Disk Bytes/Sec counter can be used to collect storage throughput requirements for Data, Log, and Temp DB files. 定义了峰值的 IOPS 和吞吐量要求后,评估 VM 大小会提供该容量。After IOPS and throughput requirements at peak are defined then evaluate VM sizes offers that capacity. 例如,如果你的工作负荷在高峰期需要 20 K 读取 IOPS 和10K 写入 IOPS,则可以选择 E16s_v3 (最多 32 K 缓存和25600未缓存 IOPS)或 M16_s (最多 20 K 缓存和10K 未缓存的 IOPS)和2个 P30 磁盘。For example if your workload requires 20 K read IOPS and 10K write IOPS at peak, you can either choose E16s_v3 (with up to 32 K cached and 25600 uncached IOPS) or M16_s (with up to 20 K cached and 10K uncached IOPS) with 2 P30 disks. 请确保了解工作负荷的吞吐量和 IOPS 要求,因为 Vm 具有不同的 IOPS 和吞吐量的规模限制。Make sure to understand both throughput and IOPS requirements of the workload as VMs has different scale limits for IOPS and throughput.

DSv_3 和 Es_v3 系列在具有 Intel Haswell 或 Broadwell 处理器的常规用途硬件上托管。DSv_3 and Es_v3-series are hosted on general purpose hardware with Intel Haswell or Broadwell processors. M 系列为最大的 SQL Server 工作负荷提供最高的 vCPU 计数和内存,并在 Skylake 处理器系列上托管在内存优化硬件上。M-series offers the highest vCPU count and memory for the largest SQL Server workloads and hosted on memory optimized hardware with Skylake processor family. 这些 VM 系列支持高级存储,建议使用主机级别的读取缓存来实现最佳性能。These VM series support premium storage, which is recommended for the best performance with host level read cache. Es_v3 和 M 系列也可用于受约束的核心大小,这为具有较低计算和高存储容量需求的工作负荷节省资金。Both Es_v3 and M series are also available in constrained core sizes, which saves money for workloads with lower compute and high storage capacity demands.

存储指导原则Storage guidance

若要详细测试具有 TPC E 和 TPC_C 基准的 Azure Vm 上的 SQL Server 性能,请参阅博客优化 OLTP 性能For detailed testing of SQL Server performance on Azure VMs with TPC-E and TPC_C benchmarks, refer to the blog Optimize OLTP performance.

对于所有生产工作负荷,建议使用高级 Ssd 的 Azure blob 缓存。Azure blob cache with premium SSDs is recommended for all production workloads.

警告

标准 HDD 和 SSD 具有不同的延迟和带宽,建议仅用于开发/测试工作负荷。Standard HDDs and SSDs have varying latencies and bandwidth and are only recommended for dev/test workloads. 生产工作负荷应使用高级 SSD。Production workloads should use premium SSDs.

此外,我们建议创建 Azure 存储帐户与 SQL Server 虚拟机在同一数据中心中,以减小传输延迟。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. 此磁盘是一个 VHD,在存储空间中存储为一个页 blob。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

操作系统磁盘是可以作为操作系统的运行版本来启动和装载的 VHD,并且标记为 C 驱动器。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.

将 TempDB 置于本地 SSD D:\ 驱动器上,用于任务关键型 SQL Server 工作负荷(在选择正确的 VM 大小之后)。Place TempDB on the local SSD D:\ drive for mission critical SQL Server workloads (after choosing correct VM size). 如果从 Azure 门户或 Azure 快速入门模板创建 VM,并将 TEMP DB 置于本地磁盘上,则无需执行任何其他操作;对于所有其他情况,请按照博客中的步骤使用 ssd 存储 TempDB来防止在重新启动后发生故障。If you create the VM from Azure portal or Azure quickstart templates and place Temp DB on the Local Disk, then you do not need any further action; for all other cases follow the steps in the blog for Using SSDs to store TempDB to prevent failures after restarts. 如果本地驱动器的容量不足以满足临时数据库大小,请将 Temp DB 置于使用只读缓存在高级 SSD 磁盘上剥离的存储池中。If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool stripped on premium SSD disks with read-only caching.

对于支持高级 Ssd 的 Vm,还可以将 TempDB 存储在支持高级 Ssd 且启用了读取缓存的磁盘上。For VMs that support premium SSDs, you can also store TempDB on a disk that supports premium SSDs with read caching enabled.

数据磁盘数Data disks

  • 将高级 ssd 磁盘用于数据和日志文件:如果不使用磁盘条带化,请使用两个高级 SSD 磁盘,其中一个磁盘包含日志文件,另一个磁盘包含数据。Use premium SSD disks for data and log files: If you are not using disk striping, use two premium SSD disks where one disk contains the log file and the other contains the data. 每个高级 SSD 根据其大小提供了许多 IOPS 和带宽(MB/s),如文章中所述,选择磁盘类型Each premium SSD provides a number of IOPS and bandwidth (MB/s) depending on its size, as depicted in the article, Select a disk type. 如果使用磁盘条带化技术,例如存储空间,则可实现最佳性能,因为将具有两个池,一个用于日志文件,另一个用于数据文件。If you are using a disk striping technique, such as Storage Spaces, you achieve optimal performance by having two pools, one for the log file(s) and the other for the data files. 但是,如果你计划使用 SQL Server 故障转移群集实例(FCI),则必须配置一个池,或者改为使用高级文件共享However, if you plan to use SQL Server failover cluster instances (FCI), you must configure one pool, or utilize premium file shares instead.

    提示

    备注

    在门户中预配 SQL Server VM 时,你可以编辑存储配置。When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. Azure 将根据你所做的配置来配置一个或多个磁盘。Depending on your configuration, Azure configures one or more disks. 使用条带化,可将多个磁盘组合到单个存储池中。Multiple disks are combined into a single storage pool with striping. 数据文件和日志文件一起位于此配置中。Both the data and log files reside together in this configuration. 有关详细信息,请参阅 SQL Server VM 的存储配置For more information, see Storage configuration for SQL Server VMs.

  • 磁盘条带化:为提高吞吐量,可以添加更多的数据磁盘,并使用磁盘条带化。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 大小对受支持的 IOP 数量和带宽有不同的限制,请参阅每个 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. 对于数据仓库工作负荷,请将 "交错(条带大小)" 设置为 64 KB (65536个字节),对于数据仓库工作负荷,请将其设置为 "256 KB (262144字节)",以避免由于分区Set the interleave (stripe size) to 64 KB (65,536 bytes) for 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 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 或更早版本,可以使用动态磁盘(操作系统条带化卷),条带大小始终为 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 中,此选项已被弃用。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.

    • 如果将存储空间直通 (S2D)SQL Server 故障转移群集实例配合使用,则必须配置单个池。If you are using Storage Spaces Direct (S2D) with SQL Server Failover Cluster Instances, you must configure a single pool. 尽管可以在该单一池上创建不同的卷,但它们将共享相同的特征,例如相同的缓存策略。Although different volumes can be created on that single pool, they will all share the same characteristics, such as the same caching policy.

    • 根据负载预期确定与你的存储池相关联的磁盘数。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. 有关详细信息,请参阅虚拟机的大小For more information, see Sizes for Virtual Machines.

    • 如果使用的不是高级 SSD(开发/测试方案),建议添加 VM 大小支持的最大数量的数据磁盘并使用磁盘条带化。If you are not using premium SSDs (dev/test scenarios), the recommendation is to add the maximum number of data disks supported by your VM size and use Disk Striping.

  • 缓存策略:请注意用于缓存策略的以下建议,具体取决于你的存储配置。Caching policy: Note the following recommendations for caching policy depending on your storage configuration.

    • 如果为数据文件和日志文件使用不同的磁盘,请在承载着数据文件和 TempDB 数据文件的数据磁盘上启用读取缓存。If you are using separate disks for data and log files, enable read caching on the data disks hosting your data files and TempDB data files. 这可能会明显提高性能。This can result in a significant performance benefit. 不要在存放日志文件的磁盘上启用缓存,因为这会导致性能稍微降低。Do not enable caching on the disk holding the log file as this causes a minor decrease in performance.

    • 如果在单个存储池中使用磁盘条带化,则大多数工作负荷都会从读取缓存受益。If you are using disk striping in a single storage pool, most workloads will benefit from read caching. 如果日志文件和数据文件分别具有单独的存储池,请仅在数据文件的存储池上启用读取缓存。If you have separate storage pools for the log and data files, enable read caching only on the storage pool for the data files. 在某些高写入工作负荷中,不使用缓存时可能会获得更好的性能。In certain heavy write workloads, better performance might be achieved with no caching. 这只能通过测试来确定。This can only be determined through testing.

    • 前面的建议适用于高级 SSD。The previous recommendations apply to premium SSDs. 如果使用的不是高级 SSD,不要在任何数据磁盘上启用任何缓存。If you are not using premium SSDs, do not enable any caching on any data disks.

    • 有关配置磁盘缓存的说明,请参阅以下文章。For instructions on configuring disk caching, see the following articles. 有关经典 (ASM) 部署模型,请参阅 Set-AzureOSDiskSet-AzureDataDiskFor the classic (ASM) deployment model see: Set-AzureOSDisk and Set-AzureDataDisk. 对于 Azure 资源管理器部署模型,请参阅: AzOSDisk and AzVMDataDiskFor the Azure Resource Manager deployment model, see: Set-AzOSDisk and Set-AzVMDataDisk.

      警告

      请在更改 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

  • 并行化应用程序和请求时可实现使用高级 SSD 的最佳结果。The best results with premium SSDs are achieved when you parallelize your application and requests. 高级 SSD 专为 IO 队列深度大于 1 的方案设计,因此对于单线程串行请求(即使它们是存储密集型),不会看到明显的性能提升。Premium SSDs are 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. 有关详细信息,请参阅 Move 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 错误日志屏幕截图

  • 设置默认的备份和数据库文件位置。Setup default backup and database file locations. 使用本文中的建议,并在“服务器属性”窗口中进行更改。Use the recommendations in this article, and make the changes in the Server properties window. 有关说明,请参阅 View or Change the Default Locations for Data and Log Files (SQL Server Management Studio)(查看或更改数据和日志文件的默认位置 (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. 有关详细信息,请参阅 Enable the Lock Pages in Memory Option (Windows)(启用在内存中锁定页面的选项 (Windows))。For more information, see Enable the Lock Pages in Memory Option (Windows).

  • 如果运行的是 SQL Server 2012,安装 Service Pack 1 Cumulative Update 10。If you are running SQL Server 2012, install Service Pack 1 Cumulative Update 10. 此更新包含修复程序,适用于在 SQL Server 2012 中执行“select into temporary table”语句时出现 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 时压缩所有数据文件。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:

备份到 Azure 存储Back up to Azure Storage

为在 Azure 虚拟机中运行的 SQL Server 执行备份时,可以使用 SQL Server 备份到 URLWhen performing backups for SQL Server running in Azure virtual machines, 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 最佳实践和故障排除以及从 Azure 存储中存储的备份还原中提供的建议操作。When you backup/restore to/from Azure storage, follow the recommendations provided at SQL Server Backup to URL Best Practices and Troubleshooting and Restoring from Backups Stored in Azure Storage. 此外还可以使用 Azure 虚拟机中 SQL Server 的自动备份自动执行这些备份。You can also automate these backups using Automated Backup for SQL Server in Azure Virtual Machines.

对于 SQL Server 2012 以前版本,可以使用 SQL Server 备份到 Azure 工具Prior to SQL Server 2012, you can use SQL Server Backup to Azure Tool. 此工具可以通过使用多个备份条带目标帮助提高备份吞吐量。This tool can help to increase backup throughput using multiple backup stripe targets.

Azure 中的 SQL Server 数据文件SQL Server Data files in Azure

Azure 中的 SQL Server 数据文件这一新功能从 SQL Server 2014 开始提供。This new feature, SQL Server Data Files in Azure, is available starting with SQL Server 2014. 使用 Azure 中的数据文件运行 SQL Server,与使用 Azure 数据磁盘时的性能特征相当。Running SQL Server with data files in Azure demonstrates comparable performance characteristics as using Azure data disks.

故障转移群集实例和存储空间Failover cluster instance and Storage Spaces

如果正在使用存储空间,则在“确认”页上向群集添加节点时,请清除标记为“将所有符合条件的存储添加到群集”的复选框。If you are using Storage Spaces, when adding nodes to the cluster on the Confirmation page, clear the checkbox labeled Add all eligible storage to the cluster.

取消选中符合条件的存储

如果正在使用存储空间,且选中了“将所有符合条件的存储添加到群集”,Windows 会在群集进程中分离虚拟磁盘。If you are using Storage Spaces and do not uncheck Add all eligible storage to the cluster, Windows detaches the virtual disks during the clustering process. 这样一来,这些虚拟磁盘将不会出现在磁盘管理器或资源管理器之中,除非从群集中删除存储空间,并使用 PowerShell 将其重新附加。As a result, they do not appear in Disk Manager or Explorer until the storage spaces are removed from the cluster and reattached using PowerShell. 存储空间将多个磁盘集合到存储池中。Storage Spaces groups multiple disks in to storage pools. 有关详细信息,请参阅存储空间For more information, see Storage Spaces.

后续步骤Next steps

有关存储和性能的详细信息,请参阅 Azure VM 上的 SQL Server 的存储配置准则For more information about storage and performance, see Storage Configuration Guidelines for SQL Server on Azure VM

有关安全最佳实践,请参阅 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 articles at SQL Server on Azure Virtual Machines Overview. 如果对 SQL Server 虚拟机有任何疑问,请参阅常见问题If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.