Interview preparation

SQL Server Interview Questions and Answers

Practical SQL Server questions for database engineers, developers and performance-focused roles — from fundamentals to production troubleshooting.

  • 20 questions
  • Beginner → Senior
  • Production-focused

SQL Server interviews reward people who can reason from evidence: read a plan, explain an estimate, find a blocker, and say what they would check next. Each answer is written to be spoken in your own words: a short answer, why it matters in production, what a strong answer usually demonstrates, the traps to avoid, and where to go deeper.

Esta guía está disponible en inglés.

SQL Server interview preparation workspace with T-SQL, execution plans, indexing, Query Store and performance troubleshooting.

At a glance

How the levels work

Q01BeginnerQuery Execution

What happens when SQL Server executes a query?

Short answer

SQL Server first parses the text (is it valid T-SQL?) and binds it (do the tables, columns and permissions exist, and what are their types?). Then the query optimizer looks for a good enough physical plan: which indexes to read, which join algorithms and join order to use, whether to go parallel. It estimates costs from statistics and stops when it finds a cheap plan; it does not search every possibility.

The chosen plan is stored in the plan cache, so the next execution of the same query can reuse it without compiling again. Finally the execution engine runs the plan: operators such as seeks, scans, joins and sorts pass rows to each other until the result reaches the client.

Why it matters in production

Most performance problems sit in one of these steps: a compilation that is too expensive or repeated too often, an optimizer choice based on wrong estimates, or a reused plan that does not fit new parameter values. Knowing the pipeline tells you where to look.

What the interviewer is looking for

A strong answer usually demonstrates that you separate the logical query you wrote from the physical plan SQL Server runs, and that you know plans are cached and reused.

Watch out

Do not say SQL Server executes clauses in the order you wrote them, or even strictly in the logical order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY). The logical order defines what the result means; the optimizer is free to reorder and combine work physically as long as the result is the same.

Back to all questions ↑

Q02BeginnerIndexing

What is a clustered index?

Short answer

In a rowstore table, the clustered index is the table. Its B-tree is ordered by the clustered key, and its leaf level contains the full data rows. A table can have only one clustered index, because the rows can only be organized one way. A table without one is a heap.

A nonclustered index is a separate structure: its leaf level holds the index key, any INCLUDE columns and a row locator back to the base table (the clustered key, or a row ID for a heap).

Why it matters in production

The clustered key is copied into every nonclustered index, so a wide or frequently updated key makes every index larger and every update more expensive. A narrow, stable, ever-increasing key (an int or bigint identity, for example) is a common choice, but the right key depends on how the table is read and written.

What the interviewer is looking for

A strong answer usually demonstrates that you know the leaf level holds the data, that the clustered key is the row locator for other indexes, and that choosing it is a design decision with consequences.

Watch out

Saying it “physically sorts the table on disk forever.” The order is logical: pages are linked in key order, but they are not guaranteed to be contiguous on disk, and page splits cause fragmentation. Also avoid saying the clustered index must be the primary key; that is only the default.

Example

CREATE TABLE dbo.Orders (
    OrderID    bigint IDENTITY NOT NULL,
    CustomerID int            NOT NULL,
    OrderDate  datetime2(0)   NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)
);

Back to all questions ↑

Q03BeginnerIndexing

What is the difference between a clustered and nonclustered index?

Short answer

Both are B-trees, but their leaf levels hold different things. The clustered index leaf level is the table itself: every column of every row, ordered by the clustered key. A nonclustered index is a separate copy of a few columns: the index key, optional INCLUDE columns, and a row locator that points back to the base row (the clustered key, or a row ID on a heap).

A table has at most one clustered index but can have many nonclustered indexes. When a nonclustered index contains every column a query needs, it covers the query and SQL Server never touches the base table. When it does not, each matching row needs a key lookup (or RID lookup) into the base table.

Why it matters in production

Nonclustered indexes speed up reads, but every INSERT, UPDATE and DELETE must maintain them too, and they use memory, storage, backup time and log space. Ten overlapping indexes on a busy table can make writes slow and blocking worse.

What the interviewer is looking for

A strong answer usually demonstrates that you know what each leaf level contains, how lookups happen, and that indexing is a read-versus-write trade-off.

Watch out

Saying “add a nonclustered index for every slow query.” More indexes are not automatically better: check the existing indexes, consolidate overlapping ones, and measure the write cost.

Back to all questions ↑

Q04WorkingIndexing

How do you decide which columns belong in an index?

Short answer

I start from the workload, not the table: the queries that run most or cost most, from Query Store or the plan cache. Key columns are the ones SQL Server seeks or orders on, usually equality predicates first, then one range predicate, plus join columns. Columns that are only returned go in INCLUDE, which makes the index covering without widening the key. Then I weigh selectivity against write cost and overlap with existing indexes. Often the best change is extending an index that already exists.

