Junior SQL interviews ask "what is an index." Senior interviews ask why the index you added is being ignored, why a query that's fast for one tenant times out for another, and why the deadlock only happens in production at 9am. SQL Server rewards people who can read a plan, reason about statistics and locks, and know when the database — not the C# — is the bottleneck. This guide is 30 questions across the four areas seniors actually get grilled on: indexing & tuning, execution plans & the optimizer, concurrency & locking, and performance & T-SQL.

Each answer is a short answer you could say in one breath, a deep dive into the mechanism with T-SQL, and the senior signal the interviewer is listening for. Every example is from Mattrx — a real multi-tenant marketing-analytics SaaS on Azure SQL with CampaignEvents at 1.2 billion rows, Events ~180M, and Campaigns ~4M, serving dashboards at ~3,200 req/sec. No Foo and Bar.

How to use this guide

Read the deep dive even when the short answer is obvious — seniority is in the why and the failure mode. The blocks build on each other. If you can explain each query comment out loud, you're ready.

---

Block A — indexing & query tuning

1. Clustered vs nonclustered index — what's the real difference?

Short answer: The clustered index is the table — the rows are physically stored sorted by its key (one per table). A nonclustered index is a separate B-tree of the key columns plus a pointer (the clustering key) back to the row; you can have many.

Deep dive: Choosing the clustered key is the most important index decision — it's in every nonclustered index as the row locator, so a wide/volatile clustered key bloats everything.

-- Clustered key: narrow, ever-increasing, unique -> good row locator, minimal fragmentation.
CREATE CLUSTERED INDEX CIX_Campaigns ON dbo.Campaigns (CampaignId);

-- Nonclustered for a common lookup path:
CREATE NONCLUSTERED INDEX IX_Campaigns_Tenant ON dbo.Campaigns (TenantId, Status);

Senior signal: You know the clustered key is embedded in every nonclustered index, so you keep it narrow, unique, and ever-increasing.

2. What is a covering index, and why does INCLUDE matter?

Short answer: A covering index contains every column a query needs — key columns for the predicate/sort, plus INCLUDEd columns for the SELECT list — so SQL Server answers the query from the index alone, with no lookup back to the table.

Deep dive: Mattrx's dashboard reads a few columns filtered by tenant+campaign; covering the query removes the key lookup entirely.

-- Query: SELECT Impressions, Clicks FROM ... WHERE TenantId=@t AND CampaignId=@c AND Day>=@from
CREATE NONCLUSTERED INDEX IX_Kpis_Cover
  ON dbo.CampaignDailyKpis (TenantId, CampaignId, Day)   -- seek/range
  INCLUDE (Impressions, Clicks, Conversions);            -- covered -> no key lookup

Senior signal: You distinguish key columns (predicate/join/sort) from INCLUDEd columns (the SELECT list) and use covering to kill key lookups.

3. Why isn't my index being used?

Short answer: Almost always because the predicate isn't SARGable — a function or calculation is applied to the indexed column, there's an implicit type conversion, or a leading wildcard — so SQL Server can't seek and falls back to a scan.

Deep dive: The fix is to make the column "bare" on one side of the predicate.

-- NOT SARGable: function on the column -> scan.
WHERE CAST(OccurredAt AS date) = @day;
WHERE YEAR(OccurredAt) = 2026;

-- SARGable: range on the bare column -> seek.
WHERE OccurredAt >= @day AND OccurredAt < DATEADD(day, 1, @day);

-- Implicit conversion also kills a seek: NVARCHAR column compared to VARCHAR literal, etc.
WHERE TenantId = @tenantId;   -- ensure @tenantId matches the column type exactly

Senior signal: You say "SARGable," point to functions/conversions on the column, and rewrite predicates to keep the column bare.

4. What is a key lookup, and how do you get rid of it?

Short answer: When a nonclustered index seek finds the rows but doesn't have all the SELECTed columns, SQL Server does a key lookup into the clustered index per row. It's fine for a few rows, brutal for thousands. Eliminate it by covering the query (INCLUDE).

Deep dive: A key lookup that runs once per matched row turns a cheap seek into a per-row round trip.

-- Seek on IX finds rows, then looks up Value for each (expensive at scale):
SELECT Value FROM dbo.CampaignEvents WHERE TenantId=@t AND CampaignId=@c;
-- FIX: include Value so the index covers it.
CREATE NONCLUSTERED INDEX IX_Events_Cover ON dbo.CampaignEvents (TenantId, CampaignId) INCLUDE (Value);

