有关优化 Azure Stack Hub 性能的 SQL Server 最佳做法SQL server best practices to optimize performance in Azure Stack Hub

本文提供的 SQL Server 最佳做法可优化 Azure Stack Hub 虚拟机 (VM) 中的 SQL Server 并提高性能。This article provides SQL server best practices to optimize SQL Server and improve performance in Azure Stack Hub virtual machines (VMs). 在 Azure Stack Hub VM 中运行 SQL Server 时,请使用适用于本地服务器环境中的 SQL Server 的相同数据库性能优化选项。When running SQL Server in Azure Stack Hub VMs, use the same database performance-tuning options applicable to SQL Server in an on-premises server environment. Azure Stack Hub 云中关系数据库的性能取决于许多因素,包括 VM 的系列大小和数据磁盘的配置。The performance of a relational database in an Azure Stack Hub cloud depends on many factors, including family size of a VM and the configuration of the data disks.

创建 SQL Server 映像时,请考虑在 Azure Stack Hub 门户中预配 VMWhen creating SQL Server images, consider provisioning your VMs in the Azure Stack Hub portal. 从 Azure Stack Hub 管理员门户中的“市场管理”下载 SQL IaaS 扩展,然后下载所选的 SQL Server VM 映像。Download the SQL IaaS Extension from Marketplace Management in the Azure Stack Hub administrator portal and download your choice of SQL Server VM images. 这些映像包括 SQL Server 2016 SP1、SQL Server 2016 SP2 和 SQL Server 2017。These include SQL Server 2016 SP1, SQL Server 2016 SP2, and SQL Server 2017.

Note

虽然本文介绍的是如何使用全球 Azure 门户预配 SQL Server VM,但相关指南也适用于 Azure Stack Hub,只是存在以下差异:SSD 不可用于操作系统磁盘,并且在存储配置上存在微小差异。While the article describes how to provision a SQL Server VM using the global Azure portal, the guidance also applies to Azure Stack Hub with the following differences: SSD isn't available for the operating system disk and there are minor differences in storage configuration.

在 VM 映像中,对于 SQL Server,只能使用自带许可 (BYOL)。In the VM images, for SQL Server, you can only use bring-your-own-license (BYOL). 对于 Windows Server,默认许可模式为预付。For Windows Server, the default license model is pay-in-advance. 有关 VM 中 Windows Server 许可模式的详细信息,请参阅 Azure Stack Hub 市场中的 Windows Server 常见问题解答For detailed information of Windows Server license model in VM, refer the article Windows Server in Azure Stack Hub Marketplace FAQ.

本文重点介绍如何在 Azure Stack Hub VM 上获取 SQL Server 的最佳性能。 Getting the best performance for SQL Server on Azure Stack Hub VMs is the focus of this article. 如果工作负荷要求较低,可能不需要每项建议的优化。If your workload is less demanding, you might not require every recommended optimization. 评估这些建议时应考虑性能需求和工作负荷模式。Consider your performance needs and workload patterns as you evaluate these recommendations.

Note

如需 Azure VM 中 SQL Server 的性能指南,请参阅此文For performance guidance for SQL Server in Azure VMs, refer to this article.

SQL Server 最佳做法清单Checklist for SQL server best practices

以下清单适合优化 Azure Stack Hub VM 中 SQL Server 的性能:The following checklist is for optimal performance of SQL Server on Azure Stack Hub VMs:

区域Area 优化Optimizations
VM 大小VM size DS3 或更高级别适合 SQL Server Enterprise 版本。DS3 or higher for SQL Server Enterprise edition.

DS2 或更高级别适合 SQL Server Standard 版本和 Web 版本。DS2 or higher for SQL Server Standard edition and Web edition.
存储Storage 使用支持高级存储的 VM 系列。Use a VM family that supports Premium storage.
磁盘Disks 至少使用两个数据磁盘(一个用于日志文件,一个用于数据文件和 TempDB),并根据容量需求选择磁盘大小。Use a minimum of two data disks (one for log files and one for data file and TempDB), and choose the disk size based on your capacity needs. 在 SQL Server 安装过程中将默认的数据文件位置设置为这些磁盘。Set the default data file locations to these disks during the SQL Server install.

避免使用操作系统或临时磁盘进行数据库存储或日志记录。Avoid using operating system or temporary disks for database storage or logging.
使用存储空间条带化多个 Azure 数据磁盘,提高 IO 吞吐量。Stripe multiple Azure data disks to get increased IO throughput using Storage Spaces.

使用规定的分配大小格式化。Format with documented allocation sizes.
I/OI/O 对数据文件启用即时文件初始化。Enable instant file initialization for data files.

使用合理的小的固定增量 (64 MB-256 MB) 限制数据库自动增长。Limit autogrow on the databases with reasonably small fixed increments (64 MB-256 MB).

禁用数据库自动收缩。Disable autoshrink on the database.

在数据磁盘而非操作系统磁盘上设置默认的备份和数据库文件位置。Set up default backup and database file locations on data disks, not the operating system disk.

启用锁定页面。Enable locked pages.

应用 SQL Server 服务包和累积更新Apply SQL Server service packs and cumulative updates.
特定于功能Feature-specific 直接备份到 Blob 存储(如果受正在使用的 SQL Server 版本支持)。Back up directly to blob storage (if supported by the SQL Server version in use).

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

VM 大小指导原则VM size guidance

对于性能敏感型应用程序,建议使用以下 VM 大小For performance-sensitive applications, the following VM sizes are recommended:

  • SQL Server Enterprise 版本: DS3 或更高SQL Server Enterprise edition: DS3 or higher

  • SQL Server Standard 版本和 Web 版本: DS2 或更高SQL Server Standard edition and Web edition: DS2 or higher

使用 Azure Stack Hub 时,DS 和 DS_v2 VM 系列没有性能差异。With Azure Stack Hub, there's no performance difference between the DS and DS_v2 VM family series.

存储指导原则Storage guidance

在 Azure Stack Hub 中,DS 系列(以及 DSv2 系列)VM 提供最大的操作系统磁盘和数据磁盘吞吐量 (IOPS)。DS-series (along with DSv2-series) VMs in Azure Stack Hub provide the maximum operating system disk and data disk throughput (IOPS). DS 或 DSv2 系列的 VM 为操作系统磁盘提供高达 1,000 的 IOPS,为数据磁盘提供高达 2,300 的 IOPS,不管所选磁盘的类型或大小如何。A VM from the DS or DSv2 series provides up to 1,000 IOPS for the operating system disk and up to 2,300 IOPS per data disk, no matter the type or size of the chosen disk.

数据磁盘吞吐量只取决于 VM 系列。Data disk throughput is determined uniquely based on the VM family series. 若要确定 VM 系列的数据磁盘吞吐量,可参阅此文You can refer to this article to identify the data disk throughput per VM family series.

Note

至于生产型工作负荷,请选择可以在操作系统磁盘和数据磁盘上提供最大可能 IOPS 的 DS 系列或 DSv2 系列的 VM。For production workloads, select a DS-series or DSv2-series VM to provide the maximum possible IOPS on the operating system disk and data disks.

在 Azure Stack Hub 中创建存储帐户时,异地复制选项无效,因为此功能在 Azure Stack Hub 中不可用。When creating a storage account in Azure Stack Hub, the geo-replication option has no effect because this capability isn't available in Azure Stack Hub.

磁盘指导原则Disks guidance

Azure Stack Hub VM 上有三种主要磁盘类型:There are three main disk types on an Azure Stack Hub VM:

  • 操作系统磁盘: 创建 Azure Stack Hub VM 时,该平台至少将一个磁盘(标记为 C 驱动器)附加到 VM 作为操作系统磁盘。Operating system disk: When you create an Azure Stack Hub VM, the platform attaches 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 Stack Hub VM 包含另一个称为临时磁盘的磁盘(标记为 D 驱动器)。Temporary disk: Azure Stack Hub VMs 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.

  • 数据磁盘: 可以将其他磁盘作为数据磁盘附加到 VM,这些磁盘在存储中存储为页 Blob。Data disks: You can attach additional disks to your VM as data disks, and these disks are 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.

