How to trace all queries being executed on Cosmos

Question

The long back story

We have a database with 10s of millions of records and we are running in to performance issues.

After analysing the applications using this database, we created a new indexing policy which EXCLUDES all fields by default, but then INCLUDES all properties that have been requested in the WHERE / ORDER BY queries used by these applications, including multiple composite indexes on the partition key field plus other key identifiers.

I then ran a simple script which executed all the queries with diagnostics enabled to see how the DB query optimizer would then handle those queries.

The result was that every single one of them reported that it "Utilised Composite Index" with "Impact: High" which, to me, was the exact result I wanted.

In fact, the whole reason for this effort was that one particular query was taking 100seconds to run, for 350 records and after the above exercise, this query now executes in under 1 second.

The problem

Now the index policy is in place, we are now experiencing pegged 100% RU consumption and high 429 error codes.

The Theory

My theory is that there are some other applications out there that we don't know about using this database and their queries have not been taken in to account for the indexing optimisation.

The Question

In Azure Portal, is there a way to record every single query being sent to the database, for say 24hours, so that we can see if there are any "rogue" queries being executed, so we can then look at them and include them in the the indexing policy.

The Apology

Sorry for the long post!

Answer 1

Hi Chris Hammond •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to trace all queries being executed on Cosmos DB.

Use Enable full-text query for logging query text:

Enabling this feature may result in additional logging costs, for pricing details visit Azure Monitor pricing. It is recommended to disable this feature after troubleshooting.

Azure Cosmos DB provides advanced logging for detailed troubleshooting. By enabling full-text query, you're able to view the deobfuscated query for all requests within your Azure Cosmos DB account. You also give permission for Azure Cosmos DB to access and surface this data in your logs.

Select Enable. This setting is applied within a few minutes. All newly ingested logs have the full-text or PIICommand text for each request.

The query metrics will help determine where the query is spending most of the time. From the query metrics, you can see how much of it's being spent on the back-end vs the client. Learn more on the query performance guide.

In the API for NoSQL SDKs, Azure Cosmos DB provides query execution statistics.

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(

UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName),

"SELECT * FROM c WHERE c.city = 'Seattle'",

new FeedOptions

{

PopulateQueryMetrics = true,

MaxItemCount = -1,

MaxDegreeOfParallelism = -1,

EnableCrossPartitionQuery = true

}).AsDocumentQuery();

FeedResponse<dynamic> result = await query.ExecuteNextAsync();

// Returns metrics by partition key range Id

IReadOnlyDictionary<string, QueryMetrics> metrics = result.QueryMetrics;

QueryMetrics provides details on how long each component of the query took to execute. The most common root cause for long running queries is scans, meaning the query was unable to apply the indexes. This problem can be resolved with a better filter condition.

Hope this information helps. Let us know if you have further queries.

Thank you.