Interview Questions on Explain Plan in Azure SQL Database

Interview Questions on Explain Plan in Azure SQL Database

Explain Plan (also known as Execution Plan) in Azure SQL Database helps in understanding how the SQL Server optimizer processes a query. It provides insights into query performance, indexes, join strategies, and cost estimations.

---

1. What is an Execution Plan in SQL Server and Azure SQL Database?

Answer:

• An execution plan is a roadmap that SQL Server’s query optimizer uses to execute a SQL query.
• It provides details on operations like table scans, index seeks, joins, sorting, and aggregations.
• It helps identify performance bottlenecks.

Types of Execution Plans:

1. Estimated Execution Plan – Generated without executing the query.
2. Actual Execution Plan – Generated after query execution, showing runtime statistics.

---

2. How do you generate an Execution Plan in Azure SQL Database?

Answer:

1. Using SSMS (SQL Server Management Studio):
   • Press Ctrl + M and execute the query.
   • Click “Display Estimated Execution Plan” (Ctrl + L).
2. Using Query Hints: SET SHOWPLAN_XML ON; GO SELECT * FROM Customers WHERE CustomerID = 100; GO SET SHOWPLAN_XML OFF; This provides the execution plan without running the query.
3. Using Query Store in Azure SQL Database:
   • Go to Azure Portal → SQL Database → Query Performance Insight → Execution Plan.

---

3. What are the key components of an Execution Plan?

Answer:

• Clustered Index Scan: Full table scan on a clustered index.
• Clustered Index Seek: Efficient lookup in a clustered index.
• Non-Clustered Index Scan: Full scan of a non-clustered index.
• Non-Clustered Index Seek: Optimized search in a non-clustered index.
• Nested Loop Join: Best for small data sets, iterates over each row.
• Merge Join: Used when both inputs are sorted.
• Hash Join: Best for large, unsorted data sets.
• Sort Operator: High cost if no suitable index exists.
• Parallelism (Parallel Execution): Indicates SQL Server is using multiple threads.

---

4. What is the difference between a Clustered Index Scan and a Clustered Index Seek?

Answer:

• Clustered Index Scan:
  • The entire index is scanned.
  • Happens when there is no filter condition or index is missing.
  • High IO and CPU cost.
• Clustered Index Seek:
  • Optimized lookup for a specific row range.
  • Uses a WHERE clause with indexed columns.
  • More efficient than a scan.

Example:

-- Causes a Clustered Index Scan (Bad Performance)
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;

-- Uses Clustered Index Seek (Better Performance)
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

Solution: Ensure appropriate indexing and avoid functions on indexed columns.

---

5. How do you identify missing indexes using Execution Plan?

Answer:

• The execution plan may display a Missing Index Recommendation.
• Use DMV Query to list missing indexes: SELECT * FROM sys.dm_db_missing_index_details;
• Create missing indexes based on recommendations: CREATE INDEX IX_OrderDate ON Orders(OrderDate);

---

6. How do you use Query Store to analyze Execution Plans in Azure SQL Database?

Answer:

• Query Store tracks query execution over time and helps analyze regressions.
• Enable Query Store: ALTER DATABASE myDatabase SET QUERY_STORE = ON;
• View execution plan history: SELECT * FROM sys.query_store_plan;
• Identify regressed queries and force a stable plan: EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

---

7. What is Parameter Sniffing, and how does it affect Execution Plans?

Answer:

• SQL Server caches the first execution plan based on the first parameter it encounters.
• Future executions may use an inefficient plan if data distribution varies.
• Example of Parameter Sniffing Issue:CREATE PROCEDURE GetOrders @CustomerID INT AS BEGIN SELECT * FROM Orders WHERE CustomerID = @CustomerID; END
  • If @CustomerID = 1 retrieves millions of rows, SQL Server may generate a scan plan.
  • If @CustomerID = 500 retrieves only 5 rows, the same scan plan is inefficient.

Solutions:

1. Use OPTION (RECOMPILE): SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);
2. Use OPTIMIZE FOR UNKNOWN: SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR UNKNOWN);

---

8. What is a Hash Join, and when does it appear in an Execution Plan?

Answer:

• Hash Join is used when two large, unsorted tables are joined.
• It builds a hash table on the smaller dataset and scans the larger dataset.
• It appears when:
  • There are no indexes on join columns.
  • The optimizer determines it’s better than a Nested Loop or Merge Join.

Example:

SELECT Orders.*, Customers.*
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Optimization:

• Add indexes on CustomerID.
• Use proper filtering to reduce dataset size.

---

9. What are Query Execution Operators, and which ones indicate performance issues?

Answer:

• Key Operators in Execution Plans:
  • Table Scan: Indicates missing indexes.
  • Index Seek: Efficient operation.
  • Sort: High cost if no index exists.
  • Hash Match: Indicates large joins; may need indexes.
  • Parallelism: High CPU usage.

Example:

SELECT * FROM Orders WHERE OrderDate = '2023-01-01';

If Table Scan occurs, adding an index on OrderDate will improve performance.

---

10. Scenario Question:

You have a query that performs well in dev but is slow in production. How would you analyze the Execution Plan?

Answer:

1. Compare Execution Plans:
   • Use Query Store in both environments.
   • Retrieve query plan from sys.dm_exec_query_stats.
2. Check Statistics:
   • If production data volume is larger, stale statistics may lead to poor plan selection.
   • Update statistics: UPDATE STATISTICS Orders;
3. Look for Parameter Sniffing:
   • If different parameter values cause different plans, use OPTION (RECOMPILE).
4. Check Indexes:
   • Ensure indexes exist in production.

---

11. Scenario Question:

A query is using a Nested Loop Join, but performance is slow. How would you optimize it?

Answer:

1. Check Data Size:
   • Nested Loop is optimal for small tables.
   • If large tables are involved, consider a Hash Join.
2. Create Indexes:
   • Ensure indexes exist on join columns.
3. Force a Hash Join if Needed: SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID OPTION (HASH JOIN);

---

12. Scenario Question:

Your query is scanning a table when it should be seeking. How do you fix it?

Answer:

1. Identify Scan in Execution Plan:
   • If a Clustered Index Scan appears, check indexing.
2. Create an Index: CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
3. Use Index Hint: SELECT * FROM Orders WITH (INDEX (IX_Orders_CustomerID)) WHERE CustomerID = 100;

---