Here are scenario-based interview questions related to Azure SQL Database query tuning and system performance tuning, along with guidance on how to answer:

1. Scenario: High CPU Usage

Question: You notice that CPU utilization on your Azure SQL Database is consistently above 90%. How would you identify the root cause and resolve it?

Answer:

1. Identify High-CPU Queries:
   • Use Query Performance Insight in the Azure portal or query sys.dm_exec_query_stats to find the top CPU-consuming queries.
   SELECT TOP 10 qs.total_worker_time / qs.execution_count AS AvgCPUTime, SUBSTRING(qt.text, qs.statement_start_offset / 2, (qs.statement_end_offset - qs.statement_start_offset) / 2) AS QueryText FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY AvgCPUTime DESC;
2. Resolve Issues:
   • Optimize high-CPU queries: Add missing indexes, rewrite complex joins, or reduce table scans.
   • Update statistics: Ensure the query optimizer has up-to-date information.
   • Scale up to a higher service tier if optimization does not resolve the issue.
3. Monitor Resource Usage:
   • Use Azure Monitor to set alerts for CPU usage thresholds.
   • Review Query Store to check for regressed queries.

2. Scenario: Slow Query Performance

Question: A query that used to run in 10 seconds now takes over 2 minutes to execute. How would you troubleshoot and fix it?

Answer:

1. Analyze Execution Plan:
   • Review the query’s current execution plan using SSMS or Query Store to identify bottlenecks like table scans or missing indexes.
2. Check Query Changes:
   • Confirm if the query logic or parameters were changed recently, causing a suboptimal execution plan.
3. Investigate Parameter Sniffing:
   • If parameter sniffing is suspected, use OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN to generate a new plan.
   SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);
4. Update Statistics and Indexes:
   • Ensure table statistics are up-to-date.
   UPDATE STATISTICS TableName;
   • Rebuild fragmented indexes.
   ALTER INDEX ALL ON TableName REBUILD;
5. Test Alternative Query Logic:
   • Rewrite or simplify the query to improve performance.

3. Scenario: Deadlocks

Question: Users report that their queries intermittently fail due to deadlocks. How would you handle and prevent deadlocks?

Answer:

1. Capture Deadlock Details:
   • Enable Deadlock Graph in Azure SQL Database.
   • Query sys.event_log to identify deadlocks.
   SELECT * FROM sys.event_log WHERE event_type = 'deadlock';
2. Analyze Root Cause:
   • Identify conflicting queries and the resources they are locking.
3. Resolve Issues:
   • Optimize queries to acquire locks in the same order.
   • Use READ COMMITTED SNAPSHOT isolation level to reduce locking.
   ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;
4. Implement Retry Logic:
   • Add retry logic in the application for transient deadlock errors.

4. Scenario: Spool Issues

Question: A user reports spool errors while running a query on a large table. How would you address this?

Answer:

1. Investigate Query:
   • Review the query to identify potential inefficiencies like missing WHERE clauses or excessive joins.
2. Optimize Indexes:
   • Add indexes to reduce temporary storage requirements (spool operations).
   • Use the sys.dm_db_missing_index_details DMV to identify missing indexes.
3. Partition Large Tables:
   • Partition the table to split data into smaller, more manageable segments.
4. Monitor TempDB:
   • Check if TempDB is being exhausted and scale the database to add more TempDB resources.

5. Scenario: Query Store Regression

Question: A query’s performance has regressed significantly after a recent database update. How would you fix this?

Answer:

1. Check Query Store:
   • Use Query Store to identify the regressed query and its historical execution plans.
2. Force a Good Plan:
   • If a previous execution plan performed better, force it using Query Store.
   EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
3. Analyze Execution Plan Changes:
   • Identify why the new plan is suboptimal (e.g., statistics, parameter sniffing).
4. Fix Underlying Issues:
   • Update statistics, add indexes, or refactor the query.
5. Validate Changes:
   • Test query performance with the changes applied.

6. Scenario: Missing Indexes

Question: Users complain about slow query performance, and you suspect missing indexes. How would you proceed?

Answer:

1. Identify Missing Indexes:
   • Use the sys.dm_db_missing_index_details DMV.
   SELECT * FROM sys.dm_db_missing_index_details;
2. Create Missing Indexes:
   • Add the recommended indexes after reviewing the impact on writes.
   CREATE NONCLUSTERED INDEX IX_Table_Column ON TableName (ColumnName);
3. Monitor Index Usage:
   • Use sys.dm_db_index_usage_stats to ensure the indexes are being used.
4. Review Index Maintenance:
   • Regularly rebuild or reorganize indexes to avoid fragmentation.

7. Scenario: Resource Bottlenecks

Question: Your Azure SQL Database is experiencing slow performance due to resource bottlenecks (CPU, memory, or IO). How would you address it?

Answer:

1. Identify Bottlenecks:
   • Use Query Performance Insight or query sys.resource_stats to analyze resource usage.
   SELECT * FROM sys.resource_stats WHERE database_name = 'MyDatabase';
2. Optimize Queries:
   • Identify top resource-consuming queries and optimize them.
3. Scale Resources:
   • Scale up the database to a higher tier or vCore model.
4. Review Application Logic:
   • Minimize unnecessary queries and batch operations.

8. Scenario: TempDB Usage

Question: Your application frequently runs into TempDB contention issues. How would you resolve this?

Answer:

1. Monitor TempDB Usage:
   • Use sys.dm_db_resource_stats to check TempDB usage.
2. Optimize Queries:
   • Reduce operations that depend on TempDB (e.g., large sort operations or hash joins).
3. Scale Up:
   • Increase the service tier to provide more TempDB resources.
4. Partition Tables:
   • Partition large tables to reduce TempDB dependency during queries.

Mohammad Gufran Jahangir