清单:有关 Azure VM 上 SQL Server 性能的最佳做法Checklist: Performance best practices for SQL Server on Azure VMs

适用于: Azure VM 上的 SQL Server

本文提供一个可用于快速查看有关 Azure 虚拟机 (VM) 上 SQL Server 性能优化的一系列最佳做法和指南的清单。This article provides a quick checklist as a series of best practices and guidelines to optimize performance of your SQL Server on Azure Virtual Machines (VMs).

如需了解更全面详尽的信息,请参阅本系列中的其他文章:VM 大小存储收集基线For comprehensive details, see the other articles in this series: VM size, Storage, Collect baseline.

概述Overview

在 Azure 虚拟机上运行 SQL Server 时,继续使用适用于本地服务器环境中的 SQL Server 的相同数据库性能优化选项。While running SQL Server on Azure Virtual Machines, continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environments. 但是,关系数据库在公有云中的性能取决于许多因素,如虚拟机的大小和数据磁盘的配置。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.

通常需要在针对成本优化和针对性能优化之间进行权衡。There is typically a trade-off between optimizing for costs and optimizing for performance. 这一系列性能最佳做法侧重于实现 Azure 虚拟机上 SQL Server 的最佳性能。This performance best practices series is focused on getting the best performance for SQL Server on Azure Virtual Machines. 如果工作负荷要求较低,可能不需要每项建议的优化。If your workload is less demanding, you might not require every recommended optimization. 评估这些建议时应考虑性能需求、成本和工作负荷模式。Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

VM 大小VM Size

下面是有关在 Azure VM 上运行 SQL Server 时应选择的 VM 大小的最佳做法的快速清单:The following is a quick checklist of VM size best practices for running your SQL Server on Azure VM:

  • 使用具有 4 个或更多 vCPU 的 VM 大小,如 Standard_M8-4msE4ds_v4DS12_v2,或使用更大的大小。Use VM sizes with 4 or more vCPU like the Standard_M8-4ms, the E4ds_v4, or the DS12_v2 or higher.
  • 使用内存优化的虚拟机大小,以实现 SQL Server 工作负载的最佳性能。Use memory optimized virtual machine sizes for the best performance of SQL Server workloads.
  • DSv2 11-15Edsv4 系列和 M- 系列提供 OLTP 工作负载所需的最佳内存与 vCore 比率。The DSv2 11-15, Edsv4 series, and the M- series offer the optimal memory-to-vCore ratio required for OLTP workloads. 这两个 M 系列的 VM 都提供最高的内存与 vCore 比率,可满足任务关键型工作负载的需求,也非常适合用于数据仓库工作负载。Both M series VMs offer the highest memory-to-vCore ratio required for mission critical workloads and are also ideal for data warehouse workloads.
  • 对于任务关键型工作负载和数据仓库工作负载,考虑应用更高的内存与 vCore 比率。Consider a higher memory-to-vCore ratio for mission critical and data warehouse workloads.
  • 使用 Azure 虚拟机市场映像作为 SQL Server 设置,并配置存储选项以获得最佳 SQL Server 性能。Use the Azure Virtual Machine marketplace images as the SQL Server settings and storage options are configured for optimal SQL Server performance.
  • 收集目标工作负载的性能特征,并使用它们来确定适用于你的业务的 VM 大小。Collect the target workload's performance characteristics and use them to determine the appropriate VM size for your business.

若要了解详细信息,请参阅内容全面的 VM 大小最佳做法To learn more, see the comprehensive VM size best practices.

存储Storage

