SQL Performance Tuning —3 (Aggregation & Sorting)

 

21. Why can GROUP BY be slow?

Large datasets may require significant sorting or hashing.

Improve by:

• Filtering earlier
• Indexing appropriately
• Reducing unnecessary columns
• Aggregating at the correct grain

---

22. Why can ORDER BY be expensive?

Answer: Sorting a large result set requires CPU and memory and may spill to disk.

---

23. Why can GROUP BY and ORDER BY together be expensive?

Both may require processing large amounts of data, especially if the database cannot use an appropriate access path.

---

24. How do you optimize a query returning millions of rows?

First question: Does the application really need millions of rows?

Then consider:

• Filtering
• Pagination
• Aggregation
• Partitioning
• Appropriate indexes
• Result-set reduction

---

25. What is pagination?

Returning data in smaller chunks instead of retrieving the entire result set.

Example:

OFFSET 1000 ROWS
FETCH NEXT 100 ROWS ONLY;

---

Indexing — Advanced

26. What is a composite index?

An index containing multiple columns.

CREATE INDEX IX_Orders
ON Orders(CustomerID, OrderDate);

---

27. Does column order matter in a composite index?

Yes.

For:

(CustomerID, OrderDate)

queries filtering by CustomerID can generally benefit more directly than queries filtering only by OrderDate.

---

28. What is a covering index?

An index that contains all columns needed by a query, allowing the database to satisfy the query from the index without additional table lookups.

---

29. What is an included column?

In SQL Server, columns can be added to a nonclustered index as included columns without becoming part of the index key.

Example:

CREATE INDEX IX_Orders_Customer
ON Orders(CustomerID)
INCLUDE (OrderDate, Amount);

---

30. Why isn't my index being used?

Possible reasons:

• Table is small
• Query returns a large percentage of rows
• Predicate isn't index-friendly
• Wrong column order
• Data type conversion
• Stale statistics
• Another access path is cheaper