Azure SQL Database query tuning and system performance tuning Interview Questions

Mohammad Gufran Jahangir January 28, 2025 0

Here are some interview questions related to Azure SQL Database query tuning and system performance tuning, along with sample answers:

Table of Contents

Answer:

Inefficient Queries: Poorly written SQL queries, lack of proper indexing, or too many joins.
Lack of Indexes: Missing indexes or unused indexes that increase query cost.
High Resource Utilization: Excessive CPU, memory, or IO usage.
Parameter Sniffing: Cached execution plans that don’t perform well for all parameters.
Concurrency Issues: Blocking, deadlocks, and contention for resources.
Suboptimal Query Plans: Poor execution plans due to outdated statistics or query structure.

Answer:

Use Query Performance Insights in the Azure portal to find high-CPU or long-running queries.
Query the sys.dm_exec_query_stats DMV to review query execution statistics.
Check sys.dm_db_index_operational_stats for index usage and performance issues.
Enable Query Store to capture query execution details and identify regressions.

Answer:

Analyze Execution Plan:
- Use the “Actual Execution Plan” to identify costly operations like table scans or sorts.
Index Optimization:
- Create missing indexes or modify existing ones.
- Use sys.dm_db_missing_index_details to identify missing indexes.
Rewrite Query:
- Simplify joins, subqueries, or complex logic.
- Replace SELECT * with specific columns.
Parameterization:
- Use parameterized queries to leverage plan reuse and avoid parameter sniffing.
Statistics Update:
- Run UPDATE STATISTICS to ensure query optimizer has up-to-date information.
Use Hints:
- Use query hints (e.g., OPTION (RECOMPILE) or FORCE INDEX) judiciously.
Partitioning:
- Partition large tables to improve performance of data reads and writes.

Answer:

Query Performance Insight: Visualize query performance metrics and identify slow queries.
Query Store: Monitor query execution plans and identify regressions.
Dynamic Management Views (DMVs):
- sys.dm_exec_query_stats for query performance.
- sys.dm_db_index_usage_stats for index usage.
Intelligent Insights: Detect and diagnose performance issues automatically.
Azure SQL Database Advisor:
- Provides recommendations for index creation or removal.
Automatic Tuning:
- Features like “Force Plan” or “Create Index” can optimize queries automatically.

Answer:

Use OPTION (RECOMPILE) to generate a new execution plan for each query execution.
Use OPTIMIZE FOR UNKNOWN to create a generic plan.
Implement query hints to guide the query optimizer.
Rewrite the query to use local variables, which prevent parameter sniffing.

Answer:

Monitor CPU/IO Utilization:
- Use Azure Monitor and set alerts for high resource usage.
Scaling Resources:
- Scale up/down compute tier (e.g., DTU or vCore models).
Elastic Pools:
- Use elastic pools to manage resource usage across multiple databases.
Throttling:
- Avoid throttling by optimizing queries and scaling appropriately.
Resource Governance:
- Use Resource Governor to allocate resources effectively for critical workloads.

Answer:

DTUs (Database Transaction Units):
- A blended measure of compute, memory, and IO resources.
- Higher DTU levels provide better performance for resource-intensive workloads.
vCores (Virtual Cores):
- Provides dedicated CPU cores, memory, and storage, offering better customization and transparency.
Impact:
- DTU model is simpler to manage but less flexible.
- vCore model is suitable for workloads requiring predictable performance.

Answer:

Blocking:
- Identify blocking queries using sys.dm_tran_locks.
- Optimize queries to minimize lock contention.
- Use NOLOCK or READ COMMITTED SNAPSHOT isolation levels where appropriate.
Deadlocks:
- Enable Deadlock Graph in SQL Server Profiler or Azure Monitor logs.
- Identify the root cause and refactor the queries.
- Minimize lock time and avoid circular dependencies in queries.

Answer:

Query Store is a feature in Azure SQL Database that captures query performance and execution plans.
It helps:
- Identify slow or regressed queries.
- Compare historical query performance.
- Force stable query execution plans for consistent performance.
Enable it using: ALTER DATABASE CURRENT SET QUERY_STORE = ON; Query insights can be accessed in the Azure portal or via DMVs like sys.query_store_plan.

Answer:

Identify Missing Indexes: SELECT * FROM sys.dm_db_missing_index_details;
Identify Unused Indexes: SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks = 0 AND user_scans = 0;
Fix:
- Create missing indexes as suggested by the DMVs.
- Drop unused indexes that consume storage and degrade write performance.

Answer:

Answer:

Use Azure Monitor to track IO-related metrics like read/write latency and throughput.
Query DMVs like: SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL);
Optimize by:
- Avoiding large table scans.
- Partitioning large tables or using clustered indexes.

Category: