使用 .NET SDK 获取 SQL 查询执行指标并分析查询性能Get SQL query execution metrics and analyze query performance using .NET SDK

本文介绍如何分析 Azure Cosmos DB 中的 SQL 查询性能。This article presents how to profile SQL query performance on Azure Cosmos DB. 可以使用从 .NET SDK 检索到且在本文中详述的 QueryMetrics 来执行这种分析。This profiling can be done using QueryMetrics retrieved from the .NET SDK and is detailed here. QueryMetrics 是一个强类型化对象,包含有关后端查询执行的信息。QueryMetrics is a strongly typed object with information about the backend query execution. 优化查询性能一文中更详细地阐述了这些指标。These metrics are documented in more detail in the Tune Query Performance article.

设置 FeedOptions 参数Set the FeedOptions parameter

DocumentClient.CreateDocumentQuery 的所有重载采用可选的 FeedOptions 参数。All the overloads for DocumentClient.CreateDocumentQuery take in an optional FeedOptions parameter. 此选项涉及到如何优化和参数化查询执行。This option is what allows query execution to be tuned and parameterized.

若要收集 SQL 查询执行指标,必须将 FeedOptions 中的参数 PopulateQueryMetrics 设置为 trueTo collect the Sql query execution metrics, you must set the parameter PopulateQueryMetrics in the FeedOptions to true. 如果将 PopulateQueryMetrics 设置为 true,FeedResponse 将包含相关的 QueryMetricsSetting PopulateQueryMetrics to true will make it so that the FeedResponse will contain the relevant QueryMetrics.

使用 AsDocumentQuery() 获取查询指标Get query metrics with AsDocumentQuery()

以下代码示例演示使用 AsDocumentQuery() 方法时如何检索指标:The following code sample shows how to do retrieve metrics when using AsDocumentQuery() method:

// Initialize this DocumentClient and Collection
DocumentClient documentClient = null;
DocumentCollection collection = null;

// Setting PopulateQueryMetrics to true in the FeedOptions
FeedOptions feedOptions = new FeedOptions
{
    PopulateQueryMetrics = true
};

string query = "SELECT TOP 5 * FROM c";
IDocumentQuery<dynamic> documentQuery = documentClient.CreateDocumentQuery(Collection.SelfLink, query, feedOptions).AsDocumentQuery();

while (documentQuery.HasMoreResults)
{
    // Execute one continuation of the query
    FeedResponse<dynamic> feedResponse = await documentQuery.ExecuteNextAsync();

    // This dictionary maps the partitionId to the QueryMetrics of that query
    IReadOnlyDictionary<string, QueryMetrics> partitionIdToQueryMetrics = feedResponse.QueryMetrics;

    // At this point you have QueryMetrics which you can serialize using .ToString()
    foreach (KeyValuePair<string, QueryMetrics> kvp in partitionIdToQueryMetrics)
    {
        string partitionId = kvp.Key;
        QueryMetrics queryMetrics = kvp.Value;

        // Do whatever logging you need
        DoSomeLoggingOfQueryMetrics(query, partitionId, queryMetrics);
    }
}

聚合 QueryMetricsAggregating QueryMetrics

上一部分提到,已多次调用 ExecuteNextAsync 方法。In the previous section, notice that there were multiple calls to ExecuteNextAsync method. 每次调用都会返回一个包含 QueryMetrics 字典的 FeedResponse 对象;每次延续查询都会返回一个此类对象。Each call returned a FeedResponse object that has a dictionary of QueryMetrics; one for every continuation of the query. 以下示例演示如何使用 LINQ 聚合这些 QueryMetricsThe following example shows how to aggregate these QueryMetrics using LINQ:

List<QueryMetrics> queryMetricsList = new List<QueryMetrics>();

while (documentQuery.HasMoreResults)
{
    // Execute one continuation of the query
    FeedResponse<dynamic> feedResponse = await documentQuery.ExecuteNextAsync();

    // This dictionary maps the partitionId to the QueryMetrics of that query
    IReadOnlyDictionary<string, QueryMetrics> partitionIdToQueryMetrics = feedResponse.QueryMetrics;
    queryMetricsList.AddRange(partitionIdToQueryMetrics.Values);
}

