Performance Tuning – Curated SQL

Performance of Regular vs Limited Relationships in DAX

Published 2026-01-02 by Kevin Feasel

Marco Russo and Alberto Ferrari do a performance comparison:

Relationships between different data islands are the most common case of limited relationships. In that scenario, performance depends on multiple factors, most of which are not under the control of a DAX developer. Indeed, when mixing data from different data islands, the DAX formula engine must act as a bridge between them, resulting in complex execution plans. Besides, when two tables reside in different data islands, only limited relationships can connect them. Therefore, a performance comparison would not make sense, as there are no alternative options to link the tables.

However, a model can have limited relationships in the very special case of two tables stored in the same data island and connected by a many-to-many cardinality relationship. By nature, many-to-many cardinality relationships are limited. While they seem like a convenient way to link two tables when the key used to link them together is not unique in both tables, many-to-many cardinality relationships are extremely expensive, and a wise data modeler should limit their use strictly to cases where they are absolutely necessary. In this article, we analyze the differences between regular and limited relationships, focusing solely on performance.

Read on to learn more.

Optimized Locking in SQL Server 2025

Published 2025-12-19 by Kevin Feasel

Tomaz Kastrun continues an advent of SQL Server 2025:

Optimized locking is a new feature in SQL Server 2025. It helps to reduce lock memory as very few locks are held even for large transactions. In addition, optimized locking avoids lock escalations and can avoid certain types of deadlocks. This allows more concurrent access to the table.

Read on to understand what its purpose is and see a demo of it in action.

Optional Parameter Plan Optimization in SQL Server 2025

Published 2025-12-17 by Kevin Feasel

Tomaz Kastrun continues looking at new functionality in SQL Server 2025:

Part of new features in IQP (Intelligent query processing) is also OPPO – Optional parameter plan optimization – which refers to a specific variation of the parameter-sensitive plan (PSP) or Parameter Sensitive Plan Optimization (PSPO) problem in which the sensitive, parameter value that exists during query execution, controls whether we need to perform a seek into or scan a table. It is part of mitigating the parameter sniffing problem.

Read on for more information, as well as a demonstration of how it works.

Comments closed

Asynchronous Disk I/O in Postgres 18

Published 2025-12-16 by Kevin Feasel

Josef Machytka gives us the skinny:

PostgreSQL 17 introduced streaming I/O – grouping multiple page reads into a single system call and using smarter posix_fadvise() hints. That alone gave up to ~30% faster sequential scans in some workloads, but it was still strictly synchronous: each backend process would issue a read and then sit there waiting for the kernel to return data before proceeding. Before PG17, PostgreSQL typically read one 8kB page at a time.

PostgreSQL 18 takes the next logical step: a full asynchronous I/O (AIO) subsystem that can keep multiple reads in flight while backends keep doing useful work. Reads become overlapped instead of only serialized. The AIO subsystem is deliberately targeted at operations that know their future block numbers ahead of time and can issue multiple reads in advance:

Read on to see some of the consequences of this change, as well as more detail on how it works.

Comments closed

Performance Tuning SQL Server in KubeVirt

Published 2025-12-12 by Kevin Feasel

Andrew Pruski speeds things up:

Following on from my last post about Getting Started With KubeVirt & SQL Server, in this post I want to see if I can improve the performance from the initial test I ran.

Andrew digs into the settings and gets to about 98% of StatefulSet performance, which is considerably better than the starting point. But it does take a good bit of configuration and effort to get there.

Comments closed

Optimized Locking Slowdowns in SQL Server 2025

Published 2025-12-05 by Kevin Feasel

Rebecca Lewis points out when optimized locking fails:

SQL Server 2025 introduces a feature called Optimized Locking, designed to reduce blocking across read–modify workloads. In many environments it helps, but certain mixed workloads experience longer lock waits, stalled S → U → X transitions, and even occasional threadpool saturation.

My inner 12-year-old has already made about a half-dozen plays on the phrase “When optimized locking SUX.”

Comments closed

Join Planning in PostgreSQL 19

Published 2025-12-04 by Kevin Feasel

Robins Tharakan notes an upcoming performance boost:

The hidden cost of knowing too much. That’s one way to describe what happens when your data is skewed, Postgres statistics targets are set high, and the planner tries to estimate a join.

For over 20 years, Postgres used a simple O(N^2) loop to compare (equi-join) Most Common Values (MCVs) during join estimation. It worked fine when statistics targets are small (default_statistics_target defaults to 100). But in the modern era – we often see Postgres best-practices recommend cranking that up. Customers are known to be using higher values (1000 and sometimes even higher) to handle complex data distributions + throw a 10 JOIN query to the mix – and this “dumb loop” can easily become a silent performance killer during planning.

That changes in Postgres 19.

Read on for an example of the problem and what is coming out to mitigate issues that currently exist.

Comments closed

Primary Keys and DAX Query Performance

Published 2025-11-14 by Kevin Feasel

Phil Seamark explains why including primary keys in summarize statements can be a bad thing:

When writing DAX queries, performance tuning often comes down to small design decisions that have big consequences. One such decision is whether to include Primary Key columns from Dimension tables in your SUMMARIZECOLUMNS statements. This is particularly important when those Dimension tables use DUAL or IMPORT storage modes.

This article explains why doing so can lead to inefficient query plans. It describes what happens under the hood. It also shows how to avoid this common pitfall.

Read on to learn more.

Comments closed

Optimized Locking and Change Event Streaming in SQL Server 2025

Published 2025-11-12 by Kevin Feasel

Deb Melkin is looking forward to a pair of features:

When I look at this release, I feel like I’ve been more tuned into what’s coming out than any other. I’m still not sure how that happened. But I think overall it’s a good thing because there really is a lot being packed into this release. If you’re just trying to figure out now, you’re already behind.

There are really 2 features that I’m really excited about:

Read on to learn more about both of them.

Comments closed

Index Skip Scans in PostgreSQL 18

Published 2025-11-11 by Kevin Feasel

Hans-Jürgen Schönig demonstrates a new capability in PostgreSQL:

PostgreSQL 18 brings a couple of performance related features to the table which will help applications to run more efficiently, providing a better and more enjoyable user experience. One of those performance features is called “skip scans”. Most of you might ask yourself at this point: Wow, sounds cool, but what is a skip scan? The purpose of this post is to shed some light and explain how this works, what it does and most importantly: How one can benefit from this feature in real life.

Click through for the demo.

Comments closed

Category: Performance Tuning