What it does

Dataverse runs on Azure SQL for standard tables, but you don't get to touch the database. Index management is Microsoft's, and your influence over query plans comes from schema design, alternate keys, and not writing the patterns the platform actively throttles.

Key facts

• Dataverse monitors data retrieval and automatically applies optimisations to poorly performing queries on standard tables. It's on by default, needs no configuration, and is why some performance problems can't be reproduced later.
• Alternate keys are the one place you deliberately create an index. Saving the key kicks off a system job to build it, and EntityKeyIndexStatus tells you where that job has got to. Key size is bound by SQL index limits — 900 bytes and 16 columns.
• Leading wildcards can't use an index and force a table scan. Dataverse returns LeadingWildcardCauseTimeout on timeout and DataEngineLeadingWildcardQueryThrottling when it throttles the query outright.
• % isn't the only offender. Strings starting _234% or [^a]234% behave the same way, and because of collation rules so do strings starting with a hyphen or apostrophe unless there's a non-wildcard character before the %. So -234% is fine and -%234 is not.
• Filtering on formula or calculated columns forces per-row computation before the filter can apply. Dedicated errors again: ComputedColumnCauseTimeout and DataEngineComputedColumnQueryThrottling.
• Conditions work on string columns with a MaxLength under 850 characters. Memo columns, and strings above 850, are treated as large text columns, are too large to index effectively, and belong in Dataverse search instead.
• Ordering by a choice column sorts on the localised label, which needs a join and more compute. Ordering by a column on a related table costs too.
• Selecting many columns hurts, and selecting many logical columns hurts more, because their values live in other database tables and have to be combined.
• When a query times out on one of these patterns, the platform now returns PerformanceValidationIssuesCauseTimeout with the offending anti-pattern names in the message — PerformanceLeadingWildCard, LargeAmountOfAttributes, OrderOnEnumAttribute and so on.

When to use / skip

There's nothing to switch on here, so the judgment call is where you spend effort. Start with the error messages — Microsoft has done you the favour of naming the anti-pattern in the exception, so a slow-query investigation should begin in the logs, not in a profiler you don't have. Then fix schema, not queries: a leading wildcard search usually means the data model is forcing users to search the middle of a string, and the durable fix is a proper reference column plus Dataverse search, not a cleverer filter. Query hints are a last resort and only when support says so.

Configuration decisions

• Which columns get alternate keys, weighed against the cost of a uniqueness constraint on data you don't fully control.
• Whether a searchable text column stays under 850 characters so it can be filtered, or goes large and moves into Dataverse search.
• Whether a value is a formula column (never filter on it) or a stored column maintained by logic (filterable, but you own the consistency).
• Whether a lookup or a choice is the right modelling call, given the sort cost of choice labels.
• For bulk work: standard versus elastic tables, and whether to bypass custom business logic during the load.

Gotchas

• Alternate key creation runs as a background job on large tables. Import a solution that adds one and the key can be there while the index is still building.
• The automatic query optimisation makes intermittent performance issues genuinely unreproducible. Don't let a client conclude the problem was imaginary.
• Throttling of leading-wildcard and calculated-column queries is defensive — it protects the environment, so it gets more aggressive as the environment gets busier. The query that worked in UAT can be refused in production.
• latematerialize can make simple queries slower. It only helps with many joins and many lookup or computed columns.
• Choice labels are localised, so query cost varies with how many languages are provisioned.

Consultant notes

• Ask for the exception text, not the description. PerformanceLargeColumnSearch,LargeAmountOfAttributes tells you more in one line than an hour of screen-sharing.
• Set the expectation early that nobody gets to add an index. Clients with a SQL background will ask, and the sooner they know the answer the sooner they design around it.
• Push back on "just make search work like Google" requirements. That's Dataverse search, and it needs to be scoped and budgeted, not bolted on with a contains filter.
• For migrations and bulk loads, the levers are table type, bulk APIs, parallelism guided by the recommended degree of parallelism header, and turning off synchronous plug-ins. That's a different conversation from day-to-day query tuning and should be planned separately.

---

Worth another look if Microsoft exposes any customer-controlled indexing, or if the anti-pattern error list grows again.