SQL Server Performance Tuning: When a Join Isn’t a Join

Recently I was deploying an update to a client’s system and ran into a performance issue with the SQL script to apply database changes, an issue that hadn’t shown up in the test environment. I had to troubleshoot the slow query on the spot and solve it quickly. The cause of the problem turned out to be a useful demonstration of some important nuances in database performance.

In this article, you’ll learn the difference between logical and physical joins, the factors SQL Server considers when deciding how to execute a join, and two approaches to resolving a slow SQL query.

The Query

Here’s the SQL query that was running slowly. I’ve changed the table names and column names to obscure details about my client. After 5 minutes of running the query without finishing, I decided to abort it and troubleshoot it.

SELECT
    v.CustomerID,
    COUNT(v.ID) AS NumberOfVisits
FROM Customers AS c
INNER JOIN Visits AS v ON c.ID = v.CustomerID
WHERE v.VisitDate BETWEEN '2024-01-01' AND '2024-02-01'
GROUP BY v.CustomerID

It’s a simple query: it just joins customers to visits and selects the number of visits that each customer made within the date range specified.

It’s important to note that the customers table is tiny, about 4,000 rows, and the visits table is large, several hundred million rows, so it’s understandable for this query to be performance-intensive. This exact query performed very fast in staging, but there was very little data for that date range in staging, so it makes sense for it to perform differently.

What really confused me is that this query was a glorified copy-paste from a query that we had been using in production to run ad-hoc reports. When we ran it, it was dealing with production-scale data, so why did this version perform so poorly?

The Execution Plan

The first step to troubleshooting a slow SQL query is to get an actual execution plan (if you can). This will show you exactly what steps SQL Server took to execute your query, and runtime stats such as how much time and CPU each step took, how many rows were returned by each step, etc.

In SQL Server Management Studio (SSMS), press CTRL+M, then run your query. By default, this shows the execution plan only after the query finishes, which can be an issue for very slow queries. If you don’t want to wait, you can press CTRL + M and click the button near the top of SSMS labeled “Include Live Query Statistics”. This not only shows you the execution plan right away, but it also shows you the runtime stats (rows read, elapsed time of each step, etc.) in real time, so it updates as you watch it.

I can’t share the screenshot of the execution plan from production, but here’s what it looked like in a development environment when I ran it later. Table and column names have been changed to obscure client details.

I was profoundly surprised by this execution plan because there’s no physical join operator, despite the fact that there’s a logical join in the SQL query.

When you go to a restaurant and order steak au poivre, you don’t give the waiter instructions to cook it (“crush some peppercorns, pat the steak dry, set the grill to medium-high…”). You just ask for steak au poivre, maybe specify medium-rare, and the chef makes it happen.

SQL is the same way. SQL is a declarative language. When we write a SELECT query, we’re not writing instructions for how to get the data; we’re declaring what data we want. SQL Server needs to make decisions about how to execute it, such as which indexes to use or whether to go parallel.

Similarly, when we write any kind of JOIN in a SQL query, what we’re writing is a logical join. Examples include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN. SQL Server needs to figure out how to make your join happen, and it will do that by using one of several physical join operators. The most common physical join operators are Nested Loops, Hash Match, and Merge Join, so every execution plan you look at for queries involving logical joins will include at least one of those three operators.

When a Logical Join Does Not Require a Physical Join

Our slow SQL query is an exception to that rule. Our query clearly has a logical join (INNER JOIN) between two tables, but the SQL Server chooses an execution plan with no physical joins.

Why?

The first thing that came to mind is that the SQL query, despite joining the Customers table and the Visits table, only includes fields from the Visits table in the SELECT clause, WHERE clause, and GROUP BY clause. We’re not using any fields from the Customers table other than its ID field as the join key.

That’s part of the explanation, but it doesn’t explain all of it. The whole point of an INNER JOIN is to return only the records that have matches in both tables. So, even though we’re only SELECTing fields from one table, SQL Server still has to determine whether a given key value is present in both tables. How can it do that without performing a physical join?

The answer is the foreign key reference that enforces the relationship between these two tables. Specifically, we have a foreign key on the Visits table on the CustomerID field, referencing the ID field of the Customers table. This foreign key enforces that no record can exist in the Visits table without a corresponding record in the Customers table.

So, let’s look at this from SQL Server’s perspective:

1. The user is asking me to perform an INNER JOIN.
2. He’s asking me to filter and group based on fields from only one of the tables.
3. He’s only asking for fields from that same table.
4. I know for a fact that all records in Visits have a corresponding Customer record.
5. Why the heck would I bother with a physical join? I’ll just read from the Visits table and ignore Customers.

