Introduction
SQL Server is powerful, but even experienced developers sometimes write queries that perform poorly in real environments. What works fine with 5,000 rows may fail when the table grows to 5 million or when concurrency increases.
Slow queries affect:
Performance degradation increases server CPU, memory, and IO load, which directly increases infrastructure cost.
The good news is most slow queries can be fixed with a few well-understood optimization techniques.
This article explains how to diagnose, analyze, and fix slow queries based on real-world scenarios.
Case Study: Inventory Dashboard Query
A developer wrote the following query for an ERP stock dashboard:
SELECT *FROM Stockline s
JOIN Warehouse w ON s.WarehouseId = w.WarehouseId
WHERE s.PartNumber LIKE '%BRAKE%'AND s.Status = 'Active'ORDER BY CreatedDate DESC;
On day one, it ran in milliseconds. After one year, with 12 million stock records, it now takes:
Step 1: Use Execution Plans
SQL Server can show how the query is executed.
Run:
SET SHOWPLAN_ALL ON;
Or in SSMS:
Query menu > Include Actual Execution Plan
Common indicators of problems:
| Indicator | Meaning |
|---|
| Table Scan | No useful index found |
| Key Lookup | Index exists but not covering |
| Hash Join | Large join operation |
| High Cost Operator | Needs optimization |
In our case, the execution plan shows:
Step 2: Check Indexes
Indexes dramatically improve speed by avoiding full table scans.
Create an index:
CREATE INDEX IX_Stockline_Status_PartNumber
ON Stockline (Status, PartNumber);
But partial search with %BRAKE% makes the index less useful.
Better pattern: avoid leading wildcard.
Instead of:
'%BRAKE%'
Try:
'BRAKE%'
If business requires substring searching, use:
Full-Text search
Search indexing engine
CREATE FULLTEXT INDEX ON Stockline(PartNumber)
KEY INDEX PK_Stockline;
Step 3: Avoid SELECT *
Selecting only required columns reduces:
IO reads
Memory use
Network transfer cost
Rewrite:
SELECT
s.StocklineId,
s.PartNumber,
s.CreatedDate,
w.WarehouseName
FROM Stockline s
JOIN Warehouse w ON s.WarehouseId = w.WarehouseId
WHERE s.PartNumber LIKE 'BRAKE%'AND s.Status = 'Active'ORDER BY s.CreatedDate DESC;
Step 4: Use Proper Filtering Order and Predicates
SQL Server uses sargable (Search ARGument Able) conditions, meaning they can utilize an index.
Bad (non-sargable):
WHERE YEAR(CreatedDate) = 2024
Better:
WHERE CreatedDate BETWEEN '2024-01-01' AND '2024-12-31'
Bad:
WHERE CONVERT(VARCHAR, PartNumber) = 'BRAKE100'
Better:
WHERE PartNumber = 'BRAKE100'
Step 5: Index JOIN Columns
Since WarehouseId is used for join, indexing improves join performance:
CREATE INDEX IX_Stockline_WarehouseId ON Stockline(WarehouseId);
Step 6: Use Temporary Tables When Needed
Complex reports sometimes benefit from staging:
SELECT StocklineId, PartNumber
INTO #ActiveStock
FROM Stockline
WHERE Status = 'Active';
SELECT *FROM #ActiveStock s
JOIN Warehouse w ON s.WarehouseId = w.WarehouseId;
Step 7: Analyze Missing Index Recommendations
SQL Server may suggest indexes.
Enable:
SELECT *
FROM sys.dm_db_missing_index_details;
Important: Do not blindly apply all recommendations; evaluate first.
Step 8: Reduce Row Counts Using Pagination
Instead of loading millions of rows:
SELECT ...
ORDER BY CreatedDate DESCOFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;
Step 9: Avoid Functions in WHERE Clause
Bad:
WHERE LOWER(PartNumber) = LOWER('BRAKE100');
Better:
WHERE PartNumber = 'BRAKE100';
Store consistent casing if needed.
Step 10: Use Appropriate Data Types
Misaligned types cause implicit conversions.
Example:
WHERE StockId = '1001'; -- string used on int column
Fix:
WHERE StockId = 1001;
Performance Troubleshooting Checklist
| Question | Yes → Fix Needed |
|---|
| Does query use SELECT *? | Yes |
| Are filters non-sargable? | Yes |
| Is indexing missing on JOIN/WHERE columns? | Yes |
| Does execution plan show Table Scan? | Yes |
| Are functions used inside WHERE? | Yes |
| Filters cause implicit conversion? | Yes |
Refactored Final Optimized Query
SELECT
s.StocklineId,
s.PartNumber,
s.CreatedDate,
w.WarehouseName
FROM Stockline s
INNER JOIN Warehouse w
ON s.WarehouseId = w.WarehouseId
WHERE s.PartNumber LIKE 'BRAKE%'AND s.Status = 'Active'ORDER BY s.CreatedDate DESCOFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;
After improvements:
| Metric | Before | After |
|---|
| Execution Time | 18s | 120ms |
| Logical IO Reads | 945,000 | 12,300 |
| App Latency | Timeout | Fast |
Summary
Query optimization is a critical skill for backend, database, and full-stack developers. Most performance issues arise from:
By applying indexing strategy, careful query structuring, and proper analysis, even large datasets can perform efficiently and scale smoothly.