Optimization interviews are a different animal from general “what is Azure SQL” interviews. Nobody’s testing whether you know what a firewall rule is anymore — they want to know if you can actually make a slow database fast, and more importantly, whether you understand why it was slow in the first place.
This list is built around that reality. It’s split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), and every answer is written the way you’d actually explain it to someone across the table — not a textbook definition, but the reasoning a good DBA carries in their head while troubleshooting.
One piece of advice before you dive in: optimization interviews reward people who ask “what would you check first?” out loud, even when answering a scripted question. Interviewers notice when your answer shows a process, not just a fact.
Beginner Level
1. What does “query optimization” actually mean in the context of Azure SQL? It means getting a query to use the fewest possible resources — CPU, I/O, memory — to return the correct result, as fast as possible, and consistently, not just once by luck. In Azure specifically, it also means being mindful that resources are metered and capped by your service tier, so a badly optimized query doesn’t just run slow — it can eat into the budget every other database in your pool depends on.
2. What is an execution plan, and why do you need one to optimize a query? It’s the roadmap the SQL engine chooses to actually retrieve your data — which indexes it uses, in what order it joins tables, whether it scans or seeks. Without looking at the plan, tuning a query is guesswork; with it, you can see exactly where the time and resources are going, like a scan on a huge table where a seek should be happening.
3. What’s the difference between an estimated and an actual execution plan? The estimated plan is what the optimizer thinks will happen, based on statistics, before the query runs. The actual plan shows what really happened, including real row counts at each step. When those two numbers are wildly different, that’s usually your root cause — stale statistics or a parameter-sniffing issue.
4. What is an index, and why does it matter for performance? An index is a separate, ordered structure built on one or more columns that lets the engine find rows without scanning the entire table. Without the right index, even a simple WHERE clause forces a full table scan — fine on a thousand rows, painful on a hundred million.
5. What’s the difference between a clustered and a non-clustered index? A clustered index defines the physical order the table’s data is stored in — there can only be one per table, because data can only be sorted one way. A non-clustered index is a separate structure that points back to the data, and you can have many of them, each optimized for a different query pattern.
6. What is a missing index recommendation, and can you always trust it? Azure SQL (via the engine’s missing index feature) tracks queries that would have benefited from an index that doesn’t exist, and surfaces suggestions. It’s a good starting point, not gospel — it doesn’t account for write overhead, doesn’t consolidate overlapping suggestions, and can recommend redundant indexes if you blindly apply every single one it lists.
7. What is a table scan, and why is it usually considered bad? A table scan reads every single row in a table to find the ones that match your query. It’s not always bad — for a small table, or a query that genuinely needs most of the rows, a scan can actually be cheaper than a seek. But on a large table with a selective filter, a scan means the engine is doing far more work than necessary.
8. What is the difference between a seek and a scan? A seek uses an index to jump directly to the rows you need, similar to using a book’s index to go straight to a page. A scan reads through rows sequentially, checking each one. Seeks are typically far cheaper when you only need a small fraction of the table’s rows.
9. What are statistics in SQL Server/Azure SQL, and why do they matter for optimization? Statistics are lightweight summaries of data distribution in a column — used by the optimizer to estimate how many rows a query will return, which drives every decision after that (join order, index choice, memory grant). Out-of-date statistics lead to bad row estimates, which lead to bad plans, even when your indexes are perfectly fine.
10. What is Query Store, and how do you use it for optimization? Query Store is a built-in feature that automatically captures query text, plans, and runtime performance over time, right inside the database. It’s usually the very first place I look for optimization work — instead of guessing what’s slow, you can directly ask it “show me the queries with the highest total CPU over the last 24 hours.”
11. What is Query Performance Insight, and who is it useful for? It’s a portal-based visual layer built on top of Query Store, showing top resource-consuming queries over time without writing a single line of KQL or T-SQL. It’s especially useful for someone newer to Azure SQL who isn’t confident querying Query Store’s DMVs directly yet.
12. What is a covering index? An index that contains every column a query needs — either in the key or as an included column — so the engine can satisfy the entire query from the index alone, without going back to the actual table (a “key lookup”). It’s one of the single most effective, and most underused, optimization techniques.
13. What’s the difference between an included column and a key column in an index? Key columns determine the sort order of the index and can be used for seeking/filtering/sorting. Included columns just ride along in the index’s leaf level for retrieval purposes — they make an index covering without bloating the key or affecting sort order, and they’re usually the cheaper way to widen an index for coverage.
14. What is a key lookup, and why does it hurt performance? When a non-clustered index doesn’t contain all the columns a query needs, the engine seeks the index, then does a separate lookup back into the clustered index (or heap) for each matching row to fetch the rest. On a query returning many rows, that’s a lot of extra random I/O — usually a strong signal you need a covering index.
15. What’s the difference between the DTU and vCore models from an optimization perspective? DTU blends CPU, memory, and I/O into one number, which means you can’t necessarily tell which resource a slow query is actually starving on without extra digging. vCore separates them, and gives you a clearer, more direct signal — a query might be memory-starved while CPU sits idle, and vCore metrics make that visible in a way DTU’s single blended number doesn’t.
16. What is a fragmented index, and how do you fix it? Fragmentation happens when pages in an index become disordered or partially empty over time (from inserts, updates, deletes), forcing the engine to read more pages than necessary. You fix it by reorganizing (lighter, for 10–30% fragmentation) or rebuilding (heavier, for >30%) the index — Azure SQL supports both, and you can even automate this via Azure SQL’s maintenance features or your own scheduled job via Elastic Jobs.
17. What tools can you use inside Azure SQL Database to check for slow queries, given there’s no server-level access? Query Store and its DMVs (sys.query_store_query, sys.query_store_runtime_stats), sys.dm_exec_query_stats, sys.dm_exec_requests for what’s running right now, sys.dm_db_resource_stats for resource pressure, and the portal’s Query Performance Insight and Performance Recommendations blades — all still fully available even without OS or instance-level access.
Intermediate Level
18. Walk through your general process for optimizing a slow query. First, confirm it’s actually the query and not resource contention — check sys.dm_db_resource_stats or the portal metrics for the time window. Then pull the actual execution plan, not just estimated, and look for the biggest cost operators — scans on large tables, sorts, key lookups, and spills. Compare estimated vs actual row counts to catch cardinality estimation issues. From there, decide if the fix is an index, a statistics update, a rewrite, or in rare cases a plan hint — in roughly that order of preference, cheapest and least invasive first.
19. What is parameter sniffing, and how do you fix it in Azure SQL? The optimizer compiles a plan based on the parameter values used the first time a query runs, then reuses that cached plan for subsequent executions — even if a wildly different parameter value would have benefited from a different plan. Fixes include OPTION (RECOMPILE) for genuinely volatile queries (at the cost of extra compilation overhead), OPTIMIZE FOR a representative value, local variables to intentionally get an “average case” plan, or letting Automatic Tuning’s FORCE LAST GOOD PLAN catch and revert the regression automatically if it recurs.
20. What’s the difference between a plan cache issue and a genuine query performance issue? A plan cache issue means the query itself is fine, but the cached plan is wrong for the current parameters or data distribution — clearing that specific plan or forcing a recompile solves it instantly. A genuine performance issue means even the best possible plan for that query is still going to be slow, usually because of missing indexes, bad schema design, or a query doing more logical work than necessary — no amount of recompiling fixes that.
21. How do you identify and fix plan regressions using Query Store? Query Store’s Regressed Queries view compares a query’s recent performance against a historical baseline and flags queries that got worse. Once you find one, you can inspect the different plans Query Store has captured for that query over time, and either manually force the better historical plan or let Automatic Tuning do it — then keep watching to confirm the forced plan actually holds up under real traffic.
22. What is a memory grant, and how can it hurt performance if sized wrong? The optimizer estimates how much memory a query needs (for sorts, hashes) based on row estimates, and reserves that memory upfront. Underestimate it, and the query spills to tempdb disk — slow. Overestimate it, and you’re holding memory hostage that other concurrent queries can’t use, potentially causing RESOURCE_SEMAPHORE waits across the whole workload.
23. What is Memory Grant Feedback, and how does it help? Part of Intelligent Query Processing — the engine remembers how much memory a query actually used across executions and adjusts future grants accordingly, instead of relying purely on the original compile-time estimate every single time. It directly reduces both spill-to-disk problems and memory over-allocation, without you writing a single hint.
24. How would you diagnose a tempdb bottleneck in Azure SQL Database, given you can’t add tempdb files manually? Check tempdb_data_size and tempdb_log_size metrics for pressure, and cross-reference with PAGELATCH waits on tempdb system pages. Since you don’t control file layout the way you would on-prem, your lever is reducing the workload’s tempdb footprint — find and fix the queries causing excessive sort/hash spills or heavy temp table/table variable use via Query Store, rather than trying to add files yourself.
25. What’s the difference between reorganizing and rebuilding an index, from a performance-impact perspective? Reorganize is always online and lower-impact, defragmenting leaf-level pages incrementally, but it doesn’t update statistics with a full scan and isn’t as thorough for heavily fragmented indexes. Rebuild fully recreates the index, is more effective, and can run online (ONLINE = ON) in most editions, but it’s a heavier operation and briefly needs more log and temp space — worth scheduling during lower-traffic windows even when online.
26. How do you use sys.dm_db_resource_stats in a real optimization scenario? It gives 15-second interval snapshots of CPU%, data I/O%, log write%, and memory% for the last hour, directly via T-SQL. When someone reports “it was slow around 2 PM,” I query this DMV filtered to that window first — it immediately tells me whether the bottleneck was actually resource contention (and which resource) before I go anywhere near a specific query’s execution plan.
27. What is a wait statistic, and how do you use wait stats to prioritize optimization work? Wait stats record what the engine’s worker threads were waiting on instead of doing active work — I/O, locks, memory grants, parallelism coordination, and so on. Instead of tuning queries randomly, you look at the top wait categories first, because they tell you objectively where the system-wide time is actually going, and you tune whatever’s driving the biggest wait category first for the best return on effort.
28. Explain LOG_RATE_GOVERNOR waits and how you’d address them. This is an Azure-specific wait that shows up when your workload is being throttled against your service tier’s transaction log write throughput cap — something that simply doesn’t exist as a concept on-prem. Fixes include reducing log-generating activity (batching large operations into smaller transactions, minimal logging for bulk loads where possible) or, if the sustained log throughput demand is genuinely expected long-term, scaling to a tier with a higher log rate limit.
29. What is a covering index strategy, and how do you decide which columns to include vs key? Put the columns used in equality/range filters and sorting as key columns, in the order the query actually filters and sorts by, and put the remaining columns the query merely selects (but doesn’t filter or sort on) as included columns. This keeps the index efficient for seeking while still avoiding a key lookup — a common mistake is putting every column in the key, which bloats the index and hurts write performance for no query benefit.
30. How do you balance index optimization against write performance? Every index speeds up reads but adds overhead to every insert/update/delete that touches the indexed columns. I look at actual index usage stats (sys.dm_db_index_usage_stats) before adding anything new, remove indexes that aren’t being used by reads but are still absorbing write cost, and consolidate near-duplicate indexes rather than accumulating a new one for every slightly different query pattern.
31. What is Adaptive Query Processing / Intelligent Query Processing, and how does it change how you optimize queries in Azure SQL? It’s a set of engine-level features — Adaptive Joins, Memory Grant Feedback, Table Variable Deferred Compilation, Batch Mode on Rowstore, among others — that let the engine self-correct at runtime instead of committing fully to a compile-time estimate. Practically, it means some classic manual workarounds (like avoiding table variables because of the “always estimate 1 row” problem) matter less than they used to, though understanding the underlying issue is still worth knowing for cases IQP doesn’t fully cover.
32. How would you troubleshoot excessive blocking in Azure SQL Database? Check sys.dm_exec_requests and sys.dm_os_waiting_tasks (or the Blocks diagnostic category via KQL if routed to Log Analytics) to identify the head blocker — the session actually holding the lock everyone else is waiting on, not just the longest-waiting session. From there, look at whether it’s a long-running transaction, a missing index causing an unnecessarily wide lock footprint, or an isolation level choice that could be relaxed (like using READ COMMITTED SNAPSHOT, which Azure SQL Database has enabled by default, to reduce reader/writer blocking).
33. What is READ COMMITTED SNAPSHOT, and why does it matter for Azure SQL optimization specifically? It’s a row-versioning-based isolation level that lets readers see a consistent snapshot of data without taking shared locks, dramatically reducing reader/writer blocking. Azure SQL Database actually enables this by default for new databases, which is a meaningful behavioral difference from on-prem SQL Server, where it’s off by default — something migrated workloads sometimes don’t realize has changed.
34. How do you approach optimizing a query with a large OR condition or many parameter combinations? OR conditions often prevent efficient index seeks because the optimizer may fall back to a scan to cover all branches. I’d look at rewriting as a UNION ALL of separately seekable conditions, each with its own supporting index, or use OPTION (RECOMPILE) combined with dynamic SQL if the parameter combinations genuinely need different plans per call — the right answer really depends on whether the underlying data distribution differs meaningfully across those branches.
35. What’s the role of Automatic Tuning in an ongoing optimization strategy, and where does it fall short? It handles the “obvious win” tier of optimization well — regressions Query Store can clearly detect, missing indexes with strong evidence behind them. Where it falls short is anything requiring judgment about trade-offs it can’t see — write overhead of a new index on a high-throughput table, business context about which query actually matters most, or schema-level redesign. I treat it as a continuous safety net underneath manual tuning, not a replacement for it.
Advanced Level
36. How would you design an ongoing, proactive query optimization program for a large, high-traffic Azure SQL estate, rather than reactive firefighting? Route QueryStoreRuntimeStatistics to Log Analytics across the fleet, then build a scheduled KQL-based process comparing each query’s rolling baseline against recent performance to catch regressions before users notice. Pair that with a monthly index usage/redundancy review (via sys.dm_db_index_usage_stats fleet-wide), and a lightweight review gate on Automatic Tuning’s applied actions so nothing silent slips through unnoticed on the most critical databases. The goal is to shift the team’s time from “why is it slow right now” to “here’s what’s trending toward slow next month.”
37. Explain how Adaptive Joins work internally, and give a scenario where they specifically help. Adaptive Join lets the optimizer defer the choice between a nested loop join and a hash join until runtime, based on the actual number of rows flowing into the join rather than the compile-time estimate — it uses a threshold row count with both plan branches embedded and picks at execution. This directly helps parameterized queries where row counts vary wildly call to call — say, a report filtered by date range that sometimes returns 50 rows and sometimes 5 million — where a single compiled choice between loop and hash join would be wrong a meaningful fraction of the time.
38. Describe how you’d diagnose and fix a tempdb spill problem that only occurs under concurrent load, not in isolated testing. This usually means the query is fine in isolation but the memory grant it gets under real concurrency is smaller than what it tested with, because Azure SQL’s resource governor is dividing available memory across more concurrently executing queries. I’d check sys.dm_exec_query_memory_grants during a real incident window, correlate with Memory Grant Feedback’s adjusted grant history in Query Store, and consider whether the fix is query-level (reduce the memory the query actually needs, via better filtering or a covering index reducing sort width) or capacity-level (the tier genuinely doesn’t have enough memory headroom for this concurrency level).
39. How does Hyperscale’s architecture change your approach to optimization compared to General Purpose or Business Critical? Because Hyperscale separates compute from a distributed, page-server-based storage layer, I/O characteristics behave differently — read scale-out replicas are asynchronous and serve a different purpose than Business Critical’s synchronous HA replicas, and very large tables benefit less predictably from traditional index strategies tuned against a single-attached-storage mental model. I’d pay closer attention to page server cache behavior and be more deliberate about testing index changes at realistic scale before assuming General Purpose intuition transfers directly.
40. Walk through how you’d use Extended Events (or their Azure SQL equivalent) for deep query optimization work. Azure SQL Database supports a subset of Extended Events via the sqlserver provider, capturing events like query_post_execution_showplan, rpc_completed, and sql_batch_completed with predicates to filter noise, streamed to an Event File target in Azure Blob Storage since there’s no local disk. I’d use this when Query Store’s aggregated view isn’t granular enough — for example, capturing full runtime plans for a specific problematic stored procedure under real production parameters, rather than relying on the sampled or trimmed plan XML Query Store sometimes stores for very large plans.
41. How would you approach optimization for a workload with severe parameter sniffing across many different stored procedures, at scale? Rather than fixing each procedure individually with OPTION (RECOMPILE) (which has real CPU cost at scale from constant recompilation), I’d first quantify how many procedures are actually affected using Query Store’s variation data, then consider database-scoped configuration options like PARAMETER_SNIFFING = OFF selectively, or Query Store-driven FORCE LAST GOOD PLAN as the automated first line of defense, reserving manual per-procedure hints for the handful of genuinely high-value, high-variance queries where the automated safety net isn’t precise enough.
42. Explain the trade-offs of Batch Mode on Rowstore, and when it actually helps. Batch Mode processes rows in batches of roughly 900 rather than one at a time, dramatically reducing CPU overhead for large aggregations and scans — originally a columnstore-only feature, extended to rowstore tables via Intelligent Query Processing. It helps most on analytical-style queries scanning and aggregating large row counts; it doesn’t meaningfully help highly selective OLTP point lookups, where the per-row overhead it optimizes away was never the bottleneck to begin with.
43. How would you design and validate an index consolidation strategy for a table with 15+ overlapping non-clustered indexes? Pull actual usage stats (seeks, scans, lookups, and — critically — updates) per index from sys.dm_db_index_usage_stats, identify indexes with near-identical leading key columns that could be merged into one wider covering index, and check for indexes with high update cost but near-zero read usage as removal candidates. I’d validate any consolidation in a pre-production environment against captured real production query patterns (via Query Store’s query text) before applying it live, since a consolidated index can accidentally change a plan for a query that depended on the specific narrower index’s statistics or selectivity.
44. Describe how you’d optimize a query that performs fine individually but degrades the whole database’s throughput under concurrency. This points toward a shared-resource bottleneck rather than a query-plan problem — likely lock contention, tempdb contention, or memory grant competition rather than the query’s own logical cost. I’d check sys.dm_os_wait_stats system-wide (not just for that one query) during the degraded window, look for lock escalation on the table this query touches, and consider whether the fix is reducing this query’s resource footprint, changing isolation level behavior, or in some cases genuinely needing more concurrency headroom via a scale action if the workload growth is real and sustained.
45. How does Query Store’s Wait Statistics view differ from traditional server-wide wait stats analysis, and how do you use it for optimization? Traditional wait stats analysis (sys.dm_os_wait_stats) gives you a system-wide, cumulative-since-restart picture with no per-query attribution. Query Store’s wait stats capture ties specific wait categories to specific queries and time windows, which means you can directly answer “which query is actually causing these lock waits” instead of inferring it indirectly — a meaningfully faster path from symptom to root cause during live troubleshooting.
46. What’s your approach to optimizing a workload that mixes heavy OLTP and heavy analytical/reporting queries on the same Azure SQL Database? I’d first consider whether they genuinely need to share the same database at all — read scale-out routing reporting queries to a secondary replica is often the cleanest fix, isolating analytical query resource consumption from the OLTP primary entirely. If they must share, I’d look at Resource Governor-style workload isolation where available (Managed Instance supports classic Resource Governor), or at minimum ensure reporting queries use READ COMMITTED SNAPSHOT‘s row-versioning to avoid blocking the OLTP writers, and separately indexed/covering strategies tuned for each workload type rather than one index set trying to serve both.
47. How would you use sys.dm_exec_query_profiles for live, in-flight query troubleshooting, and when is it more useful than a completed execution plan? sys.dm_exec_query_profiles gives real-time, per-operator progress for a currently executing query (when SET STATISTICS PROFILE or the lightweight profiling infrastructure is active) — showing exactly which operator is currently consuming time on a long-running query that hasn’t finished yet. It’s more useful than waiting for a completed plan when you have a query that’s been running for 20 minutes and you need to decide right now whether to let it finish or kill it, because it tells you which specific operator it’s stuck on rather than making you wait for the whole thing to complete first.
48. Explain how you’d approach performance testing an index change before applying it to a large production Azure SQL Database, given you can’t easily clone a multi-terabyte database for free. I’d use Database Copy (a relatively fast, native Azure SQL operation) to a lower-tier, cost-controlled copy for realistic-scale testing rather than a full separate environment, replay captured real production query patterns from Query Store against it, and compare before/after plans and resource consumption directly. For genuinely enormous databases where even a copy is expensive, I’d lean more heavily on Query Store’s “what-if” style analysis of estimated plan changes and a very conservative, monitored production rollout with an immediate rollback plan (dropping the new index, or reverting via Automatic Tuning’s own tracked history) rather than skipping validation entirely.
49. How do you decide between fixing a performance problem with an index versus a query rewrite versus a scale action? Roughly in that order of preference, because each step up costs more and fixes less precisely: an index fixes access-pattern problems cheaply and often takes effect immediately. A rewrite is needed when the query is doing logically unnecessary work no index can fix — like an accidental cross join, or non-sargable predicates wrapping a column in a function. A scale action is the last resort, appropriate only when the workload’s aggregate resource need has genuinely grown beyond what tuning can address — not a substitute for tuning a single badly-written query, which scaling will only mask, not fix, and which will resurface the moment the workload grows again.
50. Describe a genuinely hard optimization problem you’ve solved (or would expect to solve) on Azure SQL, and walk through your reasoning end-to-end. This is intentionally open-ended — interviewers want to see structured reasoning under ambiguity, not a memorized answer. A strong structure to demonstrate regardless of the specific problem: (1) confirm the symptom is real and reproducible using objective data — Query Store, resource metrics, wait stats — not just a user’s impression, (2) localize the bottleneck to a specific resource dimension and, ideally, a specific query or query pattern, (3) form a hypothesis about root cause and test it cheaply before committing to a fix (a database copy, a targeted SET STATISTICS check, an isolated repro), (4) apply the narrowest effective fix, in the index-before-rewrite-before-scale order, and (5) validate the fix actually held under real production load afterward, not just in the test that justified it. Interviewers remember candidates who describe validating their fix worked, far more than candidates who just describe applying one.
A Closing Thought
The pattern running through almost every advanced answer here is the same: check the data before you guess, fix the cheapest thing that actually addresses the root cause, and validate afterward rather than assuming. That’s not interview-specific advice — it’s genuinely how good optimization work gets done, which is exactly why it keeps showing up as the right answer.
If you’re prepping alongside the general Azure SQL interview list or the DP-300 material, this one is the depth layer underneath both — worth going through slowly, one tier at a time, rather than skimming all fifty in one sitting.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
