在 Azure SQL 数据库和 Azure SQL 托管实例中优化应用程序和数据库以提高性能Tune applications and databases for performance in Azure SQL Database and Azure SQL Managed Instance

适用于: Azure SQL 数据库 Azure SQL 托管实例

如果你确定遇到了与 Azure SQL 数据库和 Azure SQL 托管实例相关的性能问题,则以下文章可为你提供帮助:Once you have identified a performance issue that you are facing with Azure SQL Database and Azure SQL Managed Instance, this article is designed to help you:

  • 优化应用程序,应用某些可以提高性能的最佳做法。Tune your application and apply some best practices that can improve performance.
  • 通过更改索引和查询来优化数据库,以便更有效地处理数据。Tune the database by changing indexes and queries to more efficiently work with data.

本文假定你已完成了 Azure SQL 数据库数据库顾问建议和 Azure SQL 数据库自动优化建议(如果适用)。This article assumes that you have already worked through the Azure SQL Database database advisor recommendations and the Azure SQL Database auto-tuning recommendations, if applicable. 它还假定你已查看了监视和优化概述以及与性能问题故障排除相关的文章。It also assumes that you have reviewed An overview of monitoring and tuning and its related articles related to troubleshooting performance issues. 此外,本文还假定你没有 CPU 资源,与运行相关的性能问题可以通过提升计算大小或服务层级来向数据库提供更多资源来解决。Additionally, this article assumes that you do not have a CPU resources, running-related performance issue that can be resolved by increasing the compute size or service tier to provide more resources to your database.

优化应用程序Tune your application

在传统的本地 SQL Server 中,进行初始容量规划的过程经常与在生产中运行应用程序的过程分离。In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. 首先购买硬件和产品许可证,然后进行性能优化。Hardware and product licenses are purchased first, and performance tuning is done afterward. 使用 Azure SQL 时,最好是交替完成应用程序的运行和优化过程。When you use Azure SQL, it's a good idea to interweave the process of running an application and tuning it. 使用按需支付容量的模型,可以优化应用程序以使用目前所需的最少资源,而不是靠推测应用程序的未来增长计划过度预配硬件(这通常是不正确的做法)。With the model of paying for capacity on demand, you can tune your application to use the minimum resources needed now, instead of over-provisioning on hardware based on guesses of future growth plans for an application, which often are incorrect. 有些客户可能选择不优化应用程序,而是选择过度配置硬件资源。Some customers might choose not to tune an application, and instead choose to over-provision hardware resources. 如果不想在繁忙时段更改关键应用程序,不妨使用此方法。This approach might be a good idea if you don't want to change a key application during a busy period. 但是,在使用 Azure SQL 数据库和 Azure SQL 托管实例中的服务层级时,优化应用程序可以使资源需求降至最低并减少每月的费用。But, tuning an application can minimize resource requirements and lower monthly bills when you use the service tiers in Azure SQL Database and Azure SQL Managed Instance.

应用程序特征Application characteristics

尽管 Azure SQL 数据库和 Azure SQL 托管实例服务层级旨在提高应用程序的性能稳定性和可预测性,但一些最佳做法可以帮助你优化应用程序,以便更好地利用某一计算大小的资源。Although Azure SQL Database and Azure SQL Managed Instance service tiers are designed to improve performance stability and predictability for an application, some best practices can help you tune your application to better take advantage of the resources at a compute size. 虽然许多应用程序只需通过切换到更大的计算大小或服务层级便会显著提升性能,但某些应用程序需要进一步优化,才能受益于更高级别的服务。Although many applications have significant performance gains simply by switching to a higher compute size or service tier, some applications need additional tuning to benefit from a higher level of service. 若要提高性能,可考虑对具有以下特征的应用程序进行额外的优化:For increased performance, consider additional application tuning for applications that have these characteristics:

  • 因“闲聊”行为而性能变慢的应用程序Applications that have slow performance because of "chatty" behavior

    “健谈”应用程序会过多地进行易受网络延迟影响的数据访问操作。Chatty applications make excessive data access operations that are sensitive to network latency. 可能需要修改这些类型的应用程序,减少对数据库的数据访问操作的数量。You might need to modify these kinds of applications to reduce the number of data access operations to the database. 例如,可使用将即席查询成批处理或将查询移至存储过程等方法,提高应用程序性能。For example, you might improve application performance by using techniques like batching ad hoc queries or moving the queries to stored procedures. 有关详细信息,请参阅 批处理查询For more information, see Batch queries.

  • 具有不受整台计算机支持的密集型工作负荷的数据库Databases with an intensive workload that can't be supported by an entire single machine

    超过最高“高级”计算大小的资源的数据库可能受益于横向扩展工作负荷。Databases that exceed the resources of the highest Premium compute size might benefit from scaling out the workload. 有关详细信息,请参阅跨数据库分片功能分区For more information, see Cross-database sharding and Functional partitioning.

  • 具有非最优查询的应用程序Applications that have sub-optimal queries

    没有很好优化查询的应用程序(尤其是数据访问层中的那些应用程序),则可能不会受益于更大的计算大小。Applications, especially those in the data access layer, that have poorly tuned queries might not benefit from a higher compute size. 其中包括缺少 WHERE 子句、缺少索引或统计信息过时的查询。This includes queries that lack a WHERE clause, have missing indexes, or have outdated statistics. 标准查询性能优化技术能够为这些应用程序带来好处。These applications benefit from standard query performance-tuning techniques. 有关详细信息,请参阅缺少索引查询优化和提示For more information, see Missing indexes and Query tuning and hinting.

  • 具有非最优数据访问设计的应用程序Applications that have sub-optimal data access design

    选择较大的计算大小可能无法为存在固有数据访问并发问题(例如死锁)的应用程序带来好处。Applications that have inherent data access concurrency issues, for example deadlocking, might not benefit from a higher compute size. 考虑使用 Azure 缓存服务或其他缓存技术将数据缓存在客户端,减少与数据库之间的往返次数。Consider reducing round trips against the database by caching data on the client side with the Azure Caching service or another caching technology. 请参阅 应用程序层缓存See Application tier caching.

优化数据库Tune your database

在本节中,我们将了解一些用于优化数据库的技术,以获取应用程序的最佳性能,并以尽可能小的计算大小运行。In this section, we look at some techniques that you can use to tune database to gain the best performance for your application and run it at the lowest possible compute size. 有些方法可与传统的 SQL Server 优化最佳实践搭配使用,但有些方法专用于 Azure SQL 数据库和 Azure SQL 托管实例。Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database and Azure SQL Managed Instance. 在某些情况下,可通过检查数据库使用的资源找到要进一步优化的区域,扩展传统的 SQL Server 方法,使这些方法也可在 Azure SQL 数据库和 Azure SQL 托管实例中使用。In some cases, you can examine the consumed resources for a database to find areas to further tune and extend traditional SQL Server techniques to work in Azure SQL Database and Azure SQL Managed Instance.

识别和添加缺失的索引Identifying and adding missing indexes

OLTP 数据库性能有一个常见问题与物理数据库设计有关。A common problem in OLTP database performance relates to the physical database design. 设计和交付数据库架构时,经常不进行规模(负载或数据卷)测试。Often, database schemas are designed and shipped without testing at scale (either in load or in data volume). 遗憾的是,在规模较小时,查询计划的性能可能尚可接受,但面对生产级数据卷时,性能就会大幅降低。Unfortunately, the performance of a query plan might be acceptable on a small scale but degrade substantially under production-level data volumes. 此问题最常见的原因是缺乏相应的索引,无法满足筛选器的要求或查询中的其他限制。The most common source of this issue is the lack of appropriate indexes to satisfy filters or other restrictions in a query. 缺少索引经常导致表扫描,而此时索引搜寻即可满足要求。Often, missing indexes manifests as a table scan when an index seek could suffice.

在此示例中,所选查询计划在使用搜寻即可满足要求的情况下使用了扫描:In this example, the selected query plan uses a scan when a seek would suffice:

DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
    WHILE @a < 20000
        INSERT INTO dbo.missingindex(col2) VALUES (@a);
        SET @a += 1;
SELECT m1.col1
    FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
    WHERE m1.col2 = 4;


Azure SQL 数据库和 Azure SQL 托管实例可用于查找和修复常见的索引缺失情况。Azure SQL Database and Azure SQL Managed Instance can help you find and fix common missing index conditions. Azure SQL 数据库和 Azure SQL 托管实例内置的 DMV 将查找其中索引会大幅降低运行查询的估算成本的查询编译。DMVs that are built into Azure SQL Database and Azure SQL Managed Instance look at query compilations in which an index would significantly reduce the estimated cost to run a query. 在查询执行期间,数据库引擎跟踪每个查询计划的执行频率,以及跟踪执行查询计划与想象其中存在该索引的查询计划之间的估算差距。During query execution, the database engine tracks how often each query plan is executed, and tracks the estimated gap between the executing query plan and the imagined one where that index existed. 可以使用这些 DMV 迅速推测出哪些物理数据库设计更改可能减少数据库的总工作负荷成本及其真实工作负荷。You can use these DMVs to quickly guess which changes to your physical database design might improve overall workload cost for a database and its real workload.

此查询可用于评估可能缺少的索引:You can use this query to evaluate potential missing indexes:

   CONVERT (varchar, getdate(), 126) AS runtime
   , mig.index_group_handle
   , mid.index_handle
   , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS improvement_measure
   , 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
        (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL
        AND mid.inequality_columns IS NOT NULL
        THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
   , migs.*
   , mid.database_id
   , mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

在此示例中,查询生成了以下建议:In this example, the query resulted in this suggestion:

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])  

创建建议以后,同一 SELECT 语句会选取另一计划,使用搜寻而非扫描,从而提高计划执行效率:After it's created, that same SELECT statement picks a different plan, which uses a seek instead of a scan, and then executes the plan more efficiently:


重要见解是共享商用系统的 IO 容量会比专用服务器计算机的容量受到更多限制。The key insight is that the IO capacity of a shared, commodity system is more limited than that of a dedicated server machine. 客观上鼓励将不必要 IO 降至最低,最大限度地在服务层级的每个计算大小资源范围内利用系统。There's a premium on minimizing unnecessary IO to take maximum advantage of the system in the resources of each compute size of the service tiers. 选择适当的物理数据库设计方式可显著缩短单个查询的延迟、提高按缩放单元处理的并发请求的吞吐量,以及将满足查询所需的成本降至最低。Appropriate physical database design choices can significantly improve the latency for individual queries, improve the throughput of concurrent requests handled per scale unit, and minimize the costs required to satisfy the query. 有关缺少索引 DMV 的详细信息,请参阅 sys.dm_db_missing_index_detailsFor more information about the missing index DMVs, see sys.dm_db_missing_index_details.

查询优化和提示Query tuning and hinting

Azure SQL 数据库和 Azure SQL 托管实例中的查询优化器与传统的 SQL Server 查询优化器相似。The query optimizer in Azure SQL Database and Azure SQL Managed Instance is similar to the traditional SQL Server query optimizer. 有关优化查询和了解查询优化器的推理模型限制的最佳实践大多也适用于 Azure SQL 数据库和 Azure SQL 托管实例。Most of the best practices for tuning queries and understanding the reasoning model limitations for the query optimizer also apply to Azure SQL Database and Azure SQL Managed Instance. 如果优化 Azure SQL 数据库和 Azure SQL 托管实例中的查询,则可能获得额外的优势:降低总体资源需求。If you tune queries in Azure SQL Database and Azure SQL Managed Instance, you might get the additional benefit of reducing aggregate resource demands. 与未经优化的同等应用程序相比,应用程序可能能够以更低的成本运行,因为它可用更小的计算大小运行。Your application might be able to run at a lower cost than an un-tuned equivalent because it can run at a lower compute size.

在 SQL Server 中常见的一个示例(也适用于 Azure SQL 数据库和 Azure SQL 托管实例)是查询优化器如何“探查”参数。An example that is common in SQL Server and which also applies to Azure SQL Database and Azure SQL Managed Instance is how the query optimizer "sniffs" parameters. 在编译期间,查询优化器会计算参数的当前值,确定其是否能够生成更优化的查询计划。During compilation, the query optimizer evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. 尽管与不使用已知参数值进行编译的计划相比,此策略通常能够生成速度明显更快的查询计划,但目前它在 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例中的使用效果欠佳。Although this strategy often can lead to a query plan that is significantly faster than a plan compiled without known parameter values, currently it works imperfectly both in SQL Server, in Azure SQL Database, and Azure SQL Managed Instance. 有时不探查参数,有时探查参数,但对于工作负荷中的整套参数值而言,生成的计划并非最佳。Sometimes the parameter is not sniffed, and sometimes the parameter is sniffed but the generated plan is sub-optimal for the full set of parameter values in a workload. Microsoft 提供查询提示(指令),让用户可以更谨慎地指定意图并取代参数探查的默认行为。Microsoft includes query hints (directives) so that you can specify intent more deliberately and override the default behavior of parameter sniffing. 通常情况下,如果你使用提示,则可修复默认的 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例行为对于给定客户工作负载不完善的情况。Often, if you use hints, you can fix cases in which the default SQL Server, Azure SQL Database, and Azure SQL Managed Instance behavior is imperfect for a specific customer workload.

下一个示例演示查询处理器如何生成对于性能和资源要求并非最佳的计划。The next example demonstrates how the query processor can generate a plan that is sub-optimal both for performance and resource requirements. 此示例还表明,如果使用查询提示,则可缩短数据库的查询运行时间并降低资源要求:This example also shows that if you use a query hint, you can reduce query run time and resource requirements for your database:

DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));
DECLARE @a int = 0;
   WHILE @a < 20000
     INSERT INTO psptest1(col2) values (1);
     INSERT INTO psptest1(col2) values (@a);
     SET @a += 1;
   CREATE INDEX i1 on psptest1(col2);

CREATE PROCEDURE psp1 (@param1 int)
      INSERT INTO t1 SELECT * FROM psptest1
      WHERE col2 = @param1
      ORDER BY col2;

CREATE PROCEDURE psp2 (@param2 int)
      INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
      ORDER BY col2

CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));

该设置代码创建一个其数据分布处于偏斜状态的表。The setup code creates a table that has skewed data distribution. 最佳查询计划随所选参数的不同而不同。The optimal query plan differs based on which parameter is selected. 遗憾的是,计划缓存行为并非始终根据最常用参数值来重新编译查询。Unfortunately, the plan caching behavior doesn't always recompile the query based on the most common parameter value. 因此,即使另一个计划平均而言可能是更好的计划选择,也可以缓存非最佳计划并将其用于多个值。So, it's possible for a sub-optimal plan to be cached and used for many values, even when a different plan might be a better plan choice on average. 然后,查询计划会创建两个几乎相同的存储过程,唯一区别是其中一个有特殊的查询提示。Then the query plan creates two stored procedures that are identical, except that one has a special query hint.

-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;

-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
      EXEC psp1 @param1=2;
      SET @i += 1;

建议至少等待 10 分钟,再开始示例的第 2 部分,以便在所得的遥测数据中有不同结果。We recommend that you wait at least 10 minutes before you begin part 2 of the example, so that the results are distinct in the resulting telemetry data.

EXEC psp2 @param2=1;

DECLARE @i int = 0;
    WHILE @i < 1000
        EXEC psp2 @param2=2;
        TRUNCATE TABLE t1;
        SET @i += 1;

本例的每个部分均尝试将某个参数化插入语句运行 1,000 次(以产生可用作测试数据集的足够的负载)。Each part of this example attempts to run a parameterized insert statement 1,000 times (to generate a sufficient load to use as a test data set). 当执行存储过程时,查询处理器在其首次编译期间检查传递给过程的参数值(参数“探查”)。When it executes stored procedures, the query processor examines the parameter value that is passed to the procedure during its first compilation (parameter "sniffing"). 处理器会缓存生成的计划,将其用于以后的调用,即使参数值不同也是如此。The processor caches the resulting plan and uses it for later invocations, even if the parameter value is different. 可能无法在所有情况下均使用最佳计划。The optimal plan might not be used in all cases. 有时,用户需要引导优化器选取更适合普通情况而非首次编译查询时的特定情况的计划。Sometimes you need to guide the optimizer to pick a plan that is better for the average case rather than the specific case from when the query was first compiled. 在此示例中,初始计划会生成一个“扫描”计划,后者会读取所有行以查找与参数匹配的每个值:In this example, the initial plan generates a "scan" plan that reads all rows to find each value that matches the parameter:


由于我们用值 1 执行该过程,因此所得的计划对于值 1 为最佳,但对于表中的所有其他值并非最佳。Because we executed the procedure by using the value 1, the resulting plan was optimal for the value 1 but was sub-optimal for all other values in the table. 如果随机选取每个计划,结果可能不如所愿,因为计划的执行速度可能较慢,所用资源可能较多。The result likely isn't what you would want if you were to pick each plan randomly, because the plan performs more slowly and uses more resources.

如果运行测试时将 SET STATISTICS IO 设置为 ON,则会在后台完成此示例中的逻辑扫描工作。If you run the test with SET STATISTICS IO set to ON, the logical scan work in this example is done behind the scenes. 可以看到计划完成了 1,148 次读取(如果平均仅返回一行,此读取效率并不高):You can see that there are 1,148 reads done by the plan (which is inefficient, if the average case is to return just one row):


本例的第二部分使用查询提示告知优化器在编译过程中使用某个特定值。The second part of the example uses a query hint to tell the optimizer to use a specific value during the compilation process. 在本示例中,它强制查询处理器忽略作为参数传递的值,而采用 UNKNOWNIn this case, it forces the query processor to ignore the value that is passed as the parameter, and instead to assume UNKNOWN. 这是指在表中的出现频率为平均频率的值(忽略偏斜情况)。This refers to a value that has the average frequency in the table (ignoring skew). 所得的计划是一个基于搜寻的计划,平均而言,它比此示例第 1 部分中的计划速度更快且使用资源更少:The resulting plan is a seek-based plan that is faster and uses fewer resources, on average, than the plan in part 1 of this example:


可以查看 sys.resource_stats 表的影响(执行测试的时间与数据填充表的时间之间有延迟)。You can see the effect in the sys.resource_stats table (there is a delay from the time that you execute the test and when the data populates the table). 对于本例,会在 22:25:00 时间范围内执行第 1 部分,在 22:35:00 执行第 2 部分。For this example, part 1 executed during the 22:25:00 time window, and part 2 executed at 22:35:00. 越早时间范围使用的资源比越晚时间范围要多(因计划效率提高)。The earlier time window used more resources in that time window than the later one (because of plan efficiency improvements).

FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC



虽然此示例特意选择了较小的卷,但非最佳参数的影响仍很大,对于较大的数据库尤为如此。Although the volume in this example is intentionally small, the effect of sub-optimal parameters can be substantial, especially on larger databases. 这种区别在极端情况下对于快速情况和慢速情况可在数秒和数小时之间。The difference, in extreme cases, can be between seconds for fast cases and hours for slow cases.

可检查 sys.resource_stats,以确定测试使用的资源多于还是少于另一个测试。You can examine sys.resource_stats to determine whether the resource for a test uses more or fewer resources than another test. 在比较数据时,请使测试相隔一定时间,以使其不会在 sys.resource_stats 视图中的同一 5 分钟时间范围内重合。When you compare data, separate the timing of tests so that they are not in the same 5-minute window in the sys.resource_stats view. 本练习的目标是将使用的资源总量降至最低,而非将峰值资源降至最低。The goal of the exercise is to minimize the total amount of resources used, and not to minimize the peak resources. 一般而言,优化一段产生延迟的代码也会减少资源消耗。Generally, optimizing a piece of code for latency also reduces resource consumption. 请确保对应用程序所做的更改是必需的,且这些更改不会对那些可能会在应用程序中使用查询提示的人的客户体验造成负面影响。Make sure that the changes you make to an application are necessary, and that the changes don't negatively affect the customer experience for someone who might be using query hints in the application.

如果工作负荷由一组重复的查询组成,则捕获并验证所做计划选择的最优性通常很有意义,因为这样做会使托管数据库所需的资源大小单位降至最低。If a workload has a set of repeating queries, often it makes sense to capture and validate the optimality of your plan choices because it drives the minimum resource size unit required to host the database. 对其进行验证后,应偶尔重新检查计划,以帮助你确保其未降级。After you validate it, occasionally reexamine the plans to help you make sure that they have not degraded. 可以详细了解查询提示 (TRANSACT-SQL)You can learn more about query hints (Transact-SQL).

特大型数据库体系结构Very large database architectures

在用于 Azure SQL 数据库中单一数据库的超大规模服务层级发布之前,客户过去常达到单个数据库的容量限制。Before the release of Hyperscale service tier for single databases in Azure SQL Database, customers used to hit capacity limits for individual databases. Azure SQL 数据库弹性池中的共用数据库和 Azure SQL 托管实例中的实例数据库仍然存在这些容量限制。These capacity limits still exist for pooled databases in Azure SQL Database elastic pools and instance databases in Azure SQL Managed Instances. 以下两节介绍了在无法使用“超大规模”服务层级时解决 Azure SQL 数据库和 Azure SQL 托管实例中特大型数据库问题的两个选项。The following two sections discuss two options for solving problems with very large databases in Azure SQL Database and Azure SQL Managed Instance when you cannot use the Hyperscale service tier.

跨数据库分片Cross-database sharding

由于 Azure SQL 数据库和 Azure SQL 托管实例在商品硬件上运行,因此单一数据库的容量限制低于传统的本地 SQL Server 安装。Because Azure SQL Database and Azure SQL Managed Instance runs on commodity hardware, the capacity limits for an individual database are lower than for a traditional on-premises SQL Server installation. 在数据库操作超出 Azure SQL 数据库和 Azure SQL 托管实例中单一数据库的限制时,一些客户使用分片技术将这些操作分摊到多个数据库上。Some customers use sharding techniques to spread database operations over multiple databases when the operations don't fit inside the limits of an individual database in Azure SQL Database and Azure SQL Managed Instance. 在 Azure SQL 数据库和 Azure SQL 托管实例上使用分片技术的大多数客户将单个维度的数据拆分到多个数据库上。Most customers who use sharding techniques in Azure SQL Database and Azure SQL Managed Instance split their data on a single dimension across multiple databases. 对于该方法,需了解 OLTP 应用程序执行的事务经常仅适用于架构中的一行或少数几行。For this approach, you need to understand that OLTP applications often perform transactions that apply to only one row or to a small group of rows in the schema.


Azure SQL 数据库现在提供一个库来帮助分片。Azure SQL Database now provides a library to assist with sharding. 有关详细信息,请参阅弹性数据库客户端库概述For more information, see Elastic Database client library overview.

例如,如果数据库包含客户名称、订单和订单明细(如 SQL Server 附带的传统示例 Northwind 数据库),则可通过将客户与相关订单及订单明细集中在一起,将这些数据拆分到多个数据库中。For example, if a database has customer name, order, and order details (like the traditional example Northwind database that ships with SQL Server), you could split this data into multiple databases by grouping a customer with the related order and order detail information. 可以保证客户的数据保留在单一数据库中。You can guarantee that the customer's data stays in an individual database. 应用程序将不同的客户拆分到多个数据库上,实际上就是将负载分散在多个数据库上。The application would split different customers across databases, effectively spreading the load across multiple databases. 通过分片,客户不仅可以避免达到最大数据库大小限制,而且 Azure SQL 数据库和 Azure SQL 托管实例还能够处理明显大于不同计算大小限制的工作负载,前提是每个数据库适合其服务层限制。With sharding, customers not only can avoid the maximum database size limit, but Azure SQL Database and Azure SQL Managed Instance also can process workloads that are significantly larger than the limits of the different compute sizes, as long as each individual database fits into its service tier limits.

数据库分片不会减少解决方案的聚合资源容量,但在支持跨多个数据库的极大型解决方案时很有效。Although database sharding doesn't reduce the aggregate resource capacity for a solution, it's highly effective at supporting very large solutions that are spread over multiple databases. 每个数据库可以使用不同的计算大小来运行,以支持非常大的、资源要求高的“有效”数据库。Each database can run at a different compute size to support very large, "effective" databases with high resource requirements.

功能分区Functional partitioning

用户经常将许多功能集中在单一数据库内。Users often combine many functions in an individual database. 例如,如果应用程序包含管理商店库存的逻辑,则该数据库可能包含与库存、跟踪采购订单、存储过程、管理月末报告的索引视图或具体化视图关联的逻辑。For example, if an application has logic to manage inventory for a store, that database might have logic associated with inventory, tracking purchase orders, stored procedures, and indexed or materialized views that manage end-of-month reporting. 此方法可轻松管理数据库,进行备份之类的操作,但也要求用户调整硬件大小以处理应用程序所有功能的峰值负载。This technique makes it easier to administer the database for operations like backup, but it also requires you to size the hardware to handle the peak load across all functions of an application.

