Press "Enter" to skip to content

Category: Performance Tuning

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.”

Leave a Comment

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.

Leave a Comment

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

Function Volatility and PostgreSQL Partition Performance

Published 2025-11-05 by Kevin Feasel

Deepak Mahto covers how function volatility can affect how queries on partitioned data perform:

In one of our earlier blogs, we explored how improper volatility settings in PL/pgSQL functions — namely using IMMUTABLE, STABLE, or VOLATILE — can lead to unexpected behavior and performance issues during migrations.

Today, let’s revisit that topic from a slightly different lens. This time, we’re not talking about your user-defined functions, but the ones PostgreSQL itself provides — and how their volatility can quietly shape your query performance, especially when you’re using partitioned tables.

Click through for one example using date-based partitioning and date functions.

Comments closed

When repartition() Beats coalesce() in Spark

Published 2025-10-31 by Kevin Feasel

Janani Annur Thiruvengadam stands some common advice on its head:

If you’ve worked with Apache Spark, you’ve probably heard the conventional wisdom: “Use coalesce() instead of repartition() when reducing partitions — it’s faster because it avoids a shuffle.” This advice appears in documentation, blog posts, and is repeated across Stack Overflow threads. But what if I told you this isn’t always true?

In a recent production workload, I discovered that using repartition() instead of coalesce() resulted in a 33% performance improvement (16 minutes vs. 23 minutes) when writing data to fewer partitions. This counterintuitive result reveals an important lesson about Spark’s Catalyst optimizer that every Spark developer should understand.

Read on for the details on that scenario.

Comments closed