Query Tuning – Curated SQL

Issues with INSERT-EXEC

Published 2026-08-07 by Kevin Feasel

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

Bad Query Pattern: Wildcards on Both Ends

Published 2026-07-17 by Kevin Feasel

Chad Callihan covers this month’s T-SQL Tuesday topic:

A lot of SQL queries or code can still “work,” but that doesn’t mean it’s good, especially with so much vibe coding and the like going on these days. When I think of signs I’ve noticed when seeing a query for the first time, one thing that will get my attention (besides seeing NOLOCK throughout) is when a string is being searched for surrounded by percent signs.

I’ve had instances of working with someone that was looking for certain error logs. We may know the log record starts with “Error 123” and could search a field for “Error 123%” in our query.

Click through for the consequences of a search on “Error 123%” versus “%Error 123%”. Sometimes it’s simply necessary to do the full search, but if you find yourself doing that frequently, it’s a sign that you could design the table better.

Comments closed

Subqueries in the SELECT Clause

Published 2026-07-16 by Kevin Feasel

Louis Davidson tries out a few query forms:

The post states that a query such as:
SELECT soh.SalesOrderID,(SELECT  C.AccountNumberFROM    Sales.Customer AS CWHERE   C.CustomerID = SOH.CustomerID)AS CustomerAccountNumberFROM   Sales.SalesOrderHeader SOH;
will, by definition, execute that subquery on the Customer object one time per row in the SalesOrderHeader table.

But is this true?

Click through as Louis tests a few variants of this using SQL Server.

Comments closed

Ways to Find a Query Plan

Published 2026-07-14 by Kevin Feasel

Deborah Melkin has a list:

I’m very excited for this because I’ve wanted to put something together about this topic for a long time.

What inspired me is that I’ve really come to appreciate that there’s different pieces of information collected with the execution plan itself depending on where I get the plan from. Understanding what’s collected, where, and why can help make a difference when trying to troubleshoot and performance tune.

I get most of my query plans from a shady vendor with an unmarked van in a Walmart parking lot.

Comments closed

Two Pain Points in SQL Server Code

Published 2026-07-10 by Kevin Feasel

Steve Jones digs up a pair:

There are two things that immediately stand out to me when I see a query and create concern.

cross joins

functions in the where/on clause

While there are other things I might see, these two stand out and usually I can guess there will be issues.

Steve specifically calls out ANSI-89 style joins, which are awful. Before continuing, I agree with Steve’s points, but I figure I’d do a “yes, and” for this one.

When it comes to cross joins, there are specific circumstances in which I’ve written cross joins to great effect. The one that comes to mind most readily is when I need to create the raw data that will let you generate a matrix in a reporting tool.

Let’s say you want to know, for each sales territory and month, the number of new customers that month, the prior month, and the next month. The LAG() and LEAD() functions only work on rows, not intervals. Therefore, LAG() on the July row will show you the row prior to July. That might be June or it might be March, depending on how much data you have.

The solution, then, is to get the full set of months you care about from, say, a calendar table. Then, get the full set of sales territories. Cross join those two and you’ll have the entire range of relevant data. You can then left outer join to the actual data and fill in the gaps.

I have an example of this as a demo script on my Analyzing Business Data with T-SQL talk.

Comments closed

Starting Points for Query Tuning

Published 2026-07-08 by Kevin Feasel

Deborah Melkin shares some tips:

This one is really timely for me as I just started a new job. Performance tuning was part of the interview process so I’m really excited to dive back into doing more of that day-to-day. In fact, I just got added to the email reports with the top SQL results for the worst performers. Here’s some of what I’ll start looking at in that list and why:

Click through for Deborah’s red flag list. Of note, a red flag is not necessarily a bad thing. But it does merit further inspection and comment. For example, there may be specific instances in which join hints are necessary—you know you’re joining from a very small filtered subset to grab a tiny percentage of a bigger table (and you have an appropriate index on said bigger table), and so you slap on a LOOP join hint because the optimizer keeps trying to sort and merge join. But it’s worth explaining why and figuring out if there’s a better way, especially considering the consequences of slapping on that join hint.

Comments closed

Pain Points for a Query

Published 2026-07-07 by Kevin Feasel

Vlad Drumea comes up with a list:

This is my list of “main suspects” that make me instantly think a query is bad as soon as I see at least one of them.

Note, this is just based on the query text alone, seeing the execution plan is an instant confirmation.

Now, without further ado and in no specific order:

Click through for the list. There are some good items on it.

Comments closed

Category: Query Tuning