Senior signal: You recognize the key-lookup operator in a plan and cover the query rather than adding a whole new index blindly.

5. How do you order columns in a composite index?

Short answer: Leftmost-prefix rules: put equality predicates first (most selective first among them), then the range/sort column last. An index on (A, B, C) helps WHERE A= and WHERE A= AND B=, but not WHERE B= alone.

Deep dive: Mattrx queries filter equality on tenant + campaign and range on day, so the index is (TenantId, CampaignId, Day) — equality columns, then the range.

-- Supports: WHERE TenantId=@t AND CampaignId=@c AND Day BETWEEN @a AND @b  (seek + range)
CREATE NONCLUSTERED INDEX IX_Events ON dbo.CampaignEvents (TenantId, CampaignId, Day);
-- Does NOT efficiently support: WHERE Day BETWEEN ...   (Day isn't a leading column)

Senior signal: You state "equality before range, leftmost prefix," and match column order to the actual query, not alphabetical habit.

6. Is index fragmentation still worth worrying about?

Short answer: Less than people think on modern SSD/Azure SQL. Random-read seeks don't care much about fragmentation; what actually matters is out-of-date statistics and page density (fill factor). Rebuild/reorganize on real need, and keep stats fresh.

Deep dive: Mattrx stopped nightly full rebuilds (they invalidated plan caches and burned resources) and focused on statistics maintenance.

-- Reorganize (online, light) vs rebuild (heavier, updates stats). Target by real fragmentation, not a cron.
ALTER INDEX IX_Events ON dbo.CampaignEvents REORGANIZE;
UPDATE STATISTICS dbo.CampaignEvents WITH FULLSCAN;  -- often the higher-value action

Senior signal: You push back on cargo-cult nightly rebuilds and prioritize statistics + fill factor over defragmentation on SSD.

7. What is a filtered index and when is it a big win?

Short answer: A filtered index indexes only a subset of rows (a WHERE on the index), making it smaller and cheaper — ideal for a common query over a small slice of a big table (e.g., "active" or "unprocessed" rows).

Deep dive: Mattrx's outbox dispatcher only cares about Pending rows out of billions delivered.

-- Tiny index over just the rows the dispatcher scans:
CREATE NONCLUSTERED INDEX IX_Outbox_Pending
  ON dbo.OutboxMessages (CreatedAt)
  WHERE Status = 'Pending';   -- filtered: excludes the millions of Delivered rows

Senior signal: You reach for a filtered index for a hot query over a small, well-defined subset of a large table.

8. When does an index hurt?

Short answer: Every index must be maintained on every INSERT/UPDATE/DELETE, so too many indexes slow writes, bloat storage, and give the optimizer more (sometimes worse) choices. Unused and duplicate indexes are pure cost.

Deep dive: Mattrx audits index usage and drops ones with reads ≈ 0 and high writes.

-- Find write-heavy, read-light indexes (candidates to drop):
SELECT OBJECT_NAME(s.object_id), i.name, s.user_seeks + s.user_scans AS reads, s.user_updates AS writes
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
  ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() ORDER BY writes DESC;

Senior signal: You treat indexes as a write tax, audit usage, and remove unused/duplicate indexes instead of only ever adding.

---

Block B — execution plans & the optimizer

9. How do you read an execution plan?

Short answer: Read it right-to-left, top-to-bottom: data flows from the rightmost operators (seeks/scans) leftward. Arrow thickness = row counts; look for scans where you expect seeks, fat arrows, and expensive operators (sorts, hash spills, key lookups).

Deep dive: The first thing to check is seek vs scan on the big table and whether estimated rows match actual.

  right-to-left data flow:

  [Index Seek: CampaignDailyKpis] --> [Nested Loops] --> [Compute Scalar] --> SELECT
  (fat arrow = many rows; a Table/Index SCAN on a 1.2B-row table is the red flag)

Senior signal: You read right-to-left, use arrow thickness for row volume, and immediately compare estimated vs actual rows.

10. Estimated vs actual execution plan — why does the difference matter?

Short answer: The estimated plan is what the optimizer predicted from statistics; the actual plan shows what really happened. A big gap between estimated and actual row counts means bad cardinality estimation — usually stale statistics or a non-SARGable/parameter-sniffing problem.

Deep dive: Mattrx caught a slow query where estimated rows = 1 but actual = 2M — a stale-stats cardinality miss that picked nested loops instead of a hash join.

-- Turn on the actual plan (SSMS: Ctrl+M) or:
SET STATISTICS PROFILE ON;   -- estimate vs actual rows per operator
-- Big estimate/actual skew => update stats or fix the predicate.

Senior signal: You diagnose by the estimate-vs-actual row skew, not by the pretty picture, and tie it to statistics.

11. How do statistics and cardinality estimation work?

Short answer: SQL Server keeps histograms of column value distribution; the optimizer uses them to estimate how many rows a predicate returns, and picks operators (seek vs scan, loops vs hash) accordingly. Stale or missing stats → wrong estimates → wrong plan.

Deep dive: On a fast-growing table like CampaignEvents, auto-stats can lag, so recent data is under-estimated.

-- Inspect staleness and update:
SELECT name, STATS_DATE(object_id, stats_id) AS last_updated FROM sys.stats WHERE object_id = OBJECT_ID('dbo.CampaignEvents');
UPDATE STATISTICS dbo.CampaignEvents WITH FULLSCAN;

Senior signal: You connect plan quality to statistics/histograms and know auto-update lags on large, fast-growing tables.

12. What is parameter sniffing, and how do you fix a bad case?

Short answer: On first execution SQL Server "sniffs" the parameter values and compiles a plan optimized for them, then reuses it. If tenants (or values) have wildly different data distributions, the cached plan can be terrible for the next caller.

Deep dive: A Mattrx query compiled for a tiny tenant (nested loops) reused for the biggest tenant tanked p99. Fixes, in order of preference:

-- 1) OPTIMIZE FOR UNKNOWN: a stable, distribution-agnostic plan.
... WHERE TenantId = @t OPTION (OPTIMIZE FOR UNKNOWN);
-- 2) RECOMPILE: fresh plan every run (a little CPU) for volatile ranges.
... OPTION (RECOMPILE);
-- 3) Query Store: force a known-good plan. 4) Azure SQL compat 160: Parameter Sensitive Plan optimization (automatic, up to 3 variants).

