Home / Azure / Fix Azure Cosmos DB SQL Query Slow

Azure

Fix Azure Cosmos DB SQL Query Slow

Optimize slow Cosmos DB SQL queries by adding indexes, using partition keys, and reducing cross-partition scans.

Published: Apr 2, 20267 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

Cosmos DB SQL queries can become slow due to cross-partition scans, missing indexes, or inefficient query patterns. Slow queries consume more RUs, increase latency, and can cause application timeouts.

Symptoms

High query latency:

```sql -- Query takes seconds to complete SELECT * FROM c WHERE c.email = 'user@example.com'

-- Request charge shows high RU consumption -- x-ms-request-charge: 500.0 RUs (expensive!) ```

Cross-partition scan:

bash

# In query metrics:
"Cross Partition Query" : true
"Index Utilization" : 0%  // Full scan
"Documents Examined" : 100000  // Many documents scanned
"Documents Returned" : 1  // Few returned

Query timeout:

csharp

// Application timeout
Microsoft.Azure.Documents.DocumentClientException: Request timeout
RequestCharge: 50000 RUs

Common Causes

1.Missing index - Property in WHERE clause not indexed
2.Cross-partition query - Not using partition key
3.Inefficient query - Using functions on indexed properties
4.Large result set - Returning too many documents
5.SELECT * - Retrieving all properties including large ones
6.Missing TOP/LIMIT - No pagination
7. ORDER BY without index - Sorting without composite index

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

Step 1: Get Query Metrics

```csharp // Enable query metrics var queryOptions = new QueryRequestOptions { PopulateQueryMetrics = true, MaxItemCount = 100 };

var query = container.GetItemQueryIterator<dynamic>( "SELECT * FROM c WHERE c.status = 'active'", null, queryOptions);

var response = await query.ReadNextAsync(); var metrics = response.Diagnostics;

Console.WriteLine($"Request Charge: {response.RequestCharge} RUs"); Console.WriteLine($"Metrics: {metrics}"); ```

Step 2: Check Index Utilization

```sql -- Use Query Explorer in Azure Portal to see: -- - Index Utilization Score -- - Documents Examined vs Returned -- - Execution Time

-- Bad query (low index utilization): SELECT * FROM c WHERE UPPER(c.email) = 'USER@EXAMPLE.COM' -- Index can't be used due to UPPER() function

-- Good query (high index utilization): SELECT * FROM c WHERE c.email = 'user@example.com' -- Index can be used directly ```

Step 3: Add Indexing Policy

json

{
  "indexingPolicy": {
    "automatic": true,
    "indexingMode": "consistent",
    "includedPaths": [
      {
        "path": "/email/?",
        "indexes": [
          { "kind": "Hash", "dataType": "String", "precision": -1 }
        ]
      },
      {
        "path": "/status/?",
        "indexes": [
          { "kind": "Hash", "dataType": "String", "precision": -1 }
        ]
      },
      {
        "path": "/createdAt/?",
        "indexes": [
          { "kind": "Range", "dataType": "String", "precision": -1 }
        ]
      }
    ],
    "excludedPaths": [
      { "path": "/largeField/*" },
      { "path": "/nestedObject/*" }
    ]
  }
}

Apply via CLI:

bash

az cosmosdb sql container update \
  --account-name my-cosmos \
  --resource-group my-rg \
  --database-name my-db \
  --name my-container \
  --idx @indexing-policy.json

Step 4: Use Partition Key in Query

```csharp // Bad: Cross-partition query var query = container.GetItemQueryIterator<dynamic>( "SELECT * FROM c WHERE c.userId = 'user123'");

// Good: Single-partition query var query = container.GetItemQueryIterator<dynamic>( new QueryDefinition("SELECT * FROM c WHERE c.userId = @userId AND c.id = @id"), null, new QueryRequestOptions { PartitionKey = new PartitionKey("user123"), IndexingDirective = IndexingDirective.Include }); ```

Step 5: Optimize SELECT Statement

```sql -- Bad: SELECT * returns all properties SELECT * FROM c WHERE c.status = 'active'

-- Good: Select only needed properties SELECT c.id, c.name, c.email FROM c WHERE c.status = 'active'

-- Reduces payload size and RU consumption ```

Step 6: Add Pagination

```csharp // Use continuation tokens for pagination var queryOptions = new QueryRequestOptions { MaxItemCount = 100 // Return 100 items per request };

var query = container.GetItemQueryIterator<dynamic>( "SELECT * FROM c WHERE c.status = 'active'", continuationToken, queryOptions);

while (query.HasMoreResults) { var page = await query.ReadNextAsync(); var token = page.ContinuationToken;

// Process page, save token for next request } ```

Step 7: Create Composite Index for ORDER BY

json

{
  "indexingPolicy": {
    "compositeIndexes": [
      [
        { "path": "/status", "order": "ascending" },
        { "path": "/createdAt", "order": "descending" }
      ]
    ]
  }
}

sql

-- ORDER BY requires composite index for multiple properties
SELECT c.id, c.name FROM c 
WHERE c.status = 'active'
ORDER BY c.status ASC, c.createdAt DESC