Physical joins can be memory-intensive and time-consuming. Generally, if we can avoid a physical join, we’ll get our results faster. So, the execution plan makes sense.

This isn’t just theory, either. I tested it in a development environment by removing the foreign key and running the SQL query. As expected, the execution plan included a physical join. Adding the foreign key constraint back to that field and running the query again resulted once more in an execution plan with no joins.

Why Is the Query Slow If We’re Avoiding a Join?

Now that we’ve identified why the execution plan has no physical join, let’s circle back to the problem at hand: the query is slow. Why is it slow, and how do we fix it?

Let’s take another look at the execution plan, specifically the first step.

SQL Server is choosing to do an index scan. That means it starts at the very “top” of the index and reads through it to the end. It chose this index, “IX_Visits_CustomerID_VisitDate,” because it includes all the fields our query needs. This index contains a couple of fields of each row, which means SQL Server can fit more rows on a single page of data, allowing it to read more rows per page.

However, the order of the keys in this index is hurting performance. CustomerID is the first key, and VisitDate is the second key. That means the rows in the index are sorted first by CustomerID, then by VisitDate. If it was the other way around, all the rows that match our WHERE clause would be right next to each other in the index and SQL Server would be able to do a “seek” to read only the minimal pages it needs to get the data we’re asking for.

Solution 1: Add a New Index

The most obvious solution is to add a new index with VisitDate as the first key and CustomerID as the second. Here’s the resulting execution plan:

Even in this development environment, where the data is much smaller, this resulted in a 100x speed boost—the query finished in about 0.1 seconds instead of 10 seconds.

However, indexes aren’t free, especially on large tables. The more indexes you have on a table, the longer it takes to write to that table when rows are updated or inserted. This table already has more indexes than I’d like, so I’d prefer to avoid adding a new one.

Additionally, this query runs once a month as part of an automated report, so it feels silly to add a whole index just to make this one query faster.

When we solve a slow query by adding, modifying, or removing indexes, that’s called “index tuning”.

Solution 2: Change the Query

In general, if you can make a query faster by rewriting the query rather than adding indexes, you should do so. I ran the old version of the query that was previously used to generate the ad-hoc reports and found that it used a physical join operator. The reason was simple: the original query joined the Visits table to a temp table representing Customers rather than the table itself. The Visits table obviously did not have a foreign key reference to this temp table, so it was forced to perform a physical join.

For the new version of the query, I forced a physical join by selecting a field from the Customers table instead of the Visits table:

SELECT
    c.ID AS CustomerID,
    COUNT(v.ID) AS NumberOfVisits
FROM Customers AS c
INNER JOIN Visits AS v ON c.ID = v.CustomerID
WHERE v.VisitDate BETWEEN '2024-01-01' AND '2024-02-01'
GROUP BY c.ID

In this version of the query, I’m using the Customers table’s ID field for both the GROUP BY and one of the fields in the SELECT, while still using fields from the Visits table in the WHERE clause and the SELECT clause. This forces SQL Server to do a physical join, and the execution plan looks like this:

In my development environment, the query takes 0.455. In production, it takes a couple of seconds, which is fast enough for our purposes. It runs as part of an automated report and must complete within 30 seconds to avoid timeouts.

Let’s zoom in on that physical join operator:

SQL Server chose the Nested Loops join. This physical join operator is very fast and efficient when you have a small result set on one side of the join compared to the result set on the other side. We have just a few thousand rows in our Customers table and a couple hundred million rows in our Visits table, so Nested Loops is the right choice.

The way this join works is that SQL Server loops through the smaller data set, the Customers table, and for each row, performs a seek on the Visits table to get the rows corresponding to that CustomerID value. These seeks are fast because CustomerID is the first key in the index.

Because this version of the query meets our performance needs without requiring a change to the database’s structure (e.g., by adding an index), it’s the solution we chose.

When we solve a slow query by rewriting or tweaking the query text itself, as we did here, that’s called “query tuning”.

The Takeaway

What have we learned? We can take away the following lessons about SQL Server:

1. SQL Server does not always need to perform a physical join to execute a logical join.
2. Foreign key references, or lack thereof, can impact whether SQL Server performs a physical join.
3. If you only use fields from one table in your SELECT, WHERE, ORDER BY, or GROUP BY clauses, SQL Server might not bother with a physical join.
4. When trying to speed up a slow query, you should first try to rewrite the query before resorting to adding or changing indexes.
5. Sometimes an unusual solution is the best one. In rare cases, adding a join can improve performance.

This was a deep dive into a fascinating detail of how SQL Server decides when to do a physical join and how that impacts performance.

If you rely on SQL Server or Azure SQL for your business-critical applications, you need database experts who can calmly work under pressure to solve tricky problems like the one described in this article.