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

适用于: Azure VM 上的 SQL Server

本文提供了在 Azure 虚拟机中优化 SQL Server 性能的指南。This article provides guidance for optimizing SQL Server performance in Azure Virtual Machines.

概述Overview

在 Azure 虚拟机上运行 SQL Server 时,建议你继续使用适用于本地服务器环境中的 SQL Server 的相同数据库性能优化选项。While running SQL Server on 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 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.

在 Azure 门户中预配的 SQL Server 映像遵循常规存储配置最佳做法SQL Server images provisioned in the Azure portal follow general storage configuration best practices. 在预配后,请考虑应用本文中讨论的其他优化措施。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. 本文重点介绍了如何使 Azure 虚拟机上的 SQL Server 实现最佳性能。This article 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 optimization listed below. 评估这些建议时应考虑性能需求、成本和工作负荷模式。Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

快速核对清单Quick checklist

下面是一个快速核对清单,用于优化 Azure 虚拟机上的 SQL Server 性能:The following is a quick checklist for optimal performance of SQL Server on Azure Virtual Machines:

区域Area 优化Optimizations
VM 大小VM size - 使用具有 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 is also ideal for data warehouse workloads.

- 对于任务关键型工作负载和数据仓库工作负载,可能需要更高的内存与 vCore 比率。- A higher memory-to-vCore ratio may be required for mission critical and data warehouse workloads.

- 利用 Azure 虚拟机市场映像作为 SQL Server 设置,并配置存储选项以获得最佳 SQL Server 性能。- Leverage 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.
存储Storage - 若要详细了解根据 TPC-E 和 TPC_C 基准在 Azure 虚拟机上进行的 SQL Server 性能测试,请参阅博客:优化 OLTP 性能- For detailed testing of SQL Server performance on Azure Virtual Machines 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 Read only 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.

- 标准存储仅建议用于开发和测试目的或备份文件,不应将其用于生产工作负载。- 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 文件配置存储时,请在工作负荷的要求以外额外添加 20% 的高级 IOPS/吞吐容量。- Add an 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 虚拟机磁盘的缓存设置时,请停止 SQL Server 服务。Important: Stop the SQL Server service when changing the cache settings for an Azure Virtual Machines 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,并将临时数据库置于本地磁盘上,则无需执行任何其他操作;对于所有其他情况,请按博客文章使用 SSD 存储 TempDB 中的步骤操作,以防止重启后发生故障。If you create the VM from the 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. 如果本地驱动器的容量对于 TempDB 而言不足,请将 TempDB 放在位于具有只读缓存的高级 SSD 盘上的条带化存储池上。If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool striped 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 版本的最新累积更新- Evaluate and apply the latest cumulative updates for the installed version of SQL Server.
Feature-specificFeature-specific - 直接备份到 Azure Blob 存储。- Back up directly to Azure 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.

入门Getting started

如果要在 Azure VM 上创建新的 SQL Server,并且未迁移当前源系统,请根据供应商要求创建新的 SQL Server VM。If you are creating a new SQL Server on Azure VM and are not migrating a current source system, create your new SQL Server VM based on your vendor requirements. SQL Server VM 的供应商要求与在本地部署的要求相同。The vendor requirements for a SQL Server VM are the same as what you would deploy on-premises.

如果要使用为云生成的新应用程序创建新的 SQL Server VM,则随着数据和使用情况要求的变化,你可以轻松地调整 SQL Server VM 的大小。If you are creating a new SQL Server VM with a new application built for the cloud, you can easily size your SQL Server VM as your data and usage requirements evolve. 使用较低层 D 系列、B 系列或 Av2 系列开始开发环境,并随着时间的推移扩展你的环境。Start the development environments with the lower-tier D-Series, B-Series, or Av2-series and grow your environment over time.

针对生产 OLTP 环境的建议最小值是 4 个 vCore、32 GB 内存,以及内存与 vCore 比率为 8。The recommended minimum for a production OLTP environment is 4 vCore, 32 GB of memory, and a memory-to-vCore ratio of 8. 对于新环境,从有 4 个 vCore 的虚拟机开始,在数据和计算要求发生变化时缩放到 8 个、16 个、32 个或更多 vCore。For new environments, start with 4 vCore machines and scale to 8, 16, 32 vCores or more when your data and compute requirements change. 对于 OLTP 吞吐量,目标 SQL Server VM 的每个 vCore 具有 5000 个 IOPS。For OLTP throughput, target SQL Server VMs that have 5000 IOPS for every vCore.