Step 8: Avoid Functions on Indexed Properties

```sql -- Bad: Function prevents index use SELECT * FROM c WHERE LEFT(c.name, 3) = 'Joh' SELECT * FROM c WHERE c.createdAt >= DATEADD('day', -7, GetCurrentDateTime())

-- Good: Use parameterized values SELECT * FROM c WHERE STARTSWITH(c.name, 'Joh') -- Use STARTSWITH SELECT * FROM c WHERE c.createdAt >= @sevenDaysAgo -- Calculate in app

-- Even better: Use BETWEEN for ranges SELECT * FROM c WHERE c.createdAt BETWEEN @start AND @end ```

Step 9: Use TOP for Known Limits

```sql -- Use TOP when you know result limit SELECT TOP 10 c.id, c.name FROM c WHERE c.status = 'active' ORDER BY c.createdAt DESC

-- Or use OFFSET LIMIT (more expensive due to state tracking) SELECT c.id, c.name FROM c WHERE c.status = 'active' ORDER BY c.createdAt DESC OFFSET 0 LIMIT 10 ```

Step 10: Monitor Query Performance

```bash # Enable diagnostic settings for query logs az monitor diagnostic-settings create \ --name cosmos-query-logs \ --resource /subscriptions/SUB/resourceGroups/my-rg/providers/Microsoft.DocumentDB/databaseAccounts/my-cosmos \ --workspace /subscriptions/SUB/resourcegroups/my-rg/providers/microsoft.operationalinsights/workspaces/my-workspace \ --logs '[{"category":"DataPlaneRequests","enabled":true}]'

Cosmos DB Query Optimization Checklist

Check	Before	After
Index utilization	0%	>80%
Documents examined	100000	<1000
Request charge	500 RUs	<50 RUs
Partition scan	Cross-partition	Single-partition

Verification

```sql -- Test query performance after optimization SELECT c.id, c.name FROM c WHERE c.status = 'active' AND c.partitionKey = 'value' ORDER BY c.createdAt DESC

-- Check metrics: -- - Request charge should be low (<10 RUs for single item) -- - Index utilization should be high (>90%) -- - Query latency should be <10ms ```

[Fix Azure Cosmos DB Throttling](/articles/fix-azure-cosmos-db-throttling)
[Fix Azure Cosmos DB Partition Hotspot](/articles/fix-azure-cosmos-db-partition-hotspot)
[Fix Azure Cosmos DB Change Feed Lag](/articles/fix-azure-cosmos-db-change-feed-lag)

[Technical troubleshooting: Fix Azure Aks Pod Crashloopbackoff Issue in Azure](azure-aks-pod-crashloopbackoff)
[Technical troubleshooting: Fix Azure Api Management Policy Expression Runtime](azure-api-management-policy-expression-runtime-error)
[Technical troubleshooting: Fix Azure App Configuration Feature Flag Not Refre](azure-app-configuration-feature-flag-not-refreshing)
[Technical troubleshooting: Fix Azure App Service 503 Always On Disabled Issue](azure-app-service-503-always-on-disabled)
[Technical troubleshooting: Fix Azure Application Gateway Err SSL Unrecognized](azure-application-gateway-err-ssl-unrecognized-name-alert)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

Azure Cosmos DB SQL Query Slow Azure Cosmos DB SQL Query Slow Azure Azure Cosmos DB SQL Query Slow troubleshooting Azure Cosmos DB SQL Query Slow fix Optimize slow Cosmos DB SQL queries by adding indexes, using partition keys, and reducing cross-partition scans Azure Optimize slow Cosmos DB SQL queries by adding indexes, using partition keys, and reducing cross-partition scans

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Azure Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this azure-errors troubleshooting guide applies to my situation?

This guide is designed for azure-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these azure-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of azure-errors issue?

Most azure-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this azure-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for azure-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Apr 2, 2026

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Fix Azure Cosmos DB SQL Query Slow

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Get Query Metrics

Step 2: Check Index Utilization

Step 3: Add Indexing Policy

Step 4: Use Partition Key in Query

Step 5: Optimize SELECT Statement

Step 7: Create Composite Index for ORDER BY

Step 8: Avoid Functions on Indexed Properties

Step 9: Use TOP for Known Limits

Step 10: Monitor Query Performance

Cosmos DB Query Optimization Checklist

Verification

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix Azure Cosmos DB SQL Query Slow

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Get Query Metrics

Step 2: Check Index Utilization

Step 3: Add Indexing Policy

Step 4: Use Partition Key in Query

Step 5: Optimize SELECT Statement

Step 6: Add Pagination

Step 7: Create Composite Index for ORDER BY

Step 8: Avoid Functions on Indexed Properties

Step 9: Use TOP for Known Limits

Step 10: Monitor Query Performance

Cosmos DB Query Optimization Checklist

Verification

Related Issues

Related Articles

People also search for

Share this guide

More Azure Troubleshooting Guides

Browse Guides from Other Categories

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading