Query performance problems in SQL Server almost always trace back to poorly written T-SQL or missing indexes rather than hardware limits. Addressing these two areas first yields the largest returns with the least effort.

The techniques below focus on patterns that remain effective across recent SQL Server releases. They require only standard tools already available in every production environment.

#Start with the Execution Plan

Always capture the actual execution plan before changing any query. Look for expensive operators such as table scans, key lookups, and sorts. These point directly to missing indexes or suboptimal join order.

#Eliminate Common Anti-Patterns

  • Avoid SELECT * in production code; list only required columns.
  • Replace scalar UDFs in WHERE or SELECT clauses with inline table-valued functions or window functions.
  • Rewrite correlated subqueries as joins when the optimizer cannot decorrelate them automatically.
  • Parameterize queries instead of concatenating literal values.

#Apply Targeted Indexing

Create covering indexes for the most frequent read queries. Include only the columns actually referenced in the SELECT list and WHERE clause. Monitor index usage with sys.dm_db_index_usage_stats to remove unused indexes that slow down writes.

tsql 
CREATE INDEX IX_Orders_CustomerDate
ON Sales.Orders (CustomerID, OrderDate)
INCLUDE (TotalDue, Status);

#Monitor and Maintain

Schedule regular index maintenance and update statistics after large data loads. Use Query Store to track regression and force stable plans when needed. These steps keep the optimizer working with accurate cardinality estimates.

Apply these patterns iteratively on the highest-impact queries first. Measure before and after metrics with Query Store or extended events, then move to the next bottleneck. Consistent application of these fundamentals produces stable, predictable performance on production SQL Server workloads.