Senior signal: You name it, explain why multi-tenant skew triggers it, and pick the fix per query (PSP first on modern Azure SQL, then RECOMPILE/forced plan).

13. How does the plan cache and plan reuse work?

Short answer: SQL Server caches compiled plans keyed by the query text/handle so it can reuse them. Parameterized queries reuse one plan; ad-hoc queries with inline literals flood the cache with one-off plans (cache bloat).

Deep dive: Mattrx parameterizes everything (via EF/Dapper) so plans are reused; OPTIMIZE FOR AD HOC WORKLOADS reduces bloat from any stray ad-hoc.

-- Ad-hoc (bad): each literal = a new cached plan.
SELECT * FROM dbo.Campaigns WHERE TenantId = 'a1b2...';
-- Parameterized (good): one reusable plan for all tenants.
-- (sp_executesql / EF parameters) SELECT ... WHERE TenantId = @t;

Senior signal: You parameterize to reuse plans and know ad-hoc literals cause plan-cache bloat.

14. What does Query Store give you?

Short answer: Query Store records query text, plans, and runtime stats over time, so you can spot regressions (a query that got slower after a plan flip) and force a known-good plan without touching code.

Deep dive: Mattrx uses it to catch a plan regression after a stats update and pin the good plan.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
-- Then find the regressed query's good plan_id and force it:
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;

Senior signal: You use Query Store to diagnose regressions over time and force plans, rather than guessing.

15. Which plan operators signal trouble?

Short answer: A table/index scan where you expected a seek; a key lookup at high row counts; a sort that could be served by an index; a hash match with a spill to tempdb; and any operator with a huge estimate-vs-actual skew.

Deep dive: A spill means the memory grant was too small (bad estimate) and the operator went to disk.

  Red-flag operators:
   - Table Scan / Index Scan  (on a huge table)
   - Key Lookup               (x many rows)
   - Sort                     (add an index on the sort column?)
   - Hash Match (spill)       (bad memory grant / stale stats)

Senior signal: You name specific operators, tie a hash spill to a bad memory grant/estimate, and know a sort often means a missing index.

---

Block C — concurrency, locking & isolation

16. Explain isolation levels and the anomalies they prevent.

Short answer: Read Uncommitted (allows dirty reads) < Read Committed (default; no dirty reads) < Repeatable Read (no non-repeatable reads) < Serializable (no phantoms). Higher isolation = more locking = less concurrency. Snapshot uses row-versioning for consistency without blocking.

Deep dive: Most apps live at Read Committed; Mattrx uses RCSI (below) to get non-blocking reads.

  Level              Dirty  Non-repeatable  Phantom
  Read Uncommitted    yes        yes          yes
  Read Committed      no         yes          yes    <- default
  Repeatable Read     no         no           yes
  Serializable        no         no           no     <- most locking

Senior signal: You map each level to the specific anomaly it prevents and note the concurrency cost of going higher.

17. What lock types exist, and what is lock escalation?

Short answer: Mainly Shared (S) for reads, Exclusive (X) for writes, and Update (U) to prevent a common deadlock. SQL Server takes fine-grained row/page locks but escalates to a table lock once a statement holds too many (~5,000) — which can block everyone.

Deep dive: A big single UPDATE/DELETE escalates to a table lock; batching keeps locks fine-grained.

-- Avoid escalation on a large delete by batching (each batch stays under the threshold):
WHILE 1 = 1
BEGIN
  DELETE TOP (5000) FROM dbo.CampaignEvents WHERE OccurredAt < @cutoff;
  IF @@ROWCOUNT < 5000 BREAK;
END

Senior signal: You know lock escalation kicks in ~5,000 locks and batch large writes to avoid a table-lock stall.

18. Blocking vs deadlock — what's the difference?

Short answer: Blocking is normal and temporary — one transaction waits for another to release a lock. A deadlock is a cycle: two transactions each hold a lock the other needs, so neither can proceed; SQL Server detects it and kills one as the victim.

Deep dive: Blocking is a performance issue (waits); a deadlock is a correctness/UX issue (a query fails with 1205).

Senior signal: You clearly separate "waiting" (blocking) from "unbreakable cycle" (deadlock) and know SQL Server auto-resolves deadlocks by killing a victim.

19. How do deadlocks happen, and how do you prevent them?

Short answer: Two transactions acquire locks in opposite order on the same resources. Prevent them by accessing objects in a consistent order, keeping transactions short, adding indexes to shrink the lock footprint, and using RCSI so readers don't block writers.

Deep dive: Mattrx eliminated a recurring deadlock by ordering multi-table updates consistently and enabling RCSI.

-- Capture the deadlock graph to see the cycle:
-- (Extended Events 'xml_deadlock_report' / system_health session)
-- Fixes: consistent access order, short txns, cover the query, RCSI.

Senior signal: You give the cause (opposite lock order) and multiple fixes (order, short txns, indexing, RCSI), not just "add a retry."

20. Optimistic vs pessimistic concurrency — how do you implement optimistic?

Short answer: Pessimistic locks the row while you work with it; optimistic doesn't lock — it detects a conflict at write time using a rowversion column, and the loser retries. Optimistic scales better for low-contention web workloads.

Deep dive: Mattrx uses a rowversion on editable entities; EF Core maps it as a concurrency token.

ALTER TABLE dbo.Campaigns ADD RowVer rowversion;   -- auto-incremented on every update
-- UPDATE ... WHERE CampaignId=@id AND RowVer=@original;  -- 0 rows affected => someone else won -> retry

Senior signal: You implement optimistic with rowversion + "0 rows affected = conflict, retry," and pick it for low-contention web apps.

21. What is RCSI (Read Committed Snapshot), and what does it cost?

Short answer: With Read Committed Snapshot Isolation on, readers get a consistent row-version snapshot instead of taking shared locks — so readers don't block writers and writers don't block readers. The cost is extra tempdb usage for the version store.

Deep dive: RCSI is often the single biggest concurrency win for a read-heavy OLTP app like Mattrx's dashboards.

ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;  -- readers use the version store, no S-locks
-- Watch tempdb: the version store lives there.

Senior signal: You know RCSI removes reader/writer blocking at the cost of tempdb, and it's on by default in Azure SQL Database.

22. What does WITH (NOLOCK) actually do?

Short answer: NOLOCK = Read Uncommitted for that table — it reads dirty (uncommitted, possibly rolled-back) data and can even miss or double-count rows during page splits. It's not a "make it faster" button; it's a correctness trade-off.

