Press "Enter" to skip to content

Category: Performance Tuning

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

Why Not Use VARCHAR(MAX) for Everything?

Published 2025-07-01 by Kevin Feasel

David Fowler explains:

When I mentioned to the developer that it’s probably not the best idea, he turned around and asked me, ‘why not?’

It was a genuine question. Why shouldn’t we just spam VARCHAR(MAX) over all of our columns? On the upside, it would get rid of all those annoying issues that crop up when we try to insert a value that overflows the datatype.

Click through for a video as well as a blog post laying out some of the problem with using VARCHAR(MAX) all willy-nilly.

Leave a Comment

Performance Testing the pg_tde Extension

Published 2025-07-01 by Kevin Feasel

Transparent data encryption is now available in PostgreSQL and Andreas Scherbaum has some performance measures:

The performance impact of pg_tde by Percona for encrypted data pages is measurable across all tests, but not very large. The performance impact of encrypting WAL pages is about 20% for write-heavy tests. The tests were run with an extension RC (Release Candidate), however the WAL encryption feature is still in Beta stage.

Andreas also has a post on the testing specifics:

This test was run on a dedicated physical server, to avoid external influences and fluctuations from virtualization.

The server has a Intel(R) Xeon(R) Gold 5412U CPU with 48 cores, 256 GB RAM, and a 2 TB SAMSUNG MZQL21T9HCJR NVram disk dedicated for the tests (OS was running on a different disk).

Leave a Comment