Why it matters in production

A good index can turn a scan of millions of rows into a seek of a few hundred. A poorly chosen one adds write overhead, log volume and blocking without helping the queries that matter.

What the interviewer is looking for

A strong answer usually demonstrates that you design indexes for real queries, understand how key order affects seeks, and treat writes as part of the cost:

  • Key columns: equality predicates usually come first, then a range predicate (dates, for example), because a seek can use the columns after an equality but not the ones after a range.
  • Join columns often belong in the key of the index on the inner side of the join.
  • ORDER BY or GROUP BY columns can go in the key when the index order lets SQL Server avoid a sort.
  • Selectivity: does the seek actually narrow the rows?

Watch out

Rigid rules such as “always put the most selective column first.” Column order depends on how the queries filter, join and sort. Also avoid copying missing-index suggestions directly: they ignore existing indexes and column order details.

Example

-- WHERE CustomerID = @c AND OrderDate >= @from ORDER BY OrderDate, returning Status and Total
CREATE INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (Status, Total);

Back to all questions ↑

Q05WorkingIndexing

What makes an index covering?

Short answer

An index covers a query when every column the query needs from that table (in SELECT, WHERE, JOIN, GROUP BY and ORDER BY) is available in the index. SQL Server can then answer from the index alone, with no key lookup (or RID lookup on a heap) back to the base table.

Columns can be part of the index key or added with INCLUDE. Key columns are ordered and can be used for seeks and sorting; included columns are stored only at the leaf level, so they make the index covering without making the key wider. The clustered key is always present in a nonclustered index, so it never needs to be included.

Why it matters in production

A key lookup is cheap for a few rows and expensive for many: each lookup is a separate seek into the clustered index. A query that returns 50,000 rows through lookups can be slower than a scan, and the optimizer may switch between the two plans as row estimates change, which looks like unstable performance.

What the interviewer is looking for

A strong answer usually demonstrates that you can spot a key lookup in an execution plan, decide whether it is worth removing, and choose between key and included columns.

Watch out

Trying to cover every query. Each included column is copied into the index, so wide covering indexes cost storage, memory and write time. Cover the frequent or expensive queries, and accept lookups where they return few rows.

Example

-- Plan shows: Index Seek on IX_Orders_Customer + Key Lookup for Status, Total
CREATE INDEX IX_Orders_Customer
    ON dbo.Orders (CustomerID)
    INCLUDE (Status, Total)
    WITH (DROP_EXISTING = ON);

Back to all questions ↑

Q06WorkingExecution Plans

How do you read a SQL Server execution plan?

Short answer

I read it as data flow: rows start at the seeks and scans on the right and move left through joins, sorts and aggregates to the result. I use it to test a hypothesis about why the query is slow. With an actual plan, I first check warnings, then compare estimated and actual rows on each operator, because a big gap usually explains the bad choices downstream. Then I look at access methods, lookups, join types, sorts and spills, and the operator properties.

Why it matters in production

The plan explains why a query reads too much or waits on memory. Without it, tuning becomes guessing: adding indexes and hoping.

What the interviewer is looking for

A strong answer usually demonstrates a method: follow the rows, compare estimates with actuals, read operator properties, and turn findings into a specific fix you can verify. The checks, in roughly this order:

  • Warnings on the root and on operators: spills to tempdb, implicit conversions, missing join predicates, excessive memory grants.
  • Estimated versus actual rows on each operator. A large gap points to a cardinality problem.
  • Access methods: a scan of a large table where few rows are needed, or a seek that runs millions of times inside a nested loop.
  • Key lookups that execute for many rows.
  • Join types: nested loops for small inputs, hash or merge for large ones. A nested loop with a huge outer input is suspicious.
  • Sorts and hashes, which need memory and can spill.
  • Predicates in operator properties: a residual predicate applied after reading many rows is not a seek predicate.
  • Memory grant, degree of parallelism and the compiled parameter values on the root operator.

Arrow thickness shows how many rows move between operators.

Watch out

Trusting cost percentages. They are the optimizer’s estimates, even in an actual plan, so they are wrong exactly when the estimates are wrong. “The seek is 2%, the sort is 80%” says little if the seek returned a million rows instead of ten.

Back to all questions ↑

Q07WorkingExecution Plans

What is the difference between an estimated and an actual execution plan?

Short answer

An estimated plan is what the optimizer intends to do. SQL Server compiles the query but does not run it, so the plan only contains estimates: estimated rows, costs and memory.