// Aggregate the QueryMetrics using the + operator overload of the QueryMetrics class.
QueryMetrics aggregatedQueryMetrics = queryMetricsList.Aggregate((curr, acc) => curr + acc);
Console.WriteLine(aggregatedQueryMetrics);

按分区 ID 将查询指标分组Grouping query metrics by Partition ID

可按分区 ID 将 QueryMetrics 分组。You can group the QueryMetrics by the Partition ID. 按分区 ID 分组可将特定的分区与其他分区进行比较,以此判断该分区是否导致性能问题。Grouping by Partition ID allows you to see if a specific Partition is causing performance issues when compared to others. 以下示例演示如何使用 LINQ 将 QueryMetrics 分组:The following example shows how to group QueryMetrics with LINQ:

List<KeyValuePair<string, QueryMetrics>> partitionedQueryMetrics = new List<KeyValuePair<string, QueryMetrics>>();
while (documentQuery.HasMoreResults)
{
    // Execute one continuation of the query
    FeedResponse<dynamic> feedResponse = await documentQuery.ExecuteNextAsync();

    // This dictionary is maps the partitionId to the QueryMetrics of that query
    IReadOnlyDictionary<string, QueryMetrics> partitionIdToQueryMetrics = feedResponse.QueryMetrics;
    partitionedQueryMetrics.AddRange(partitionIdToQueryMetrics.ToList());
}

// Now we are able to group the query metrics by partitionId
IEnumerable<IGrouping<string, KeyValuePair<string, QueryMetrics>>> groupedByQueryMetrics = partitionedQueryMetrics
    .GroupBy(kvp => kvp.Key);

// If we wanted to we could even aggregate the groupedby QueryMetrics
foreach(IGrouping<string, KeyValuePair<string, QueryMetrics>> grouping in groupedByQueryMetrics)
{
    string partitionId = grouping.Key;
    QueryMetrics aggregatedQueryMetricsForPartition = grouping
        .Select(kvp => kvp.Value)
        .Aggregate((curr, acc) => curr + acc);
    DoSomeLoggingOfQueryMetrics(query, partitionId, aggregatedQueryMetricsForPartition);
}

LINQ on DocumentQueryLINQ on DocumentQuery

还可以使用 AsDocumentQuery() 方法从 LINQ 查询中获取 FeedResponseYou can also get the FeedResponse from a LINQ Query using the AsDocumentQuery() method:

IDocumentQuery<Document> linqQuery = client.CreateDocumentQuery(collection.SelfLink, feedOptions)
    .Take(1)
    .Where(document => document.Id == "42")
    .OrderBy(document => document.Timestamp)
    .AsDocumentQuery();
FeedResponse<Document> feedResponse = await linqQuery.ExecuteNextAsync<Document>();
IReadOnlyDictionary<string, QueryMetrics> queryMetrics = feedResponse.QueryMetrics;

耗费大量资源的查询Expensive Queries

可以捕获每个查询消耗的请求单位数,以调查高开销的查询,或者消耗了大量吞吐量的查询。You can capture the request units consumed by each query to investigate expensive queries or queries that consume high throughput. 可以使用 FeedResponse 中的 RequestCharge 属性获取请求费用。You can get the request charge by using the RequestCharge property in FeedResponse. 若要详细了解如何使用 Azure 门户和不同的 SDK 获取请求费用,请参阅查找请求单位费用一文。To learn more about how to get the request charge using the Azure portal and different SDKs, see find the request unit charge article.

string query = "SELECT * FROM c";
IDocumentQuery<dynamic> documentQuery = documentClient.CreateDocumentQuery(Collection.SelfLink, query, feedOptions).AsDocumentQuery();

while (documentQuery.HasMoreResults)
{
    // Execute one continuation of the query
    FeedResponse<dynamic> feedResponse = await documentQuery.ExecuteNextAsync();
    double requestCharge = feedResponse.RequestCharge

    // Log the RequestCharge how ever you want.
    DoSomeLogging(requestCharge);
}

获取查询执行时间Get the query execution time

计算执行客户端查询所需的时间时,请确保仅包含调用 ExecuteNextAsync 方法所需的时间,而不要包含基代码的其他部分。When calculating the time required to execute a client-side query, make sure that you only include the time to call the ExecuteNextAsync method and not other parts of your code base. 只是考虑这些调用就能帮助你计算查询执行所需的时间,如以下示例所示:Just these calls help you in calculating how long the query execution took as shown in the following example:

