Erik Darling has a new mini-series. First up is degrees of parallelism and bitmaps:
But wait! This query runs at DOP 4. You can tell by looking at the number of executions.
Who runs queries at DOP 4?
Fools.
This DOP goes up to 11.
Next, Erik versus equality predicates:
But not every query has an available equality join predicate. For various reasons, like poor normalization choices, or just the requirements of the query, you may run into things like I’m about to show you.
If there’s a headline point to this post, it’s that joins that don’t have an equality predicate in them only have one join choice: Nested Loops. You cannot have a Hash or Merge join with this type of query. Even with the best possible indexes, some of these query patterns just never seem to pan out performance-wise.
Read on for the implications of this, as well as techniques to improve performance.
and this is all not needed in CosmosDB because that engine dont need no performance tuning, its always fast, no wait…