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 SQL Enterprise Edition:- DS3_v2 或更高版本。- DS3_v2 or higher for SQL Enterprise edition.

SQL Standard 和 Web Edition:- DS2_v2 或更高版本。- DS2_v2 or higher for SQL Standard and Web editions.
存储Storage - 使用高级 SSD- Use premium SSDs. 仅建议将标准存储用于开发/测试。Standard storage is only recommended for dev/test.

- 将存储帐户和 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 个 P30 磁盘(一个用于日志,另一个用于数据文件,包括 TempDB)。- Use a minimum of 2 P30 disks (1 for log files and 1 for data files including TempDB). 对于需要约 50,000 IOPS 的工作负荷,请考虑使用超级 SSD。For workloads requiring ~50,000 IOPS, consider using an Ultra SSD.

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

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

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

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

- 将 TempDB 放在本地 SSD 上,用于任务关键型 SQL Server 工作负荷(在选择正确的 VM 大小后)。- Place TempDB on local SSD for mission critical SQL Server workloads (after choosing correct VM size).
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.

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

- 启用锁定页面。- Enable locked pages.

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

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

VM 大小指导原则VM size guidance

对于性能敏感型应用程序,建议使用以下虚拟机大小For performance sensitive applications, it's recommended that you use the following virtual machines sizes:

  • SQL Server Enterprise 版本:DS3_v2 或更高版本SQL Server Enterprise Edition: DS3_v2 or higher
  • SQL Server Standard 和 Web 版本:DS2_v2 或更高版本SQL Server Standard and Web Editions: DS2_v2 or higher

DSv2 系列 VM 支持高级存储,高级存储是能提供最佳性能的推荐选择。DSv2-series VMs support premium storage, which is recommended for the best performance. 这里推荐的大小是基线,实际选择的计算机大小取决于工作负载的需求。The sizes recommended here are baselines, but the actual machine size you select depends on your workload demands. DSv2 系列 VM 是常规用途的 VM,适用于各种工作负载,而其他计算机大小针对特定工作负载类型进行了优化。DSv2-series VMs are general purpose VMs that are good for a variety of workloads, whereas other machines sizes are optimized for specific workload types. 例如,M 系列为最大型的 SQL Server 工作负载提供最高的 vCPU 数量和内存。For example, the M-series offers the highest vCPU count and memory for the largest SQL Server workloads. DSv2 系列 11-15 经过优化,可满足较大的内存需求。The DSv2-series 11-15 is optimized for large memory requirements. 还可以在受约束的核心大小中选择这两种系列,这样能降低计算需求,从而节省工作负载的成本。Both of those series are also available in constrained core sizes, which saves money for workloads with lower compute demands.

存储指导原则Storage guidance

DS 系列(以及 DSv2 系列)VM 支持高级 SSDDS-series (along with DSv2-series) VMs support premium SSDs. 建议为所有生产工作负荷使用高级 SSD。Premium SSDs are 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.

D 系列和 Dv2 系列 VM 上的临时驱动器基于 SSD。For D-series and Dv2-series VMs, the temporary drive on these VMs is SSD-based. 如果工作负荷重度使用 TempDB(例如,要处理临时对象或复杂联接),在 D 驱动器上存储 TempDB 可能会提高 TempDB 吞吐量并降低 TempDB 延迟。If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency. 有关示例方案,请参阅以下博客文章中的 TempDB 讨论:Storage Configuration Guidelines for SQL Server on Azure VM(Azure VM 上的 SQL Server 的存储配置准则)。For an example scenario, see the TempDB discussion in the following blog post: Storage Configuration Guidelines for SQL Server on Azure VM.

对于支持高级 SSD 的 VM(DS 系列和 DSv2 系列),建议将 TempDB 存储在支持高级 SSD 且已启用读取缓存的磁盘上。For VMs that support premium SSDs (DS-series, and DSv2-series), we recommend storing TempDB on a disk that supports premium SSDs with read caching enabled.

这项建议有一种例外情况;如果 TempDB 的使用是写入密集型的,则可以通过将 TempDB 存储在本地 D 驱动器(在这些计算机大小上也是基于 SSD)上来实现更高性能 。There is one exception to this recommendation: if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.

数据磁盘数Data disks

  • 将数据磁盘用于数据和日志文件:如果不使用磁盘条带化,请使用两个高级 SSD P30 磁盘,其中一个磁盘包含日志文件,另一个磁盘包含数据和 TempDB 文件。Use data disks for data and log files: If you are not using disk striping, use two premium SSD P30 disks where one disk contains the log file(s) and the other contains the data and TempDB file(s). 每个高级 SSD 均根据其大小提供许多 IOP 和带宽 (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.

    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(65536 字节),对于数据仓库工作负荷,将交错(条带大小)设置为 256 KB(262144 字节),以避免分区定位错误导致的性能影响。Set the interleave (stripe size) to 64 KB (65536 bytes) for OLTP workloads and 256 KB (262144 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 开始不推荐使用此选项。Note that 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. 请注意,虽然可以在该单个池上创建不同的卷,但它们都拥有相同的特征,例如相同的缓存策略。Note that 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:

后续步骤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.