在 Azure SQL Database 中手動微調查詢效能Manual tune query performance in Azure SQL Database

在您發現 SQL Database 的效能問題後,這篇文章可協助您: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 Database 資料庫建議程式的建議和 Azure SQL Database 自動微調建議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 Database 時,最好是將執行和微調應用程式的程序交織在一起。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 Database 中的服務層時,微調應用程式可以最小化資源需求並降低每月帳單。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 Database 服務層的設計可以改善應用程式的效能穩定性和可預測性,但某些最佳做法可以協助您微調應用程式,以更充分利用某一計算大小的資源。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 Database 的資料存取作業數目。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 Database 的往返作業。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 Database 以獲取應用程式的最佳效能,並且盡可能在最小的計算大小中執行。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 Database。Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database. 在某些情況下,您可以檢查資料庫已取用的資源來尋找要進一步微調的區域,並擴充傳統的 SQL Server 技術以使其適用於 Azure SQL Database。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 Database 可協助您尋找和修正常見的遺漏索引狀況。Azure SQL Database can help you find and fix common missing index conditions. Azure SQL Database 內建的 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 Database 會追蹤每個查詢計劃的執行頻率,並追蹤執行查詢計劃和其中存在該索引之假設查詢計劃之間的預估落差。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 Database 的服務層中,於每個計算大小的 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 Database 內的查詢最佳化工具類似於傳統的 SQL Server 查詢最佳化工具。The query optimizer in Azure SQL Database is similar to the traditional SQL Server query optimizer. 微調查詢和了解查詢最佳化工具之推論模型限制的大多數最佳做法也適用於 Azure SQL Database。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 Database 中的查詢,您可以因為減少彙整資源需求而獲得額外好處。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 Database 的範例是查詢最佳化工具如何「探查」參數。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 Database 中的運作狀況並不完美。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 Database 行為不完美的情況。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 Database 的查詢執行時間和資源需求︰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

此範例中的每個部分都嘗試執行參數化的 insert 陳述式 1000 次 (以產生足夠的負載來做為測試資料集)。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). 在此範例中,第 1 部分會在 22:25:00 時間範圍期間執行,而第 2 部分會在 22:35:00 執行。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 Database 會在商用硬體上執行,所以個別資料庫的容量限制會比傳統的內部部署 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 Database 中的個別資料庫限制時,使用分區化技術在多個資料庫散佈這些作業。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 Database 上使用分區化技術的大部分客戶都會在跨多個資料庫的單一維度上分割其資料。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 Database 現在提供可協助分區化的程式庫。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 Database 也能處理明顯大於不同計算大小限制的工作負載,前提是每個個別資料庫符合其 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 Database 中使用相應放大架構,則最好將應用程式的不同功能分割至不同資料庫。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 Database 層之間的網路通訊上。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 Database 都位於相同的資料中心,兩者之間的網路延遲還是可能因為大量資料存取作業而放大。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 Database。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 Database 中快取查詢計劃的機會增加,因此您可以再次使於預存程序。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 Database 應用程式的批次處理技術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 Database 而有機會降低支援資料庫所需的計算大小。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 Cache for Redis時,如果您具有大量讀取工作負載,您可以讀取資料一次 (或可能每個應用程式層電腦讀取一次,取決於設定方式),然後將該資料儲存在 SQL Database 之外。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