将 SQL Server VM 市场映像与门户中的存储配置结合使用。Use the SQL Server VM marketplace images with the storage configuration in the portal. 这样便可更轻松地正确创建具有工作负载所需的大小、IOPS 和吞吐量的存储池。This will make it easier to properly create the storage pools necessary to get the size, IOPS, and throughput necessary for your workloads. 必须选择支持高级存储和高级存储缓存的 SQL Server VM。It is important to choose SQL Server VMs that support premium storage and premium storage caching. 有关详细信息,请参阅存储部分。See the storage section to learn more.

SQL Server 数据仓库和任务关键型环境通常需要缩放到内存与 vCore 比率大于 8。SQL Server data warehouse and mission critical environments will often need to scale beyond the 8 memory-to-vCore ratio. 对于中等环境,你可能希望选择核心与内存比率为 16,而对于较大的数据仓库环境,则可能希望该比率为 32。For medium environments, you may want to choose a 16 core-to-memory ratio, and a 32 core-to-memory ratio for larger data warehouse environments.

SQL Server 数据仓库环境通常受益于大型计算机的并行处理。SQL Server data warehouse environments often benefit from the parallel processing of larger machines. 因此,对于大型数据仓库环境,强烈建议选择 M 系列和 Mv2 系列。For this reason, the M-series and the Mv2-series are strong options for larger data warehouse environments.

VM 大小指导原则VM size guidance

使用源计算机的 vCPU 和内存配置作为将当前本地 SQL Server 数据库迁移到 Azure VM 上的 SQL Server 的基线。Use the vCPU and memory configuration from your source machine as a baseline for migrating a current on-premises SQL Server database to SQL Server on Azure VMs. 在 Azure 中使用你的核心许可证,以利用 Azure 混合权益并节省 SQL Server 许可成本。Bring your core license to Azure to take advantage of the Azure Hybrid Benefit and save on SQL Server licensing costs.

对于生产 SQL Server 工作负载,Microsoft 建议以内存与 vCore 比率为 8 作为起点。Microsoft recommends a memory-to-vCore ratio of 8 as a starting point for production SQL Server workloads. 对于非生产工作负载,可接受更小的比率。Smaller ratios are acceptable for non-production workloads.

根据工作负载(OLTP 或数据仓库),选择最适合 SQL Server 性能的内存优化常规用途、存储优化或受约束的 vCore 虚拟机大小。Choose a memory optimized, general purpose, storage optimized, or constrained vCore virtual machine size that is most optimal for SQL Server performance based on your workload (OLTP or data warehouse).

内存优化Memory optimized

内存优化的虚拟机大小是 SQL Server VM 的主要目标,也是 Azure 推荐的选择。The memory optimized virtual machine sizes are a primary target for SQL Server VMs and the recommended choice by Azure. 内存优化的虚拟机提供更大的内存与 CPU 比率和大中型缓存选项。The memory optimized virtual machines offer stronger memory-to-CPU ratios and medium-to-large cache options.

M 和 Mv2 系列M and Mv2 series

M 系列 为某些最大的 SQL Server 工作负载提供较高的 vCore 数量和内存。The M-series offers vCore counts and memory for some of the largest SQL Server workloads.

例如,Standard_M64ms 的内存与 vCore 比率为 28。The Standard_M64ms�has a 28 memory-to-vCore ratio for example.

M 系列的高级存储高级存储缓存支持以及写入加速等功能可提升 SQL Server 的性能。Some of the features of the M series attractive for SQL Server performance include premium storage and premium storage caching support, and write acceleration.

Edsv4 系列Edsv4-series

Edsv4 系列专为内存密集型应用程序而设计。The Edsv4-series is designed for memory-intensive applications. 与以前的 Ev3/Esv3 大小的第 2 代 VM 相比,这些 VM 具有较大的本地存储 SSD 容量、更多的本地磁盘 IOPS、高达 504 GiB 的 RAM 以及更强大的计算能力。These VMs have a large local storage SSD capacity, strong local disk IOPS, up to 504 GiB of RAM, and improved compute compared to the previous Ev3/Esv3 sizes with Gen2 VMs. 这些虚拟机的内存与 vCore 比率几乎全都为 8,非常适合标准 SQL Server 工作负载。There is a nearly consistent memory-to-vCore ratio of 8 across these virtual machines, which is ideal for standard SQL Server workloads.

