SQL Query Optimization Interview Questions for .NET Architects

Q1 How do you decide what goes in a clustered index versus a nonclustered index?#

Short answer: The clustered index defines the physical storage order of the entire table — there can be only one — so it should be narrow, ideally unique or near-unique, and ever-increasing or at least insertion-friendly, because every nonclustered index on the table carries the clustering key as its row locator, and a wide or volatile clustering key bloats and fragments every other index too.

A surrogate IDENTITY/SEQUENCE integer or a sequential GUID as the clustering key keeps new rows appending at the end of the B-tree, avoiding the page splits and fragmentation you get from inserting into the middle of a clustered index built on a random GUID or a non-monotonic natural key. Nonclustered indexes are separate B-trees pointing back to the clustered key (or a heap's row ID if there's no clustered index), and you create as many as your write throughput tolerates — each speeds up matching reads but adds write cost, since every insert/update/delete maintains every index covering the changed columns.

This decision compounds across the whole table, not just one query, which is why it belongs in a schema review rather than being bolted on later: a wide, volatile clustering key is one of the most expensive schema mistakes to walk back once a table has real data volume and a dozen dependent nonclustered indexes, since fixing it means rebuilding every index on the table, not just one.

What interviewers look for: the "the clustering key rides along in every nonclustered index" mechanism specifically — it's the detail that shows you understand why the choice matters, not just the rule of thumb.

• Common mistakes: choosing a natural key (like an email address or a composite business key) as the clustering key without considering its width and volatility; assuming every table needs an IDENTITY clustered primary key regardless of access pattern.

Q2 What makes a nonclustered index "covering," and how do you spot the need for one in an execution plan?#

Short answer: A covering index includes every column a query needs — in the key or via INCLUDE — so the engine can satisfy the query entirely from the index itself without a Key Lookup back to the clustered index for the remaining columns; in an execution plan, a Key Lookup operator sitting next to an Index Seek, often with a disproportionately high relative cost, is the signal that a covering index would help.

-- Query: filter by CustomerId, return OrderDate and Total
SELECT OrderDate, Total FROM Orders WHERE CustomerId = @CustomerId;

-- Covering index: CustomerId as the seek key, OrderDate/Total included in the leaf
CREATE INDEX IX_Orders_CustomerId_Covering
    ON Orders (CustomerId)
    INCLUDE (OrderDate, Total);

The distinction between putting a column in the index key versus in INCLUDE matters: key columns are sorted and can be used for seeking, filtering and ordering, but they also widen every level of the B-tree; INCLUDE columns are stored only at the leaf level, so they satisfy the query without the sorting/width cost of being part of the key. The rule of thumb: put columns you filter, join or sort on in the key, in selectivity order (most selective first when the query always filters on all of them together), and put columns you only need to return in INCLUDE.

Reading the plan to justify this isn't just "look for Key Lookup" — compare its estimated cost percentage against the Index Seek feeding it; when the seek itself is cheap but the lookup dominates because it's happening once per matched row, that's the exact shape a covering index eliminates, since the whole operation collapses to a single Index Seek with no lookup at all.

What interviewers look for: knowing the key-versus-INCLUDE distinction with a rationale, not just "add INCLUDE," and being able to name the specific plan operator (Key Lookup) that justifies the change.

Q3 When would you reach for a filtered index, and what limits its usefulness?#

Short answer: A filtered index is a nonclustered index built over a WHERE-clause-defined subset of rows — most commonly an active/non-deleted subset — which makes it smaller, cheaper to maintain, and lets its statistics be far more accurate for that subset than a full-table index's statistics would be, but it's only usable by a query whose predicate the optimizer can prove is a subset of the index's filter, which parameter sniffing and dynamic SQL both make harder to guarantee.

CREATE INDEX IX_Orders_Open
    ON Orders (CustomerId, OrderDate)
    WHERE Status = 'Open';

This shines on tables where one status/flag value is a small fraction of total rows but is disproportionately queried — open orders on a table dominated by historical closed orders, active users on a table full of deactivated accounts. Because the index only covers matching rows, it's dramatically smaller than an equivalent full index, which means less I/O to scan it and statistics that reflect the actual distribution of that subset rather than being diluted by the majority of rows that never match.

