How to measure RU in DocumentDB?

Question

Given that Azure DocumentDB uses Requests Units as a measurement for throughput I would like to make sure my queries utilize the least amount of RUs as possible to ncrease my throughput. Is there a tool that will tell me how many RUs a query will take and if the query is actually using an index or not?

Answer 1

Another option is to use the emulator with the trace collection option turned on. https://learn.microsoft.com/en-us/azure/cosmos-db/local-emulator

I was trying to profile LINQ aggregate queries, which currently seems to be impossible with c# SDK.

Using the trace output from the emulator I was able to identify the request charges and a host of other metrics. There is a lot of data to wade through through. I found the request charge stored under this event key DocDBServer/Transport_Channel_Processortask/Genericoperation

Example output:
ThreadID="141,928" FormattedMessage="EndRequest DocumentServiceId localhost, ResourceType 2, OperationType 15, ResourceId 91M7AL+QPQA=, StatusCode 200, HRESULTHex 0, ResponseLength 61, Duration 70,546, HasQuery 1, PartitionId a4cb495b-38c8-11e6-8106-8cdcd42c33be, ReplicaId 1, ConsistencyLevel 3, RequestSessionToken 0:594, ResponseSessionToken 594, HasContinuation 0, HasPreTrigger 0, HasPostTrigger 0, IsFeedUnfiltered 0, IndexingDirective 5, XDate Fri, 09 Jun 2017 08:49:03 GMT, RetryAfterMilliseconds 0, MaxItemCount -1, ActualItemCount 1, ClientVersion 2017-02-22, UserAgent Microsoft.Azure.Documents.Common/1.13.58.2, RequestLength 131, NetworkBucket 2, SubscriptionId 00000000-0000-0000-0000-000000000000, Region South Central US, IpAddress 192.168.56.0, ChannelProtocol RNTBD, RequestCharge 51.424, etc...

This can then be correlated with data from another event which contains the query info: DocDBServer/ServiceModuletask/Genericoperation

Note you need perfview to view the ETL log files. See here for more info: https://github.com/Azure/azure-documentdb-dotnet/blob/master/docs/documentdb-sdk_capture_etl.md

Answer 2

As you discovered, certain tools will provide RU's upon completion of a query. This is also available programmatically, as the x-ms-request-charge header is returned in the response, and easily retrievable via the DocumentDB SDKs.

For example, here's a snippet showing RU retrieval using JS/node:

var queryIterator = client.queryDocuments(collLink, querySpec );
queryIterator.executeNext(function (err, results, headers) {
  if (err) {
    // deal with error...
  } else {
    // deal with payload...
    var ruConsumed = headers['x-ms-request-charge'];
  }
});

As far as your question regarding indexing, and determining if a property is indexed (which should then answer your question about a query using or not using an index): You may query the collection, which returns the indexing details in the response header.

For example: given some path dbs/<databaseId>/colls/<collectionId>:

var collLink = 'dbs/' + databaseId+ '/colls/'+ collectionId;

client.readCollection(collLink, function (err, coll) {
    if (err) {
        // deal with error

    } else {
        // compare indexingPolicy with your property, to see if it's included or excluded
        // this just shows you what these properties look like
        console.log("Included: " + JSON.stringify(coll.indexingPolicy.includedPaths))
        console.log("Excluded: " + JSON.stringify(coll.indexingPolicy.excludedPaths))
    }
});

You'll see includedPaths and excludedPaths looking something like this, and you can then search for your given property in any way you see fit:

Included: [{"path":"/*","indexes":[{"kind":"Range","dataType":"Number","precision":-1},{"kind":"Hash","dataType":"String","precision":3}]}]
Excluded: []

Answer 3

I found DocumentDb Studio which shows the response header that provide the RUs on every query.