该 VM 系列非常适合于内存密集型企业应用程序和受益于低延迟、高速本地存储的应用程序。This VM series is ideal for memory-intensive enterprise applications and applications that benefit from low latency, high-speed local storage.

Edsv4 系列虚拟机支持高级存储高级存储缓存The Edsv4-series virtual machines support premium storage, and premium storage caching.

DSv2 系列 11-15DSv2-series 11-15

DSv2 系列 11-15 的内存和磁盘配置与之前的 D 系列相同。The DSv2-series 11-15 has the same memory and disk configurations as the previous D-series. 该系列的所有虚拟机的内存与 CPU 比率均为 7。This series has a consistent memory-to-CPU ratio of 7 across all virtual machines.

DSv2 系列 11-15 支持高级存储高级存储缓存,强烈建议使用此系列来获取最佳性能。The DSv2-series 11-15 supports premium storage and premium storage caching, which is strongly recommended for optimal performance.

常规用途General Purpose

常规用途虚拟机大小旨在为较小的入门级工作负载(如开发和测试、Web 服务器和较小的数据库服务器)提供均衡的内存与 vCore 比率。The general purpose virtual machine sizes are designed to provide balanced memory-to-vCore ratios for smaller entry level workloads such as development and test, web servers, and smaller database servers.

由于常规用途虚拟机的内存与 vCore 比率较小,因此必须仔细监视基于内存的性能计数器,以确保 SQL Server 能够获取所需的缓冲区缓存内存。Because of the smaller memory-to-vCore ratios with the general purpose virtual machines, it is important to carefully monitor memory-based performance counters to ensure SQL Server is able to get the buffer cache memory it needs. 有关详细信息,请参阅内存性能基线See memory performance baseline for more information.

由于对于生产工作负载,建议的最低内存与 vCore 比率为 8,因此对于运行 SQL Server 的常规用途 VM,建议的最低配置为 4 个 vCPU 和 32 GB 内存。Since the starting recommendation for production workloads is a memory-to-vCore ratio of 8, the minimum recommended configuration for a general purpose VM running SQL Server is 4 vCPU and 32 GB of memory.

Ddsv4 系列Ddsv4 series

Ddsv4 系列的 vCPU、内存和临时磁盘数量相当,但支持的内存与 vCore 比率较小。The Ddsv4-series offers a fair combination of vCPU, memory, and temporary disk but with smaller memory-to-vCore support.

Ddsv4 VM 的延迟更低、本地存储速度更高。The Ddsv4 VMs include lower latency and higher-speed local storage.

这些虚拟机非常适合需要快速访问临时存储和部门关系数据库的并行 SQL 和应用部署。These machines are ideal for side-by-side SQL and app deployments that require fast access to temp storage and departmental relational databases. 本系列的所有虚拟机的标准内存与 vCore 比率均为 4。There is a standard memory-to-vCore ratio of 4 across all of the virtual machines in this series.

因此,建议利用 D8ds_v4 作为本系列中的入门虚拟机,它具有 8 个 vCore 和 32 GB 内存。For this reason, it is recommended to leverage the D8ds_v4 as the starter virtual machine in this series, which has 8 vCores and 32 GBs of memory. 该系列中最大的虚拟机是 D64ds_v4,它拥有 64 个 vCore 和 256 GB 的内存。The largest machine is the D64ds_v4, which has 64 vCores and 256 GBs of memory.

Ddsv4 系列虚拟机支持高级存储高级存储缓存The Ddsv4-series virtual machines support premium storage and premium storage caching.

备注

Ddsv4 系列的内存与 vCore 比率并非针对 SQL Server 工作负载建议的 8。The Ddsv4-series does not have the memory-to-vCore ratio of 8 that is recommended for SQL Server workloads. 因此,请仅考虑将这些虚拟机用于较小的应用程序和开发工作负载。As such, considering using these virtual machines for smaller application and development workloads only.

B 系列B-series

