SQL Server performance is mostly written in application code

Most SQL Server performance problems are not hardware problems, and they are not really database-administration problems either. They start in application code, in the way queries are written, parameterised and shaped. The good news is that the highest-impact fixes are the ones developers control directly. This guide walks through the SQL Server performance mistakes we see most often in developer-written T-SQL, what they cost you, and the best-practice patterns that avoid them. We have deliberately kept deep index tuning out of scope, because that is where a DBA earns their keep, and focused instead on what you can change in your next pull request.

A quick definition to anchor everything that follows: a query is SARGable (Search ARGument able) when SQL Server can use an index seek to satisfy it. A large share of developer performance mistakes come down to writing predicates that are non-SARGable, which forces the engine to scan entire tables instead of seeking straight to the rows it needs.

1. Non-SARGable predicates: the most common miss

A predicate becomes non-SARGable the moment you wrap the indexed column in something, or compare it to a value of a different type. SQL Server can no longer use the index on that column, so it falls back to scanning every row. On a few thousand rows you will never notice. On a few million, it is the difference between a 5-millisecond seek and a 5-second scan.

The classic offenders:

-- Non-SARGable: a function on the column defeats the index
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- SARGable: a date range lets the optimiser seek
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';
-- Non-SARGable: a leading wildcard cannot use a normal index
SELECT * FROM Customers WHERE CustomerName LIKE '%smith';

-- SARGable: a trailing wildcard can
SELECT * FROM Customers WHERE CustomerName LIKE 'smith%';

The rule of thumb: keep the indexed column bare on one side of the comparison, and move any calculation to the other side. If you find yourself applying YEAR(), ISNULL(), LEFT(), UPPER() or arithmetic to a column inside a WHERE clause, that is the smell.

2. Implicit conversions: the non-SARGable predicate you cannot see

This one is sneaky because the query looks fine. If a column is varchar and your parameter arrives as nvarchar (the default for many ORMs and .NET clients), SQL Server has to convert one side to compare them. Because of data-type precedence it converts the column, which makes the predicate non-SARGable and triggers a scan, exactly as if you had wrapped the column in a function.

-- AccountNumber is varchar, but the parameter arrives as nvarchar
-- Result: an implicit conversion on the column, and a table scan
SELECT * FROM Accounts WHERE AccountNumber = N'A12345';

The fix is to match the parameter type to the column type. This is one of the most common issues we surface in a SQL Server health check, and it is almost always introduced unknowingly at the application layer. The tell is a warning triangle on an operator in the execution plan, noting a type conversion that may affect the cardinality estimate.

3. Parameter sniffing and bad cached plans

SQL Server compiles an execution plan the first time a stored procedure or parameterised query runs, then caches and reuses it. To build that plan it "sniffs" the parameter values from that first execution. Usually that is a good thing, because it tailors the plan to real values. The problem is when that first set of values is atypical.

Imagine a procedure filtered by CustomerId. The first call is for a customer with five orders, so the optimiser caches a plan built around nested-loop joins and index seeks, which is perfect for tiny result sets. Then a call arrives for a customer with two million orders, and that same seek-heavy plan runs millions of times instead of doing one efficient scan. A query that should take a second takes minutes. This is parameter sniffing, and it is behind a lot of the maddening "it was fast yesterday" complaints.

Best practice, in order of preference:

  • Parameterise properly in the first place. Use parameterised queries or sp_executesql, not string-concatenated ad-hoc SQL. This keeps the plan cache healthy and lets SQL Server reuse plans safely. As a bonus, it closes off the most common SQL injection vector.
  • When one plan genuinely cannot serve all values, target the specific query. OPTION (RECOMPILE) builds a fresh plan on every execution, which suits a query with widely varying parameters at a small CPU cost. OPTIMIZE FOR lets you compile for a representative value. Use these surgically, not as a blanket policy.
  • Avoid "fixes" that mask the cause. Copying parameters into local variables purely to defeat sniffing can quietly disable index usage and make things worse. If you have reached that point, it is usually the signal to bring a DBA in to read the plan.

4. SELECT * and over-fetching

SELECT * is the most common pattern we see in developer SQL, and it costs more than people expect. It forces SQL Server to read every column, including large varchar(max) values the application never touches, it increases I/O and network traffic, and it stops the optimiser using a narrow covering index that could otherwise satisfy the query on its own.

-- Reads everything, defeats covering indexes
SELECT * FROM Customers;

-- Reads only what you need, and can be served entirely from an index
SELECT CustomerId, CustomerName, Email FROM Customers;

Naming your columns also makes code more robust, because a schema change is far less likely to silently break a result set.

5. Row-by-row logic where a set would do

SQL Server is built for set-based work. Cursors, WHILE loops, and scalar user-defined functions called per row force it to process one record at a time, which is often orders of magnitude slower. Scalar UDFs in a WHERE or SELECT are a double hit, because they run per row and historically blocked parallelism. SQL Server 2019 introduced scalar UDF inlining to soften this, but the better habit is to express logic as a set operation or an inline table-valued function from the start.

Where indexes (and your DBA) come in

We have deliberately not turned this into an indexing masterclass. Indexing strategy, statistics maintenance, partitioning and execution-plan analysis are where a good DBA adds the most value, and getting them right depends on your specific workload, data distribution and hardware. The point worth making to developers is simpler: well-written, SARGable queries are what let good indexes do their job. The best index in the world cannot rescue a query that wraps its key column in a function. Get the query patterns right, and the indexing work your DBA does pays off many times over. If queries are still slow after cleaning up the patterns above, that is the right moment to bring in performance-tuning expertise rather than guessing.

Developers and DBAs are better together

The organisations that get the most out of SQL Server are the ones where developers and DBAs are not working in separate silos. Developers understand the application's intent and its access patterns. DBAs understand how the engine will execute that intent at scale. When the two collaborate early, code review catches non-SARGable predicates before they ship, the DBA tunes indexes around how the application actually queries, and nobody is firefighting a parameter-sniffing incident at 2am. The patterns in this article are the developer's half of that partnership. They are also exactly the kind of issues our SQL Server health check is built to surface across a whole estate.

Key takeaways

  • SARGable wins. Keep indexed columns bare in WHERE clauses: no functions, no calculations, no leading wildcards.
  • Match your data types. Mismatched varchar and nvarchar parameters cause invisible implicit conversions and full table scans.
  • Parameterise properly. It keeps the plan cache healthy and is your first defence against parameter sniffing. Reach for RECOMPILE or OPTIMIZE FOR only when a single plan genuinely cannot serve all values.
  • Ask only for the columns you need. SELECT * defeats covering indexes and inflates I/O.
  • Think in sets, not rows. Avoid cursors and per-row scalar functions.
  • Pair developer discipline with DBA expertise. Clean queries are what let good indexing and tuning deliver their full value.