临时磁盘Temporary disk

不保留标记为 D 驱动器的临时存储驱动器。The temporary storage drive, labeled as the D drive, isn't persistent. 不要在 D 驱动器上存储你不想要丢失的数据。Don't store any data you're unwilling to lose on the D drive. 这包括用户数据库文件和用户事务日志文件。This includes your user database files and user transaction log files.

建议将 TempDB 存储在数据磁盘上,因为每个数据磁盘提供高达 2,300 的 IOPS。We recommend storing TempDB on a data disk as each data disk provides a maximum of up to 2,300 IOPS per data disk.

数据磁盘数Data disks

  • 将数据磁盘用于数据和日志文件。Use data disks for data and log files. 如果不使用磁盘条带化,请使用支持高级存储的 VM 中的两个数据磁盘,一个磁盘包含日志文件,另一个包含数据和 TempDB 文件。If you're not using disk striping, use two data disks from a VM that supports Premium storage, where one disk contains the log files and the other contains the data and TempDB files. 每个数据磁盘均提供可观的 IOPS,具体取决于 VM 系列,如 Azure Stack Hub 中支持的 VM 大小中所述。Each data disk provides a number of IOPS depending on the VM family, as described in VM sizes supported in Azure Stack Hub. 如果使用磁盘条带化方法(例如存储空间),请将所有数据文件和日志文件(包括 TempDB)放在同一驱动器上。If you're using a disk-striping technique, such as Storage Spaces, place all data and log files on the same drive (including TempDB). 此配置可以为你提供最大数目的 IOPS 供 SQL Server 使用,不管哪个文件在特定时刻需要它们。This configuration gives you the maximum number of IOPS available for SQL Server to consume, no matter which file needs them at any particular time.

Note

在门户中预配 SQL Server VM 时,你可以编辑存储配置。When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. Azure Stack Hub 根据配置来配置一个或多个磁盘。Depending on your configuration, Azure Stack Hub configures one or more disks. 多个磁盘会组合成一个存储池。Multiple disks are combined into a single storage pool. 数据文件和日志文件一起位于此配置中。Both the data and log files reside together in this configuration.

  • 磁盘条带化: 为提高吞吐量,可以添加更多的数据磁盘,并使用磁盘条带化。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, analyze the number of IOPS required for your log files and for your data and TempDB files. 请注意,IOPS 限制是按数据磁盘来设置的,取决于 VM 系列而不是 VM 大小。Notice that IOPS limits are per data disk based on the VM series family, and not based on the VM size. 但是,网络带宽限制取决于 VM 大小。Network bandwidth limits, however, are based on the VM size. 请参阅 Azure Stack Hub 中的 VM 大小中的表,了解更多详细信息。See the tables on VM sizes in Azure Stack Hub for more detail. 遵循以下指南:Use the following guidelines:

    • 对于 Windows Server 2012 或更高版本,请按照以下指南使用存储空间For 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 online transaction processing (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. 配置的磁盘超过八个时,请使用 PowerShell(而不是服务器管理器 UI)。Use PowerShell when configuring more than eight disks (not Server Manager UI).

        例如,以下 PowerShell 创建新的存储池时将交错大小设为 64 KB,将列数设为 2:For example, the following PowerShell creates a new storage pool with the interleave size set 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
        
  • 根据负载预期确定与你的存储池相关联的磁盘数。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. 有关详细信息,请参阅 Azure Stack Hub 中支持的 VM 大小For more information, see VM sizes supported in Azure Stack Hub.

  • 若要获取针对数据磁盘的最大可能 IOPS,建议增加 VM 大小支持的最大数量的数据磁盘并使用磁盘条带化。To get the maximum possible IOPS for data disks, the recommendation is to add the maximum number of data disks supported by your VM size and to use disk striping.

  • NTFS 分配单元大小: 格式化数据磁盘时,我们建议对数据和日志文件以及 TempDB 使用 64-KB 分配单元大小。NTFS allocation unit size: When formatting the data disk, we recommend you use a 64-KB allocation unit size for data and log files as well as TempDB.

  • 磁盘管理做法: 删除数据磁盘时,请在更改过程中停止 SQL Server 服务。Disk management practices: When removing a data disk, stop the SQL Server service during the change. 另外,请勿更改磁盘上的缓存设置,因为这样做不会改进性能。Also, don't change cache settings on the disks as it doesn't provide any performance improvements.

Warning

在进行这些操作时,如果没有停止 SQL 服务,则可能导致数据库损坏。Failure to stop the SQL Service during these operations can cause database corruption.

I/O 指导原则I/O guidance

  • 请考虑启用即时文件初始化以减少初始文件分配所需的时间。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're 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 isn't 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 a contingency for unexpected growth. 请勿使用自动增长来管理数据和日志每天的增长。Don't 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.

  • 设置默认的备份和数据库文件位置。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 shows where to make these changes:

    查看或更改默认配置

  • 建立锁定的页以减少 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).

  • 请考虑在传入/传出 Azure Stack Hub 时压缩所有数据文件,包括备份。Consider compressing any data files when transferring in/out of Azure Stack Hub, including backups.