可突发 B 系列虚拟机大小非常适合不需要一致性能的工作负载,如概念验证以及非常小的应用程序和开发服务器。The burstable B-series virtual machine sizes are ideal for workloads that do not need consistent performance such as proof of concept and very small application and development servers.

大多数可突发 B 系列虚拟机大小的内存与 vCore 比率为 4。Most of the burstable B-series virtual machine sizes have a memory-to-vCore ratio of 4. 其中最大的虚拟机为 Standard_B20ms,它具有 20 个 vCore 和 80 GB 内存。The largest of these machines is the Standard_B20ms with 20 vCores and 80 GB of memory.

该系列是独一无二的,因为应用能够在营业时间突发,并且可突发的额度因虚拟机大小而异。This series is unique as the apps have the ability to burst during business hours with burstable credits varying based on machine size.

当额度用尽时,VM 会恢复到基线虚拟机性能。When the credits are exhausted, the VM returns to the baseline machine performance.

与其他系列中的其他 VM 大小相比,B 系列的优势是可以节省计算成本,尤其是在需要全天候尽量少使用处理能力时。The benefit of the B-series is the compute savings you could achieve compared to the other VM sizes in other series especially if you need the processing power sparingly throughout the day.

此系列支持高级存储,但不支持高级存储缓存This series supports premium storage, but does not support premium storage caching.

备注

可突发 B 系列的内存与 vCore 比率并非针对 SQL Server 工作负载建议的 8。The burstable B-series does not have the memory-to-vCore ratio of 8 that is recommended for SQL Server workloads. 因此,请仅考虑将这些虚拟机用于较小的应用程序、Web 服务器和开发工作负载。As such, consider using these virtual machines for smaller applications, web servers, and development workloads only.

Av2 系列Av2-series

Av2 系列 VM 最适合入门级工作负载,如开发和测试、低流量 Web 服务器、中小型应用数据库和概念验证。The Av2-series VMs are best suited for entry-level workloads like development and test, low traffic web servers, small to medium app databases, and proof-of-concepts.

只有 Standard_A2m_v2(2 个 vCore 和 16 GB 内存)、Standard_A4m_v2(4 个 vCore 和 32 GB 内存)和 Standard_A8m_v2(8 个 vCore 和 64 GB 内存)的内存与 vCore 比率为 8,使这三个虚拟机在此系列中排名前三。Only the Standard_A2m_v2 (2 vCores and 16GBs of memory), Standard_A4m_v2 (4 vCores and 32GBs of memory), and the Standard_A8m_v2 (8 vCores and 64GBs of memory) have a good memory-to-vCore ratio of 8 for these top three virtual machines.

这些虚拟机非常适合小型开发和测试 SQL Server 计算机。These virtual machines are both good options for smaller development and test SQL Server machines.

对于小型应用程序和 Web 服务器来说,具有 8 个 vCore 的 Standard_A8m_v2 可能也是一个不错的选择。The 8 vCore Standard_A8m_v2 may also be a good option for small application and web servers.

备注

Av2 系列不支持高级存储,因此不建议将其用于生产 SQL Server 工作负载,即使是内存与 vCore 比率为 8 的虚拟机也是如此。The Av2 series does not support premium storage and as such, is not recommended for production SQL Server workloads even with the virtual machines that have a memory-to-vCore ratio of 8.

存储优化Storage optimized

存储优化的 VM 大小适合特定用例。The storage optimized VM sizes are for specific use cases. 这些虚拟机经过专门涉及,具有经过优化的磁盘吞吐量和 IO。These virtual machines are specifically designed with optimized disk throughput and IO. 此虚拟机系列适用于大数据方案、数据仓库和大型事务数据库。This virtual machine series is intended for big data scenarios, data warehousing, and large transactional databases.

受约束的 vCoreConstrained vCores

高性能 SQL Server 工作负载通常需要大量内存、IO 和吞吐量,而无需较高的 vCore 计数。High performing SQL Server workloads often need larger amounts of memory, IO, and throughput without the higher vCore counts.

大多数 OLTP 工作负载都是由大量较小的事务驱动的应用程序数据库。Most OLTP workloads are application databases driven by large numbers of smaller transactions. 对于 OLTP 工作负载,仅读取或修改少量数据,但由用户数驱动的事务量要高得多。With OLTP workloads, only a small amount of the data is read or modified, but the volumes of transactions driven by user counts are much higher. 必须向缓存计划提供 SQL Server 内存、存储最近访问的数据以提高性能,以及确保可将物理读取内容快速读取到内存中。It is important to have the SQL Server memory available to cache plans, store recently accessed data for performance, and ensure physical reads can be read into memory quickly.

