Query Performance Tuning in Azure DocumentDB: A Practical Guide

Writing a query that works is easy. Writing a query that scales is a different story.

As collections grow to millions of documents, queries that returned results in milliseconds during development start taking seconds or timing out entirely. The root cause is almost always the same: the database is doing far more work than it needs to.

In this post, we’ll take a single, realistic query and walk through the full optimization process: running explain() to understand what’s happening under the hood, building indexes step by step, and applying the ESR rule to design a compound index that eliminates wasted scans and in-memory sorts. By the end, you’ll have a repeatable mental model for diagnosing and fixing slow queries in Azure DocumentDB.

Before You Optimize: Find the Slow Queries

Before diving into explain(), you need to know which queries to investigate. In Azure DocumentDB, slow query detection is done through Diagnostic Logs integrated with Azure Log Analytics.

To get started, enable diagnostic logs for your cluster and route them to a Log Analytics workspace. Full setup instructions are available in the official documentation.

Once logs are flowing, use the following KQL query in Log Analytics to surface the slowest queries:

VCoreMongoRequests
| where DurationMs > 1000
| project TimeGenerated, DatabaseName, CollectionName,
          OperationName, DurationMs, PiiCommandText
| order by DurationMs desc
| take 20

Key fields to look at in the results:

• DurationMs: Query execution time in milliseconds
• OperationName: Type of operation (find, aggregate, update, etc.)
• CollectionName: Which collection the query ran against
• PiiCommandText: The actual command that was executed

Once you have a candidate query, copy it and run explain() on it directly against the database.

The Problem Query

Let’s start with something you’ve probably written before:

db.orders.find({
  status: "shipped",
  customerId: "C-4821",
  createdAt: { $gte: ISODate("2024-01-01") }
}).sort({ createdAt: -1 })

Looks innocent. But under load, this query can become your worst enemy. Let’s diagnose it.

Step 1: Run explain() and Read It

db.orders.find({
  status: "shipped",
  customerId: "C-4821",
  createdAt: { $gte: ISODate("2024-01-01") }
}).sort({ createdAt: -1 }).explain("executionStats")

The explain output:

{
  "explainVersion": 2,
  "command": "db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})",
  "explainCommandPlanningTimeMillis": 0.644,
  "explainCommandExecTimeMillis": 81.374,
  "queryPlanner": {
    "namespace": "db.orders",
    "winningPlan": {
      "stage": "PARALLEL_SORT_MERGE",
      "startupCost": 36640.95,
      "totalCost": 40692.13,
      "workersPlanned": 2,
      "estimatedTotalKeysExamined": 34722,
      "inputStage": {
        "stage": "SORT",
        "startupCost": 35640.93,
        "totalCost": 35684.33,
        "sortKeysCount": 1,
        "sortKey": [
          {
            "createdAt": -1
          }
        ],
        "estimatedTotalKeysExamined": 17361,
        "inputStage": {
          "stage": "COLLSCAN",
          "startupCost": 0,
          "totalCost": 34418.4,
          "runtimeFilterSet": [
            {
              "status": {
                "$eq": "shipped"
              }
            },
            {
              "customerId": {
                "$eq": "C-4821"
              }
            },
            {
              "createdAt": {
                "$gte": {
                  "$date": "2024-01-01T00:00:00Z"
                }
              }
            }
          ],
          "estimatedTotalKeysExamined": 17361
        }
      }
    }
  },
  "executionStats": {
    "nReturned": 18,
    "executionTimeMillis": 81.312,
    "executionStartAtTimeMillis": 79.32900000000001,
    "totalDocsExamined": 18,
    "totalKeysExamined": 18,
    "executionStages": {
      "stage": "PARALLEL_SORT_MERGE",
      "nReturned": 18,
      "executionTimeMillis": 81.312,
      "executionStartAtTimeMillis": 79.32900000000001,
      "totalDocsExamined": 18,
      "totalKeysExamined": 18,
      "numBlocksFromCache": 25076,
      "parallelWorkers": 2,
      "inputStage": {
        "stage": "SORT",
        "nReturned": 6,
        "executionTimeMillis": 71.656,
        "executionStartAtTimeMillis": 71.655,
        "totalDocsExamined": 6,
        "totalKeysExamined": 6,
        "sortMethod": "quicksort",
        "totalDataSizeSortedBytesEstimate": 25,
        "numBlocksFromCache": 25076,
        "inputStage": {
          "stage": "COLLSCAN",
          "nReturned": 6,
          "executionTimeMillis": 71.595,
          "executionStartAtTimeMillis": 10.154,
          "totalDocsExamined": 333333,
          "totalKeysExamined": 6,
          "totalDocsRemovedByRuntimeFilter": 333327,
          "numBlocksFromCache": 25000
        }
      }
    }
  },
  "ok": 1
}

Considering the executionTimeMillis alone, the query looks good and returns the results in a few milliseconds, but let’s look at some key fields in the output:

• stage: “COLLSCAN”: Full collection scan, no index used
• totalDocsExamined: How many docs were scanned
• totalKeysExamined: How many index keys were examined
• nReturned: How many docs matched
• executionTimeMillis: Total execution time

