Query Tuning – Curated SQL

DOP and Tipping Points

Published 2026-11-09 by Kevin Feasel

Erik Darling here with Darling Data. And I’ve got a question that I thought was interesting enough to actually answer with a brief demonstration, so I’m going to do that today. But it is about DOP and tipping points, or as I will lovingly refer to them from now on, DOPing points, because why not?

This video is more conjectural in nature and shows an interesting use of MAXDOP(1) to nudge the optimizer toward seeks rather than scans given a certain style of query.

Using Batch Mode on Rowstore Indexes in SQL Server

Published 2026-11-03 by Kevin Feasel

Mehdi Ghapanvari speeds things up:

SQL Server columnstore indexes can improve the performance of aggregation queries. However, there are many cases where they are not efficient. I discussed one of these scenarios in “When Using a Clustered Columnstore Index Does Not Make Sense.” In that article, I discussed that columnstore indexes have three main features, one of which is Batch Mode Processing. SQL Server 2019 introduced a new feature called Batch Mode on Rowstore, which enables batch mode processing on Rowstore tables. In this article, I will explore this feature and explain why it is valuable in real-world scenarios.

In Mehdi’s solution, the question comes up of “Who would run an aggregation query on an OLTP system?” The answer is “everybody.” I’ve never seen an OTLP system with zero aggregation work. Perhaps these systems really do exist but some level of hybrid operation is the default that I’ve experienced.

Query Plan Hints in Postgres 19

Published 2026-10-01 by Kevin Feasel

Elizabeth Garrett Christensen says you may not need it:

For years the Postgres community was against adding support for hints. They felt if you had correctly analyzed tables, the Postgres query planner would make the right choice. And they’re not wrong, for the most part. Yet many DBAs and developers will tell you of a time when a plan suddenly changed or something was saved by a hint. We can debate the good and bad elements of hints, but the cool thing is that you will soon have a choice in Postgres 19+.

Read on to see how plan hints will work and what you should try before using them.

Comments closed

Window Functions and Virtual Tables

Published 2026-09-29 by Kevin Feasel

Dualcore DBA pushes a predicate:

In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server.

I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, I am running in compatibility level 150 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM, though the issue being illustrated also occurs in earlier compatibility levels.

Click through for the demonstration and to see what happens when you switch to compatibility level 160.

Comments closed

Fetching Query Plans in Fabric Data Warehouse

Published 2026-09-24 by Kevin Feasel

Louis Davidson shows a process:

So in this next entry in this series, I want to start to cover a topic that every nerdy SQL programmer is going to want to know. “How do I make a query faster?” If you have worked with relational databases, you know the simple answer is to… check the estimated query plan, run the query and look at the actual plan, and in many cases to be completely honest, add indexes.

Click through to see how closely your current SQL Server skills translate in this instance. The specifics of query tuning and table design in the Fabric Data Warehouse will differ a bit from on-premises SQL Server, but at this level, it’s quite similar.

Comments closed

The Pain of Time Zones

Published 2026-09-14 by Kevin Feasel

Erik Darling makes an admission:

Erik Darling here with Darling Data, and in this video, we’re going to talk about mostly AT TIME ZONE performance, but also some other interesting stuff about AT TIME ZONE. I’m going to be real honest with you, I hate timezone stuff. I hate all of it. I’ve never taken naturally to it. It’s all too weird, and managing it is just such a nightmare.

I agree with Erik’s point about storing everything in UTC. Click through to learn how you can slow things down considerably using AT TIME ZONE in certain ways.

Comments closed

Cross-Table Date Math

Published 2026-09-04 by Kevin Feasel

Erik Darling does some date math covering multiple tables:

So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.

Click through for a clever use of an indexed view and a non-clustered columnstore index. Which, incidentally, marks one of the few times in which I’ve seen actual value in non-clustered columnstore indexes.

Comments closed

The Pain of Residual Predicates

Published 2026-08-28 by Kevin Feasel

Brent Ozar has a new animation:

If we modify our query a little by selecting all of the columns instead of just Id and Location, then we have to do a Key Lookup, like we talked about in the How to Think Like the Engine class. For each person who lives in Helsinki, we have to look up their row in the clustered index in order to fetch all the columns we need. That’s not really a big deal, though, as long as a relatively limited number of people live in Helsinki. Like I wrote in that post, the index seek + key lookup is essentially two index seeks: one into Helsinki, and then one seek (for each Helsinki resident) on the clustered index, by their Id.

However, let’s add a little more complexity to the query:

Click through for a scenario in which a more selective query can result in worse performance than a less-selective variant.

Comments closed

sp_executesql and Building Execution Plans

Published 2026-08-19 by Kevin Feasel

Dualcore DBA needs a plan:

Whilst I would say the actual execution plan is the most useful, estimated execution plans have their place – sometimes you just need to see estimates or a quick verification that a change you have made has had some effect on plan shape. I find them helpful to quickly see if a change made to the code, an index, database setting or similar has had an effect on the execution plan without having to wait for the query to finish (especially if I am performance tuning a query that has a long run time).

sp_executesql is also useful – it helps us to execute dynamically created queries and supports parameterisation. It is also used by the .NET SqlCommand class to issue queries to the database engine in a parameterised form, assisting with SQL injection prevention along the way.

I recently found myself troubleshooting some code coming from an application. It was a Slow in the app, fast in SSMS problem so I was using sp_executesql to replicate the application behaviour. I also wanted to get the estimated execution plan. Here is an example query and what I was greeted with when I asked for the estimated plan:

Read on for one challenge you might find when trying to tune operations that you’ve built using sp_executesql, as well as what you can do about it.

Comments closed

Digging into Postgres Plan Caching

Published 2026-08-13 by Kevin Feasel

Jordan Boich runs an experiment:

In my last post, I talked about how I was amazed that PostgreSQL doesn’t have a shared plan cache like SQL Server does. I wanted to create an experiment / lab where I could see this in action, so I built one. I’m going to be leveraging a free PostgreSQL database hosted on an Azure Flexible Server. I’ll also be using DBeaver to connect to my PG Database.

Click through to see what Jordan learned.

Comments closed

Category: Query Tuning