这些 OLTP 环境需要更高的内存量、快速存储和所需的 I/O 带宽才能以最佳方式执行。These OLTP environments need higher amounts of memory, fast storage, and the I/O bandwidth necessary to perform optimally.

为了保持此性能级别而不增加 SQL Server 许可成本,Azure 提供了具有受约束的 vCPU 计数的 VM 大小。In order to maintain this level of performance without the higher SQL Server licensing costs, Azure offers VM sizes with constrained vCPU counts.

这有助于通过减少可用的 vCore,同时保持与父虚拟机相同的内存、存储和 I/O 带宽来控制许可成本。This helps control licensing costs by reducing the available vCores while maintaining the same memory, storage, and I/O bandwidth of the parent virtual machine.

可以将 vCPU 计数限制为原始 VM 大小的一半到四分之一。The vCPU count can be constrained to one-half to one-quarter of the original VM size. 减少虚拟机可用的 vCore 将实现更高的内存与 vCore 比率。Reducing the vCores available to the virtual machine, will achieve higher memory-to-vCore ratios.

这些新的 VM 大小有一个用于指定活动 vCPU 数的后缀,使其更易于识别。These new VM sizes have a suffix that specifies the number of active vCPUs to make them easier to identify.

例如,M64-32ms 只需要许可 32 个 SQL Server vCore,并且 M64msM64-16ms 的内存、IO 和吞吐量只需要许可 16 个 vCore。For example, the M64-32ms requires licensing only 32 SQL Server vCores with the memory, IO, and throughput of the M64ms and the M64-16ms requires licensing only 16 vCores. 虽然 M64-16ms 的 SQL Server 许可成本是 M64ms 的四分之一,但虚拟机的计算成本是相同的。Though while the M64-16ms has a quarter of the SQL Server licensing cost of the M64ms, the compute cost of the virtual machine will be the same.

备注

  • 大中型数据仓库工作负载仍可能受益于受约束的 vCore VM,但数据仓库工作负载通常具有以下特点:用户较少,通过并行运行的查询计划处理大量数据的进程也较少。Medium to large data warehouse workloads may still benefit from constrained vCore VMs, but data warehouse workloads are commonly characterized by fewer users and processes addressing larger amounts of data through query plans that run in parallel.
  • 计算成本(包括操作系统许可)与父虚拟机保持相同。The compute cost, which includes operating system licensing, will remain the same as the parent virtual machine.

存储指导原则Storage guidance

要详细了解根据 TPC-E 和 TPC-C 基准在 Azure 虚拟机上进行的 SQL Server 性能测试,请参阅博客优化 OLTP 性能For detailed testing of SQL Server performance on Azure Virtual Machines 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.

警告

标准 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. 有关详细信息,请参阅 《High Availability and Disaster Recovery for SQL Server on Azure Virtual Machines》(Azure 虚拟机中 SQL Server 的高可用性和灾难恢复)。For more information, see High Availability and Disaster Recovery for SQL Server on Azure Virtual Machines.

磁盘指导原则Disks guidance

Azure 虚拟机上有三种主要磁盘类型:There are three main disk types on Azure virtual machines:

  • 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 the 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,并将临时数据库置于本地磁盘上,则无需执行任何其他操作;对于所有其他情况,请按博客文章使用 SSD 存储 TempDB 中的步骤操作,以防止重启后发生故障。If you create the VM from the 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. 如果本地驱动器的容量对于 TempDB 而言不足,请将 TempDB 放在位于具有只读缓存的高级 SSD 盘上的条带化存储池上。If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool striped 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. 对于 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,其列数等于存储池中的物理磁盘数量:For example, the following PowerShell creates a new storage pool with the interleave size to 64 KB and the number of columns equal to the amount of physical disk in the storage pool:

      $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 $PhysicalDisks .Count -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.

      警告

      请在更改 Azure 虚拟机磁盘的缓存设置时停止 SQL Server 服务,以免出现数据库损坏的情况。Stop the SQL Server service when changing the cache setting of Azure Virtual Machines 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. 如果将 TempDB 放置在临时磁盘(D:\ 驱动器)上,则利用该驱动器获得的性能会超过对 64 KB 分配单元大小的需求。If TempDB is placed on the temporary disk (D:\ drive) the performance gained by leveraging this drive outweighs the need for a 64-KB allocation unit size.

  • 磁盘管理最佳做法:删除数据磁盘或更改其缓存类型时,请在更改过程中停止 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.

  • 请注意,自动增长 被视为只是非预期增长的偶发情况。Be aware that 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 错误日志屏幕截图

  • 设置默认的备份和数据库文件位置。Set up 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 on 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 check box 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.