An actual plan is the same plan shape plus runtime information collected while the query ran: actual rows and executions per operator, the memory actually granted and used, spill warnings, elapsed and CPU time per operator, and the compiled and runtime parameter values. The plan shape is normally the same; what changes is what you can see.

Why it matters in production

Most tuning decisions depend on actual versus estimated rows. An estimated plan cannot show that an operator expected 10 rows and got 2 million, or that a sort spilled to tempdb. An actual plan can.

On a production system, capturing an actual plan means running the query, with its full cost, locks and side effects. A DELETE really deletes. Alternatives are the cached plan, Query Store, sys.dm_exec_query_statistics_xml for a running query, or sys.dm_exec_query_plan_stats for the last actual plan when LAST_QUERY_PLAN_STATS is enabled (SQL Server 2019 and later).

What the interviewer is looking for

A strong answer usually demonstrates that you know which information exists only at runtime, how to get it safely, and that the compiled parameter values explain why a plan was chosen.

Watch out

Testing with different parameter values from production and concluding “the plan looks fine.” Check the parameter list on the root operator: a plan is only meaningful for the values it was compiled and run with. Also avoid running a slow query in SSMS with different SET options than the application, which can produce a different plan.

Back to all questions ↑

Q08WorkingStatistics & Cardinality

What causes bad cardinality estimates?

Short answer

Estimates go wrong when statistics or the estimation model can’t describe the data or the predicate. The usual causes are stale or poorly sampled statistics, skewed data that a 200-step histogram averages away, correlated columns treated as independent, and functions or implicit conversions that hide the predicate. Others are a plan compiled for one parameter value and reused for others, table variables and multi-statement functions on older versions, and complex joins that compound small errors. I find where the estimate first goes wrong before choosing a fix.

Why it matters in production

Join type, join order, memory grant, parallelism and index choice all follow from these estimates. A tenfold error on one operator can produce a plan that is a thousand times slower.

What the interviewer is looking for

A strong answer usually demonstrates that you find where the estimate first goes wrong in an actual plan and explain why:

  • Stale or sampled statistics: rows added since the last update (newest dates are a classic case), or a sample that missed important values.
  • Skewed data: one customer has 40% of the orders, but the histogram has at most 200 steps and averages the rest.
  • Correlated columns: City = 'Madrid' AND Country = 'Spain' treated as if the two filters were independent.
  • Expressions and functions on columns, implicit conversions, or non-inlined scalar functions that hide the predicate from statistics.
  • Parameter values: a plan compiled for one value and reused for very different ones.
  • Table variables and multi-statement table-valued functions, which historically had fixed guesses. SQL Server 2019 improved table variables with deferred compilation and 2017 added interleaved execution for multi-statement functions, at the right compatibility level.
  • Model limits: complex joins and many predicates compound small errors.

Watch out

Answering “update statistics” for everything. It fixes staleness, not skew, correlation, non-sargable predicates or parameter-sensitive reuse, and it also forces recompilation, which can make the problem seem to disappear for the wrong reason.

Back to all questions ↑

Q09WorkingStatistics & Cardinality

What are SQL Server statistics and why are they important?

Short answer

Statistics describe the distribution of values in one or more columns, and the optimizer uses them to estimate how many rows a predicate or join returns. Those estimates drive the whole plan. Each statistics object has a header, a density vector and a histogram of up to 200 steps on the first column. SQL Server creates them for indexes and, by default, for columns used in predicates, and updates them automatically after enough changes. I update them myself after large loads, or with a full scan for skewed columns.

Why it matters in production

Stale or poorly sampled statistics produce wrong estimates, which lead to wrong join types, memory grants and index choices. The newest data is often the most queried and the least represented in the histogram.

What the interviewer is looking for

A strong answer usually demonstrates that you know what is inside a statistics object and how it is maintained:

  • a header: when it was last updated, how many rows the table had and how many were sampled;
  • a density vector: the average selectivity of the leading column combinations, used when a specific value is unknown;
  • a histogram on the first column, with up to 200 steps, each recording a range boundary, the rows equal to it and the rows between steps.

With AUTO_CREATE_STATISTICS on, SQL Server creates single-column statistics for predicates; with AUTO_UPDATE_STATISTICS on, it refreshes them after enough modifications. Since compatibility level 130 the threshold shrinks as the table grows, so large tables are no longer left waiting for 20% of rows to change.

Example

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_Customer_Date');

SELECT s.name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

Back to all questions ↑

Q10SeniorQuery Store & Plan Stability

What is parameter sniffing, and when is it actually a problem?

Short answer

When SQL Server compiles a parameterized query, it builds the plan for the parameter values of that first call, caches it, and reuses it for other values. Most of the time that’s good: compiling is expensive and one plan fits most values. It becomes a problem with skewed data, when one plan can’t serve the whole range. A plan built for a customer with 20 orders is a disaster for one with 2 million, and performance depends on which value compiled first. I confirm it in Query Store and the actual plan before choosing a fix.

Why it matters in production

The symptoms look like other problems: sudden regressions, the same query fast in SSMS and slow from the application, issues that “fix themselves” after a recompile or a statistics update. The opposite case also happens: a plan built for the big customer gives the small ones a large memory grant and a scan. Applying a fix without proving the cause can hide a real statistics or indexing problem.

What the interviewer is looking for

A strong answer usually demonstrates that you confirm it first: Query Store shows multiple plans or high variance in duration for the same query; the actual plan’s compiled values differ from the runtime values; estimates are right for one value and wrong for another. Then you pick a mitigation that fits:

  • Rewrite or index so one plan works well for all values.
  • OPTION (RECOMPILE) for a statement that runs rarely enough to pay compilation each time.
  • OPTIMIZE FOR a representative value, or OPTIMIZE FOR UNKNOWN when an average plan is acceptable.
  • Query Store plan forcing, or Query Store hints (SQL Server 2022 and Azure SQL), when you cannot change the code.
  • Parameter Sensitive Plan optimization in SQL Server 2022 (compatibility level 160), which can cache several plans for one query.

Watch out

Prescribing local variables as a universal fix. Copying a parameter into a local variable hides its value, so SQL Server uses the density average: one mediocre plan for everyone, chosen by accident. Forcing a plan without monitoring it is also risky: data changes, and today’s good plan can be next year’s regression.

Example

CREATE OR ALTER PROCEDURE dbo.GetOrders @CustomerID int
AS
SELECT OrderID, OrderDate, Total
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);  -- only if it runs rarely and the compile cost is acceptable

Back to all questions ↑

Q11WorkingQuery Store & Plan Stability

What is Query Store and how do you use it during a performance incident?

Short answer

Query Store is a per-database flight recorder. It keeps query text, the plans each query used, and runtime statistics over time, and unlike the plan cache it survives restarts. In an incident I use it to answer three questions: what changed and when, whether a plan changed at that moment, and whether it’s one query or many. If a plan regressed and the old one was good, I can force it or add a Query Store hint as a quick mitigation. Then I find out why it changed.

Why it matters in production

Without history, a performance incident is a guess about what used to happen. Query Store turns “it was fast yesterday” into evidence: two plans, two time windows, measurable differences. It records duration, CPU, logical reads, memory and waits aggregated by time interval, and it is on by default for new databases since SQL Server 2022.

What the interviewer is looking for

A strong answer usually demonstrates that you use Query Store to compare periods and plans, know that forcing is a mitigation, and check that Query Store is actually capturing (not read-only because it ran out of space). In practice:

  1. What changed, and when? Compare the incident window with a normal period using Top Resource Consuming Queries or Regressed Queries.
  2. Did a plan change? The Tracked Queries view (or Regressed Queries) shows each plan a query used over time, and whether a new plan appeared at the moment performance dropped.
  3. Is it one query or many? A single regressed query points to plans or parameters; everything slower at once points to blocking or resources.

Query Store hints are available in SQL Server 2022 and Azure SQL, and apply without changing the code.

Watch out

Treating plan forcing as the fix. A forced plan can fail later (an index is dropped) or become wrong as data grows. Find the root cause: statistics, parameter sensitivity, a schema or code change.

Back to all questions ↑

Q12SeniorPerformance Troubleshooting

A query that normally runs in two seconds suddenly takes two minutes. How do you investigate it?

Short answer

I treat it as a change investigation: something is different from the good period, and I want evidence before touching anything. First, scope: is it just this query, for every parameter value, and since when? Then I catch it while it’s slow. Is it running or waiting? If it’s blocked, the plan doesn’t matter, so I find the head blocker. If it’s running, I compare its plan in Query Store with the good period, check the compiled parameter values, and look at estimates against actuals. Then resource pressure and recent changes. Mitigate first, then fix the root cause.

Why it matters in production

A sudden regression on a query that used to be fast is almost never fixed by a new index: the index did not disappear. Jumping to solutions wastes the incident window and can add new risk.

What the interviewer is looking for

