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

概述Overview

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

Tip

通常需要在针对成本优化和针对性能优化之间进行权衡。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 的 VM 大小,例如 E4S_v3 或更大,或者 DS12_v2 或更大。- Use VM sizes with 4 or more vCPU like E4S_v3 or higher, or DS12_v2 or higher.

- Es 和 Ds 系列提供最佳的内存-vCPU 比,可满足 OLTP 工作负荷的性能需求。- Es and Ds Series offers the optimum memory to vCPU ratio required for OLTP workload performance.

- M 系列提供最高的内存-vCPU 比,可满足任务关键型工作负荷的性能需求,非常适合用于数据仓库工作负荷。- 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 性能测试结果,请参阅博客文章 Optimize OLTP performance(优化 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. 为数据文件配置只读缓存,不要为日志文件配置缓存。Configure ReadOnly cache for data files and no cache for the log file.

- 在选择磁盘类型之前,通过监视应用程序来收集 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. 如果存储延迟必须小于 1 毫秒,请使用高级 SSD。If <1ms storage latencies are required, then use premium SSD.

- 建议使用高级文件共享作为 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).

- 对于要求 IO 延迟小于 1 毫秒的工作负荷,请为 M 系列启用写入加速器。- For workloads requiring <1 ms IO latencies, enable write accelerator for M series.

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

- 为 SQL Server 数据、日志和 TempDB 文件配置存储时,请添加比工作负荷所需的高级 IOPS/吞吐量容量多出 20% 的容量- 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,并将临时数据库置于本地磁盘上,则无需执行任何其他操作;对于所有其他情况,请遵循博客文章 Using SSDs to store TempDB(使用 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. 如果本地驱动器的容量不足以存储临时数据库,请将临时数据库置于具有只读缓存的高级 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.

- 使用多个临时数据库文件,每个核心处理 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 计数器收集数据、日志和临时数据库文件的存储吞吐量要求\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. 例如,如果工作负荷在高峰期需要 20K 次读取 IOPS 和 10K 次写入 IOPS,则可以选择 E16s_v3(最高提供 32K 次缓存的 IOPS 和 25600 次未缓存的 IOPS),或包含 2 个 P30 磁盘的 M16_s(最高提供 20K 次缓存的 IOPS 和 10K 次未缓存的 IOPS)。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 性能测试结果,请参阅博客文章 Optimize OLTP performance(优化 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.

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

Warning

标准 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.

此外,我们建议在 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. 此磁盘是一个 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,并将临时数据库置于本地磁盘上,则无需执行任何其他操作;对于所有其他情况,请遵循博客文章 Using SSDs to store TempDB(使用 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. 如果本地驱动器的容量不足以存储临时数据库,请将临时数据库置于具有只读缓存的高级 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.

    Tip

    Note

    在门户中预配 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. 对于 OLTP 工作负荷,将交错(条带大小)设置为 64 KB(65,536 字节),对于数据仓库工作负荷,将交错(条带大小)设置为 256 KB(262,144 字节),以避免分区定位错误导致的性能影响。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 资源管理器部署模型,请参阅:Set-AzOSDiskSet-AzVMDataDiskFor the Azure Resource Manager deployment model, see: Set-AzOSDisk and Set-AzVMDataDisk.

      Warning

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

    Warning

    在进行这些操作时,如果无法停止 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.