多个实例Multiple instances

在将多个 SQL Server 实例部署到单个虚拟机时,请考虑以下最佳做法:Consider the following best practices when deploying multiple SQL Server instances to a single virtual machine:

  • 为每个 SQL Server 实例设置最大服务器内存,确保有剩余内存供操作系统使用。Set the max server memory for each SQL Server instance, ensuring there is memory left over for the operating system. 如果更改分配给虚拟机的内存量,请确保更新 SQL Server 实例的内存限制。Be sure to update the memory restrictions for the SQL Server instances if you change how much memory is allocated to the virtual machine.
  • 为数据、日志和 TempDB 设置单独的 LUN,因为它们都具有不同的工作负载模式,并且你不希望它们相互影响。Have separate LUNs for data, logs, and TempDB since they all have different workload patterns and you do not want them impacting each other.
  • 在接近生产的巨大工作负载下彻底测试你的环境,确保它可应对你的应用程序 SLA 内的工作负载峰值。Thoroughly test your environment under heavy production-like workloads to ensure it can handle peak workload capacity within your application SLAs.

系统过载的信号可能包括但不限于工作线程用尽、响应时间过长和/或调度程序系统内存停滞。Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times, and/or stalled dispatcher system memory.

收集性能基线Collect performance baseline

要使用更规范的方法,请使用 PerfMon/LogMan 收集性能计数器并捕获 SQL Server 等待统计信息,以更好地了解源环境的一般压力和潜在瓶颈。For a more prescriptive approach, gather performance counters using PerfMon/LogMan and capture SQL Server wait statistics to better understand general pressures and potential bottlenecks of the source environment.

首先按照应用程序性能检查表收集源工作负载在峰值时间的 CPU、内存、IOPS吞吐量延迟Start by collecting the CPU, memory, IOPS, throughput, and latency of the source workload at peak times following the application performance checklist.

在高峰时段收集数据,例如典型工作日期间的工作负载,以及其他高负载过程(如日结束处理和周末 ETL 工作负载)。Gather data during peak hours such as workloads during your typical business day, but also other high load processes such as end-of-day processing, and weekend ETL workloads. 请考虑为非典型的繁重工作负载(如季度末处理)纵向扩展资源,然后在工作负载完成后纵向缩减。Consider scaling up your resources for atypically heavily workloads, such as end-of-quarter processing, and then scale done once the workload completes.

使用性能分析选择可根据工作负载的性能要求缩放的 VM 大小Use the performance analysis to select the VM Size that can scale to your workload's performance requirements.

IOPS 和吞吐量IOPS and Throughput

SQL Server 的性能在很大程度上取决于 I/O 子系统。SQL Server performance depends heavily on the I/O subsystem. 除非数据库适合物理内存,否则 SQL Server 会不断将数据库页引入或引出缓冲池。Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. 应以不同的方式处理 SQL Server 的数据文件。The data files for SQL Server should be treated differently. 对日志文件的访问是按顺序进行的,除非需要回滚事务,此时将随机访问数据文件(包括 TempDB)。Access to log files is sequential except when a transaction needs to be rolled back where data files, including TempDB, are randomly accessed. 如果 I/O 子系统速度较慢,则用户可能会遇到性能问题,例如响应时间长和任务因超时而无法完成。If you have a slow I/O subsystem, your users may experience performance issues such as slow response times and tasks that do not complete due to time-outs.

默认情况下,Azure 市场虚拟机在物理磁盘上具有与数据文件不同的日志文件。The Azure Marketplace virtual machines have log files on a physical disk that is separate from the data files by default. TempDB 数据文件计数和大小符合最佳做法,并以临时 D:/ 驱动为目标。The TempDB data files count and size meet best practices and are targeted to the ephemeral D:/ drive..

