迁移性能:SQL Server 到 SQL 托管实例性能分析Migration performance: SQL Server to SQL Managed Instance performance analysis

适用于: Azure SQL 托管实例

创建性能基线,对 SQL 托管实例上工作负载的性能和 SQL Server 上运行的原始工作负载的性能进行比较。Create a performance baseline to compare the performance of your workload on a SQL Managed Instance with your original workload running on SQL Server.

创建基线Create a baseline

理想情况下,迁移后的性能应与之前相当或比之前更好,因此,请务必测量和记录源上的基线性能值,然后将其与目标环境进行比较。Ideally, performance is similar or better after migration, so it is important to measure and record baseline performance values on the source and then compare them to the target environment. 性能基线是一组参数,用于定义源上的平均工作负载。A performance baseline is a set of parameters that define your average workload on your source.

选择一组对你的业务工作负载非常重要并代表业务工作负载的查询。Select a set of queries that are important to, and representative of your business workload. 测量和记录这些查询的最小/平均/最大持续时间和 CPU 使用情况,以及源服务器上的性能指标,例如平均/最大 CPU 使用情况、平均/最大磁盘 IO 延迟、吞吐量、IOPS、平均/最大页生存期和 tempdb 的平均最大大小。Measure and document the min/average/max duration and CPU usage for these queries, as well as performance metrics on the source server, such as average/max CPU usage, average/max disk IO latency, throughput, IOPS, average / max page life expectancy, and average max size of tempdb.

以下资源可帮助定义性能基线:The following resources can help define a performance baseline:

  • 监视 CPU 使用情况Monitor CPU usage
  • 监视内存使用情况 并确定不同组件(如缓冲池、计划缓存、列存储池、 内存中 OLTP 等)使用的内存量。此外,还应查找页生存期内存性能计数器的平均值和峰值。Monitor memory usage and determine the amount of memory used by different components such as buffer pool, plan cache, column-store pool, In-Memory OLTP, etc. In addition, you should find average and peak values of the Page Life Expectancy memory performance counter.
  • 使用  sys.dm_io_virtual_file_stats  视图或 性能计数器监视源 SQL Server 实例上的磁盘 IO 使用情况。Monitor disk IO usage on the source SQL Server instance using the sys.dm_io_virtual_file_stats view or performance counters.
  • 检查动态管理视图(若要从 SQL Server 2016 及更高版本迁移,请检查查询存储),以监视工作负载和查询性能。Monitor workload and query performance by examining Dynamic Management Views (or Query Store if you are migrating from SQL Server 2016 and later). 确定工作负载中最重要的查询的平均持续时间和 CPU 使用情况。Identify average duration and CPU usage of the most important queries in your workload.

应在迁移之前解决源 SQL Server 上的任何性能问题。Any performance issues on the source SQL Server should be addressed prior to migration. 将已知问题带到任何新系统可能会导致意外的结果,并使得任何性能比较失效。Migrating known issues to any new system might cause unexpected results and invalidate any performance comparison.

比较性能Compare performance

定义基线后,请比较目标 SQL 托管实例上的类似工作负载性能。After you have defined a baseline, compare similar workload performance on the target SQL Managed Instance. 为保证准确性,SQL 托管实例环境应尽可能与 SQL Server 环境相似,这一点非常重要。For accuracy, it is important that the SQL Managed Instance environment is comparable to the SQL Server environment as much as possible.

SQL 托管实例基础结构存在差异,因此不太可能实现性能匹配。There are SQL Managed Instance infrastructure differences that make matching performance exactly unlikely. 某些查询的运行速度可能比预期更快,而另一些查询的运行速度可能较慢。Some queries may run faster than expected, while others may be slower. 比较的目的是验证托管实例中的工作负载性能是否与 SQL Server 上的性能匹配(平均值),并识别性能与原始性能不匹配的任何关键查询。The goal of this comparison is to verify that workload performance in the managed instance matches the performance on SQL Server (on average) and to identify any critical queries with performance that don't match your original performance.