The limitation that catches people: the optimizer will only use a filtered index when it can statically prove your query's predicate is compatible with the index's filter — a parameterized query like WHERE Status = @status generally can't use a filtered index on WHERE Status = 'Open', because the engine can't know at compile time that @status will always be 'Open'. This makes filtered indexes a strong fit for queries with hardcoded, known predicates (a background job that always processes 'Open' orders) and a poor fit for general-purpose parameterized application queries, unless you're deliberately writing a separate, literal-valued query path for that specific case.

What interviewers look for: the parameterization limitation specifically — it's the difference between someone who's read about filtered indexes and someone who's tried to use one from a parameterized ORM query and had it silently not get picked.

Q4 You're handed an execution plan for a slow query. What do you look at first?#

Short answer: I start with the gap between estimated and actual row counts, because a large divergence there — not the raw operator cost percentages — is the single most reliable signal that the optimizer made a bad decision, and everything downstream of a bad estimate (join type, memory grant, operator order) tends to be wrong for a coherent reason once you find it.

A practical sequence: capture the actual execution plan, not an estimated one, since only the actual plan shows real row counts to compare against the optimizer's estimates. Look for warning icons on operators — an implicit conversion (comparing an nvarchar column against a non-Unicode literal, silently defeating an index) or a missing-index suggestion. Check Sort and Hash Match operators for memory grants that spilled to tempdb, which usually shares the same root cause as a bad cardinality estimate upstream. Finally, sanity-check join operators against the row counts involved: Nested Loops is efficient when one side is small, but expensive when the side the optimizer thought was small turns out, at execution time, to be large — exactly the pattern stale statistics or parameter sniffing produces.

Relative operator cost percentages, the number most people stare at first, are themselves estimated and can be badly wrong when the underlying estimates are wrong — they show where the optimizer thinks the time went, not necessarily where it actually did.

What interviewers look for: leading with actual-vs-estimated rows rather than "the operator with the highest cost %," which shows you understand that plan cost percentages are themselves estimates and can mislead.

• Follow-up questions: How would you capture an actual execution plan for a query that only runs slowly in production, not locally? What's the difference between a spill warning and a memory grant that's simply too large?

Q5 What is parameter sniffing, and how do you diagnose a stored procedure that's "fast sometimes, slow other times"?#

Short answer: SQL Server compiles and caches a stored procedure's execution plan based on the parameter values supplied the first time it's compiled (or recompiled), and reuses that plan for every subsequent call — which is usually the right optimization, but becomes a liability when the column's data distribution is skewed enough that a plan good for one parameter value is badly wrong for another, producing the classic "fast for most customers, catastrophically slow for the one customer with a million rows" symptom.

