10 SQL Server Performance Fixes I Apply on Every Client Audit

Most SQL Server performance problems come from a small, repeatable set of causes — missing or wrong indexes, queries that force table scans, and parameter sniffing gone wrong — not exotic edge cases. Here are the 10 fixes that show up, and pay off, on nearly every production system I audit.

1. Missing Indexes on Foreign Keys

SQL Server doesn't automatically index foreign key columns the way primary keys are indexed. Joins and cascading deletes on unindexed foreign keys are one of the most common, and easiest to fix, sources of slow queries I find during an audit.

2. Indexes That Don't Match the Actual Query Pattern

An index on the wrong column order is nearly as bad as no index at all for a given query. If your WHERE clause filters on (TenantId, CreatedDate) but your index is ordered (CreatedDate, TenantId), the optimizer often can't use it efficiently. Matching index column order to actual query filter order is a five-minute fix with an outsized impact.

3. SELECT * Instead of Named Columns

Pulling every column when a query only needs three defeats the purpose of a covering index and increases I/O for no benefit. This is one of the simplest wins — rewriting SELECT * to named columns, paired with a covering index, regularly cuts query time dramatically with zero schema changes.

4. Implicit Conversions in WHERE Clauses

Comparing a column of one data type against a parameter of another (a very common case: an NVARCHAR column compared against a VARCHAR parameter from application code) silently disables index usage on that column. This one is sneaky because the query still returns correct results — it's just doing a full scan to get there, and nothing in the error log flags it.

5. Parameter Sniffing on Highly Variable Queries

SQL Server caches an execution plan based on the first parameter value it sees for a query. If that query runs very differently for different parameter values (a classic case: a date range filter that's sometimes a day and sometimes a decade), the cached plan can be badly wrong for later calls. OPTION (RECOMPILE) on the specific problem queries, or restructuring the query, fixes this without a blanket "recompile everything" performance tax.

6. Missing or Stale Statistics

The query optimizer relies on statistics to estimate row counts and choose a plan. Stale statistics on tables with high write volume lead to bad plan choices even when indexes are otherwise correct. Scheduled statistics updates on high-churn tables are cheap insurance against this.

7. Over-Indexing on Write-Heavy Tables

The opposite problem also shows up regularly: tables with far more indexes than the query patterns justify, each one adding write overhead on every insert/update/delete. Auditing actual index usage stats and dropping genuinely unused indexes is routine, high-value cleanup.

8. N+1 Query Patterns from the Application Layer

This isn't a database-level fix so much as an application-level one, but it shows up constantly in database audits: an application loop that issues one query per row instead of a single batched query. Entity Framework and similar ORMs make this mistake easy to introduce accidentally via lazy-loaded navigation properties.

9. Wrong Isolation Level for the Workload

READ COMMITTED is the default and correct choice for most OLTP workloads, but I regularly find systems running under an isolation level that's either causing unnecessary blocking or, less commonly, risking data consistency issues it wasn't designed to handle. Matching isolation level to actual concurrency requirements, rather than leaving whatever default was set years ago, resolves a surprising number of "random slowness" reports.

10. No Query Store or Monitoring in Place

The most common root cause behind all of the above going unnoticed for months: nobody's actually watching. Enabling Query Store (built into SQL Server since 2016) turns "why is this randomly slow sometimes" into a five-minute investigation with historical plan data, instead of a guessing game.

FAQ

If your production database has gotten slower as it's grown and you're not sure why, get in touch — this is the exact kind of audit I run for clients regularly.