性能比较可能会出现以下结果:Performance comparison is likely to result in the following outcomes:

  • 托管实例上的工作负载性能与源 SQL Server 上的性能相当,或者优于后者。Workload performance on the managed instance is aligned or better than the workload performance on your source SQL Server. 如果存在这种情况,则表示迁移成功。In this case, you have successfully confirmed that migration is successful.

  • 工作负载中的大多数性能参数和查询按预期工作,但也存在一些性能下降的例外情况。The majority of performance parameters and queries in the workload perform as expected, with some exceptions resulting in degraded performance. 对于这种情况,需要识别差异及其重要性。In this case, identify the differences and their importance. 如果某些重要查询出现性能下降,请调查是基础 SQL 计划已经更改还是查询遇到了资源限制。If there are some important queries with degraded performance, investigate whether the underlying SQL plans have changed or whether queries are hitting resource limits. 可以通过直接或使用计划指南来对关键查询(例如,更改兼容级别、旧基数估算器)应用一些提示来缓解这种情况。You can mitigate this by applying some hints on critical queries (for example, change compatibility level, legacy cardinality estimator) either directly or using plan guides. 确保统计信息和索引在两个环境中都是最新且等效的。Ensure statistics and indexes are up to date and equivalent in both environments.

  • 与源 SQL Server 实例相比,托管实例上的大多数查询速度较慢。Most queries are slower on a managed instance compared to your source SQL Server instance. 对于这种情况,请尝试识别差异的根本原因,如 即将达到资源限制(如 IO、内存或实例日志速率限制)。In this case, try to identify the root causes of the difference such as reaching some resource limit such as IO, memory, or instance log rate limits. 如果没有任何资源限制会导致出现差异,请尝试更改数据库的兼容性级别,或更改数据库设置(例如传统基数估算),然后重新运行测试。If there are no resource limits causing the difference, try changing the compatibility level of the database or change database settings like legacy cardinality estimation and rerun the test. 查看托管实例或查询存储视图中提供的建议来识别使性能倒退的查询。Review the recommendations provided by the managed instance or Query Store views to identify the queries with regressed performance.

SQL 托管实例具有内置的自动计划更正功能(默认已启用)。SQL Managed Instance has a built-in automatic plan correction feature that is enabled by default. 此功能确保过去正常工作的查询在将来不会导致性能下降。This feature ensures that queries that worked fine in the past do not degrade in the future. 如果未启用此功能,请用旧设置运行工作负载,使 SQL 托管实例可以了解性能基线。If this feature is not enabled, run the workload with the old settings so SQL Managed Instance can learn the performance baseline. 然后,启用此功能,并使用新设置重新运行工作负载。Then, enable the feature and run the workload again with the new settings.

更改测试参数或升级到更高服务层级,以达到满足需求的工作负载性能的最佳配置。Make changes in the parameters of your test or upgrade to higher service tiers to reach the optimal configuration for the workload performance that fits your needs.

监视性能Monitor performance

SQL 托管实例提供用于监视和故障排除的高级工具,你应使用这些工具来监视实例的性能。SQL Managed Instance provides advanced tools for monitoring and troubleshooting, and you should use them to monitor performance on your instance. 要监视的一些关键指标如下:Some of the key metrics to monitor are:

  • 实例上的 CPU 使用率:确定预配的 vCore 数目是否与工作负载适当匹配。CPU usage on the instance to determine if the number of vCores that you provisioned is the right match for your workload.
  • 托管实例上的页生存期:确定是否需要增加内存Page-life expectancy on your managed instance to determine if you need additional memory.
  • 标识存储 IO 问题的统计信息(例如 INSTANCE_LOG_GOVERNOR 或 PAGEIOLATCH),尤其是在“常规用途”层级上,可能需要在其中预先分配文件才能获得更好的 IO 性能。Statistics like INSTANCE_LOG_GOVERNOR or PAGEIOLATCH that identify storage IO issues, especially on the General Purpose tier, where you might need to pre-allocate files to get better IO performance.

注意事项Considerations

比较性能时,请注意以下事项:When comparing performance, consider the following:

  • 源和目标之间的设置是否匹配。Settings match between source and target. 验证两个环境中的各个实例、数据库和 tempdb 设置是否等效。Validate that various instance, database, and tempdb settings are equivalent between the two environments. 配置、兼容级别、加密设置、跟踪标志等方面的差异都会使性能产生偏差。Differences in configuration, compatibility levels, encryption settings, trace flags etc., can all skew performance.

  • 存储是根据最佳做法配置的。Storage is configured according to best practices. 例如,对于常规用途,你可能需要预先分配文件大小以提高性能。For example, for General Purpose, you may need to pre-allocate the size of the files to improve performance.

  • 存在关键环境差异,可能导致托管实例与 SQL Server 之间出现性能差异。There are key environment differences that might cause the performance differences between a managed instance and SQL Server. 确定与环境有关的可能导致性能问题的风险。Identify risks relevant to your environment that might contribute to a performance issue.

  • 应在 SQL 托管实例上启用查询存储和自动优化,因为它们有助于衡量工作负载性能并自动缓解潜在的性能问题。Query store and automatic tuning should be enabled on your SQL Managed Instance as they help you measure workload performance and automatically mitigate potential performance issues.

后续步骤Next steps

如需详细了解如何优化新的 Azure SQL 托管实例环境,请参阅以下资源:For more information to optimize your new Azure SQL Managed Instance environment, see the following resources: