您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

手动优化 Azure SQL 数据库中的查询性能Manual tune query performance in Azure SQL Database

如果你确定遇到了与 SQL 数据库相关的性能问题,则以下文章可为你提供帮助:Once you have identified a performance issue that you are facing with SQL Database, 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. 它还假定你已查看了监视和优化概述以及与性能问题故障排除相关的文章。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 Database, 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 数据库中的服务层级时,优化应用程序可以使资源需求降至最低并减少每月的费用。But, tuning an application can minimize resource requirements and lower monthly bills when you use the service tiers in Azure SQL Database.

应用程序特征Application characteristics

尽管 Azure SQL 数据库服务层级旨在提高应用程序的性能稳定性和可预测性,但一些最佳做法可以帮助你优化应用程序,以便更好地利用某一计算大小的资源。Although Azure SQL Database 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. 可能需要修改这些类型的应用程序,以减少对 SQL 数据库进行的数据访问操作的数量。You might need to modify these kinds of applications to reduce the number of data access operations to the SQL 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 缓存服务或其他缓存技术将数据缓存在客户端,减少与 Azure SQL 数据库之间的往返次数。Consider reducing round trips against the Azure SQL Database by caching data on the client side with the Azure Caching service or another caching technology. 请参阅 应用程序层缓存See Application tier caching.

优化数据库Tune your database

在本节中,我们将了解一些用于优化 Azure SQL 数据库的技术,以获取应用程序的最佳性能,并以尽可能小的计算大小运行。In this section, we look at some techniques that you can use to tune Azure SQL Database to gain the best performance for your application and run it at the lowest possible compute size. 其中某些技术可与传统 SQL Server 优化最佳做法搭配使用,但其他技术是特定于 Azure SQL 数据库的。Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database. 在某些情况下,可以检查数据库的使用资源找到进一步优化的区域,并扩展传统 SQL Server 技术以便在 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.

发现并添加缺失索引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;
SET NOCOUNT ON;
BEGIN TRANSACTION
    WHILE @a < 20000
    BEGIN
        INSERT INTO dbo.missingindex(col2) VALUES (@a);
        SET @a += 1;
    END
    COMMIT TRANSACTION;
    GO
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 Database can help you find and fix common missing index conditions. Azure SQL 数据库内置的 DMV 将查找其中索引会大幅降低运行查询的估算成本的查询编译。DMVs that are built into Azure SQL Database look at query compilations in which an index would significantly reduce the estimated cost to run a query. 在查询执行期间,SQL 数据库跟踪每个查询计划的执行频率,并跟踪执行查询计划与想象其中存在该索引的查询计划之间的差距。During query execution, SQL Database 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:

SELECT
   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 降至最低,最大限度地在 Azure SQL 数据库服务层级的每个计算大小 DTU 范围内利用系统。There's a premium on minimizing unnecessary IO to take maximum advantage of the system in the DTU of each compute size of the Azure SQL Database 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 数据库中的查询优化器与传统的 SQL Server 查询优化器相似。The query optimizer in Azure SQL Database is similar to the traditional SQL Server query optimizer. 有关优化查询和了解查询优化器的推理模型限制的最佳实践也大多适用于 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. 如果优化 Azure SQL 数据库中的查询,则可能会获得另一个降低总资源需求的好处。If you tune queries in Azure SQL Database, 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 数据库的一个示例是,查询优化器如何“探查”参数。An example that is common in SQL Server and which also applies to Azure SQL Database 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 数据库中都不能正常工作。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 and in Azure SQL Database. 有时不探查参数,有时探查参数,但对于工作负荷中的整套参数值而言,生成的计划并非最佳。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 数据库行为对于特定客户工作负荷不完善的情况。Often, if you use hints, you can fix cases in which the default SQL Server or Azure SQL Database 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. 此示例还显示,当使用查询提示时,可以减少 SQL 数据库的查询运行时间和资源需求:This example also shows that if you use a query hint, you can reduce query run time and resource requirements for your SQL database:

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

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

CREATE PROCEDURE psp2 (@param2 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
      ORDER BY col2
      OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
   END
   GO

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

该设置代码将创建一个包含偏斜数据分布的表。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;
TRUNCATE TABLE t1;

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

建议至少等待 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;
TRUNCATE TABLE t1;

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

本例的每个部分均尝试将某个参数化插入语句运行 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).

SELECT TOP 1000 *
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).

跨数据库分片Cross-database sharding

由于 Azure SQL 数据库在商品硬件上运行,因此单一数据库的容量限制低于传统的本地 SQL Server 安装。Because Azure SQL Database runs on commodity hardware, the capacity limits for an individual database are lower than for a traditional on-premises SQL Server installation. 在数据库操作超出 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. 在 Azure SQL 数据库中使用分片技术的大多数客户将单个维度的数据拆分到多个数据库上。Most customers who use sharding techniques in Azure SQL Database 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.

备注

SQL 数据库现在提供一个库来帮助分片。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 数据库还能够处理明显大于不同计算大小限制的工作负荷,前提是每个数据库适合其 DTU。With sharding, customers not only can avoid the maximum database size limit, but Azure SQL Database 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 DTU.

尽管数据库分片不会减少解决方案的总体资源容量,但最好支持将超大型解决方案分散在多个数据库中。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

SQL Server 用户经常将许多功能集中在单一数据库内。SQL Server 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 数据库内使用扩大体系结构,最好将应用程序的不同功能拆分到不同的数据库中。If you use a scale-out architecture in Azure SQL Database, 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

对于通过大量、频繁的即席查询访问数据的应用程序,在应用层与 Azure SQL 数据库层之间的网络通信上花费了大量响应时间。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 Azure SQL Database tier. 即使在应用程序与 Azure SQL 数据库同处一个数据中心时,大量数据访问操作也可能会增大二者之间的网络延迟。Even when both the application and Azure SQL 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. 如果批处理即席查询,可将多个查询作为一个大批次在一次行程中发送到 Azure SQL 数据库。If you batch the ad hoc queries, you can send multiple queries as one large batch in a single trip to Azure SQL Database. 将即席查询编入存储过程,可获得与批处理相同的结果。If you compile ad hoc queries in a stored procedure, you could achieve the same result as if you batch them. 使用存储过程还有一个好处,即增加将查询计划缓存在 Azure SQL 数据库中的机会,以便再次执行同一存储过程。Using a stored procedure also gives you the benefit of increasing the chances of caching the query plans in Azure SQL 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 中 SQL 数据库应用程序的批处理技术For an evaluation of different techniques you can use, see Batching techniques for SQL 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 数据库的数据库所需的计算大小。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. 通过 Azure Redis 缓存,如果有一个读取作业繁重的工作负荷,可以读取数据一次(或者也许可以按应用层计算机读取一次,具体取决于其配置方式),然后将该数据存储在 SQL 数据库外部。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 your SQL 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