A strong answer usually demonstrates that you check running versus waiting first, use history to compare good and bad, and separate mitigation from root cause. The full workflow:

  1. Confirm scope and time. This query only, or everything? Since when? For every parameter value, or some? From every application server? The answer splits “plan problem” from “environment problem”.
  2. Capture the current state while it is slow. sys.dm_exec_requests shows the wait type, the blocking session, CPU and reads so far. If it is blocked, find the head blocker and why its transaction is open.
  3. Look at its waits. sys.dm_exec_session_wait_stats for the session: locks, memory grants (RESOURCE_SEMAPHORE), I/O, parallelism or CPU each lead somewhere different.
  4. Compare plans. Query Store shows whether the query used a different plan in the good period. A new plan at the moment of regression is the strongest clue.
  5. Check parameter values. The compiled values in the plan versus the slow call’s values. A skewed value reusing someone else’s plan is classic parameter sensitivity.
  6. Check statistics and data growth. Estimates versus actuals in the current plan. A table that doubled, a new month outside the histogram, or a statistics update that changed the plan.
  7. Check resource pressure. Memory grants waiting, tempdb spills, CPU saturation from other workloads.
  8. Check recent changes. Deployments, index changes, compatibility level, configuration, maintenance jobs, failover.

The fix then follows the cause: force the previous plan as mitigation, address the parameter sensitivity, update or improve statistics, fix a blocker, or adjust the code, and verify against the same metrics from the good period.

Watch out

Starting with “I’d add an index” or “I’d clear the plan cache.” DBCC FREEPROCCACHE on a production instance throws away every plan and causes a compilation storm; if a recompile is needed, target the single plan.

Example

SELECT r.session_id, r.status, r.wait_type, r.wait_time, r.blocking_session_id,
       r.cpu_time, r.logical_reads, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID AND r.session_id > 50;

Back to all questions ↑

Q13WorkingBlocking & Concurrency

What is blocking in SQL Server?

Short answer

SQL Server uses locks to keep transactions consistent. Blocking happens when one session holds a lock and another needs an incompatible lock on the same resource, so the second session waits. In a chain, the session at the top that isn’t waiting on anyone is the head blocker. Short blocking is normal. It becomes a problem when locks are held for a long time, usually because of long or forgotten open transactions. Isolation levels matter too.

Why it matters in production

Blocking looks like slowness everywhere with low CPU: requests pile up, timeouts appear, connection pools fill. Killing random sessions makes it worse; the head blocker is what matters.

What the interviewer is looking for

A strong answer usually demonstrates that you can find the head blocker, see what it is doing (or that it is idle with an open transaction), and fix the cause: shorter transactions, better indexes so fewer rows are locked, or row versioning. Typical long transactions are a large update, a transaction left open by the application, a slow query inside BEGIN TRAN, or a user prompt while a transaction is open. SERIALIZABLE and REPEATABLE READ hold read locks longer, while row versioning (READ_COMMITTED_SNAPSHOT or SNAPSHOT) lets readers avoid blocking writers.

Watch out

Confusing blocking with deadlocks, or recommending NOLOCK as the fix. NOLOCK reads uncommitted data and can return missing or duplicated rows.

Example

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       s.open_transaction_count, t.text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

Back to all questions ↑

Q14SeniorBlocking & Concurrency

What is a deadlock, and how is it different from blocking?

Short answer

Blocking is a one-way wait: session B waits for a lock that session A holds. When A commits or rolls back, B continues. It can last a long time, but it resolves on its own.

A deadlock is a cycle: A holds a lock B needs, and B holds a lock A needs (or a longer chain). Neither can ever continue, so SQL Server’s lock monitor detects the cycle, usually within about five seconds, and picks a deadlock victim: the session with the lowest DEADLOCK_PRIORITY, or if they are equal, the one that is cheapest to roll back. The victim’s transaction is rolled back and it receives error 1205; the other session continues.

Why it matters in production

Deadlocks are failed transactions, not slow ones. If the application does not handle error 1205 by retrying, users see errors and work is lost. Frequent deadlocks also mean frequent rollbacks, which add load.

What the interviewer is looking for

A strong answer usually demonstrates that you capture the deadlock graph before guessing. The built-in system_health Extended Events session records xml_deadlock_report by default, so recent deadlocks are usually already there. The graph shows the processes, the statements, the resources (keys, pages, objects) and the lock modes.

Typical causes and fixes:

  • Different access order: two procedures update tables A and B in opposite orders. Make them use the same order.
  • Long transactions that hold locks while doing other work. Make them shorter.
  • Missing indexes, so an update scans and locks many more rows than it changes. A supporting index reduces the lock footprint.
  • Lookup deadlocks between a reader using a nonclustered index plus a key lookup and a writer updating both. A covering index or row versioning (READ_COMMITTED_SNAPSHOT) often removes them.
  • Serializable range locks from an isolation level or framework default.

And the application should retry on error 1205, because some deadlocks remain possible in any concurrent system.

Watch out

Saying deadlocks are “just bad blocking” or that NOLOCK fixes them. Also avoid “increase the lock timeout”: a deadlock is not a timeout, and no amount of waiting resolves a cycle.

Example

