Press "Enter" to skip to content

Category: Query Tuning

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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

From CTE to Temp Table

Published 2026-08-12 by Kevin Feasel

Brent Ozar describes a pattern I often use:

Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.

When SQL Server gets that process wrong, switch to temp tables.

Read on for an example.

Comments closed

Dealing with Catch-All Queries

Published 2026-08-11 by Kevin Feasel

Dualcore DBA handles an overly broad class of query:

We have a query that SELECTs from the Users table that takes multiple optional parameters and filters the output based on those.

Whilst fairly easy to read and write, queries written in this way often underperform.

This is a very generous understatement. Click through for two ways to improve the performance of such queries, as well as the pros and cons of each.

Comments closed

Issues with INSERT-EXEC

Published 2026-08-07 by Kevin Feasel

Erik Darling has two problems:

All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…

why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.

Click through to learn more about both problems, including windows of time when SQL Server gets blackout drunk and can’t account for its time.

Comments closed

Challenges with DATE_BUCKET()

Published 2026-07-30 by Kevin Feasel

Erik Darling has a new video:

But what’s also strange here, too, is that SQL Server estimates one row very reliably for a date bucket. So, you know, you might be careful about that as well, especially if you’re returning far more than one row. You might be unhappy with the one row estimate.

It might bring you back to the bad old days of table variables and off histogram values, stuff like that. But, yeah, anyway, I had a point with all that. Let’s do this.

Click through to learn Erik’s point.

Comments closed

Retrieving Plans from DMVs and Query Store

Published 2026-07-28 by Kevin Feasel

Deborah Melkin concludes a video series on how to get execution plans in SQL Server:

I really wanted to put this together because I feel like understanding these differences is important to understanding how we can troubleshoot performance problems and where we need to be looking for these pieces of information to get that full picture of where to spend our time. It makes us better performance tuners. I hope you found this helpful and you leave with a better appreciation for these nuances.

Click through for the video, as well as a transcription on the blog post.

Comments closed

Tracking Actual Execution Plans in SSMS

Published 2026-07-21 by Kevin Feasel

Deborah Melkin continues a series on query plans:

This is the second part of the series. Hopefully you are all caught up on Part 1. If not, you can see that here.

Part 2 is about Actual Execution Plans. I have the (slightly edited for garbled words) transcript below for those who prefer to read, with references to places in the video. Otherwise, watch the video and let me know what you think.

Click through for the video and transcript.

Comments closed