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 is 30 questions across the four areas seniors actually get grilled on: indexing & tuning, execution plans & the optimizer, concurrency & locking, and performance & T-SQL. Every 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 multi-tenant marketing-analytics SaaS on Azure SQL with CampaignEvents at 1.2 billion rows, serving dashboards at ~3,200 req/sec.
The lens for all 30
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.
Block A highlight — why your index isn't used (SARGability)
Almost always because the predicate isn't SARGable — a function/calculation on the indexed column, an implicit type conversion, or a leading wildcard — so SQL Server can't seek and falls back to a scan.
-- 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);
And the companion fix — a covering index that kills the key lookup:
CREATE NONCLUSTERED INDEX IX_Kpis_Cover
ON dbo.CampaignDailyKpis (TenantId, CampaignId, Day) -- seek/range (equality before range, leftmost prefix)
INCLUDE (Impressions, Clicks, Conversions); -- covered -> no key lookup
Block B highlight — parameter sniffing
On first execution SQL Server "sniffs" the parameter values and compiles a plan for them, then reuses it. If tenants have wildly different data distributions, the cached plan can be terrible for the next caller — a plan compiled for a tiny tenant (nested loops) reused for the biggest tanks p99.
-- 1) OPTIMIZE FOR UNKNOWN: a stable, distribution-agnostic plan.
... WHERE TenantId = @t OPTION (OPTIMIZE FOR UNKNOWN);
-- 2) RECOMPILE: fresh plan every run for volatile ranges.
... OPTION (RECOMPILE);
-- 3) Query Store: force a known-good plan.
-- 4) Azure SQL compat 160: Parameter Sensitive Plan optimization (automatic).
Diagnose it by the estimate-vs-actual row skew in the actual plan — not the pretty picture. A big gap usually means stale statistics or a sniffing/SARGability problem.
Block C highlight — RCSI vs NOLOCK
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 for the version store.
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON; -- readers use the version store, no S-locks
WITH (NOLOCK) is not a "make it faster" button — it's Read Uncommitted, reading dirty data and able to miss or double-count rows during page splits. Seniors reach for RCSI to get non-blocking reads without the dirty-read hazards.
And the lock-free queue pattern (SKIP LOCKED):
-- UPDLOCK reserves, READPAST skips rows other workers locked -> no double-dispatch, no blocking.
UPDATE TOP (100) dbo.OutboxMessages WITH (UPDLOCK, READPAST, ROWLOCK)
SET Status = 'Processing'
OUTPUT inserted.Id, inserted.Payload
WHERE Status = 'Pending';
Block D highlight — keyset pagination and safe big writes
OFFSET n still reads and discards the first n rows, so page 10,000 gets linearly slower. Keyset pagination seeks straight past the last row seen in constant time:
-- OFFSET: reads + throws away 200,000 rows. KEYSET: seeks directly.
SELECT TOP (20) ... WHERE EventId > @lastEventId ORDER BY EventId;
And a large DELETE/UPDATE is batched so it never escalates to a table lock or bloats the log:
WHILE 1 = 1
BEGIN
DELETE TOP (5000) FROM dbo.CampaignEvents WHERE OccurredAt < @cutoff;
IF @@ROWCOUNT < 5000 BREAK;
END
The through-line
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, change one thing at a time.
The full guide has all 30 — covering indexes, key lookups, filtered indexes, cardinality estimation, Query Store, isolation levels, lock escalation, deadlock prevention, optimistic concurrency, columnstore, tempdb, and more — each with the short answer, the deep dive with T-SQL, and the exact senior signal:
Originally published on PrepStack.
Top comments (0)