-- Recent deadlock graphs captured by the system_health session
SELECT xed.value('@timestamp', 'datetime2') AS occurred_utc,
       xed.query('.') AS deadlock_report
FROM (
    SELECT CAST(target_data AS xml) AS target_data
    FROM sys.dm_xe_session_targets AS st
    JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed);

Back to all questions ↑

Q15WorkingPerformance Troubleshooting

What are wait statistics and how do you use them?

Short answer

Whenever a task can’t continue, SQL Server records what it’s waiting for: a lock, a page from disk, a memory grant, the log, other parallel threads, or the client reading results. Wait statistics show where time goes when queries aren’t running on CPU. I look at them instance-wide as a delta over a window, compared with a baseline, and per session or request when I’m chasing one problem. They give me a direction, which I then confirm with other evidence.

Why it matters in production

Waits turn “the server is slow” into a direction: locking, storage, memory, log, parallelism or the application.

What the interviewer is looking for

A strong answer usually demonstrates that you filter out benign waits (background tasks that are always waiting), compare against normal behavior, and confirm with other evidence before acting. It also knows the scopes and the main wait families:

  • Instance-wide (sys.dm_os_wait_stats): cumulative since the last restart, useful only as a delta or against a baseline.
  • Session (sys.dm_exec_session_wait_stats, SQL Server 2016 and later): what one session waited on.
  • Current request (sys.dm_exec_requests): what a query is waiting for right now.
  • Typical families: locks (LCK_M_*), data pages from disk (PAGEIOLATCH_*), memory grants (RESOURCE_SEMAPHORE), the transaction log (WRITELOG), parallelism (CXPACKET, CXCONSUMER), and the client (ASYNC_NETWORK_IO).
  • Each wait has a resource part and a signal part (resource available, waiting for a CPU). High signal wait time compared with total wait time suggests CPU pressure.

Watch out

Tuning from one wait name in isolation. High CXPACKET does not mean “set MAXDOP to 1”; it often means large scans that a better plan would avoid. PAGEIOLATCH may be slow storage, or a query reading far more data than it should. ASYNC_NETWORK_IO is usually the application consuming rows slowly, not the network.

Back to all questions ↑

Q16SeniorPerformance Troubleshooting

How would you troubleshoot a high-CPU SQL Server?

Short answer

First I confirm SQL Server is actually the process using the CPU; if it isn’t, query tuning won’t help. Then I work from now to history. What’s running right now, ordered by CPU? Which queries used the most CPU over time in Query Store, and is that new? Total CPU matters more than per-execution CPU. Then I read the plans of the top consumers, looking for big scans, bad estimates or implicit conversions, and check compilation rates and parallelism. Finally I compare with a normal baseline. I keep the diagnosis lightweight and don’t restart anything.

Why it matters in production

At 100% CPU everything slows down, including the diagnostic queries. Keep investigation light: read DMVs, avoid heavy traces, and do not restart the instance, which erases the evidence and the plan cache.

What the interviewer is looking for

A strong answer usually demonstrates production-safe diagnosis: verify the source, find the top consumers, explain why they burn CPU, fix the cause (plan regression, missing index, parameterization), then compare with the baseline. In detail:

  1. Confirm the consumer. Host-level counters (or sys.dm_os_ring_buffers scheduler monitor records) separate SQL Server CPU from other processes, antivirus or a backup agent.
  2. Current requests. sys.dm_exec_requests ordered by cpu_time, with the query text and plan. One query, or many running concurrently? Many runnable tasks waiting for a scheduler (high signal waits, SOS_SCHEDULER_YIELD)?
  3. Historical consumers. Query Store (Top Resource Consuming Queries by CPU) or sys.dm_exec_query_stats. A 50 ms query running 2,000 times per second beats a 30-second report.
  4. Plans of the top queries. Large scans, hash joins and sorts on big inputs, bad estimates, implicit conversions, non-sargable predicates, scalar functions running per row. Many of these came from a plan change, which Query Store can confirm.
  5. Compilations. High SQL Compilations/sec relative to batch requests points to ad hoc queries without parameterization or frequent recompiles.
  6. Parallelism. Many parallel plans at once can saturate CPUs. Check MAXDOP and cost threshold for parallelism in context, not as a reflex.
  7. Baseline. More users, a new feature or a batch job can explain it without anything being “wrong”.

Watch out

Lowering MAXDOP, adding CPUs or restarting as the first answer. Those hide the cause. Also avoid DBCC FREEPROCCACHE: the recompilation storm it triggers adds more CPU load.

Example

