Troubleshooting interviews are where a lot of otherwise strong candidates stumble — not because they don’t know the DMVs or the metrics, but because they jump straight to a fix without describing how they’d actually confirm the problem first. Good troubleshooting is a process, and interviewers can tell within thirty seconds whether you have one or you’re pattern-matching to a symptom you’ve seen before.
Split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually talk it through in a room — the diagnostic reasoning behind each answer, not just a tool name.
One framing worth carrying through the whole list: almost every good troubleshooting answer follows the same shape — confirm the symptom with objective data, localize it to a specific resource or query, form a hypothesis, test it cheaply, then apply the narrowest fix that addresses the actual cause. The specific tools change question to question; that shape doesn’t.
Beginner Level
1. A user says “the database is slow.” What’s your first step? Confirm it with objective data before assuming anything — check sys.dm_db_resource_stats or the Azure Monitor metrics for the time window they’re describing, to see whether there was actually a resource spike (CPU, I/O, memory) at that time, rather than immediately jumping into query tuning based on a vague description alone.
2. What is sys.dm_db_resource_stats, and why is it usually the first thing you check? It’s a DMV specific to Azure SQL Database that reports CPU%, data I/O%, log write%, and memory% at 15-second intervals for the last hour — the fastest way to confirm, directly via T-SQL, whether a reported slowness actually correlates with genuine resource pressure, and if so, which resource.
3. How do you check what’s currently running on an Azure SQL Database right now? sys.dm_exec_requests shows currently executing requests, including their status, wait type, and how long they’ve been running — the standard first stop for “what’s happening on this database right now” during a live incident.
4. What’s the difference between checking sys.dm_exec_requests and looking at Query Store for a performance issue? sys.dm_exec_requests shows what’s happening right now, in real time — useful for catching something actively stuck or long-running. Query Store shows historical performance over time, useful for confirming whether a query’s current behavior is actually different from its normal baseline, or spotting a trend rather than a single live snapshot.
5. A connection attempt is failing. What are the first few things you’d check? Firewall rules (is the connecting IP actually allowed), whether the credentials/authentication method are correct, whether the server name is right (a surprisingly common typo, especially the .database.windows.net suffix), and the specific error message itself, since Azure SQL’s connection error messages are usually fairly specific about which of these is the actual problem.
6. What is blocked_by_firewall, and how would you use it to diagnose connection issues? It’s a metric that specifically counts connection attempts rejected by firewall rules — checking it quickly confirms or rules out “the firewall is the problem” as a hypothesis, rather than guessing between a firewall issue and an authentication issue when troubleshooting a failed connection.
7. What does a rising connection_failed metric trend usually indicate? Could be several things — throttling due to hitting session or worker limits for the service tier, firewall misconfiguration, or an application-side issue like a connection leak — but a rising trend specifically (rather than a one-off blip) points toward something systemic worth investigating rather than a transient network hiccup.
8. How do you check if a database has hit its session or worker limit? The sessions_percent and workers_percent metrics show how close the database is to its service tier’s hard concurrency caps — if either is at or near 100%, new connections will be flatly rejected regardless of how much CPU or DTU headroom exists, which is a completely different problem than a resource bottleneck.
9. What’s the difference between a database being “slow” and a database being “unavailable”? Slow means it’s responding, just not quickly — a performance problem. Unavailable means connections are failing outright — often a completely different category of problem (firewall, authentication, resource exhaustion hitting hard limits, or a genuine platform issue) requiring a different diagnostic starting point than a pure performance investigation would.
10. What is Resource Health, and when would you check it during troubleshooting? It reports whether the specific database resource itself is Available, Degraded, or Unavailable, with a health history timeline — a fast way to confirm or rule out “is the platform itself reporting a problem with my specific database” early in an investigation, before spending time on query-level analysis that would be irrelevant if the resource itself is unhealthy.
11. What’s the difference between Resource Health and Service Health, and which would you check first during an outage? Resource Health is specific to your database resource; Service Health reports broader Azure platform incidents across regions and services. I’d generally check Resource Health first since it’s specific to what’s actually reported as impacted, but if that shows Available and the user still can’t connect, Service Health helps rule out (or confirm) a broader regional issue that Resource Health alone might not fully capture depending on the failure mode.
12. How would you check for recent deadlocks on an Azure SQL Database? Via the Deadlocks diagnostic category if routed to Log Analytics (queryable with KQL), or through Extended Events captured directly, showing the deadlock graph — which tells you exactly which sessions and resources were involved, not just that a deadlock happened.
13. What’s the first thing to check when a query that used to run fast suddenly runs slow? Query Store — specifically, whether the query now has a different execution plan than it used to, and whether that plan change correlates with when the slowdown started. This is the classic signature of a plan regression, and Query Store is purpose-built to surface exactly this comparison.
14. What is a timeout error, and what’s a common first-pass diagnostic step? A timeout means the client gave up waiting for a response within its configured timeout period — not necessarily that the query would never have finished. I’d check whether the query was actually still running (via sys.dm_exec_requests) or blocked (waiting on another session) at the time of the timeout, since those point toward different root causes.
15. What does it mean if sys.dm_exec_requests shows a query with a wait type of LCK_M_X (or similar LCK_M_*)? The session is waiting to acquire a lock that another session currently holds — a blocking scenario, not a resource exhaustion problem. The next step is identifying the head blocker — the session actually holding that lock — rather than assuming it’s a resource capacity issue.
16. What’s a basic first step for troubleshooting a storage-full or near-full alert? Check the storage_percent metric trend to see how quickly it’s actually growing and confirm the alert is current, then look for the obvious usual suspects — recent large data loads, index rebuild operations temporarily consuming extra space, or (if in Full recovery model) an unexpectedly large or unmanaged transaction log — rather than assuming it’s simply organic table growth without checking.
17. What tools are available for troubleshooting Azure SQL Database given there’s no OS-level or server-level access the way there is on-prem? Query Store and its DMVs, sys.dm_exec_requests for live activity, sys.dm_db_resource_stats for resource pressure, Azure Monitor metrics and diagnostic logs, Resource Health and Service Health, and Query Performance Insight in the portal — a genuinely capable toolkit, just accessed differently than an on-prem Perfmon/Profiler-based workflow.
Intermediate Level
18. Walk through your process for diagnosing intermittent slowness that’s hard to reproduce on demand. I’d start by getting as specific a timeframe as possible from whoever reported it, then check sys.dm_db_resource_stats (or Azure Monitor metrics, for a longer historical window) for that exact period across all four resource dimensions — CPU, data I/O, log I/O, memory — since intermittent slowness that doesn’t show up in a live check is often something that only appears at specific load moments. If resource metrics don’t show an obvious spike, I’d pivot to Query Store’s wait statistics view for that window, since blocking or a specific query’s plan variance can cause intermittent slowness without necessarily showing up as an overall resource ceiling being hit.
19. How would you diagnose a blocking chain, and specifically identify the actual head blocker rather than just the longest-waiting session? sys.dm_exec_requests combined with sys.dm_os_waiting_tasks lets you trace the blocking chain — each blocked session’s blocking_session_id points to what it’s waiting on, and following that chain (a session can be blocked by a session that’s itself blocked by another) leads to the actual head blocker, the one session at the root that isn’t itself waiting on anyone else. It’s a common mistake to just kill the longest-waiting session, when the actual head blocker further up the chain is the real problem to address.
20. What’s the difference between diagnosing a blocking issue versus a deadlock, and why does that distinction matter for your approach? Blocking is one session waiting for another to release a lock — it resolves on its own once the blocking session finishes or is killed, and the diagnostic goal is finding and addressing the head blocker. A deadlock is a genuine mutual lock cycle the engine detects and resolves itself by killing one of the participants automatically — you’re not intervening live to break a deadlock, you’re investigating after the fact (via the deadlock graph) to understand why it happened and prevent recurrence, which is a fundamentally different, more retrospective kind of investigation.
21. How would you diagnose whether a query timeout is caused by the query genuinely being slow versus the query being blocked? Check sys.dm_exec_requests for the session at (or near) the time of the timeout — if its wait_type shows a lock-related wait (LCK_M_*), it was blocked, and the fix is addressing the blocker. If it shows I/O or CPU-related waits, or no wait at all (actively executing), the query itself was genuinely working and just slow, pointing toward query tuning or resource investigation instead — the same symptom (a timeout) has two genuinely different root causes and diagnostic paths.
22. What’s your approach to diagnosing a sudden spike in LOG_RATE_GOVERNOR waits? This wait means the workload is being throttled against the service tier’s transaction log write throughput cap, which is Azure-specific and doesn’t exist on-prem. I’d correlate the timing against recent workload changes (a new bulk load, index rebuild, or ETL job) via Query Store or Activity Log, confirm the correlation against the log_write_percent metric, and then decide between reducing log-generating activity (smaller batched transactions, minimal logging where applicable) or a genuine scale action if the elevated log throughput need is real and expected to persist.
23. How would you troubleshoot a database that’s hitting its sessions_percent or workers_percent limit even though CPU and DTU both look healthy? This points toward a connection management problem rather than a query performance problem — I’d check for a connection leak in the application (connections opened but never properly closed or returned to a pool), verify the application’s connection pooling configuration is actually being used correctly, and confirm whether the current tier’s session/worker limits are genuinely being outgrown by legitimate load versus being consumed by connections that shouldn’t still be open.
24. What’s your diagnostic approach for a query that has a good estimated execution plan but performs poorly when actually run? This is the classic signature of a cardinality estimation problem — comparing the actual execution plan’s real row counts at each operator against the estimated plan’s predicted row counts usually reveals a significant mismatch, which points toward stale statistics, parameter sniffing, or a complex predicate the optimizer can’t estimate accurately. I’d check statistics freshness first (a quick, low-effort check) before assuming something more complex.
25. How would you diagnose whether a performance problem is caused by the query itself versus genuine resource contention from other workload on the same database (or elastic pool)? sys.dm_db_resource_stats shows overall resource pressure at the database level; if it’s elevated broadly (not just during this one query’s execution), and especially if the database is in an elastic pool, I’d check whether other databases in the same pool are experiencing simultaneous load — a query that performs fine in isolation but poorly under real concurrent load often points toward shared-resource contention (memory grants, tempdb, pool-level capacity) rather than a problem with the query’s own logical plan.
26. What’s your approach to troubleshooting tempdb pressure in Azure SQL Database, given you can’t add tempdb files manually the way you would on-prem? Check tempdb_data_size/tempdb_log_size metrics for actual pressure, and PAGELATCH waits on tempdb system pages as a corroborating signal. Since file configuration isn’t something you control directly, the diagnostic goal shifts to identifying which specific query or queries are driving excessive tempdb usage — sort/hash spills from memory grant issues, heavy temp table or table variable use — via Query Store, since reducing the workload’s tempdb footprint is the available lever, not adding capacity to tempdb itself.
27. How would you diagnose a memory grant issue causing queries to spill to tempdb? sys.dm_exec_query_memory_grants shows currently executing queries’ requested versus granted memory, and comparing that against actual usage can reveal under-granted queries spilling to disk. Query Store’s tracked memory grant history for a specific query over time also shows whether Memory Grant Feedback has been adjusting the grant, and whether that adjustment has actually stabilized the query’s spill behavior or not.
28. What’s your process for troubleshooting a database that suddenly shows as suspect or in an unexpected state? This is a drop-everything situation — I’d check the error log (via diagnostic logs routed to Log Analytics, filtered to Errors category) immediately for the specific reason behind the state change, rather than attempting any corrective action first, since the appropriate fix depends entirely on the actual cause (which can range from a transient platform issue that self-resolves to something requiring an actual restore).
29. How would you troubleshoot a failed or stuck scaling operation (service tier change)? Check the operation’s status via the portal’s activity log or PowerShell/CLI for the specific error returned, since a stuck or failed scale operation usually has a specific, surfaced reason (insufficient quota in the target tier, an incompatible combination of settings, or occasionally a transient platform issue) — I’d avoid immediately retrying blindly without first understanding why it failed, since retrying the same failing operation without addressing the underlying cause just reproduces the same failure.
30. What’s your approach when a stored procedure that normally runs fine suddenly runs slowly only for specific parameter values? This is a strong signal of parameter sniffing — I’d compare the currently cached plan (via sys.dm_exec_query_plan for the procedure’s cached plan handle) against what Query Store shows as having performed well historically for different parameter value ranges, confirming whether a single compiled plan optimized for one value range is now being reused for a very different value range it wasn’t well-suited for.
31. How would you diagnose whether an elastic pool’s databases are experiencing noisy-neighbor contention? I’d check each member database’s individual resource consumption relative to the pool’s total capacity and each database’s configured min/max eDTU/vCore bounds — if one or a few databases are consistently consuming a disproportionate share, especially bumping against their configured max, that’s the noisy-neighbor signature, distinguishable from genuine pool-wide capacity exhaustion where most or all member databases show elevated pressure simultaneously.
32. What’s your approach to troubleshooting a linked query or Elastic Query performance problem? Elastic Query’s performance profile is meaningfully different from a local query — I’d check how much filtering is happening remotely at the source database versus how much data is being pulled back and processed locally, since a poorly filtered Elastic Query can pull far more data across the network than necessary, and that data movement, not local query execution, is often the actual bottleneck worth investigating first.
33. How would you diagnose a sudden increase in PAGEIOLATCH waits? This points toward I/O contention — physical read waits, typically from data not being found in buffer pool memory and needing to be read from storage. I’d check physical_data_read_percent to confirm I/O saturation relative to the service tier’s limit, and cross-reference with recent query changes or data growth that might explain why more physical reads are suddenly needed than the buffer pool can absorb from cache.
34. What’s your approach when Automatic Tuning has applied a change and performance got worse afterward, not better? I’d check the tuning recommendation history to confirm exactly what was applied and when, correlate that timestamp precisely against when performance actually degraded to confirm causation, and if confirmed, manually revert the specific action (dropping an automatically created index, or un-forcing a plan) rather than assuming the system’s own self-monitoring will necessarily catch and revert it fast enough — while also flagging it for review, since a case where Automatic Tuning’s own validation didn’t catch a regression it caused is worth understanding, not just working around.
35. How would you approach troubleshooting a performance issue that only your monitoring shows, but the application team insists users haven’t reported anything? I’d treat this as useful information, not a reason to drop the investigation — a metric-level regression that hasn’t yet produced a user-visible complaint might still be an early warning worth addressing proactively before it does become user-visible, especially if the trend is worsening. I’d present the objective data plainly and let it inform priority rather than either dismissing the finding because no one’s complained yet, or overstating urgency the data doesn’t yet support.
Advanced Level
36. Walk through a complete diagnostic process for a production incident where users report the application is “completely down,” and initial checks show the database is healthy. I’d start by confirming what “down” actually means from the user’s perspective — a hard connection error, a hang, or an application-level error that isn’t actually a database problem at all — since Resource Health showing Available and connections succeeding via SSMS strongly suggests the issue is elsewhere in the path: application-tier connection pool exhaustion, a firewall or network change between the app and the database, an expired Azure AD token if using that authentication method, or a DNS/private endpoint resolution issue. I’d work outward from the database rather than assuming the database is the problem just because it’s the visible shared dependency everyone points to first.
37. Explain how you’d diagnose a case where sys.dm_db_resource_stats shows normal resource utilization, but users are still experiencing genuine slowness. This rules out classic resource exhaustion as the cause, which redirects the investigation toward session/worker limits (sessions_percent/workers_percent, a completely different constraint than CPU/IO/memory), blocking (which doesn’t necessarily show up as elevated resource consumption, since a blocked session is waiting, not consuming CPU), network latency between the application and the database rather than the database itself being slow, or application-side issues (connection pool exhaustion, inefficient application-level data processing after retrieval) masquerading as a database problem. The key insight: normal resource metrics don’t rule out a performance problem, they rule out a specific category of performance problem, narrowing rather than closing the investigation.
38. How would you diagnose a performance regression that correlates with an Azure platform-level event (a patch or maintenance window), rather than any change you made? I’d first confirm via Service Health whether a platform maintenance event genuinely occurred in that window, then compare Query Store data immediately before and after that window for the affected queries — a platform-level engine update occasionally does shift optimizer behavior for specific query patterns, which would show up as a plan change with no corresponding change on your end. If confirmed, FORCE LAST GOOD PLAN (via Automatic Tuning or manually) is often the fastest mitigation while you separately assess whether the new plan is a genuine, permanent regression worth deeper investigation or something that self-resolves as statistics or cache state stabilizes post-maintenance.
39. Walk through diagnosing a case where a query performs well when run directly in SSMS but poorly when executed by the application. This is a classic parameter sniffing or SET options mismatch signature — the application likely uses different connection-level settings (ARITHABORT, ANSI_NULLS, or similar) than SSMS’s defaults, which can produce a different cached plan for what looks like the identical query text, or the application is passing genuinely different parameter values than your manual test, hitting a different, worse-suited cached plan. I’d capture the actual query and parameter values the application is sending (via Extended Events or Query Store, matching on the actual application-originated execution) rather than assuming your manual SSMS test is representative of what the application is really doing.
40. How would you troubleshoot a scenario where CPU utilization looks fine in Azure Monitor, but sys.dm_exec_query_stats shows individual queries with very high CPU time? This apparent contradiction usually means the high-CPU queries aren’t running frequently enough, or concurrently enough, to move the overall aggregate CPU% metric meaningfully — a query that’s individually expensive but runs once a day isn’t going to show up as a sustained CPU utilization problem in an hourly-granularity metric view, even though it’s clearly worth tuning on its own merits. I’d treat aggregate resource metrics and per-query cost data as answering different questions — “is the database under overall pressure” versus “which specific queries are expensive” — rather than expecting one to always corroborate the other.
41. Explain your approach to diagnosing intermittent connection failures that don’t correlate with any resource metric spike, firewall change, or Service Health incident. This is genuinely one of the harder categories, and I’d widen the investigation beyond the database itself — checking for DNS resolution issues or TTL-related caching problems on the client side, transient network path issues between the client and Azure (which wouldn’t necessarily show up in Azure-side monitoring at all since the problem might be on the client’s network path), or connection pool exhaustion/misconfiguration on the application side manifesting as intermittent failures that look database-related but genuinely aren’t. I’d also check whether the failures correlate with connection retry storms — a client aggressively retrying failed connections can itself cause session/worker limit pressure that then causes further failures, a self-reinforcing pattern worth specifically ruling in or out.
42. How would you diagnose a case where Query Store itself appears to be missing data for a period when you know queries were executing? Check Query Store’s actual configured capture mode and size limits — if Query Store hit its configured max size and is in read-only mode, or if the capture policy is set to sample rather than capture all queries, gaps in captured data are expected behavior, not a bug. I’d verify the current Query Store configuration (ACTUAL_STATE, size quota, capture policy) via sys.database_query_store_options before assuming there’s a genuine data loss problem, since a misconfigured or maxed-out Query Store is a surprisingly common and easily-overlooked cause of “missing” historical data during an investigation.
43. Walk through diagnosing a performance problem specific to a read-scale-out secondary replica, where the primary performs fine. Since secondaries in Business Critical/Premium tiers are asynchronously updated relative to the primary, I’d first check replication lag between primary and secondary — a lagging secondary can present as “slow” from the application’s perspective even if it’s really a staleness/consistency issue rather than a raw performance problem. If lag is genuinely low and the secondary is still measurably slower for equivalent queries, I’d consider whether the read-only workload routed there has a fundamentally different access pattern than what the primary’s Automatic Tuning history has optimized for, since indexing decisions are typically driven by primary-side Query Store data.
44. How would you approach diagnosing a case where a database’s storage is growing much faster than expected, and it’s not obviously explained by application data growth? I’d check for index fragmentation and recent rebuild operations (which temporarily, and sometimes not-so-temporarily if not followed by proper space reclamation, increase storage footprint), transaction log growth if the database is in Full recovery without adequate log backup frequency relative to write volume, tempdb spill activity if that’s somehow being conflated with primary database storage in the investigation, and Query Store’s own size if it’s been allowed to grow to a large configured maximum without regular cleanup — storage growth in Azure SQL has several possible sources beyond raw table data, and I wouldn’t assume it’s simply “the application is writing more data” without checking each of these.
45. Explain how you’d diagnose a scenario where a database is performing poorly specifically after a scale-up operation, which should intuitively make performance better, not worse. This is counter-intuitive enough that I’d specifically check whether the scale operation actually completed and took effect (via dtu_limit/cpu_limit metrics confirming the new ceiling is genuinely active, not still showing the old limit), whether the scale operation caused a plan cache flush that’s now forcing fresh compilations and potentially different — and possibly worse — plan choices under the new resource profile, and whether NUMA or core-count changes from the new tier have shifted parallelism behavior for specific queries in an unexpected way. A scale-up genuinely making things worse is rare but not impossible, and it’s worth actually confirming the change took effect correctly before assuming the scale-up itself is paradoxically the problem.
46. How would you diagnose a subtle data consistency issue reported by users, where the same query seems to return different results depending on when or where it’s run? I’d first check whether read scale-out is in play and the discrepancy correlates with primary versus secondary replica routing, since replication lag can genuinely cause this exact symptom in an application not designed to tolerate eventual consistency for that specific query. If read scale-out isn’t the explanation, I’d check the transaction isolation level being used — under READ COMMITTED SNAPSHOT (Azure SQL Database’s default), concurrent modifications during a long-running read can produce a result reflecting a consistent snapshot from query start, which can look surprising to someone expecting to always see the very latest committed state.
47. Walk through your approach to diagnosing a performance problem that only manifests under a very specific, hard-to-reproduce concurrency pattern. I’d try to capture the actual production conditions during a real occurrence — via Extended Events with a targeted filter, or Query Store’s captured wait statistics for that specific window — rather than attempting to reproduce it synthetically first, since an artificial load test often fails to replicate the exact concurrency and data-distribution conditions that trigger the real issue. Once I have real captured evidence from an actual occurrence, I’d look specifically for resource contention patterns (memory grant competition, tempdb contention, lock escalation thresholds being crossed) that only emerge under genuine concurrent load, which isolated single-session testing wouldn’t surface.
48. How would you approach diagnosing a mysterious, sporadic spike in Automatic Tuning activity — many recommendations or actions appearing at once, unexpectedly? I’d check whether a recent bulk data load, major schema change, or significant shift in query patterns (a new application feature going live, for instance) recently occurred, since a genuine, sudden change in workload characteristics is exactly the kind of event that would legitimately trigger a burst of new Query Store-driven recommendations all at once — this is often the system correctly reacting to a real underlying change, not a malfunction, and I’d confirm that underlying cause before treating the burst of activity itself as the problem to fix.
49. Explain how you’d approach a case where multiple, seemingly unrelated symptoms (some slow queries, some connection failures, some elevated storage) all appeared around the same time — do you treat them as separate problems or look for a common cause? I’d resist immediately treating them as three separate investigations and instead look for a plausible common trigger first — a recent deployment, a scale event, a burst in overall traffic, or a specific large batch job — since seemingly unrelated symptoms clustering in the same time window is a meaningful signal worth checking before assuming coincidence. If a genuine common cause explains all three, that’s a much more efficient (and often more accurate) diagnosis than independently root-causing three separate things that were actually downstream effects of the same underlying event.
50. Walk through a genuinely difficult troubleshooting incident you’ve handled or would expect to handle, end to end. Intentionally open-ended, because interviewers are evaluating your process under real ambiguity, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) confirm the symptom with objective data before acting on a secondhand description, (2) localize the problem to a specific resource dimension, query, or component rather than treating “the database” as a monolith, (3) form a specific, testable hypothesis about root cause rather than trying random fixes, (4) validate that hypothesis cheaply before committing to a fix — a targeted query, a quick DMV check — rather than applying a change and hoping, (5) apply the narrowest fix that addresses the confirmed cause, and (6) validate afterward that the fix actually worked under real conditions, not just that the symptom happened to stop. Interviewers consistently favor candidates who describe validating their hypothesis before acting, and validating the fix worked afterward, over candidates who jump straight from symptom to fix without either check — that gap is usually exactly what separates someone who’s genuinely troubleshot production incidents from someone who’s only read about how to.
A Closing Thought
Every advanced answer above comes back to the same discipline: confirm before you localize, localize before you hypothesize, and hypothesize before you fix — skipping any step in that order is usually where a troubleshooting effort goes sideways, whether in an interview or in a real 2 AM incident.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
