All ten run fine on a developer machine with a thousand rows. It is in production with ten million that the difference shows up.
1. A function on the column in the WHERE clause
This is the most common one, and the most expensive:
-- Scans the whole table WHERE YEAR(created) = 2025 -- Can use an index on created WHERE created >= '2025-01-01' AND created < '2026-01-01'
As soon as you wrap the column in a function, the optimiser cannot use the index on it. It has to evaluate the expression for every row, which means reading every row. The same problem applies to UPPER(name) = 'OLA' and CAST(customer_no AS varchar) = '1004'. The rule is simple: keep the left side of the comparison clean and move the calculation onto the value you are comparing against.
2. A leading wildcard in LIKE
LIKE 'Ola%' can use an index, because it knows the start of the string and can look it up in the sorted structure. LIKE '%Ola%' cannot. It has to check every row. If you need to search inside text on a large table, the answer is full-text indexing, not a bigger server.
3. SELECT * in anything that crosses the network
The problem is rarely the rows, it is the columns. If the query only needs id and name, and both sit in a covering index, the database can answer without touching the table itself. Add an nvarchar(max) free-text column to the same SELECT and it has to fetch every row from the base table and push the contents across the network. Same query, entirely different cost.
On top of that, SELECT * makes the code brittle: if someone adds a column to the table, the shape of the result changes without anyone asking for it.
4. N+1 queries from the ORM
This one rarely comes from the SQL, it comes from the code above it. You fetch a hundred orders, loop over the list and read order.Customer.Name inside the loop. The result is one query for the orders and a hundred for the customers.
// 1 + N queries
var orders = db.Orders.Where(o => o.Active).ToList();
foreach (var o in orders)
Console.WriteLine(o.Customer.Name);
// 1 query
var orders = db.Orders
.Where(o => o.Active)
.Include(o => o.Customer)
.ToList();
Each individual query is fast, so nothing looks wrong in a log that measures averages. It is the sum of the round trips that ruins the response time. Turn on logging of generated queries in the test environment — it is the quickest way to find this.
5. Indexes that are never used
Indexes are not free. Each one has to be updated on INSERT, UPDATE and DELETE, and takes memory that could otherwise hold data. Tables that have grown through several years of developers often have three overlapping indexes and two that no query has touched since 2021.
SQL Server keeps statistics on this in sys.dm_db_index_usage_stats. The counters reset on restart, so read them after the server has been up for a while and through a normal month — some things are only used by the monthly report. Remove what is demonstrably unused, one index at a time, with a way to put it back.
6. Implicit conversion: the bug that does not look like one
This deserves its own section, because it is invisible in the code and brutal in production. The column is varchar(20), the parameter arrives as nvarchar from .NET, which is the default when you pass a C# string. The database cannot compare the two types directly, so it converts the column — every row — and the index becomes useless.
-- The customer_no column is varchar(20).
-- This converts the entire column, row by row:
WHERE customer_no = @nvarcharParameter
-- In .NET: state the type explicitly
cmd.Parameters.Add("@customerNo", SqlDbType.VarChar, 20).Value = customerNo;
The execution plan shows this as a CONVERT_IMPLICIT warning. The symptom in production is a query that suddenly takes seconds instead of milliseconds without anyone having changed it — it was simply called from new code with a different parameter type.
7. OR in the WHERE clause
The optimiser handles OR worse than people expect. A query meant to match on either email or phone number often ends up scanning, because no single index covers both alternatives. Two queries joined with UNION ALL can use one index each and be dramatically faster:
-- Often a single scan WHERE email = @value OR phone = @value -- Two seek operations SELECT ... FROM customer WHERE email = @value UNION ALL SELECT ... FROM customer WHERE phone = @value;
This is not a rule that OR is always wrong. It is a reminder to look at the execution plan when an OR query is slow, before concluding that the table is too big.
8. OFFSET pagination on deep pages
OFFSET 100000 ROWS FETCH NEXT 20 ROWS has to fetch and discard a hundred thousand rows to give you twenty. That is cheap on page 2 and expensive on page 5000. In an admin interface where nobody goes past page three, it is fine. In an API that lets a client paginate through the whole table, it is a guaranteed performance complaint.
The alternative is keyset pagination: remember the last id from the previous page and continue from there with WHERE id > @lastId ORDER BY id. Page 5000 then costs the same as page 1. The downside is that you lose the ability to jump straight to an arbitrary page, which is rarely a real requirement in an API.
9. Index column order matters
A composite index on (customer_id, created) helps a query that filters on customer and sorts by date. It does not help a query that only filters on date, because then it has to read the whole index anyway — the columns are sorted by customer first. This is the most common reason a table has "an index on that field" and still scans.
Think of it as a phone book sorted by surname and then first name. You find everyone called Hansen quickly. You cannot find everyone called Ola without reading the whole book. The order in the index definition is therefore a design decision, not a detail — put the column you filter on by equality first, and the column you sort or range-search on after it.
10. Statistics and parameter sensitivity
The optimiser picks a plan based on statistics about the data distribution, and reuses that plan for later calls with different parameter values. If customer number 1 has three orders and customer number 2 has two million, the optimal plan differs between them — but whichever ran first decides. The symptom is a stored procedure that is fast for most callers and inexplicably slow for a few.
There are several antidotes — updated statistics, OPTION (RECOMPILE) on the query where the compilation cost is worth it, or splitting the procedure up. The point here is to recognise the pattern: when performance depends on who is asking rather than what is being asked, you are looking at plan reuse, not a slow query.
Read the execution plan, do not guess
All the patterns above are just that — patterns, not laws. The only reliable way to know what is happening is to look at the actual execution plan for the query with a production-like volume of data.
A plan is read from the bottom inwards: the innermost operators run first. Look for three things. First the operator type — a Clustered Index Scan on a table with millions of rows, where you expected an Index Seek, means no usable index was found or the predicate could not use one. Then the row estimates: if it says 40 estimated rows and 2.4 million actual, the optimiser chose its strategy on a false premise, and everything it chose afterwards is probably wrong too. Finally the warnings — the yellow exclamation mark usually means implicit conversion or a spill to disk during sorting, and both are worth clearing up.
A practical order of work
When a slowness complaint lands: find the query that is actually slow, not the one you think it is. Get the execution plan with a production-like volume of data. Look at operator type, row estimates and warnings, in that order. Fix one thing. Measure again. The tedious part of performance work is that there is no shortcut around the measuring — the good news is that the ten patterns above cover the great majority of cases you will meet.