Press "Enter" to skip to content

Category: Performance Tuning

SSIS Slowdowns in Paging to Disk

Published 2025-07-23 by Kevin Feasel

Andy Brownsword notes a major performance risk in Integration Services:

One particular performance issue with SSIS data flows can fly under the radar – spilling to disk. This isn’t clearly visible through regular debugging or execution so can go unnoticed. And it hurts.

Paging to disk is bad for performance. Disks are much slower to access than memory, so we want to keep our data away when possible.

Andy calls out two reasons why we might find spilling to disk, as well as how to track if this is happening.

Comments closed

Fast-Path Search in OrioleDB

Published 2025-07-18 by Kevin Feasel

Alexander Korotkov describes a new feature coming to OrioleDB:

When you optimize the CPU time of a transactional database management system, it comes down to one question: how fast can you read a page without breaking consistency? In this post, we explore how OrioleDB avoids locks, trims memory copies, and — starting with beta12 — even bypasses both copying and tuple deforming altogether for fixed-length types during intra-page search. This means that not only are memory copies skipped, but the overhead of reconstructing tuples is also eliminated. The result: an even faster read path, with no manual tuning required.

Read on to see what’s new and how it works.

Comments closed

GUID Hunting for Power BI Performance Load Testing

Published 2025-07-17 by Kevin Feasel

Gilbert Quevauvilliers finds some UUIDs:

When completing the Power BI performance load testing, you will need to get details from your Power BI report and App Workspace, which will later be used in the PBIReport.JSON file.

In this blog post I will show you how to find those details, so that when it comes time to add it to the PBIReport.JSON file, it will be easy to plug the values in.

The reason for a separate blog post is because you will have to find the GUIDs that are used, which takes a bit of time and knowledge to find the correct GUID for the right value.

Click through for the most unsatisfying Easter egg hunt you could imagine. Gilbert then continues to pull out slider and filter data values.

Comments closed

Fixing Slow Row-Level Security Policies in PostgreSQL

Published 2025-07-15 by Kevin Feasel

Dian Fay troubleshoots some row-level security slowness:

At my day job, we use row-level security extensively. Several different roles interact with Postgres through the same GraphQL API; each role has its own grants and policies on tables; whether a role can see record X in table Y can depend on its access to record A in table B, so these policies aren’t merely a function of the contents of the candidate row itself. There’s more complexity than that, even, but no need to get into it.

Read on for a dive into row-level security and several tips to make the operation faster.

Comments closed

Efficiency of Sparse Hash Tables in PostgreSQL

Published 2025-07-15 by Kevin Feasel

Ashutosh Bapat runs some tests:

The hash_create() API in PostgreSQL takes initial size as an argument. It allocates memory for those many hash entries upfront. If more entries are added, it will expand that memory later. The point of argument was what should be the initial size of the hash table, introduced by that patch, containing the derived clauses. During the discussion, David hypothesised that the size of the hash table affects the efficiency of the hash table operations depending upon whether the hash table fits cache line. While I thought it’s reasonable to assume so, the practical impact wouldn’t be noticeable. I thought that beyond saving a few bytes choosing the right hash table size wasn’t going to have any noticeable effects. If an derived clause lookup or insert became a bit slower, nobody would even notice it. It was practically easy to address David’s concern by using the number of derived clauses at the time of creating the hash table to decide initial size of the hash table. The patch was committed.

Read on to see how things didn’t quite turn out this way, and what the results of testing look like.

Comments closed

Event Notification via LISTEN/NOTIFY in PostgreSQL Doesn’t Scale

Published 2025-07-11 by Kevin Feasel

Elliot Levin takes us through a performance issue:

We love Postgres and it lives at the heart of our service! But this extremely concurrent, write-heavy workload resulted in a stalled-out Postgres. This is the story of what happened, how we ended up discovering a bottleneck in the LISTEN/NOTIFY feature of Postgres (the event notifier that runs based on triggers when something changes in a row), and what we ended up doing about it.

Click through for details, as well as what the team there did to migrate away from this feature.

Comments closed

Capture Long-Running Queries via Extended Events

Published 2025-07-11 by Kevin Feasel

Tom Collins has another extended events session for us:

A SQL Server Extended Event to track SQL queries taking longer than 100 seconds to complete. Adjust accoring to your requriements.

There is also a query below to extract the column details from the xel file 

Click through for the code. This kind of extended events session is rather useful for performance tuning and finding issues before customers e-mail.

Comments closed

Selective Caching in SSIS

Published 2025-07-10 by Kevin Feasel

Andy Brownsword takes us through a pattern:

We’ve recently looked at how caching can improve performance and I wanted to show how we can eek even more performance out of caches by using a custom approach I’ll term Selective Caching.

I’ll note here that there’s a potential gotcha with this approach which we’ll get to before the end of the post!

Click through for a description of the pattern and when it starts to break down.

Comments closed

Random Page Cost and PostgreSQL Query Plans

Published 2025-07-09 by Kevin Feasel

Tomas Vondra takes us through a setting:

Last week I posted about how we often don’t pick the optimal plan. I got asked about difficulties when trying to reproduce my results, so I’ll address that first (I forgot to mention a couple details). I also got questions about how to best spot this issue, and ways to mitigate this. I’ll discuss that too, although I don’t have any great solutions, but I’ll briefly discuss a couple possible planner/executor improvements that might allow handling this better.

Tomas’s points around the random_page_cost setting sound a lot like the cost threshold for parallelism setting in SQL Server in inverse: a setting whose default makes sense in a world of spinning disks at 7200 RPM, but not really in a solid state world.

Comments closed

Using a File Cache in SSIS

Published 2025-07-07 by Kevin Feasel

Andy Brownsword makes those SSIS jobs run faster:

Last week we looked at using a cache to improve lookup performance. We saw how a cache improves performance by being able to reuse reference data repeatedly. That used a regular cache but it’s not the only option available to us.

In this post we’re going to look at the File Cache option which can achieve the same results – plus a little more.

My experience with file caches is that they’re a bit finicky but when you get them running, they can provide a significant speed-up to data enrichment tasks.

Comments closed