Press "Enter" to skip to content

Category: Performance Tuning

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

Moving tempdb to a Temporary Disk in a Cloud

Published 2025-06-26 by Kevin Feasel

Jeff Horner doesn’t need permanence:

One of the simplest yet most impactful performance optimizations for SQL Server is relocating the tempdb system database to a fast, dedicated disk—especially if that disk is temporary or ephemeral. This is a common practice in cloud environments and high-performance database systems where tempdb churn is high and doesn’t require persistence across reboots.

Click through for more information on how this can work well for you, as well as how to do it safely and things to keep in mind. One thing I would add is, I vaguely recall that on Azure at least, the temporary D: drive might not be mapped at startup, and it may take a few seconds after startup for the mapping to occur. Thus, it may be a good idea to have the SQL Server service start on a delay to ensure that you don’t run into an issue due to task ordering on startup.

Leave a Comment

Changes to Accelerated Database Recovery in 2025

Published 2025-06-20 by Kevin Feasel

Jordan Boich points out something interesting coming in SQL Server 2025:

Accelerated Database Recovery (ADR) was introduced in SQL Server 2019. Its main purpose is to allow for faster database recovery in the event of a crash or unexpected shutdown. Traditionally, the database engine handles crash recovery through a series of phases—analysis, redo, and undo—which can be inefficient and slow, especially when dealing with long-running transactions.

To make a long story short, ADR “shortcutsˮ the recovery process by introducing a new approach to handling undo operations. Instead of relying heavily on scanning the transaction log—which can be painfully slow for uncommitted or long-running transactions—ADR maintains a version store within the user database to track row-level changes. This allows SQL Server to quickly roll back uncommitted transactions without scanning the entire log. The result is much faster crash recovery, quicker rollbacks, and improved overall database availability, particularly in high-transaction environments.

Read on to see what’s new, as well as some of the consequences of enabling this feature.

Comments closed

Result Set Caching in Microsoft Fabric Data Warehouse

Published 2025-06-17 by Kevin Feasel

Emily Tehrani makes an announcement:

Result Set Caching is now available in preview for Microsoft Fabric Data Warehouse and Lakehouse SQL analytics endpoint. This performance optimization works transparently to cache the results of eligible T-SQL queries. When the same query is issued again, it directly retrieves the stored result, instead of recompiling and recomputing the original query. This operation drastically cuts execution time for complex queries. The cache is then automatically managed on the user’s behalf. This lightweight performance boost is most beneficial for workloads like reports, that issue many repetitive T-SQL queries to the DW and SQL analytics endpoint.

This is something I’ve wished we had on-premises for years and years, especially for data warehouses where you know the data only changes once every x hours or days. You can, of course, do this yourself with the cache-aside pattern and some caching solution, but that implies you have a layer between your end user and the data source that you fully control.

Comments closed