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:
- Estimated Execution Plan – Generated without executing the query.
- Actual Execution Plan – Generated after query execution, showing runtime statistics.
2. How do you generate an Execution Plan in Azure SQL Database?
Answer:
- Using SSMS (SQL Server Management Studio):
- Press
Ctrl + M
and execute the query. - Click “Display Estimated Execution Plan” (
Ctrl + L
).
- Press
- 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. - 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.
- If
Solutions:
- Use OPTION (RECOMPILE):
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);
- 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:
- Compare Execution Plans:
- Use Query Store in both environments.
- Retrieve query plan from
sys.dm_exec_query_stats
.
- Check Statistics:
- If production data volume is larger, stale statistics may lead to poor plan selection.
- Update statistics:
UPDATE STATISTICS Orders;
- Look for Parameter Sniffing:
- If different parameter values cause different plans, use
OPTION (RECOMPILE)
.
- If different parameter values cause different plans, use
- 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:
- Check Data Size:
- Nested Loop is optimal for small tables.
- If large tables are involved, consider a Hash Join.
- Create Indexes:
- Ensure indexes exist on join columns.
- 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:
- Identify Scan in Execution Plan:
- If a Clustered Index Scan appears, check indexing.
- Create an Index:
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
- Use Index Hint:
SELECT * FROM Orders WITH (INDEX (IX_Orders_CustomerID)) WHERE CustomerID = 100;