T-SQL Tuesday #200 – things that make me instantly think a query is bad

This month’s T-SQL Tuesday invitation comes from Brent Ozar, and it’s titled “When I’m looking at a query, I bet it’s bad if I see __.”.

This is a fun topic, so, obviously, I couldn’t stay away.

Some background info

To give some context to folks who may not be familiar with what I do, I tend to spend a lot of time working on SQL Server and Oracle query and performance tuning.
And, from August 2022 until last month (June 2026), I’ve worked as a Principal Dev DBA for a fairly large ISV.
During this time I’ve had the opportunity to see all sorts of interesting queries and database design decisions.

Things that make me instantly think a query is bad

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:

DISTINCT in queries with multiple JOINs

Whenever I see a DISTINCT in a fairly large query with multiple JOINs, the first question that comes to mind is:
“What data crimes is this DISTINCT hiding?”

And by “data crimes” I’m referring to one or both of:

1. Those JOINs are poorly defined and lead to a Cartesian product.
2. One or more of those tables ended up with a lot of duplicate data.

Regardless if it’s 1, 2, or both, the developer considered that adding DISTINCT in their query fixes this problem permanently and will never come back to haunt them on environments with more data than their dev/test databases.

The same goes for slapping GROUP BY on a query to fix a duplicate data/Cartesian product problem.

Story time

At some point last year, I was contacted by some of my colleagues regarding a query that, after running for hours, errored out due to tempdb being full in Azure SQL DB.

Everyone was confused because the query should have returned a single row.
So, why would it write so much to tempdb?

The query had 3 LEFT JOINs, two of which were between the base table (the one in the FROM) and itself, and defined in a way that was causing a Cartesian product.
Due to this specific combination of JOINs, compounded with some unintended data duplication, and masked by the DISTINCT, it churned through way more records than intended.
This led to the query spilling 780GB+ of data in tempdb for the sort operation required by DISTINCT.

Replacing the DISTINCT and column list with a COUNT(*) (to get a sense of how many rows SQL Server would have to churn to return that one row) resulted in an arithmetic overflow. So, the total number of rows was beyond INT’s 2,147,483,647 limit.

Once I pinpointed that, the devs were able to clean up the data, fix the JOINs, and the DISTINCT was no longer required.

While on the topic of LEFT JOINs….

A bunch of LEFT JOINs

Just to be clear, I’m not saying that LEFT JOINs are evil by default, but once you have 20+ of them in your query you should probably start taking a better look at your query logic and database design.

Bonus: if there are views (especially nested) thrown in the mix.

This point makes the “a bunch of LEFT JOINs” thing even worse since:

1. It makes troubleshooting harder than it has any right to be.
   Think about dealing with views that have 3+ levels of nesting and going from view definition to view definition to figure out what’s happening.
2. The query optimizer will have a hard time finding the best execution plan in a decent amount of time so you’ll get the “best guess” one that in most cases is nowhere near the best plan.

If you want to read more on the topic, Randolph West has a blog post with more details about nested views.

Horrors beyond human comprehension

And when I’m talking about a bunch of LEFT JOINs and nested views, this is how a resulting execution plan looks like:

I wish this was some AI-generated image of a fake execution plan, but it isn’t.
Unfortunately, it’s an actual execution plan from a query written by real-life developers.

A bunch of CTEs, especially if they’re recursive

Now, I don’t have anything against CTEs per se.
I’m just not a fan of how some people tend to use CTEs for things that can be easily handled inside the main query, or, worse, people who think that CTEs improve performance and use them thinking they work like temp tables (they don’t).

User defined functions to handle various date filtering

Whenever I see CROSS JOINs or WHERE clauses with UDFs that return the fiscal year/quarter, or determine working days, weekends, and bank holidays, I’m certain that said query is serial (just a fancier term for single-threaded), regardless of how expensive its plan is, and it would benefit a lot from switching to date dimension/calendar tables.

If you’re about to suggest that SQL Server 2019+ fixes some of these cases via scalar UDF inlining: it’s pretty hit or miss and, IMHO, is still fairly buggy.
For the time being, date/calendar tables are a better option.

All string parameters are NVARCHAR(MAX)

I tend to see this a lot in queries coming from ORMs where devs didn’t bother tweaking parameters to match the data types of their intended columns.

The impact here depends on what data types the columns actually use and where those data types are on the data type precedence scale.

As a brief example, if a table has a column that’s VARCHAR(100) that also has an index on it and the query uses a NVARCHAR(MAX) parameter to filter on that column, the index will be ignored and SQL Server will do a full table scan.

If you want to read more on this, feel free to check out my blog post on implicit conversions.

But wait, there’s more

And even if your column is NVARCHAR(100), so there won’t be any implicit up-conversion from VARCHAR to NVARCHAR needed, the execution plan might still not look like you’d expect when comparing against NVARCHAR(MAX).

I’m using the 180GB StackOverflow database for this example on SQL Server 2022 with the latest CU applied.
I create the following index on the Users table:

Then I run these two queries in one go:

And check their execution plans:

Note the additional plan operators, including the Nested Loop, and the Seek Predicate for the query using NVARCHAR(MAX) that’s looking for a range, as opposed to the one using NVARCHAR(100) that’s just doing an equality search.

And, throwing in OPTION(RECOMPILE) to fix those terrible cardinality estimates that are present in both plans, won’t do anything about the first plan’s shape.

NOLOCK hints

This one’s pretty self-explanatory.
If I see NOLOCK hints in queries that aren’t exploratory or meant for troubleshooting, I automatically suspect that said queries are involved in locking, blocking and maybe even deadlocking. And someone thought that sprinkling NOLOCK will magically fix that.
Spoiler alert: it doesn’t.

What NOLOCK actually does in this case is open up avenues for other unsavory issues such as dirty reads, duplicate rows, and missing rows.

Wait, no cursors or loops?

No.
The invitation says “query”, a query is whatever takes place between a SELECT, or WITH in case of CTEs, and the first statement terminator (aka a semicolon).

Conclusion

Not much to say, really.
As a rule of thumb, whenever I spot a query with one or more of these patterns in it I instantly think I’m in “this query is bad” territory.
If you see all of them in the same query, my condolences.

Just note that the important verb here is “think” since some of these aren’t automatically bad by themselves (DISTINCT, being one of those cases) unless they’re confirmed by the execution plan, query and IO stats (you know, the kind of performance info you’d see in the plan cache and query store pages of PSBlitz).
While others are clear red flags like the NOLOCK hint.