Press "Enter" to skip to content

Curated SQL Posts

Making PostgreSQL Slower

Published 2025-07-28 by Kevin Feasel

Jacob Jackson takes on a unique challenge:

Everyone is always wondering how to make Postgres faster, more efficient, etc, but nobody ever thinks about how to make Postgres slower. Now, of course, most of those people are being paid to focus on speed, but I am not (although, if you wanted to change that, let me know). As I was writing a slightly more useful guide, I decided someone needed to try to create a Postgres configuration optimized to process queries as slowly as possible. Why? I am not sure, but this is what came of that thought.

I spent a few moments thinking about an equivalent sort of torture test on SQL Server, doing things like forcing CPU affinity through one core, monkeying with cost threshold for parallelism, and using trace flags to turn off different join optimizations (like, say, hash matches and merge joins, forcing everything to be nested loops). It’s a fun thought experiment.

Leave a Comment

Common Table Expressions in SQL Server and Materialization

Published 2025-07-28 by Kevin Feasel

Vlad Drumea is back to following Betteridge’s Law of Headlines and the people rejoice:

There’s this weird misconception floating around LinkedIn and reddit that SQL Server CTEs somehow store results in either memory or tempdb.

This is wrong and whoever states that CTEs store results either have no idea what they’re talking about or are intentionally trying to mislead people for engagement farming.

Click through for the proof of this.

As a quick note, you can materialize common table expressions in some relational database platforms like PostgreSQL, but SQL Server does not have that option.

Leave a Comment

Managing Point in Time Restores with SQL Server

Published 2025-07-28 by Kevin Feasel

Aaron Bertrand figures out available database restoration options:

In my last tip, Enhancing SQL Server Backups for Hundreds of Databases, I created a single, central backup catalog representing all the full, log, and differential backups across our environment. I demonstrated how I use it to build restore statements for any given database for validating the backup, performing DBCC checks, or to recover data at a specific point in time. But can this data help us in other ways, too? Let’s see how we can use this data for a point in time restore.

Read on for some neat queries you can run based on Aaron’s process in the original post.

Leave a Comment

Building a Test Bed for Page Latch Waits

Published 2025-07-28 by Kevin Feasel

Jared Poche generates some test data:

I’ve already posted a blog on page latch waits and some of the ways to minimize them, but I wanted to add some more on the issue. I wanted to test out some strategies to see how effective they are.

Setting up tests can be very difficult in some cases, and this attempt encountered a very unusual problem. At the risk of delaying the intended results, I wanted to point this out. It’s both interesting and frustrating to work with SQL Server for 20 years and run into new problems.

Read on to see what Jared tried, some interesting consequences, and a small fix that enabled a big problem.

Leave a Comment

Retry Resiliency in Apache Kafka Pipelines

Published 2025-07-24 by Kevin Feasel

Ravi Teja Thutari explains the value of idempotence in moving data between systems:

In modern flight booking systems, streaming fare updates and reservations through distributed microservices is common. These pipelines must be retry-resilient, ensuring that transient failures or replays don’t cause duplicate bookings or stale pricing. A core strategy is idempotency: each event (e.g., a fare-update or booking command) carries a unique identifier so processing it more than once has no adverse effect. 

Read on to learn more. For reference, idempotence is a property of an operation where you can run through the operation as many times as you wish and will always end up at the same result. In the data operations world, this ties to the final state in a database. If I run a process once and it adds three rows to the database, I should be able to run the process a second time and end up with those exact three rows, no more, no fewer, and no different.

Leave a Comment

Copilots, MCP Servers, and Connection Strings

Published 2025-07-24 by Kevin Feasel

Chad Baldwin shares a warning:

Well, a few days ago, I ran into the result of one of those awkward pieces when combining the MSSQL extension for VS Code, MSSQL MCP Server and Copilot.

The short of it is…I asked Copilot to change the connection used by the MSSQL extension to use a particular database. I later asked Copilot to describe a table in the database (which uses the MSSQL MCP server), only for it to claim the table didn’t exist. I realized right away it was due to competing connections between the MSSQL extension and the MSSQL MCP Server configuration. It was also at that moment where I realized this situation could potentially be SO MUCH worse than simply not finding a table…

So let’s set up a worst case scenario and see what happens.

This is basically the equivalent of “Wait, that SSMS window was production? Uh-oh.” Not that this has ever happened to me, of course. Or any of you. Nope.

Leave a Comment

Optimizing Multi-Notebook Jobs in Microsoft Fabric and AWS Glue

Published 2025-07-24 by Kevin Feasel

Daniel Janik flips a switch:

Are your Azure Fabric pipelines with multiple notebooks running slower than you’d like? Are you paying for more Spark compute time than you should be? The culprit might be a simple setting that’s easy to miss. In this blog post, we’ll dive into the “For pipeline running multiple notebooks” setting in Azure Fabric and explain why enabling it can significantly improve your pipeline’s performance and reduce your costs.

Click through for this, as well as a comparison with AWS Glue and ways to perform something similar there.

Leave a Comment

A Deep Dive into IDENTITY Columns

Published 2025-07-24 by Kevin Feasel

Vlad Drumea performs a deep dive:

In SQL Server, IDENTITY is a column-level property that is used to provide an auto-incremented value for every new row inserted.

All you have to do is provide a seed value and an increment value when defining said column, and SQL Server will handle it from there.

Unlike sequences, identity columns do not require additional objects like default constraints or triggers to ensure the column is populated.

I’m glad that Vlad made a demo showing how @@IDENTITY works and how it can give you unexpected outputs if you’re not aware of a trigger working with a separate identity column. That one tends to get people.

Leave a Comment

Post-Install Configuration Tips for SQL Server

Published 2025-07-24 by Kevin Feasel

Kevin Hill shares some suggestions:

The SQL Server installer has gotten better: tempdb configuration, MAXDOP, and even max memory can now be configured during setup.

But don’t be fooled: there’s still a post-install checklist that can make or break your environment over time. If you’ve ever inherited a server that “just ran” for years and started getting slower over time you’ve likely seen what happens when this list gets ignored.

These are not in any particular order, but some do require a restart of the server or the SQL Server Engine service to take effect:

Click through for several tips. Most of them I agree without reservation. For a couple of them, like backup compression, there are specific circumstances in which I’d recommend against turning it on, namely when working with storage subsystems that deduplicate your data automatically. In that case, you might want to think harder about backup compression (or backup encryption, for that matter) before turning it on.

Leave a Comment

Testing Plumber APIs from R

Published 2025-07-23 by Kevin Feasel

Jakub Sobolewski builds some tests:

When building Plumber APIs in R, effective testing is crucial for ensuring reliability and maintainability.

This guide explores a proven pattern for testing own Plumber APIs that maintains fast feedback loops while providing robust coverage of both business logic and API contracts.

Read on for some good advice on API testing in general, followed by its application in R. Jakub also has a link to a GitHub repo showing a concrete example of how this testing can work. H/T R-Bloggers.

Leave a Comment