SQL Server Internals for App Devs

Key Points

• Read execution plans. Ctrl+M in SSMS or EXPLAIN in psql. Look for table scans, key lookups, missing indexes.
• Indexes are 80% of perf. Cover hot queries with indexes; check via execution plans + DMVs.
• Statistics drive the optimizer. Stale stats → bad plans. Auto-update is on by default; large tables may need manual UPDATE STATISTICS.
• Plan cache holds compiled plans. Parameter sniffing can pin a bad plan for varied inputs — OPTION (RECOMPILE) for skewed data.
• Batch updates beat row-by-row. Modern data layers (ExecuteUpdate, TVPs) are far better than per-row UPDATEs.
• DMVs (sys.dm_exec_*) show running queries, blocking, plan cache, missing indexes — every senior should know the top 5.

Concepts (deep dive)

Reading an execution plan

A simple query: SELECT * FROM Orders WHERE CustomerId = 42

Execution plan (right-to-left, top-down):

   Index Seek (Orders.IX_CustomerId)    ← good: uses index
       │
       ▼
   Key Lookup (Orders.PK)                ← bad: extra lookup per row
       │
       ▼
   SELECT

Index Seek + Key Lookup = the index found the rows but had to look up other columns from the clustered index. Fix: include the columns the query needs in the index (covering index) or INCLUDE clause.

CREATE INDEX IX_CustomerId ON Orders(CustomerId) INCLUDE (Total, Status);

Now the index seek can return everything without lookups.

Bad shapes

Indexes 101

-- Clustered: stored sorted; one per table; defines physical order
CREATE CLUSTERED INDEX IX_Orders_Created ON Orders(CreatedAt);

-- Nonclustered: separate B-tree; multiple per table
CREATE INDEX IX_Orders_Customer ON Orders(CustomerId);

-- Composite: column order matters
CREATE INDEX IX_Orders_CustomerDate ON Orders(CustomerId, CreatedAt);
-- Useful for: WHERE CustomerId = X AND CreatedAt > Y
-- Useful for: WHERE CustomerId = X (leading column)
-- NOT useful for: WHERE CreatedAt > Y alone

-- Filtered: index a subset
CREATE INDEX IX_Orders_Active ON Orders(CustomerId) WHERE Status = 'Active';

Statistics

The optimizer uses statistics (histograms of column distributions) to estimate row counts:

DBCC SHOW_STATISTICS('Orders', IX_Customer);   -- inspect

UPDATE STATISTICS Orders WITH FULLSCAN;        -- refresh manually

Auto-update fires when ~20% of rows have changed (configurable). For huge tables, this can be too slow → fall behind. Schedule a maintenance job or update on demand.

Parameter sniffing

-- First call:
EXEC sp_GetOrders @CustomerId = 1;   -- Customer 1 has 10 orders → small-result plan
-- Plan cached.

-- Second call:
EXEC sp_GetOrders @CustomerId = 999;  -- Customer 999 has 1M orders → small plan is wrong
-- Slow because cached plan doesn't fit data.

Mitigations: - OPTION (RECOMPILE) — recompile per call (cost: more compile time). - OPTION (OPTIMIZE FOR (@CustomerId = UNKNOWN)) — use average estimate. - Multiple stored procs with different plan needs. - Trace flag 4136 (instance-wide).

Top DMVs every senior should know

-- Currently running queries
SELECT * FROM sys.dm_exec_requests WHERE status = 'running';

-- Blocking
SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT';

-- Top expensive queries
SELECT TOP 20 *, total_worker_time/execution_count AS avg_cpu, total_logical_reads
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

-- Missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

-- Plan cache
SELECT * FROM sys.dm_exec_cached_plans;

sys.dm_db_missing_index_details is a goldmine — SQL Server tracks "this query would have benefited from an index on X" recommendations. Don't blindly create them all (each index has write cost), but it's a great starting list.

Implicit conversions kill plans

-- Schema: Email NVARCHAR(100)
WHERE Email = 'foo@bar'   -- string literal: ASCII varchar
-- Implicit conversion to nvarchar; blocks index seek!

Match parameter type to column type. SQL parameter providers (.NET) often default to NVARCHAR, so passing string works fine.

Tempdb

Heavy use of temp tables, table variables, sorts that don't fit RAM, snapshot isolation row versions, hash joins — all use tempdb. Provision tempdb on fast storage; size it large enough.

