You can now use the Azure Cosmos DB indexing metrics, announced as general available at Microsoft Ignite, to optimize query performance! The 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>\u00a0defaults to\u00a0false. The indexing metrics are supported in the .NET SDK (version 3.21.0 or later) and Java SDK (version 4.19.0 or later).<\/p>\n You should check the indexing metrics after you have already identified an expensive query, through methods such as using diagnostic logs<\/a>. We don\u2019t recommend proactively logging the indexing metrics because they won\u2019t change between different executions of the same query with the same indexing policy.<\/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 <\/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 Next steps:<\/strong><\/p>\n Do you have any feedback about the indexing metrics? Share feedback directly with the Azure Cosmos DB engineering team: cosmosdbindexing [@] microsoft.com<\/p>\n","protected":false},"excerpt":{"rendered":" You can now use the Azure Cosmos DB indexing metrics, now generally available, to optimize query performance! The indexing metrics show indexes that the query engine used and index recommendations for improving query performance.<\/p>\n","protected":false},"author":12128,"featured_media":61,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,818],"tags":[],"class_list":["post-3546","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-query"],"acf":[],"blog_post_summary":" You can now use the Azure Cosmos DB indexing metrics, now generally available, to optimize query performance! The indexing metrics show indexes that the query engine used and index recommendations for improving query performance.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/3546","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\/12128"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=3546"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/3546\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/61"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=3546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=3546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=3546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SELECT *\r\nFROM c\r\nWHERE c.age > 30<\/pre>\n
{<\/span>\r\n\u00a0\u00a0\u00a0\u00a0\"id\":\u00a0\"qd471022kj\",<\/span>\r\n \u00a0\u00a0\u00a0\"name\":\u00a0\"Mark Brown\",<\/span>\r\n \u00a0\u00a0\u00a0\"address\": \"123 Cosmos DB Way\"<\/span>\r\n \u00a0\u00a0\u00a0\"company\":\u00a0\"Microsoft\",<\/span>\r\n \u00a0\u00a0\u00a0\"job\": \"Software Developer\",<\/span>\r\n \u00a0 \"_ts\": 1589840249<\/span>\r\n}<\/span><\/pre>\nSELECT *\r\nFROM c\r\nWHERE c.name = \u201cTim Sander\u201d\r\n\r\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<\/pre>\n
Index 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\r\nWHERE c.job = \u201cDeveloper\u201d\r\nAND c._ts > 1589840249\r\n\r\n<\/pre>\n
Index 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
\n