下面是有关在 Azure VM 上运行 SQL Server 时的存储配置的最佳做法的快速清单:The following is a quick checklist of storage configuration best practices for running your SQL Server on Azure VM:

  • 在选择磁盘类型之前,监视应用程序并确定 SQL Server 数据、日志和 tempdb 文件的存储带宽和延迟要求Monitor the application and determine storage bandwidth and latency requirements for SQL Server data, log, and tempdb files before choosing the disk type.
  • 为了优化存储性能,请规划可用的最高未缓存 IOPS,并使用数据缓存作为数据读取的性能功能,同时避免虚拟机和磁盘上限/限制To optimize storage performance, plan for highest uncached IOPS available and use data caching as a performance feature for data reads while avoiding virtual machine and disks capping/throttling.
  • 将数据、日志和 tempdb 文件放在不同的驱动器上。Place data, log, and tempdb files on separate drives.
    • 对于数据驱动器,仅使用高级 P30 和 P40 磁盘以确保可提供缓存支持For the data drive, only use premium P30 and P40 disks to ensure the availability of cache support
    • 对于日志驱动器,规划容量并测试性能与成本,同时评估高级 P30 - P80 磁盘For the log drive plan for capacity and test performance versus cost while evaluating the premium P30 - P80 disks.
    • 在选择最佳 VM 大小后,请将 tempdb 放置在大多数 SQL Server 工作负载的本地临时 SSD D:\ 驱动器上。Place tempdb on the local ephemeral SSD D:\ drive for most SQL Server workloads after choosing the optimal VM size.
      • 如果本地驱动器的容量对 tempdb 来说不足够,请考虑增加 VM 的大小。If the capacity of the local drive is not enough for tempdb, consider sizing up the VM. 有关详细信息,请参阅数据文件缓存策略See Data file caching policies for more information.
  • 使用存储空间对多个 Azure 数据磁盘进行条纹化,以将 I/O 带宽增加到目标虚拟机的 IOPS 和吞吐量上限。Stripe multiple Azure data disks using Storage Spaces to increase I/O bandwidth up to the target virtual machine's IOPS and throughput limits.
  • 将数据文件磁盘的主机缓存设置为只读。Set host caching to read-only for data file disks.
  • 将日志文件磁盘的主机缓存设置为无。Set host caching to none for log file disks.
    • 请不要在包含 SQL Server 文件的磁盘上启用读取/写入缓存。Do not enable read/write caching on disks that contain SQL Server files.
    • 更改磁盘的缓存设置之前,请始终停止 SQL Server 服务。Always stop the SQL Server service before changing the cache settings of your disk.
  • 对于开发和测试工作负荷,请考虑使用标准存储。For development and test workloads consider using standard storage. 不建议将标准 HDD/SDD 用于生产工作负载。It is not recommended to use Standard HDD/SDD for production workloads.
  • 基于额度的磁盘突发 (P1-P20) 仅应考虑用于较小的开发/测试工作负载和部门系统。Credit-based Disk Bursting (P1-P20) should only be considered for smaller dev/test workloads and departmental systems.
  • 预配与 SQL Server VM 位于同一区域的存储帐户。Provision the storage account in the same region as the SQL Server VM.
  • 在存储帐户上禁用 Azure 异地冗余存储(异地复制)并使用 LRS(本地冗余存储)。Disable Azure geo-redundant storage (geo-replication) and use LRS (local redundant storage) on the storage account.
  • 将数据磁盘格式化,为临时 D:\ 驱动器(默认为 4 KB)以外的驱动器上放置的所有数据文件使用 64 KB 的分配单元大小。Format your data disk to use 64 KB allocation unit size for all data files placed on a drive other than the temporary D:\ drive (which has a default of 4 KB). 通过 Azure 市场部署的 SQL Server VM 附带经过格式化的数据磁盘,其中分配单元大小和存储池的交错设置为 64 KB。SQL Server VMs deployed through Azure Marketplace come with data disks formatted with allocation unit size and interleave for the storage pool set to 64 KB.

若要了解详细信息,请参阅内容全面的存储最佳做法To learn more, see the comprehensive Storage best practices.

Azure 和 SQL 特定功能Azure & SQL feature specific

下面是有关在 Azure VM 上运行 SQL Server 时 SQL Server 和 Azure 特定配置的最佳做法的快速清单:The following is a quick checklist of best practices for SQL Server and Azure-specific configurations when running your SQL Server on Azure VM:

  • 启用数据库页面压缩。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 版本的最新累积更新Evaluate and apply the latest cumulative updates for the installed version of SQL Server.
  • 直接备份到 Azure Blob 存储。Back up directly to Azure Blob storage.
  • 使用多个 tempdb 文件,每个核心 1 个文件,最多 8 个文件。Use multiple tempdb files, 1 file per core, up to 8 files.

后续步骤Next steps

若要了解详细信息,请参阅本系列中的其他文章:To learn more, see the other articles in this series:

有关安全最佳做法,请参阅 Azure 虚拟机上 SQL Server 的安全注意事项For security best practices, see Security considerations for SQL Server on 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.