功能特定指南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 may help you achieve better performance:

  • 备份到 Azure 存储。Back up to Azure storage. 为在 Azure Stack Hub VM 中运行的 SQL Server 执行备份时,可以使用“SQL Server 备份到 URL”。When making backups for SQL Server running in Azure Stack Hub VMs, 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 or restore using Azure storage, follow the recommendations provided in SQL Server Backup to URL Best Practices and Troubleshooting and Restoring From Backups Stored in Azure. 此外还可以使用 Azure VM 中 SQL Server 的自动备份自动执行这些备份。You can also automate these backups using Automated Backup for SQL Server in Azure VMs.

  • 备份到 Azure Stack Hub 存储。Back up to Azure Stack Hub storage. 可以备份到 Azure Stack Hub 存储,所用方式类似于备份到 Azure 存储。You can back up to Azure Stack Hub storage in a similar fashion as with backing up to Azure Storage. 在 SQL Server Management Studio (SSMS) 中创建备份时,需手动输入配置信息。When you create a backup inside SQL Server Management Studio (SSMS), you need to enter the configuration information manually. 不能使用 SSMS 创建存储容器或共享访问签名。You can't use SSMS to create the storage container or the Shared Access Signature. SSMS 仅连接到 Azure 订阅,不连接到 Azure Stack Hub 订阅。SSMS only connects to Azure subscriptions, not Azure Stack Hub subscriptions. 只需通过 Azure Stack Hub 门户或 PowerShell 创建存储帐户、容器和共享访问签名。Instead, you need to create the storage account, container, and Shared Access Signature in the Azure Stack Hub portal or with PowerShell.

    SQL Server 备份

    Note

    共享访问签名是 Azure Stack Hub 门户中的 SAS 令牌,在字符串中没有前导“?”。The Shared Access Signature is the SAS token from the Azure Stack Hub portal, without the leading '?' in the string. 如果使用门户中的复制功能,则需删除前导“?”,才能使令牌在 SQL Server 中正常工作。If you use the copy function from the portal, you need to delete the leading '?' for the token to work within SQL Server.

    在 SQL Server 中设置并配置备份目标以后,即可备份到 Azure Stack Hub Blob 存储。Once you have the Backup Destination set up and configured in SQL Server, you can then back up to the Azure Stack Hub blob storage.

后续步骤Next steps

使用 Azure Stack Hub 的服务或构建适用于 Azure Stack Hub 的应用Using services or building apps for Azure Stack Hub