Writing a working query is easy.
Writing a fast and scalable query is a different skill.

In Microsoft SQL Server, query performance depends on how efficiently SQL Server can read, filter, and join data.

This guide covers advanced techniques used in real production systems.

---

1️⃣ Understand the Execution Plan First

Before optimizing anything:

✔ Check Actual Execution Plan
✔ Identify expensive operations

Look for:

❌ Table Scan
❌ Index Scan on large tables
❌ Key Lookup
❌ High cost operators

📌 Never optimize blindly.

---

2️⃣ Reduce Data Early (Filtering Strategy)

Always filter data as early as possible.

❌ Bad

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2025;

✅ Good

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01';

✔ Uses index
✔ Reduces rows early

---

3️⃣ Avoid SELECT *

Selecting unnecessary columns increases IO.

❌ Bad

SELECT *
FROM Orders;

✅ Good

SELECT OrderId, Amount
FROM Orders;

✔ Faster
✔ Less memory usage

---

4️⃣ Optimize Joins Properly

Joins are one of the biggest performance factors.

Best Practices

✔ Index join columns
✔ Use correct join type
✔ Avoid unnecessary joins

---

Example Optimization

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

---

5️⃣ Eliminate Key Lookups

Key lookup = extra read operation.

Solution → Covering Index

CREATE INDEX IX_Orders_UserId
ON Orders(UserId)
INCLUDE (OrderId, Amount);

✔ Eliminates lookup
✔ Improves performance

---

6️⃣ Use EXISTS Instead of IN (for Large Data)

❌ IN (slower for large data)

SELECT *
FROM Orders
WHERE UserId IN (SELECT UserId FROM Users);

✅ EXISTS

SELECT *
FROM Orders O
WHERE EXISTS (
    SELECT 1
    FROM Users U
    WHERE U.UserId = O.UserId
);

✔ Better for large datasets

---

7️⃣ Avoid Functions on Indexed Columns

❌ Bad

WHERE YEAR(OrderDate) = 2024;

✅ Good

WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

📌 Functions prevent index usage.

---

8️⃣ Use Proper Data Types

Wrong data types cause implicit conversions.

❌ Bad

WHERE UserId = '100';

✅ Good

WHERE UserId = 100;

✔ Enables index usage

---

9️⃣ Use Temp Tables for Complex Queries

Break complex queries into steps.

SELECT *
INTO #TempOrders
FROM Orders
WHERE OrderDate > '2025-01-01';

Then process further.

✔ Improves readability
✔ Helps optimizer

---

🔟 Use Window Functions Efficiently

Window functions are powerful but expensive.

Example:

SELECT 
    UserId,
    ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY OrderDate) AS RowNum
FROM Orders;

✔ Use only when necessary
✔ Combine with indexes

---

1️⃣1️⃣ Avoid Cursors (Use Set-Based Logic)

❌ Cursor (slow)

DECLARE cursor_example CURSOR FOR
SELECT UserId FROM Users;

✅ Set-based

UPDATE Users
SET Status = 'Active'
WHERE LastLogin > GETDATE() - 30;

✔ Much faster
✔ Better scalability

---

1️⃣2️⃣ Handle Parameter Sniffing

Use techniques like:

✔ Local variables
✔ OPTION (RECOMPILE)

OPTION (RECOMPILE);

📌 Useful for variable data patterns.

---

1️⃣3️⃣ Optimize Aggregations

❌ Bad

SELECT SUM(Amount)
FROM Orders;

(Without index)

✅ Better

CREATE INDEX IX_Orders_Amount ON Orders(Amount);

✔ Speeds up aggregation

---

1️⃣4️⃣ Real Production Example

❌ Problem

Query taking 20 seconds

🔍 Issue

• Table scan
• Key lookup
• Missing index

✅ Fix

CREATE INDEX IX_Orders_UserId
ON Orders(UserId)
INCLUDE (Amount, OrderDate);

Result

20 sec → 80 ms

---

1️⃣5️⃣ Optimization Checklist

Before finalizing any query:

✔ Check execution plan
✔ Avoid SELECT *
✔ Use proper indexes
✔ Remove unnecessary joins
✔ Avoid functions in WHERE
✔ Validate data types

---

✍️ Conclusion

Advanced query optimization is about:

✔ Understanding how SQL Server works
✔ Reducing unnecessary work
✔ Helping the optimizer choose better plans

Mastering these techniques makes you:

✔ Faster at debugging
✔ Better at performance tuning
✔ Highly valuable in production systems

---