如果在 Azure SQL 数据库和 Azure SQL 托管实例中使用横向扩展体系结构,则可将应用程序的不同功能拆分到不同的数据库中。If you use a scale-out architecture in Azure SQL Database and Azure SQL Managed Instance, it's a good idea to split different functions of an application into different databases. 每个应用程序均可使用此方法独立缩放。By using this technique, each application scales independently. 随着应用程序变得更加繁忙(并且数据库负载不断增长),管理员可针对应用程序中的每项功能选择单独的计算大小。As an application becomes busier (and the load on the database increases), the administrator can choose independent compute sizes for each function in the application. 在限制范围内,使用此体系结构时,由于负载分散在多个计算机上,因此应用程序的规模可超出单个商用计算机的处理能力。At the limit, with this architecture, an application can be larger than a single commodity machine can handle because the load is spread across multiple machines.

批处理查询Batch queries

对于以大量、频繁的即席查询形式访问数据的应用程序,在应用程序层与数据库层之间的网络通信上花费了大量响应时间。For applications that access data by using high-volume, frequent, ad hoc querying, a substantial amount of response time is spent on network communication between the application tier and the database tier. 即使在应用程序与数据库同处一个数据中心时,大量数据访问操作也可能会增大二者之间的网络延迟。Even when both the application and the database are in the same data center, the network latency between the two might be magnified by a large number of data access operations. 要减少进行数据访问操作所需的网络往返,可考虑使用相应选项,要么批处理即席查询,要么将其编译为存储过程。To reduce the network round trips for the data access operations, consider using the option to either batch the ad hoc queries, or to compile them as stored procedures. 如果将即席查询分批,可将多个查询作为一个大批次在一次行程中发送到数据库。If you batch the ad hoc queries, you can send multiple queries as one large batch in a single trip to the database. 将即席查询编入存储过程可获得与分批相同的结果。If you compile ad hoc queries in a stored procedure, you could achieve the same result as if you batch them. 使用存储过程还有一个好处,即可以有更多的机会将查询计划缓存在数据库中,以便再次使用存储过程。Using a stored procedure also gives you the benefit of increasing the chances of caching the query plans in the database so you can use the stored procedure again.

某些应用程序频繁写入。Some applications are write-intensive. 有时,通过考虑如何统一批处理写入,可以减少数据库上的总 IO 负载。Sometimes you can reduce the total IO load on a database by considering how to batch writes together. 通常,这与在存储过程和即席批处理中使用显式事务代替自动提交事务一样简单。Often, this is as simple as using explicit transactions instead of auto-commit transactions in stored procedures and ad hoc batches. 有关各种可用方法的评估,请参阅 Azure 中数据库应用程序的批处理技术For an evaluation of different techniques you can use, see Batching techniques for database applications in Azure. 使用自己的工作负荷进行实验,找到正确的批处理模型。Experiment with your own workload to find the right model for batching. 请务必了解,模型的事务一致性保证可能略有不同。Be sure to understand that a model might have slightly different transactional consistency guarantees. 要找到将资源用量降至最低的正确工作负荷,需要找到一致性与性能折中的正确组合。Finding the right workload that minimizes resource use requires finding the right combination of consistency and performance trade-offs.

应用程序层缓存Application-tier caching

某些数据库应用程序的工作负荷包含大量的读取操作。Some database applications have read-heavy workloads. 缓存层可减少数据库上的负载,还有可能通过使用 Azure SQL 数据库和 Azure SQL 托管实例降低支持数据库所需的计算大小。Caching layers might reduce the load on the database and might potentially reduce the compute size required to support a database by using Azure SQL Database and Azure SQL Managed Instance. 通过 Azure Cache for Redis,如果你有一个读取作业繁重的工作负载,可以读取数据一次(或者也许可以按应用层计算机读取一次,具体取决于其配置方式),然后将该数据存储在数据库外部。With Azure Cache for Redis, if you have a read-heavy workload, you can read the data once (or perhaps once per application-tier machine, depending on how it is configured), and then store that data outside of your database. 这样可降低数据库负载(CPU 和读取 IO),但对于事务一致性有影响,因为从缓存读取的数据可能与数据库中数据不同步。This is a way to reduce database load (CPU and read IO), but there is an effect on transactional consistency because the data being read from the cache might be out of sync with the data in the database. 虽然许多应用程序可接受一定程度的不一致,但并非所有工作负荷都是这样。Although in many applications some level of inconsistency is acceptable, that's not true for all workloads. 应该先完全了解任何应用程序要求,再实施应用程序层缓存策略。You should fully understand any application requirements before you implement an application-tier caching strategy.

后续步骤Next steps