Diagnosis starts with confirming it's actually parameter sniffing and not simply stale statistics: check sys.dm_exec_query_stats for wildly different execution counts/times across calls to the same plan, and use Query Store's regressed-queries view to see whether the same query has multiple recorded plans with very different runtimes. Once confirmed, the classic mitigations are OPTION (RECOMPILE) (pay a compilation cost every execution in exchange for a plan tailored to that call's actual values — fine for rarely-called, expensive procedures, wasteful for high-frequency ones), OPTIMIZE FOR a representative or average value, or declaring local variables and assigning the parameters to them inside the procedure body, which deliberately defeats sniffing in favor of a generic, average-case plan.

SELECT * FROM Orders WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

Two newer engine features reduce how often you need to hand-tune a procedure at all: Parameter Sensitive Plan optimization, from SQL Server 2022 at compatibility level 160, automatically maintains multiple plan variants for a query with skew-prone equality predicates and dispatches each execution to the variant matching its parameter's value bucket, with no query rewrite. Optional Parameter Plan Optimization, new in SQL Server 2025 at compatibility level 170, targets the common WHERE (@Status IS NULL OR Status = @Status) pattern for an optional predicate, which previously forced a manual OPTION (RECOMPILE) to avoid a one-size-fits-none plan.

What interviewers look for: distinguishing parameter sniffing from stale statistics (they present similarly but have different fixes), and knowing at least one concrete mitigation by name with its trade-off, not just "add RECOMPILE everywhere."

• Common mistakes: slapping OPTION (RECOMPILE) on every procedure regardless of call frequency, trading a rare bad-plan problem for a constant, guaranteed compilation-overhead problem.

Q6 How do statistics drive the optimizer, and when do you need to intervene manually?#

Short answer: The optimizer estimates how many rows a predicate will match using a histogram-based statistics object maintained per index/column, and every cost-based decision downstream — join type, join order, memory grant, parallelism — flows from that cardinality estimate; when statistics are stale or the histogram doesn't capture your data's real distribution, every one of those downstream decisions can be wrong even though the query and indexes themselves are fine.

SQL Server auto-creates and auto-updates statistics by default, triggered once enough rows have changed since the last update — modern compatibility levels scale that threshold with table size rather than using a flat percentage, so very large tables trigger a refresh after a smaller proportional change than older versions required. Bulk loads, batch deletes and large migrations are the classic scenario where auto-update doesn't keep up in practice, since the update fires only after the threshold is crossed, and only synchronously by default — blocking the triggering query while stats refresh — unless asynchronous statistics update is enabled.

Manual intervention is warranted for a small set of high-value, skewed tables: UPDATE STATISTICS Orders WITH FULLSCAN after a large load, rather than trusting the default sampled scan, when the column's distribution is critical to a hot query's plan quality; and updating statistics as an explicit pipeline step after a bulk import instead of waiting for the next query to trigger an auto-update mid-request.

What interviewers look for: connecting statistics staleness directly to cardinality estimation and from there to plan quality, and knowing that the auto-update threshold is size-aware on modern compatibility levels rather than a fixed percentage.

• Follow-up questions: Why might UPDATE STATISTICS with a full scan on a huge table be worse for a maintenance window than a sampled scan? How do you tell from an execution plan that a bad estimate came from stale statistics rather than parameter sniffing?

Q7 What is Query Store, and how would you use it to catch and fix a performance regression right after a deployment?#

Short answer: Query Store is SQL Server's built-in, always-on-by-default (since SQL Server 2022, and by default on Azure SQL Database) history of every query's compiled plans and their runtime statistics over time, which turns "the deploy made things slower" from a guess into something you can prove and fix in minutes by forcing the database back onto the plan that was fast before the regression, without touching application code.

The workflow: after a suspected regression, open the Regressed Queries view (in SSMS or via the underlying catalog views) and sort by the metric that matters — duration, CPU, logical reads — over the window since the deployment; Query Store retains multiple historical plans per query specifically so you can compare the new plan against the previously-good one side by side. If the regression is clearly a plan change rather than a genuine data-volume increase, you force the earlier plan with sys.sp_query_store_force_plan(@query_id, @plan_id), which is enforced going forward with no code deployment required, and later remove the forcing with sys.sp_query_store_unforce_plan once you've shipped a real fix (a better index, an updated query, refreshed statistics) and verified it.

-- Find the query and its two candidate plans from the catalog views, then:
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 137;
-- later, once the underlying fix has shipped:
EXEC sys.sp_query_store_unforce_plan @query_id = 42, @plan_id = 137;

This is a materially better tool than the older plan-guide mechanism for the same problem, because Query Store already has the history captured automatically — you're choosing among plans SQL Server actually ran and measured, not hand-authoring a hint and hoping. SQL Server 2025 extended this further with Query Store support for secondary (Always On readable) replicas, consolidating plan and runtime history from every replica back into one Query Store on the primary, which matters directly for architectures that route reads to replicas, since regressions on a read replica are now visible in the same place as primary-replica regressions.

What interviewers look for: the specific plan-forcing workflow (sp_query_store_force_plan/sp_query_store_unforce_plan) as an immediate mitigation distinct from the actual root-cause fix, and awareness that this is a standard, low-risk first response to a deployment-triggered regression.

Q8 When does table partitioning actually help, and when is it a trap for a typical OLTP workload?#

Short answer: Partitioning helps most clearly for large, time-ordered tables where queries and maintenance naturally align with the partition boundary — reporting queries that scan a specific date range benefit from partition elimination, and archival of old data becomes a fast, near-metadata-only SWITCH PARTITION instead of a slow, log-heavy DELETE; it's a trap when it's adopted as a general performance feature for OLTP point-lookup workloads, where it adds real operational complexity without speeding up the queries that actually dominate that workload's traffic.

The mechanism that makes archival cheap: ALTER TABLE Orders SWITCH PARTITION 3 TO OrdersArchive PARTITION 1 reassigns a partition's data by updating metadata rather than moving or deleting rows, which is why sliding-window archival strategies built on partitioning can retire millions of old rows in milliseconds of actual work, compared to a batched DELETE that has to log every row.

The trap, concretely: a single point-lookup query by primary key doesn't get faster because the table is partitioned — the optimizer still has to find the right partition and then seek within it, and if your indexes aren't partition-aligned, you lose the elimination benefit for particular query shapes. Partitioning also doesn't automatically parallelize a single query across partitions the way people sometimes assume; parallelism is a separate optimizer decision. The honest framing for an architecture discussion: partitioning is primarily a data-lifecycle and very-large-table-maintenance tool that happens to also help range-scan patterns, not a blanket performance lever for OLTP.

What interviewers look for: naming SWITCH PARTITION's near-instant, metadata-only nature as the actual value proposition, and being explicit that it doesn't help a typical point-lookup OLTP query — a common architect-interview trap for candidates who've only read the marketing pitch for partitioning.

• Common mistakes: proposing partitioning as a fix for a slow single-row lookup; forgetting to keep supporting indexes partition-aligned, silently losing elimination.

Q9 How would you choose between scaling reads with replicas and scaling writes with sharding for a .NET system under growing load?#

Short answer: Read replicas (SQL Server Always On readable secondaries, or the equivalent on a managed cloud database) scale read throughput cheaply and transparently to the application, at the cost of asynchronous replication lag that your .NET code has to account for explicitly; sharding scales write throughput and total data volume by splitting rows across independent database instances by a shard key, at the cost of turning cross-shard queries, joins and transactions into distributed-systems problems your application now has to solve itself. Reach for replicas first, and only consider sharding once write volume or total dataset size genuinely exceeds what a single primary instance (plus replicas for reads) can handle.

The lag problem with replicas is the one that actually bites in practice: a user who just wrote data and is immediately redirected to a lagging replica can fail to see their own write, so the routing decision — which connections use ApplicationIntent=ReadOnly to target a replica versus which stay on the primary — has to be made deliberately per use case, not globally. A common .NET pattern is a read/write-split repository or a DbContext factory that picks a connection string based on whether the operation that follows needs read-your-own-write consistency.

Server=myserver;Database=Sales;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;

Sharding is the harder, more invasive choice, and picking the shard key is the decision that makes or breaks it — a key that distributes write load evenly and keeps most queries within a single shard (a tenant ID in a multi-tenant system, for example) works well; a key that forces most real queries to fan out across every shard just relocates the bottleneck instead of removing it. Once sharded, cross-shard joins, cross-shard transactions and even a globally-unique, sortable ID generator all become explicit engineering problems a single-instance-plus-replicas architecture never has to solve — which is why sharding belongs later in the scaling roadmap, not earlier.

What interviewers look for: naming the read-your-own-writes problem specifically for replicas, and shard-key selection specifically for sharding, since both are the "have you actually operated this" details that separate real experience from textbook knowledge.

• Follow-up questions: How would you handle a report that needs to join data living on two different shards? What would make you choose vertical partitioning (splitting tables, not rows) over either replicas or sharding?

Q10 You don't have direct production database access. How do you work effectively with a DBA team on a performance problem?#

Short answer: I bring evidence, not a conclusion — the actual execution plan, the Query Store history if it's available, and a specific, justified index or query change proposal — rather than "the database is slow, please look at it," because that shifts the conversation from a vague complaint the DBA has to independently investigate to a concrete change they can evaluate against concerns I likely don't have full visibility into, like write amplification, storage budget and maintenance windows.

Concretely, I'd hand over the captured plan (actual, not estimated), the query text with realistic parameter values, and, where I have a hypothesis, a proposed index with its trade-off spelled out — expected read benefit versus write and storage cost — since a DBA managing dozens of write-heavy tables is right to push back on an unjustified addition. I'd treat missing-index DMV suggestions as a hypothesis to validate against the actual plan, not an instruction to blindly create, since they ignore overlapping indexes and write cost. For a schema change, I'd ask about ONLINE = ON build options and maintenance windows up front, and for a suspected regression I'd lead with Query Store evidence, since forcing a previous plan is often something a DBA can do immediately as a stopgap.

The broader point: the DBA relationship works best as a shared-evidence conversation about trade-offs neither side owns alone — the DBA has operational context the application team lacks (storage, maintenance windows, other workloads on the instance), and the application team has the query-shape and business-priority context the DBA lacks.

What interviewers look for: framing this as a collaborative, evidence-driven relationship rather than either "I just ask the DBA to fix it" or "I don't need a DBA" — both of which are red flags at the architect level.