Deep dive: Seniors reach for RCSI to get non-blocking reads without dirty-read hazards, rather than sprinkling NOLOCK.

-- Dangerous: can read data that never committed, or skip/duplicate rows mid-scan.
SELECT SUM(Value) FROM dbo.CampaignEvents WITH (NOLOCK) WHERE ...;
-- Prefer RCSI at the database level for consistent, non-blocking reads.

Senior signal: You describe the specific hazards (dirty reads, missing/duplicate rows) and prefer RCSI over NOLOCK.

23. How do you build a safe queue in SQL Server (the SKIP LOCKED pattern)?

Short answer: Use WITH (UPDLOCK, READPAST, ROWLOCK) to atomically claim the next available rows while skipping rows other workers have already locked — so multiple workers dequeue concurrently without blocking or double-processing.

Deep dive: This is exactly Mattrx's outbox dispatcher claiming pending messages across many workers.

-- Claim a batch: UPDLOCK reserves, READPAST skips others' locked rows -> no double-dispatch, no blocking.
UPDATE TOP (100) dbo.OutboxMessages WITH (UPDLOCK, READPAST, ROWLOCK)
SET Status = 'Processing', ClaimedAt = SYSUTCDATETIME()
OUTPUT inserted.Id, inserted.Payload
WHERE Status = 'Pending';

Senior signal: You know UPDLOCK + READPAST is SQL Server's SKIP LOCKED and use it for lock-free concurrent dequeue.

---

Block D — performance, T-SQL & production

24. Set-based vs cursors (RBAR) — why does it matter so much?

Short answer: SQL Server is optimized for set-based operations; a cursor or loop that processes one row at a time (RBAR — row-by-agonizing-row) is often orders of magnitude slower. Rewrite loops as a single statement.

Deep dive: A Mattrx nightly job that looped per campaign to update rollups went from minutes to seconds as one UPDATE ... FROM ... GROUP BY.

-- RBAR (slow): a cursor updating one campaign at a time.
-- SET-BASED (fast): one statement over the whole set.
UPDATE k SET k.Impressions = s.Imp
FROM dbo.CampaignDailyKpis k
JOIN (SELECT CampaignId, SUM(...) AS Imp FROM dbo.CampaignEvents GROUP BY CampaignId) s
  ON s.CampaignId = k.CampaignId;

Senior signal: You instinctively rewrite cursors as set-based statements and can explain why RBAR is slow (per-row overhead, no batch optimization).

25. What makes a predicate SARGable, concretely?

Short answer: SARGable = the optimizer can use an index seek: the indexed column appears bare on one side, compared with a constant/parameter, no function/calculation on it, and no implicit type conversion.

Deep dive: Push functions to the other side of the comparison.

-- NOT SARGable:
WHERE DATEDIFF(day, OccurredAt, @now) <= 7
WHERE LEFT(Name, 3) = 'Cmp'
-- SARGable:
WHERE OccurredAt >= DATEADD(day, -7, @now)
WHERE Name LIKE 'Cmp%'     -- trailing wildcard is seekable; leading %Cmp is not

Senior signal: You transform predicates to keep the column bare and know trailing LIKE 'x%' seeks but leading '%x' scans.

26. OFFSET/FETCH vs keyset pagination — why does deep paging die?

Short answer: OFFSET n still reads and discards the first n rows, so page 10,000 gets linearly slower. Keyset (seek) pagination uses WHERE key > @lastSeen to jump straight to the next page in constant time.

Deep dive: Mattrx switched its event browser from OFFSET to keyset and deep pages went from seconds to milliseconds.

-- OFFSET: reads + throws away 200,000 rows to show page 10,000.
SELECT ... ORDER BY EventId OFFSET 200000 ROWS FETCH NEXT 20 ROWS ONLY;
-- KEYSET: seeks directly past the last row seen -> constant time.
SELECT TOP (20) ... WHERE EventId > @lastEventId ORDER BY EventId;

Senior signal: You know OFFSET scans-and-discards and reach for keyset pagination for deep/infinite scroll.

27. How do you run a large UPDATE/DELETE safely?

Short answer: Batch it. One giant statement escalates to a table lock, blocks everyone, and bloats the transaction log. Loop in batches of a few thousand, each its own short transaction, so locks stay fine-grained and the log can truncate between batches.

