Guidance to create high-performing queries in Azure Boards
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019 | TFS 2018
While you can easily create work item queries, to create high-performing queries requires a deeper understanding. By improving your query performance, you improve your individual productivity, dashboard performance, and resource rate limits.
Reference to service or resource rate limits only applies to queries run against Azure DevOps Services. To learn more, see Service limits and rate limits.
This article provides general guidelines on how to write a high-performing query. These guidelines apply to the following queries you create:
Create focused, selective queries
The first guideline for a high-performing query is to define a highly selective query. To do this, apply all filters that are necessary for your query. The more selective the query is, the smaller the result set is. The smaller the result set is, the more targeted and selective your query is.
Use tags to categorize work items
Use work item tags to categorize your work items instead of a custom field. Queries that filter on tags usually perform faster over those queries that filter on string matches.
Unlike custom field matches or partial matches, a query with a
Tags Contains operation doesn't require a complete scan of all work item tables.
Contains words for string matches
To filter on a string match, use the
Contains Words instead of the
Contains operator. The
Contains Words operator runs a full-text search on the specified field, which tends to complete more quickly.
Contains operator runs a table scan, which is a slower operation than that used with the
Contains Words operator. It also consumes more CPU cycles. These CPU cycles can cause you to encounter rate limitations. For more information, see Service limits and rate limits and Rate limits.
Specify small groups with the
In Group operator
In Group operator filters work items based on matches within a group of values. The group of values correspond to those contained within a team, security group, or work tracking category. For example, you can create a query to find all work items that are assigned to any member of a team. Or, find all work items that belong to the requirements category (
When you filter on a group that contains a large number of members, your result set tends to be larger and non-selective. Also, if a group corresponds to a large Azure Active Directory (Azure AD) group, the query generates a fairly large cost to resolve that group from Azure AD.
Avoid use of negated operators
Negated operators—such as
<>, Not In, Not Under, Not In Group—tend to generate non-selective queries and large result sets.
Only use negated operators when absolutely necessary. Always try to find alternatives first. For example, if Field1 has values A, B, C, D; specify the
Field1 In A, B, C clause, instead of the negated
Field1 <> D clause.
Avoid string comparisons
Queries that contain string comparisons generate table scans that are inherently inefficient. Instead, we recommend you use tags or a specific custom field as alternatives, particularly when a query performs poorly.
Try to limit the number of
Or operators defined in your query. Queries run better when fewer
Or operators are used. Too many
Or operators can make your query non-selective. If your query runs slowly, reorder the
Or operator clause towards the top of the query clauses.
Save your query
Save your query to improve query performance. Due to internal optimizations, saved queries tend to perform better over unsaved queries. Always save your query when you plan to reuse it. Even WIQL queries run through a REST API, save the WIQL through the web portal to make your REST API calls less prone to future performance regressions.
To programmatically interact with queries, see one of these REST API resources:
- Azure DevOps Services REST API Reference
- Work item query language
- Fetch work items with queries programmatically
Submit and view feedback for