Below is a complete, practical, real-world SQL Server performance-tuning case study.
Each step includes the original slow query, root cause, fix, and final optimized version.
1. Slow Query Due to Missing Index
Problem Query
SELECT * FROM Orders WHERE CustomerId = 4521;
Root Cause
Full table scan → no index on CustomerId.
Fix
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
Optimized Query
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 4521;
2. Query Using SELECT * with Wide Table
Problem Query
SELECT * FROM Stockline WHERE QuantityOnHand > 0;
Root Cause
Unnecessary column reads + more I/O.
Fix
Select only the required columns.
Optimized Query
SELECT StockLineId, PartNumber, QuantityOnHand
FROM Stockline
WHERE QuantityOnHand > 0;
3. OR Conditions Preventing Index Usage
Problem Query
SELECT * FROM Users
WHERE Email = '[email protected]' OR Phone = '9999999999';
Root Cause
OR breaks index seek.
Fix
Use UNION ALL.
Optimized Query
SELECT UserId, Name, Email FROM Users WHERE Email = '[email protected]'
UNION ALL
SELECT UserId, Name, Phone FROM Users WHERE Phone = '9999999999';
4. Function on Indexed Column
Problem Query
SELECT * FROM Invoice WHERE YEAR(InvoiceDate) = 2025;
Root Cause
YEAR() disables index seek.
Fix
SELECT * FROM Invoice
WHERE InvoiceDate >= '2025-01-01' AND InvoiceDate < '2026-01-01';
5. Inefficient JOIN on Unindexed Foreign Keys
Problem Query
SELECT o.OrderId, c.Name
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId;
Root Cause
Missing FK index on Orders.CustomerId.
Fix
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
6. Large IN Clauses (10K IDs)
Problem Query
SELECT * FROM Sales WHERE SaleId IN (1,2,3 ...10000);
Root Cause
IN with big lists causes heavy parsing + suboptimal plans.
Fix
Store values in temp table.
Optimized Query
CREATE TABLE #Ids (Id INT PRIMARY KEY);
-- Bulk insert list here
SELECT s.*
FROM Sales s
JOIN #Ids i ON s.SaleId = i.Id;
7. Scalar User-Defined Function in SELECT
Problem Query
SELECT OrderId, dbo.GetProfit(OrderId)
FROM Orders;
Root Cause
Scalar UDF = row-by-row execution.
Fix
Convert scalar UDF into inline table-valued function.
Optimized Query
CREATE FUNCTION dbo.GetProfit_Inline()
RETURNS TABLE
AS
RETURN
(
SELECT OrderId, (Total - Cost) AS Profit FROM Orders
);
SELECT o.OrderId, p.Profit
FROM Orders o
JOIN dbo.GetProfit_Inline() p ON o.OrderId = p.OrderId;
8. GROUP BY on Massive Dataset Without Index
Problem Query
SELECT CustomerId, SUM(TotalAmount)
FROM Orders
GROUP BY CustomerId;
Root Cause
No index on CustomerId → full scan + heavy sort.
Fix
CREATE INDEX IX_Orders_CustomerId_Total ON Orders(CustomerId, TotalAmount);
9. Multiple Nested Subqueries
Problem Query
SELECT *
FROM Orders
WHERE OrderId IN (
SELECT OrderId FROM Payments WHERE Status = 'Failed'
);
Root Cause
Unoptimized subquery.
Fix
SELECT o.*
FROM Orders o
JOIN Payments p ON o.OrderId = p.OrderId
WHERE p.Status = 'Failed';
10. Missing Pagination (Returns Millions of Rows)
Problem Query
SELECT * FROM Logs ORDER BY LogDate DESC;
Root Cause
Returns entire log table → too slow.
Fix
Use OFFSET–FETCH.
Optimized Query
SELECT LogId, Message, LogDate
FROM Logs
ORDER BY LogDate DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;
Conclusion: Key Takeaways
Always check execution plan first.
Eliminate table scans wherever possible.
Avoid functions on indexed columns.
Add proper covering indexes.
Replace scalar UDFs with inline TVFs.
Use pagination for large datasets.
Rewrite subqueries using JOINs where appropriate.