Deep dive: Mattrx purges old events in 5,000-row batches to avoid a table-lock stall and log explosion.

WHILE 1 = 1
BEGIN
  DELETE TOP (5000) FROM dbo.CampaignEvents WHERE OccurredAt < @cutoff;
  IF @@ROWCOUNT < 5000 BREAK;
  -- optional: WAITFOR DELAY to ease pressure; each batch commits and frees log/locks
END

Senior signal: You batch big writes to dodge lock escalation and log growth, and mention partition switching for bulk archival.

28. What is tempdb, and why is it a hotspot?

Short answer: tempdb is the shared scratch database used for sorts, hash spills, temp tables/table variables, and the version store (snapshot/RCSI). Because it's shared and write-heavy, it's a common contention point — allocation-page contention and space blowups from bad plans.

Deep dive: A query that spills a huge hash to tempdb (bad estimate) can drag the whole instance.

-- Watch tempdb usage by session (spills, version store, temp objects):
SELECT session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count
FROM sys.dm_db_session_space_usage ORDER BY internal_objects_alloc_page_count DESC;

Senior signal: You know tempdb serves sorts/spills/version-store, and tie tempdb pressure to bad estimates (spills) and RCSI.

29. When should you use a columnstore index?

Short answer: For analytics/aggregation over large tables — columnstore stores data by column (great compression, ~10x) and runs batch-mode execution, making SUM/COUNT/GROUP BY over millions of rows dramatically faster. Keep rowstore for OLTP point lookups.

Deep dive: Mattrx put a clustered columnstore on the 1.2B-row CampaignEvents for dashboard aggregates while keeping rowstore access for lookups.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_CampaignEvents ON dbo.CampaignEvents;
-- Aggregates run in batch mode over compressed segments; segment elimination skips ranges.

Senior signal: You match columnstore to append-only, aggregate-heavy workloads and know it's the wrong tool for single-row OLTP lookups.

30. What does a long-running transaction actually cost?

Short answer: It holds locks (blocking others), prevents the transaction log from truncating (so the log grows), and keeps the version store alive under RCSI. Keep transactions short — do slow work (HTTP calls, file I/O) outside the transaction.

Deep dive: A Mattrx bug wrapped an external call inside a DB transaction; under load it held locks for seconds and cascaded into blocking.

-- BAD: external/slow work inside the transaction holds locks the whole time.
-- GOOD: keep the transaction to the DB writes only; do I/O before/after.
BEGIN TRAN;
  UPDATE dbo.Campaigns SET Status='Active' WHERE CampaignId=@id;  -- fast, minimal scope
COMMIT;

Senior signal: You keep transactions short and never wrap external I/O in one, connecting long txns to blocking + log growth + version-store bloat.

---

Bonus — what makes an answer "senior" in SQL Server?

Short answer: A junior recites feature definitions; a senior reasons from the plan, the statistics, and the locks. "Why is it slow?" gets "it does a scan" from a junior and "the predicate isn't SARGable so it scans, and the estimate-vs-actual skew says the stats are stale — here's the covering index and the RCSI change" from a senior.

Deep dive: The recurring senior lens across all 30: can the optimizer seek (SARGability + the right index), does it estimate correctly (statistics), and who's blocking whom (locks/isolation)? Diagnose from the plan, not a guess; measure before changing.

Senior signal: You reach for the actual plan and DMVs first, quantify with estimate-vs-actual, and volunteer the failure mode before being asked.

The model to carry forward

Senior SQL Server is three instincts: make the query SARGable so the optimizer can seek; keep statistics fresh so it estimates right; and manage locks/isolation so reads and writes don't fight. Nearly every "slow query" and "random deadlock" traces back to one of those. Read the plan, compare estimated to actual, and change one thing at a time — that's the difference between guessing and engineering.

Further reading

• React Interview Questions for Senior Developers — Hooks, Reconciliation, Fiber & Performance (Deep Answers)
• ASP.NET Core Interview Questions for Senior Developers — Middleware, DI, Kestrel & Auth (Deep Answers)
• How We Query 1.2 Billion Rows in Under 50ms — Partitioning, Columnstore & Read Models

---

Prepping for a senior data/.NET interview and want these drilled live, or a mock focused on plans and locking? I'm always happy to help — reach me at randhir.jassal@gmail.com.