以下 PerfMon 计数器可帮助验证 SQL Server 所需的 IO 吞吐量:The following PerfMon counters can help validate the IO throughput required by your SQL Server:

  • \LogicalDisk\Disk Reads/Sec(读取和写入 IOPS)\LogicalDisk\Disk Reads/Sec (read and write IOPS)
  • \LogicalDisk\Disk Writes/Sec(读取和写入 IOPS)\LogicalDisk\Disk Writes/Sec (read and write IOPS)
  • \LogicalDisk\Disk Bytes/Sec(数据、日志和 TempDB 文件的吞吐量要求)\LogicalDisk\Disk Bytes/Sec (throughput requirements for the data, log, and TempDB files)

使用峰值级别的 IOPS 和吞吐量要求,评估与测量的容量相匹配的 VM 大小。Using IOPS and throughput requirements at peak levels, evaluate VM sizes that match the capacity from your measurements.

如果你的工作负载要求读取和写入的 IOPS 分别为 2 万次和 1 万次,你可以选择 E16s_v3(有最多 3.2 万次缓存的和 2.56 万次未缓存的 IOPS)或 M16_s(有最多 2 万次缓存的和 1 万次未缓存的 IOPS)以及 2 个使用存储空间进行条带化的 P30 磁盘。If your workload requires 20 K read IOPS and 10K write IOPS, 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 striped using Storage Spaces.

请确保了解工作负载的吞吐量和 IOPS 要求,因为 VM 具有不同的 IOPS 和吞吐量缩放限制。Make sure to understand both throughput and IOPS requirements of the workload as VMs have different scale limits for IOPS and throughput.

内存Memory

跟踪 OS 使用的外部内存以及 SQL Server 内部使用的内存。Track both external memory used by the OS as well as the memory used internally by SQL Server. 识别任一组件的压力将有助于调整虚拟机的大小并识别调整的机会。Identifying pressure for either component will help size virtual machines and identify opportunities for tuning.

以下 PerfMon 计数器可帮助验证 SQL Server 虚拟机的内存运行状况:The following PerfMon counters can help validate the memory health of a SQL Server virtual machine:

计算/处理Compute / Processing

Azure 中的计算管理方式与本地计算管理方式不同。Compute in Azure is managed differently than on-premises. 由于管理开销和获取新硬件的成本,本地服务器设计为持续数年无需升级。On-premises servers are built to last several years without an upgrade due to the management overhead and cost of acquiring new hardware. 虚拟化可缓解其中一些问题,并且应用程序经过优化,可充分利用基础硬件,这意味着任何对资源消耗的重大更改都需要重新平衡整个物理环境。Virtualization mitigates some of these issues but applications are optimized to take the most advantage of the underlying hardware, meaning any significant change to resource consumption requires rebalancing the entire physical environment.

在 Azure 中,这并不困难,因为在不同系列的硬件上,甚至在不同的区域中,很容易获得新虚拟机。This is not a challenge in Azure where a new virtual machine on a different series of hardware, and even in a different region, is easy to achieve.

在 Azure 中,你希望利用尽可能多的虚拟机资源,因此,应将 Azure 虚拟机的平均 CPU 配置得尽可能高,而不影响工作负载。In Azure, you want to take advantage of as much of the virtual machines resources as possible, therefore, Azure virtual machines should be configured to keep the average CPU as high as possible without impacting the workload.

以下 PerfMon 计数器可帮助验证 SQL Server 虚拟机的计算运行状况:The following PerfMon counters can help validate the compute health of a SQL Server virtual machine:

  • \Processor Information(_Total)% Processor Time\Processor Information(_Total)% Processor Time
  • \Process(sqlservr)% Processor Time\Process(sqlservr)% Processor Time

备注

理想情况下,尝试使用 80% 的计算能力,峰值时超过 90%,但在任何持续的时间内均不会达到 100%。Ideally, try to aim for using 80% of your compute, with peaks above 90% but not reaching 100% for any sustained period of time. 从根本上说,你只需预配应用程序所需的计算能力,然后计划根据业务需求进行纵向扩展或纵向缩减。Fundamentally, you only want to provision the compute the application needs and then plan to scale up or down as the business requires.

后续步骤Next steps

有关安全最佳做法,请参阅 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.