Lock escalation revisited

If a single statement touches > ~5000 rows, SQL Server may escalate row/page locks to a table lock. Mitigations: - WHERE filters that match indexes (lock fewer rows). - Smaller batches (UPDATE TOP(1000) ... ; repeat). - Disable escalation per-table (rare, careful).

Query store

ALTER DATABASE [MyDb] SET QUERY_STORE = ON;

Built-in store of plans and runtime stats. UI in SSMS shows top queries, regressed queries, plan history. Essential for ongoing performance work.

Code: correct vs wrong

❌ Wrong: querying without indexes

db.Orders.Where(o => o.CustomerId == id).ToList();
// CustomerId not indexed → table scan

✅ Correct: ensure index

b.Entity<Order>().HasIndex(o => o.CustomerId);

❌ Wrong: implicit conversion

WHERE PhoneNumber = 12345    -- column is varchar; parameter is int → conversion → no seek

✅ Correct

WHERE PhoneNumber = '12345'

❌ Wrong: large UPDATE in one shot

UPDATE Orders SET Status = 'Archived' WHERE CreatedAt < '2020-01-01';
-- 10M rows; table lock; lock escalation; long transaction

✅ Correct: batch

WHILE @@ROWCOUNT > 0
    UPDATE TOP(10000) Orders SET Status = 'Archived'
    WHERE CreatedAt < '2020-01-01' AND Status <> 'Archived';

Design patterns for this topic

Pattern 1 — "Index hot queries"

• Intent: seek over scan.

Pattern 2 — "Covering index for read-heavy paths"

• Intent: avoid Key Lookup.

Pattern 3 — "Batch large mutations"

• Intent: avoid table locks / long transactions.

Pattern 4 — "Match parameter types"

• Intent: preserve seek-ability.

Pattern 5 — "Use Query Store / DMVs to find bad plans"

• Intent: measure, then optimize.

Pros & cons / trade-offs

When to use / when to avoid

• Use Query Store in production for visibility.
• Use covering indexes for read-heavy hot paths.
• Use batching for large UPDATE/DELETE.
• Avoid implicit conversions in WHERE.
• Avoid wholesale mutations without batching.

Interview Q&A

Q1. Read this plan: Index Seek + Key Lookup. What's wrong? The index seek finds rows but doesn't include all needed columns; key lookup goes back to the clustered index per row. Fix: covering index with INCLUDE.

Q2. What is parameter sniffing? SQL Server compiles a plan based on the first parameter values it sees. Subsequent calls with very different values may get a bad plan.

Q3. How do you mitigate parameter sniffing? OPTION (RECOMPILE), OPTION (OPTIMIZE FOR UNKNOWN), plan-guide hints, separate procedures.

Q4. Difference between clustered and nonclustered index? Clustered: physical order of data in the table; one per table; the leaf is the table data. Nonclustered: separate B-tree pointing to clustered key; many per table.

Q5. What's a covering index? Includes all columns the query needs (WHERE + SELECT) so no key lookup is needed.

Q6. What's Query Store? SQL Server's built-in store of query plans and runtime stats. Lets you find regressed queries and force older plans.

Q7. Why might statistics matter? The optimizer uses statistics histograms to estimate row counts. Stale stats → wrong estimates → bad plans (e.g., choosing nested loop when hash would be better).

Q8. What's lock escalation? SQL coarsens row/page locks to a table lock when a statement touches many rows. Reduces overhead but blocks others.

Q9. What's RCSI for? Read Committed Snapshot Isolation — readers use row versions; readers don't block writers. Reduces lock contention significantly.

Q10. How do you find missing indexes? sys.dm_db_missing_index_details — SQL Server records suggestions per query plan. Combine with execution plan analysis.

Gotchas / common mistakes

• ⚠️ Index without using it — wrong column order or filter mismatch.
• ⚠️ Sniffing-pinned bad plan under skewed data.
• ⚠️ Implicit conversion blocks seeks.
• ⚠️ Large transactions → lock escalation.
• ⚠️ Stale stats on huge tables → bad estimates.
• ⚠️ Too many indexes → slow writes; rebuild cost.

Further reading

• Execution plan basics (Microsoft)
• DMVs
• Query Store
• Brent Ozar's blog — practical SQL Server perf