Red flag output:

"stage": "COLLSCAN",
"totalDocsExamined": 333333,
"nReturned": 18,
"executionTimeMillis": 81.312

Scanned 333K documents to return 18 (top stage). That’s a 99.99% waste.

Step 2: Add a Naive Index (and Why It’s Not Enough)

Your first instinct might be:

db.orders.createIndex({ status: 1 })

Run explain() again:

"inputStage": {
  "stage": "IXSCAN",
  "nReturned": 249800,
  "executionTimeMillis": 22.078,
  "executionStartAtTimeMillis": 22.077,
  "indexName": "status_1",
  "indexUsage": {
    "scanLoops": 249800,
    "scanType": "regular",
    "scanKeys": [
      "key 1: [(isInequality: false, estimatedEntryCount: 250172)]"
    ]
  },
  "totalKeysExamined": 249800,
  "numBlocksFromCache": 70
}

Better — now it’s an index scan (‘IXSCAN’). But we’re still scanning 249K index keys to return 18 results. The index on ‘status’ narrowed the field, but it didn’t help with ‘customerId’ or ‘createdAt’.

The sort still happens in memory (‘SORT’ stage after ‘IXSCAN’), which can be expensive.

Step 3: Apply the ESR Rule

The ESR rule is the golden standard for designing compound indexes:

> Equality → Sort → Range

Fields used in equality filters come first, followed by fields used in sort, and finally fields used in range filters.

For our query:

• Equality: ‘status’ (“shipped”) and ‘customerId’ (“C-4821”) — exact matches
• Sort: ‘createdAt’ — the sort direction
• Range: ‘createdAt’ — the ‘$gte’ filter

Since ‘createdAt’ is used in both sort and range, it appears once, in the sort position (which also satisfies the range scan naturally):

db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })

Why this order?

1. ‘customerId’ comes first because it is the most selective equality field — it narrows the result set the most aggressively, so the index skips the vast majority of entries immediately
2. ‘status’ follows as a secondary equality filter, further narrowing the remaining entries
3. ‘createdAt: -1’ matches the sort direction, so the engine can walk the index in order instead of sorting in memory
4. The range filter on ‘createdAt’ is applied last, on an already-narrow result set

Run explain() one more time:

{
  "explainVersion": 2,
  "command": "db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})",
  "explainCommandPlanningTimeMillis": 29.028000000000002,
  "explainCommandExecTimeMillis": 0.10200000000000001,
  "queryPlanner": {
    "namespace": "db.orders",
    "winningPlan": {
      "stage": "FETCH",
      "ns": "db.orders",
      "startupCost": 0,
      "totalCost": 2.01,
      "estimatedTotalKeysExamined": 41667,
      "inputStage": {
        "stage": "IXSCAN",
        "ns": "db.orders",
        "indexName": "customerId_1_status_1_createdAt_-1",
        "direction": "Forward",
        "indexUsage": {
          "indexKeyString": "{\"customerId\": 1,\"status\": 1,\"createdAt\": -1}",
          "isMultiKey": false,
          "bounds": [
            "[\"customerId\": [\"C-4821\", \"C-4821\"], \"status\": [\"shipped\", \"shipped\"], \"createdAt\": DESC[{ \"$date\" : \"2024-01-01T00:00:00Z\" }, { \"$date\" : \"292278994-08-17T07:12:55.807Z\" }]]"
          ]
        },
        "startupCost": 0,
        "totalCost": 2.01,
        "hasOrderBy": true,
        "indexFilterSet": [
          {
            "status": {
              "$eq": "shipped"
            }
          },
          {
            "customerId": {
              "$eq": "C-4821"
            }
          },
          {
            "createdAt": {
              "$gte": {
                "$date": "2024-01-01T00:00:00Z"
              }
            }
          }
        ],
        "estimatedTotalKeysExamined": 41667
      }
    },
    "indexCosts": [
      {
        "namespace": "db.orders",
        "costs": [
          {
            "indexName": "_id_",
            "startupCost": 0.425,
            "totalCost": 19644.425,
            "selectivity": 1,
            "correlation": 0.75,
            "estimatedPercentIndexPagesLoaded": 100,
            "estimatedTotalIndexEntries": 1000000,
            "boundarySelectivity": 1
          }
        ]
      }
    ]
  },
  "executionStats": {
    "nReturned": 18,
    "executionTimeMillis": 0.058,
    "executionStartAtTimeMillis": 0.036000000000000004,
    "totalDocsExamined": 18,
    "totalKeysExamined": 18,
    "executionStages": {
      "stage": "FETCH",
      "nReturned": 18,
      "executionTimeMillis": 0.058,
      "executionStartAtTimeMillis": 0.036000000000000004,
      "totalKeysExamined": 18,
      "numBlocksFromCache": 25,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 18,
        "executionTimeMillis": 0.058,
        "executionStartAtTimeMillis": 0.036000000000000004,
        "indexName": "customerId_1_status_1_createdAt_-1",
        "indexUsage": {
          "scanLoops": 19,
          "scanType": "ordered",
          "scanKeys": [
            "key 1: [(isInequality: true, estimatedEntryCount: 18)]"
          ]
        },
        "totalKeysExamined": 18,
        "numBlocksFromCache": 25
      }
    }
  },
  "ok": 1
}