SELECT TOP (10) qs.total_worker_time / 1000 AS total_cpu_ms, qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
         (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE qs.statement_end_offset END
          - qs.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

Back to all questions ↑

Q17SeniorPerformance Troubleshooting

How would you troubleshoot high logical reads?

Short answer

High logical reads mean a query touches far more pages than it needs. First I find the top readers in Query Store or the plan cache, by total and per execution. Then I use STATISTICS IO to see which table the reads come from, and read the actual plan for that access. I look for scans where few rows are needed, seeks with residual predicates that discard most rows, lookups or seeks repeated inside a nested loop, and bad estimates. Sometimes the fix is in the query itself: fewer columns, fewer rows, set-based logic.

Why it matters in production

Reads are a stable, comparable metric, unlike duration, which depends on concurrency. A logical read is one 8 KB page from the buffer pool, so reducing reads usually reduces CPU, memory pressure, physical I/O and blocking at the same time.

What the interviewer is looking for

A strong answer usually demonstrates that you trace reads to a specific table and operator, then fix the cause: index design, predicate rewrite, fewer columns or rows, or a better estimate. The method:

  1. Identify the top readers. Query Store or sys.dm_exec_query_stats ordered by total logical reads, and per execution. A query that reads 500,000 pages to return 20 rows is the target.
  2. Measure precisely. SET STATISTICS IO ON shows reads per table.
  3. Read the actual plan for that table’s access.
    • A scan where a few rows are needed: is there an index whose key matches the predicate? Is the predicate sargable, or hidden by a function, an implicit conversion or a leading wildcard?
    • A seek with a residual predicate that reads many rows and discards most of them: the index key does not match the filter well.
    • A seek or key lookup inside a nested loop executing hundreds of thousands of times: a bad estimate on the outer input, or a missing covering column.
  4. Check estimates. A row estimate far below actual often explains why the optimizer chose nested loops and lookups.
  5. Question the query itself. SELECT * pulling wide columns, reading years of history for a report on one month, repeated calls in a loop that should be one set-based query, or a data model that forces many joins.

Watch out

“Scans are bad, seeks are good.” A scan of a small table, or of most rows of a table, is the right choice. A seek executed a million times can read more than one scan. Judge by reads and rows, not operator names.

Back to all questions ↑

Q18WorkingT-SQL & Large Data Changes

When would you use temp tables instead of table variables?

Short answer

Both live in tempdb; the real difference is what the optimizer knows about them. A temp table has column statistics, supports any index, and can trigger recompiles when its row count changes, so I use it for larger or variable row counts that feed joins and aggregations. A table variable has no column statistics and only inline indexes. Since SQL Server 2019, deferred compilation gives it a real row count, but still no distribution. I’d use it for small sets or table-valued parameters.

Why it matters in production

A wrong row estimate on an intermediate result spreads through the rest of the plan: nested loops over 500,000 rows, memory grants too small, spills. Changing one @table to a #table is a common, effective fix for this kind of regression.

What the interviewer is looking for

A strong answer usually demonstrates that you choose by size, need for statistics and indexes, and recompilation behavior, and know how the behavior changed with SQL Server 2019:

  • A temp table (#orders) behaves like a normal table: column statistics, any index you create after it exists, and recompiles when its row count changes significantly.
  • A table variable (@orders) has no column statistics, only indexes declared inline (primary key, unique, or inline INDEX), and does not cause recompiles. Historically the optimizer guessed one row. Since SQL Server 2019 (compatibility level 150), table variable deferred compilation compiles the statement at first execution using the actual row count, but there is still no distribution information, and the count is fixed for later executions of the cached plan.
  • Other differences: a table variable is scoped to the batch, is not affected by a transaction rollback, and can pass data as a table-valued parameter.

Watch out

Absolute claims such as “table variables always estimate one row” or “table variables are in memory.” Both are outdated or wrong. The choice depends on the workload and version; test it with an actual plan.

Back to all questions ↑

Q19SeniorT-SQL & Large Data Changes

How do you safely update or delete millions of rows?

Short answer

Not in one statement and one transaction. That fills the transaction log, holds locks the whole time, almost certainly escalates to a table lock, and a failure means an equally long rollback. Instead I work in small batches that each commit quickly, walking an indexed key range so every batch is a seek. I record progress so the job can stop and resume, watch log usage, blocking and replicas, and pause between batches on busy systems. Before starting, I confirm the row count and make sure there’s a tested way back.

Why it matters in production

Large data changes are a classic cause of incidents: full logs, blocked applications, lagging replicas and hours-long rollbacks. Batching turns one risky operation into many small, controllable ones. In a single transaction, the log cannot be truncated until it finishes, and SQL Server tries lock escalation at around 5,000 locks on one object.

What the interviewer is looking for

A strong answer usually demonstrates that you think about the log, locks, replicas and recovery, not just the SQL statement:

  • Small transactions, typically a few thousand rows each, so each batch commits quickly, releases its locks and stays below lock escalation. Tune the size by measuring duration and blocking.
  • Deterministic batches that walk an indexed key range (WHERE Id > @last AND Id <= @last + @batch) instead of repeatedly scanning for “the next 5,000 matching rows”, which gets slower as the table empties.
  • An index supporting the batch predicate, so each batch is a seek, not a scan.
  • Resumability: record progress (the last key processed) so the job can stop and restart safely.
  • Log management: in the full recovery model, make sure log backups run often enough during the job; in an availability group, watch the redo and send queues.
  • Pauses or throttling between batches on busy systems, and running during low-activity windows.
  • Monitoring and validation: track rows affected per batch, blocking, log usage and replicas; verify counts when finished.
  • Before the first batch, confirm the WHERE clause with a SELECT COUNT(*) and make sure there is a tested way back: a backup, or a copy of the affected rows.

Watch out

TOP (n) batches without an ORDER BY or key range on a large table, which rescans the same pages repeatedly. Also, when most rows go, copying the rows to keep into a new table and swapping can be faster than deleting the rest.

Example

DECLARE @batch int = 5000, @last bigint = 0, @max bigint;
SELECT @max = MAX(OrderID) FROM dbo.Orders WHERE OrderDate < '2020-01-01';

WHILE @last < @max
BEGIN
    DELETE FROM dbo.Orders
    WHERE OrderID > @last AND OrderID <= @last + @batch
      AND OrderDate < '2020-01-01';

    SET @last += @batch;  -- persist @last somewhere to resume
    WAITFOR DELAY '00:00:01';  -- give other sessions room
END;

Back to all questions ↑

Q20SeniorPerformance Troubleshooting

A production SQL Server is slow, but CPU, memory and storage all look normal. What do you investigate next?

Short answer

Normal resource graphs mean the time is going somewhere else: queries are waiting, doing more work than they should, or the slowness is outside the engine. First I define the symptom precisely: which application, which operations, since when. Then I look at what requests are doing right now, running or waiting, and on what. With low CPU, blocking is the most common answer, so I find the head blocker. After that I compare waits and Query Store history with a good period, looking for plan regressions, parameter sensitivity and excessive reads. Then I check workload changes, application behavior and recent deployments.

Why it matters in production

“Resources are fine” often leads teams to look away from the database, or to add hardware. Many of the worst incidents (blocking chains, worker exhaustion, plan regressions, chatty applications) happen with idle CPUs.

What the interviewer is looking for

A strong answer usually demonstrates evidence-driven troubleshooting: a clear symptom, running versus waiting, blocking and waits, history from Query Store, comparison with a baseline, willingness to look at the application, and a fix verified against the same metrics. The full sequence:

  1. Define “slow”. A precise symptom (“checkout takes 12 seconds since Tuesday’s release”) narrows the search more than any counter.
  2. What requests are doing right now. sys.dm_exec_requests: running or waiting, and on what. This is the fastest split.
  3. Blocking. Find the head blocker: a long transaction, an open transaction from the application, a batch job or maintenance holding locks. Check the transaction scope in the code, not just the query.
  4. Waits over a window, compared with a baseline: lock waits, memory grant waits, WRITELOG on commit-heavy workloads, THREADPOOL (worker exhaustion while CPU looks idle), ASYNC_NETWORK_IO (the client is slow to consume results).
  5. Plan regressions. Query Store, comparing this period with a good one: new plans, higher duration or reads for the same query.
  6. Parameter sensitivity and cardinality. One plan reused for skewed values; estimates far from actuals after data growth or a statistics change.
  7. Excessive logical reads. A query that reads ten times more pages but still fits in memory raises duration without moving storage graphs much.
  8. Concurrency and workload changes. More executions per second of the same query, a new feature, a report running at peak hours. Per-execution metrics look fine; totals do not.
  9. Application behavior. Chatty patterns (thousands of round-trips per page), N+1 queries from an ORM, connection pool exhaustion, client-side processing, retries. Server-side duration looks short, but the user waits.
  10. Recent changes. Deployments, schema or index changes, compatibility level, configuration, failover, maintenance jobs.

The network comes in only when the evidence points there, for example ASYNC_NETWORK_IO with a client that consumes results quickly, or a difference between application servers.

Watch out

“Check CPU and add indexes,” or restarting the service. A restart clears blocking and the plan cache, so the symptom disappears with the evidence, and returns later.

Back to all questions ↑

Keep preparing

New questions are added as they are written. Interview questions for other topics live on the interview questions hub. The best preparation is still hands-on: follow the SQL Server Foundations path, work through the SQL Server performance tuning workflow, and keep the SQL Server cheat sheets close for quick reference.