string query = "SELECT * FROM c";
IDocumentQuery<dynamic> documentQuery = documentClient.CreateDocumentQuery(Collection.SelfLink, query, feedOptions).AsDocumentQuery();
Stopwatch queryExecutionTimeEndToEndTotal = new Stopwatch();
while (documentQuery.HasMoreResults)
{
    // Execute one continuation of the query
    queryExecutionTimeEndToEndTotal.Start();
    FeedResponse<dynamic> feedResponse = await documentQuery.ExecuteNextAsync();
    queryExecutionTimeEndToEndTotal.Stop();
}

// Log the elapsed time
DoSomeLogging(queryExecutionTimeEndToEndTotal.Elapsed);

扫描查询(通常速度缓慢且开销较高)Scan queries (commonly slow and expensive)

扫描查询是指不由索引提供服务的查询,因此,在返回结果集之前需要加载许多文档。A scan query refers to a query that wasn't served by the index, due to which, many documents are loaded before returning the result set.

下面是扫描查询的一个示例:Below is an example of a scan query:

SELECT VALUE c.description 
FROM   c 
WHERE UPPER(c.description) = "BABYFOOD, DESSERT, FRUIT DESSERT, WITHOUT ASCORBIC ACID, JUNIOR"

此查询的筛选器使用系统函数 UPPER,该函数不是由索引提供服务。This query's filter uses the system function UPPER, which isn't served from the index. 针对大型集合执行此查询在首次延续时生成了以下查询指标:Executing this query against a large collection produced the following query metrics for the first continuation:

QueryMetrics

Retrieved Document Count                 :          60,951
Retrieved Document Size                  :     399,998,938 bytes
Output Document Count                    :               7
Output Document Size                     :             510 bytes
Index Utilization                        :            0.00 %
Total Query Execution Time               :        4,500.34 milliseconds
  Query Preparation Times
    Query Compilation Time               :            0.09 milliseconds
    Logical Plan Build Time              :            0.05 milliseconds
    Physical Plan Build Time             :            0.04 milliseconds
    Query Optimization Time              :            0.01 milliseconds
  Index Lookup Time                      :            0.01 milliseconds
  Document Load Time                     :        4,177.66 milliseconds
  Runtime Execution Times
    Query Engine Times                   :          322.16 milliseconds
    System Function Execution Time       :           85.74 milliseconds
    User-defined Function Execution Time :            0.00 milliseconds
  Document Write Time                    :            0.01 milliseconds
Client Side Metrics
  Retry Count                            :               0
  Request Charge                         :        4,059.95 RUs

请注意查询指标输出中的以下值:Note the following values from the query metrics output:

Retrieved Document Count                 :          60,951
Retrieved Document Size                  :     399,998,938 bytes

此查询加载了 60,951 个文档,总共为 399,998,938 字节。This query loaded 60,951 documents, which totaled 399,998,938 bytes. 加载这么多的字节会导致开销或请求单位费用增大。Loading this many bytes results in high cost or request unit charge. 它还花费了较长时间来执行查询,花费在属性上的明确总时间为:It also takes a long time to execute the query, which is clear with the total time spent property:

Total Query Execution Time               :        4,500.34 milliseconds

这意味着,执行该查询花费了 4.5 秒(而且这只是第一次延续)。Meaning that the query took 4.5 seconds to execute (and this was only one continuation).

若要优化此示例查询,请避免在筛选器中使用 UPPER。To optimize this example query, avoid the use of UPPER in the filter. 在创建或更新文档时,必须插入全大写的 c.description 值。Instead, when documents are created or updated, the c.description values must be inserted in all uppercase characters. 然后,该查询将变成:The query then becomes:

SELECT VALUE c.description 
FROM   c 
WHERE c.description = "BABYFOOD, DESSERT, FRUIT DESSERT, WITHOUT ASCORBIC ACID, JUNIOR"

现在,可以从索引为此查询提供服务。This query is now able to be served from the index.

若要详细了解如何优化查询性能,请参阅优化查询性能一文。To learn more about tuning query performance, see the Tune Query Performance article.

参考References

后续步骤Next steps