18 keys examined, 18 returned, 0.058ms, zero in-memory sort. That’s what an efficient query looks like.

Step 4: Confirm the Sort is Index-Backed

Look for the absence of a ‘SORT’ stage in the winning plan. A plan like this is ideal:

FETCH

└── IXSCAN { customerId: 1, status: 1, createdAt: -1 }

If you see this instead, the sort is still in memory:

SORT

└── FETCH

└── IXSCAN { status: 1 }

The ESR-ordered compound index eliminates the in-memory sort because the index already delivers documents in the right order.

Step 5: Go Further with Covered Queries

Even with a perfect ‘IXSCAN’, the engine still performs a ‘FETCH’ — it reads the index to find matching keys, then goes back to the collection to retrieve the full document. For high-throughput queries, eliminating this fetch is the next level of optimization.

A covered query is one where the index contains all the fields the query needs — filters, sort, and projected output. The engine never touches the documents themselves.

Add a projection that only returns fields already in the index:

db.orders.find(
  {
    status: "shipped",
    customerId: "C-4821",
    createdAt: { $gte: ISODate("2024-01-01") }
  },
  { _id: 0, customerId: 1, status: 1, createdAt: 1 }
).sort({ createdAt: -1 }).explain("executionStats")

The winning plan now looks like:

IXSCAN { customerId: 1, status: 1, createdAt: -1 }

No ‘FETCH’ stage at all. The query is fully served by the index.

"stage": "IXSCAN",
"totalKeysExamined": 18,
"nReturned": 18,
"executionTimeMillis": 0.053

Common Mistakes to Avoid

Putting range fields first

❌ Wrong — range before equality

db.orders.createIndex({ createdAt: -1, customerId: 1, status: 1 })

This forces the engine to scan a wide range of dates before applying the equality filters.

Mismatching directions in a multi-field sort

To ensure maximum index utilization, the sort specification must match both the fields and their directions as declared in the index — or be the complete reverse of the index.

Given the index { a: 1, b: -1 }:

• .sort({ a: 1, b: -1 }) → Full index-based sort (forward scan)
• .sort({ a: -1, b: 1 }) → Full index-based sort (reverse scan)
• .sort({ a: 1, b: 1 }) → Partial — only ‘a’ uses the index; ‘b’ sorted in memory
• .sort({ a: 1 }) → Partial — only ‘a’ uses the index

Over-indexing

Every index costs write performance and storage. Create indexes for your actual query patterns, not preemptively.

Quick Reference: ESR Checklist

Before creating a compound index, answer these three questions:

1. Which fields use exact match ($eq)? → Put them first
2. Which field is used in sort()? → Put it in the middle
3. Which fields use range operators ($gt, $lt, $gte, $lte, $in)? → Put them last

Summary

• Diagnostic Logs + Log Analytics — Identifies slow queries in production before they become incidents
• Replace COLLSCAN with IXSCAN — Eliminates full collection scan
• Apply ESR rule to compound index — Narrows key scans to near-exact matches
• Match index direction to sort — Eliminates expensive in-memory sort
• Use covered queries (projection) — Eliminates the FETCH stage — index serves the full query

A single well-designed index transformed an 81.3ms query into a 0.053ms one without application code changes required. With fewer resources consumed per query, your application gains more headroom to scale and can handle higher request rates without adding infrastructure.

Frequently asked questions

How can I find slow queries in Azure DocumentDB?

Use Diagnostic Logs routed to an Azure Log Analytics workspace. Query `VCoreMongoRequests` for long-running operations, then run `explain()` on a candidate query.

Learn how to configure Diagnostic Logs for your cluster here.

What should I check in `explain(“executionStats”)`?

Review `stage`, `totalDocsExamined`, `totalKeysExamined`, `nReturned`, and `executionTimeMillis`. `COLLSCAN` indicates a full collection scan, while `IXSCAN` means an index was used.

To dive deeper into explain analysis and query performance diagnostics, see the Query Explain guide here.

Why might a single-field index still be inefficient?

A single-field index may support one filter while leaving other filters and the sort unsupported. This can result in excessive index scans and an in-memory `SORT` stage.

What is the ESR rule for compound indexes?

ESR stands for Equality, Sort, Range. Place exact-match fields first, followed by the sort field and then range fields.

How can I tell whether a sort is index-backed?

Check that the winning execution plan does not contain a `SORT` stage. For multi-field sorts, the fields and directions should match the index or its complete reverse.

What is a covered query?

A covered query has all filter, sort, and projected output fields in the index. You can confirm coverage when `explain()` shows no `FETCH` stage.

Should I create indexes for every possible query?

No. Each index adds storage and write-performance costs. Create indexes for real query patterns, then use `explain()` to confirm that they reduce scans and unnecessary sorting.