{"id":11091,"date":"2025-11-05T04:00:10","date_gmt":"2025-11-05T12:00:10","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=11091"},"modified":"2025-11-05T09:57:02","modified_gmt":"2025-11-05T17:57:02","slug":"query-advisor-for-azure-cosmos-db-actionable-insights-to-improve-performance-and-cost","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/query-advisor-for-azure-cosmos-db-actionable-insights-to-improve-performance-and-cost\/","title":{"rendered":"Query Advisor for Azure Cosmos DB: Actionable insights to improve performance and cost"},"content":{"rendered":"<p>Azure Cosmos DB for NoSQL now features <em>Query Advisor<\/em>, designed to help you write faster and more efficient queries. Whether you\u2019re optimizing for performance, cost, or scalability, Query Advisor provides actionable recommendations to help you get the most out of your data.<\/p>\n<h1>Why Query Optimization Matters<\/h1>\n<p>Azure Cosmos DB\u2019s SQL API is flexible and expressive, allowing developers to query JSON data with familiar SQL-like syntax. But as applications grow in complexity, small differences in query structure can have a big impact on performance and Request Units (RUs).<\/p>\n<p>For example, two queries that return the same result may differ dramatically in efficiency based on how predicates are written, whether filters use array operators or subqueries, and how indexes are leveraged.<\/p>\n<p><strong>Query Advisor<\/strong> analyzes your queries and offers targeted recommendations to help you:<\/p>\n<ul>\n<li><strong>Reduce RU costs<\/strong> by identifying inefficient expressions or unnecessary filters.<\/li>\n<li><strong>Improve query performance<\/strong> through more optimal query structures.<\/li>\n<li><strong>Understand the \u201cwhy\u201d behind each suggestion<\/strong>, with explanations written in clear, developer-friendly language.<\/li>\n<\/ul>\n<p>Query Advisor empowers developers to <strong>experiment safely.<\/strong> it doesn\u2019t automatically rewrite or execute your query, but instead provides suggestions you can review, test, and adopt based on your scenario.<\/p>\n<h1>How It Works<\/h1>\n<p>When you submit a query for analysis, the Query Advisor runs a static analysis pass over your query plan, evaluating patterns that may cause high RU consumption, excessive scans, or unnecessary processing. It then returns a set of recommendations that include:<\/p>\n<ul>\n<li>A description of the potential issue.<\/li>\n<li>A rewritten version of the query that may perform better.<\/li>\n<li>A short explanation of <em>why<\/em> the change could help.<\/li>\n<\/ul>\n<h1>Example: Optimizing Array Queries<\/h1>\n<p>Let\u2019s look at a simple but common case: searching inside arrays.<\/p>\n<p>Suppose a user writes the following query:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">SELECT VALUE ARRAY_CONTAINS(c.auditEvents, { \"type\": \"invoice\", \"status\": \"Success\" }, true)\r\nFROM c<\/code><\/pre>\n<p>This query checks whether the <em>auditEvents<\/em> array contains an object with both type and status properties. It works correctly, but it\u2019s not the most efficient way to express this condition. Query Advisor analyzes the query and suggests a substitute for the operation:<\/p>\n<p><em><strong>Suggestion:<\/strong> \u201cInstead of ARRAY_CONTAINS, consider using VALUE EXISTS with a subquery. This approach can improve performance by reducing full array scans.\u201d<\/em><\/p>\n<p>The query can then be updated to:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">SELECT VALUE EXISTS (\r\nSELECT VALUE t\r\nFROM t IN c.auditEvents\r\nWHERE t.type = \"invoice\" AND t.status = \"Success\"\r\n)\r\nFROM c<\/code><\/pre>\n<p>This change can significantly reduce RU consumption in large collections, especially when auditEvents arrays vary in length or contain complex objects.<\/p>\n<h1>Using Query Advisor<\/h1>\n<p>You can enable query advisor capabilities by setting the PopulateQueryAdvice property in QueryRequestOptions to true. When not specified, PopulateQueryAdvice defaults to false. To access the advice, via the string property FeedOptions.QueryAdvice. Please note that the query advice is only returned on the first round trip and is unavailable on subsequent continuation calls.<\/p>\n<h2>Usage example<\/h2>\n<pre class=\"prettyprint language-cs language-csharp\"><code class=\"language-cs language-csharp\">string query = \"SELECT VALUE r.id FROM root r WHERE CONTAINS(r.name, 'Abc') \";\r\n\r\nQueryRequestOptions requestOptions = new QueryRequestOptions() { PopulateQueryAdvice = true }\r\n\r\nusing FeedIterator&lt;CosmosElement&gt; itemQuery = testContainer.GetItemQueryIterator&lt;CosmosElement&gt;(\r\nquery, requestOptions: requestOptions);\r\n\r\nstring queryAdvice = null;\r\n\r\nwhile (itemQuery.HasMoreResults)\r\n{\r\n   if (queryAdvice != null)\r\n{\r\n   break;\r\n}\r\nFeedResponse&lt;Item&gt; page = itemQuery.ReadNextAsync().Result;\r\nqueryAdvice = page.QueryAdvice;\r\n}\r\nConsole.WriteLine(queryAdvice);<\/code><\/pre>\n<h2>Example output<\/h2>\n<p>In this example query, we observe that there is one single advice, called QA1002:<\/p>\n<p><em>QA1002: Instead of CONTAINS, consider using STARTSWITH or computed properties, which may improve performance. For more information, please visit <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1002\">https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1002<\/a><\/em><\/p>\n<p>The query advice contains three important information:<\/p>\n<ul>\n<li>The Query Advice id: \u201cQA1002\u201d<\/li>\n<li>The advice description: \u201cInstead of..\u201d<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1002\" target=\"_blank\" rel=\"noopener\">The link to the documentation<\/a><\/li>\n<\/ul>\n<p>We encourage you to visit the provided link to learn more about the query advice where you can see further examples, detailed explanations, and suggestions to improve your query.<\/p>\n<h2>Additional Examples<\/h2>\n<h3>Example Query<\/h3>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">SELECT GetCurrentTicks()\r\nFROM root r\r\nWHERE GetCurrentTimestamp() &gt; 10<\/code><\/pre>\n<h3>Query Advice<\/h3>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">QA1009: Instead of using GetCurrentTimestamp, consider using GetCurrentTimestampStatic, which may improve performance. For more information, please visit https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1009\r\n\r\nQA1008: Instead of using GetCurrentTicks, consider using GetCurrentTicksStatic, which may improve performance. For more information, please visit <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1008\">https:\/\/learn.microsoft.com\/en-us\/azure\/cosmos-db\/nosql\/query\/queryadvisor\/QA1008<\/a><\/code><\/pre>\n<p>In this example, there are 2 pieces of advice returned by the Query Advisor, QA1008 and QA1009. Each piece of advice is separated into a new line in the QueryAdvice string.<\/p>\n<h2><strong>Conclusion<\/strong><\/h2>\n<p>Query Advisor is a powerful addition to Azure Cosmos DB for NoSQL, giving developers actionable insights to optimize queries for performance, cost, and scalability. By surfacing clear recommendations and explanations, it helps you make informed decisions without guesswork\u2014so your applications stay fast and efficient as they grow. Ready to start optimizing your queries? Learn more about Query Advisor and explore best practices in our <a href=\"https:\/\/learn.microsoft.com\/azure\/cosmos-db\/nosql\/query\/query-advisor\">documentation located here<\/a>.<\/p>\n<h2><strong>About Azure Cosmos DB<\/strong><\/h2>\n<p>Azure Cosmos DB is a fully managed and serverless NoSQL and vector database for modern app development, including AI applications. With its SLA-backed speed and availability as well as instant dynamic scalability, it is ideal for real-time NoSQL and MongoDB applications that require high performance and distributed computing over massive volumes of NoSQL and vector data.<\/p>\n<p><a href=\"https:\/\/cosmos.azure.com\/try\/\">Try Azure Cosmos DB for free here.<\/a> To stay in the loop on Azure Cosmos DB updates, follow us on <a href=\"https:\/\/twitter.com\/AzureCosmosDB\">X<\/a>, <a href=\"https:\/\/aka.ms\/AzureCosmosDBYouTube\">YouTube<\/a>, and <a href=\"https:\/\/www.linkedin.com\/company\/azure-cosmos-db\/\">LinkedIn<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Azure Cosmos DB for NoSQL now features Query Advisor, designed to help you write faster and more efficient queries. Whether you\u2019re optimizing for performance, cost, or scalability, Query Advisor provides actionable recommendations to help you get the most out of your data. Why Query Optimization Matters Azure Cosmos DB\u2019s SQL API is flexible and expressive, [&hellip;]<\/p>\n","protected":false},"author":118435,"featured_media":11094,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,818],"tags":[499,1872,1797,1765],"class_list":["post-11091","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-query","tag-azure-cosmos-db","tag-nosql","tag-performance","tag-query"],"acf":[],"blog_post_summary":"<p>Azure Cosmos DB for NoSQL now features Query Advisor, designed to help you write faster and more efficient queries. Whether you\u2019re optimizing for performance, cost, or scalability, Query Advisor provides actionable recommendations to help you get the most out of your data. Why Query Optimization Matters Azure Cosmos DB\u2019s SQL API is flexible and expressive, [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/11091","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/118435"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=11091"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/11091\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/11094"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=11091"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=11091"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=11091"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}