SQL Performance Tuning —1

 

1. A query is taking 10 minutes. What will you check first?

Answer: Check the execution plan, indexes, joins, filters, row counts, statistics, and whether unnecessary data is being scanned.

---

2. What is an Execution Plan?

Answer: It shows how the database optimizer plans to execute a query, including scans, seeks, joins, sorts, and estimated costs.

---

3. What is the difference between Index Seek and Index Scan?

Index Seek: Retrieves specific matching rows efficiently.

Index Scan: Reads a large portion or all of an index/table.

Interview point: A scan isn't always bad; it can be appropriate for small tables or when most rows are needed.

---

4. What is a Table Scan?

Answer: Reading the entire table to find required rows. It can be expensive on large tables.

---

5. What is an Index?

Answer: A data structure that helps the database locate rows faster without scanning the entire table.

---

6. Why can too many indexes hurt performance?

Answer: Every INSERT, UPDATE, and DELETE may need to maintain those indexes, increasing storage and write overhead.

---

7. Clustered vs Non-Clustered Index?

Clustered: Determines the physical/logical storage order of table rows in systems such as SQL Server.

Non-clustered: Separate structure containing indexed keys and row locators.

---

8. Can a table have multiple clustered indexes?

Answer: No. A table can have only one clustered index because the data can have only one clustered ordering.

---

9. What columns should be indexed?

Usually columns frequently used in:

• WHERE
• JOIN
• ORDER BY
• GROUP BY

But actual benefit should be confirmed with workload and execution plans.

---

10. Why is this query potentially inefficient?

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

Answer: Applying a function to the indexed column can prevent an efficient index access path.

Better:

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