Showing posts with label SQL Performance Tuning —1. Show all posts
Showing posts with label SQL Performance Tuning —1. Show all posts

Thursday, August 20, 2026

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';