T-SQL Tuesday #200 – Signs of a Bad Query

It’s T-SQL Tuesday 200! To celebrate this bicentennial, Brent Ozar invites us to say to ourselves, “When I’m looking at a query, I bet it’s bad if I see ____.” and fill in the blank. To read the full invite, along with a bit of history about how T-SQL Tuesday began and evolved, click the T-SQL logo to the right.

They Know Not What They Do

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. But when I see the query that’s in use, it’s querying based on “%Error 123%”, meaning SQL Server can’t tell where to start looking in the index to perform an index seek.

Find the Error

Imagine we have this table full of error logs and an index on Err_Msg:

What should we query if we only want to focus in on “Login failed” errors? One option that will work, but isn’t optimal, is to wrap “Login failed” with percent signs:

SELECT ID, Err_Msg
FROM ErrorLog
WHERE Err_Msg LIKE '%Login failed%';

This will give us results:

But SQL Server needed to perform an Index Scan on the Err_Msg index:

Since we know our error message starts with “Login failed” we can drop the % from the front of “Login failed” in our query and search with:

SELECT ID, Err_Msg
FROM ErrorLog
WHERE Err_Msg LIKE 'Login failed%';

Now we’ll get our results and be able to use our index to seek. SQL Server only has to read the matching range instead of scanning the entire index:

On a table with millions of rows, this type of change can make a world of difference when it comes to performance.

Just Getting Started

Thanks to all who have been involved with T-SQL Tuesday. For me personally, it’s helped to keep me blogging when I may get busy or feel a bit of writer’s block. Cheers to another 200 and more!

Thanks for reading!