You can now use the Azure Cosmos DB index metrics to optimize query performance with Index Advisor! Indexing metrics show two important pieces of information:<\/p>\n
Enabling indexing metrics:<\/strong><\/p>\n You can enable indexing metrics for a query by setting the\u00a0PopulateIndexMetrics<\/strong>\u00a0property to\u00a0true. When not specified,\u00a0PopulateIndexMetrics<\/strong> defaults to false. The indexing metrics are supported in the .NET SDK (version 3.21.0 or later) and Java SDK (version 4.19.0 or later), and the Python SDK (version 4.6.0 or later)<\/p>\n Index impact score:<\/strong><\/p>\n The index impact score is the likelihood that an indexed path, based on the query shape, has a significant impact on query performance. In other words, the index impact score is the probability that, without that specific indexed path, the query RU charge would have been substantially higher.<\/p>\n There are two possible index impact scores:\u00a0high<\/strong>\u00a0and\u00a0low<\/strong>. If you have multiple potential indexed paths, we recommend focusing on indexed paths with a\u00a0high<\/strong>\u00a0impact score.<\/p>\n The only criteria used in the index impact score is the query shape. For example, in the below query, the indexed path\u00a0\/age\/?\u00a0would be assigned a\u00a0high<\/strong>\u00a0index impact score:<\/p>\n The actual impact depending on the nature of the data, so not all indexes with high <\/strong>index impact scores will improve query performance. In the above example, the impact of an \/age\/? indexed path would be larger if fewer items matched the c.age > 30 filter expression.<\/p>\n Examples:<\/strong><\/p>\n To show example indexing metrics, we\u2019ve created an example container with about 9 million items. Here\u2019s an example item:<\/p>\n The container has the default indexing policy of all paths indexed with a range index. We\u2019ll show the index metrics, both the utilized indexes and potential single indexes, for several different queries.<\/p>\n Query 1:<\/strong><\/p>\n Index metrics:<\/strong><\/p>\n Explanation<\/strong>:<\/p>\n In this simple example, the indexing metrics show that the query used the existing \/name\/? indexed path. Given the high<\/strong> index impact score, there\u2019s a high likelihood that this indexed path was critical for query performance. There aren\u2019t any recommendations for further improving query performance.<\/p>\n Query 2: <\/strong><\/p>\n Original RU charge:<\/strong> 222.35 RUs<\/p>\n Index metrics:<\/strong><\/p>\n Explanation<\/strong>:<\/p>\n This query averages the timestamp employees with the job \u201cDeveloper\u201d. While the query used the existing indexed paths \/job\/? and \/_ts\/?, the indexing metrics show a recommended composite index with a high likelihood of impact. In this case, after adding a composite index on (\/job ASC, \/_ts ASC), the RU charge of the query is reduced from 222.35 RUs to 137.56 RUs.<\/p>\n Query 3: <\/strong><\/p>\n Original RU charge:<\/strong> 31.3 RUs<\/p>\n Index metrics:<\/strong><\/p>\n Explanation<\/strong>:<\/p>\n This filters to all developers and sorts by timestamp . While the query used the existing indexed paths \/job\/? and \/_ts\/?, the indexing metrics show a recommended composite index with a high likelihood of impact. In this case, after adding a composite index on (\/job ASC, \/_ts ASC), the RU charge of the query is reduced from RUs 31.3 to 23.7 RUs.<\/p>\n Do you have any feedback about the indexing metrics? Share feedback directly with the Azure Cosmos DB engineering team: cosmosdbindexing [@] microsoft.com<\/a><\/p>\n Azure Cosmos DB is a fully managed and serverless distributed database for modern app development, with SLA-backed speed and availability, automatic and instant scalability, and support for open-source PostgreSQL, MongoDB and Apache Cassandra.\u00a0<\/span>Try Azure Cosmos DB for free here<\/span><\/a>. To stay in the loop on Azure Cosmos DB updates, follow us on\u00a0<\/span>X<\/span><\/a>,\u00a0<\/span>YouTube<\/span><\/a>, and\u00a0<\/span>LinkedIn<\/span><\/a>. To quickly build your first database, watch our\u00a0<\/span>Get Started videos<\/span><\/a>\u00a0on YouTube and explore ways to\u00a0<\/span>dev\/test free.<\/span><\/a><\/p>\n","protected":false},"excerpt":{"rendered":" You can now use the Azure Cosmos DB index metrics to optimize query performance with Index Advisor! Indexing metrics show two important pieces of information: Utilized indexes – Which paths in the indexing policy were used in your query and utilized by the Azure Cosmos DB query engine Recommended indexes – Which indexes can improve […]<\/p>\n","protected":false},"author":118435,"featured_media":8109,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14],"tags":[],"class_list":["post-8000","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api"],"acf":[],"blog_post_summary":" You can now use the Azure Cosmos DB index metrics to optimize query performance with Index Advisor! Indexing metrics show two important pieces of information: Utilized indexes – Which paths in the indexing policy were used in your query and utilized by the Azure Cosmos DB query engine Recommended indexes – Which indexes can improve […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/8000","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=8000"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/8000\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/8109"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=8000"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=8000"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=8000"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SELECT * \r\nFROM c \r\nWHERE c.age > 35<\/code><\/pre>\n{\r\n\t\"id\": \"ax92n6231\",\r\n\t\"name\": \"James Codella\",\r\n\t\"address\": \"1 Cosmos DB Avenue\",\r\n\t\"company\": \"Microsoft\",\r\n\t\"job\": \"Product Manager\",\r\n\t\"_ts\": 3141592653589\r\n}<\/code><\/pre>\nSELECT * \r\nFROM c \r\nWHERE c.name = \u201cGeorge Washington\u201d<\/code><\/pre>\nOriginal RU charge:<\/strong> 2.79 RUs<\/pre>\n
Index Utilization Information\r\n Utilized Single Indexes\r\n \u00a0\u00a0 Index Spec: \/name\/?\r\n \u00a0\u00a0 Index Impact Score: High\r\n \u00a0\u00a0 ---\r\n Potential Single Indexes\r\n Utilized Composite Indexes\r\n Potential Composite Indexes<\/pre>\n
SELECT AVG(c._ts)\r\nFROM c\r\nWHERE c.job = \u201cDeveloper\u201d<\/code><\/pre>\nIndex Utilization Information\r\n Utilized Single Indexes\r\n \u00a0\u00a0 Index Spec: \/job\/?\r\n \u00a0\u00a0 Index Impact Score: High\r\n \u00a0\u00a0 ---\r\n \u00a0\u00a0 Index Spec: \/_ts\/?\r\n \u00a0\u00a0 Index Impact Score: High\r\n \u00a0\u00a0 ---\r\n Potential Single Indexes\r\n Utilized Composite Indexes\r\n Potential Composite Indexes\r\n \u00a0\u00a0 Index Spec: \/job ASC, \/_ts ASC\r\n \u00a0\u00a0 Index Impact Score: High<\/pre>\n
SELECT TOP 10 *\r\nFROM c WHERE c.job = \u201cDeveloper\u201d\r\nAND c._ts > 1589840249<\/code><\/pre>\nIndex Utilization Information\r\n Utilized Single Indexes\r\n \u00a0\u00a0 Index Spec: \/job\/?\r\n \u00a0\u00a0 Index Impact Score: High\r\n \u00a0\u00a0 ---\r\n \u00a0\u00a0 Index Spec: \/_ts\/?\r\n \u00a0\u00a0 Index Impact Score: High\r\n \u00a0\u00a0 ---\r\n Potential Single Indexes\r\n Utilized Composite Indexes\r\n Potential Composite Indexes\r\n \u00a0\u00a0 Index Spec: \/job ASC, \/_ts ASC\r\n \u00a0\u00a0 Index Impact Score: High<\/pre>\n
Next steps:<\/strong><\/h3>\n
\n
About Azure Cosmos DB<\/h2>\n