SQL Server Interview

Advanced Query Optimization

SQL Server interview questions covering advanced query optimization.

20 Questions Filter by level

Interview questions

Open a question for the full answer, code and interview guidance.

20 Results
201
SeniorAdvanced

What is the SQL Server Query Optimizer?

The Query Optimizer is the cost-based component of SQL Server that evaluates possible execution strategies for a query and selects an execution plan it estimates will have a relatively low cost. It considers available indexes, statistics, estimated row counts, join order, join algorithms, parallelism, predicates, and many other factors. The optimizer does not exhaustively test every possible plan for complex queries because that would itself be too expensive; it searches the plan space using optimization rules and cost estimates. A performance problem can therefore arise when the optimizer makes a reasonable decision based on inaccurate estimates or incomplete information.

202
SeniorAdvanced

What is cardinality estimation?

Cardinality estimation is the process of predicting how many rows each operator in an execution plan will process or return. These estimates strongly influence index selection, join algorithms, join order, memory grants, and parallelism. If SQL Server estimates 10 rows but actually processes 1,000,000, the chosen plan may be inappropriate. Statistics, data distribution, predicate relationships, parameter values, and the Cardinality Estimator model all influence these estimates. Comparing Estimated Number of Rows with Actual Number of Rows is therefore an important performance diagnostic.

203
SeniorAdvanced

What causes inaccurate cardinality estimates?

Common causes include stale or insufficient statistics, skewed data, correlated columns, complex predicates, parameter sensitivity, table variables in some scenarios, expressions or functions applied to columns, implicit conversions, and optimizer assumptions that do not match the actual data. The solution depends on the cause. Possible actions include updating or improving statistics, rewriting predicates, using appropriate indexes, changing data modeling, or addressing parameter-sensitive behavior. Do not assume that every estimate mismatch should be fixed; focus on mismatches that materially affect plan quality.

204
SeniorAdvanced

What are the main physical join operators in SQL Server?

The three major physical join algorithms are Nested Loops, Hash Match, and Merge Join. Nested Loops is often effective when one input is small and the other can be accessed efficiently through an index. Hash Match is commonly effective for larger unsorted inputs, especially equality joins, but requires memory and can spill to tempdb if the grant is insufficient. Merge Join can be highly efficient when both inputs are appropriately sorted on the join keys. No join operator is inherently best. The correct choice depends on row counts, indexes, sorting, memory, and data distribution.

205
SeniorAdvanced

How does a Nested Loops join work?

Nested Loops takes rows from an outer input and, for each row, searches or scans the inner input for matching rows. It can be extremely efficient when the outer input is small and the inner input has a useful index. It can become expensive when the outer input unexpectedly contains many rows and each row causes additional inner-table work. When diagnosing a slow Nested Loops plan, inspect actual row counts, inner seeks/lookups, and whether the optimizer underestimated the outer input.

206
SeniorAdvanced

How does a Hash Match join work?

A Hash Match join generally builds a hash table from one input and probes it using rows from the other input. It is commonly used for equality joins involving relatively large datasets when useful ordering or indexes are unavailable. Hash joins require memory. If the memory grant is insufficient, SQL Server may spill hash data to tempdb, increasing I/O and elapsed time. A Hash Match is not automatically evidence of a bad query. It can be the most efficient operator for the workload.

207
SeniorAdvanced

How does a Merge Join work?

A Merge Join processes two inputs ordered by the join keys and advances through them to match values. It is particularly effective for large, already-sorted datasets. If the inputs are not naturally ordered by suitable indexes, SQL Server may need Sort operators before the merge, which can change the cost significantly. Merge joins commonly support equality joins and can be very efficient when appropriate ordering already exists.

208
SeniorAdvanced

What is a Sort operator and why can it be expensive?

A Sort operator orders rows for requirements such as ORDER BY, DISTINCT, Merge Join, ranking, or other plan operations. Sorting can consume significant CPU and memory. If the memory grant is insufficient, the sort can spill to tempdb. Large sorts may sometimes be avoided by using an index whose key order already matches the required access pattern. However, an index should not be added merely to eliminate every Sort operator; the write and storage costs must also be considered.

209
SeniorAdvanced

What is a memory grant in SQL Server?

A memory grant is workspace memory reserved for an executing query, primarily for operators such as Sort and Hash Match. If the grant is too small, an operator may spill to tempdb. If it is excessively large, the query can reserve memory it does not need and reduce concurrency by making other queries wait for workspace memory. Memory grant quality depends heavily on cardinality estimates. Modern SQL Server versions include memory grant feedback features for eligible workloads that can adjust future grants based on previous executions.

210
SeniorAdvanced

What is a spill to tempdb?

A spill occurs when an operator such as Sort or Hash Match does not have enough granted memory to perform its work entirely in memory and writes intermediate data to tempdb. Spills can increase I/O and query duration substantially, particularly with large datasets. Actual execution plans can expose spill warnings and runtime details. The underlying cause may be inaccurate cardinality estimates, insufficient memory, excessive row width, poor query design, or an unsuitable plan. Simply increasing server memory is not always the correct solution.

211
SeniorAdvanced

What is parallel query execution?

Parallelism allows SQL Server to divide parts of a query across multiple worker threads so work can be performed concurrently on multiple CPU schedulers. Parallel plans can improve performance for expensive analytical queries, but parallelism also has coordination overhead. Operators such as Distribute Streams, Repartition Streams, and Gather Streams appear in parallel plans. Whether a query goes parallel depends on estimated cost, server configuration, query hints, available resources, and optimizer decisions.

212
SeniorAdvanced

What are MAXDOP and Cost Threshold for Parallelism?

MAXDOP controls the maximum degree of parallelism available to an individual parallel query. Cost Threshold for Parallelism influences when SQL Server considers a parallel plan based on the estimated cost of a serial plan. These settings should be configured according to workload, hardware, NUMA topology, SQL Server version, and operational guidance. Using MAXDOP 1 globally merely to remove CX-related waits can unnecessarily disable beneficial parallelism. Per-query MAXDOP can also be specified when a specific workload has a justified requirement.

213
SeniorAdvanced

What is the SQL Server plan cache?

SQL Server can cache compiled execution plans so later executions of the same or compatible statements can reuse them instead of paying compilation cost every time. Plan reuse is generally beneficial, but cached plans can sometimes be inappropriate for later parameter values or changing data distributions. Excessive ad-hoc query text can also produce many single-use plans. Plan-cache troubleshooting should be evidence-based. Clearing the entire cache is disruptive and usually not an appropriate routine solution.

214
SeniorAdvanced

What is query recompilation?

Recompilation means SQL Server generates a new execution plan instead of reusing the existing cached plan. It can happen automatically after relevant changes or can be requested explicitly. OPTION (RECOMPILE) compiles the statement using values available for that execution and does not reuse that statement's compiled plan in the normal way. This can help highly parameter-sensitive queries, but repeated compilation consumes CPU. Recompilation is therefore a targeted technique rather than a default performance setting.

215
SeniorAdvanced

What is parameter-sensitive query behavior?

A parameter-sensitive query is one where different parameter values can require substantially different execution strategies. For example, one CustomerId may match five rows while another matches several million. A plan optimized for one distribution can perform poorly for the other. Traditional approaches include query redesign, OPTION (RECOMPILE), OPTIMIZE FOR, carefully designed dynamic SQL, or separating workload patterns. SQL Server 2022 introduced Parameter Sensitive Plan optimization for eligible queries, allowing multiple plan variants for different parameter ranges.

216
SeniorAdvanced

What is the OPTIMIZE FOR query hint?

OPTIMIZE FOR tells the optimizer to compile a statement using a specified parameter value rather than the value that might otherwise be used at compilation. OPTIMIZE FOR UNKNOWN asks the optimizer to use statistical distribution assumptions rather than optimize for a specific sniffed value. These hints can stabilize some parameter-sensitive workloads, but they encode assumptions about data distribution. They should be used only after measuring the workload and understanding why the existing plan is unsuitable.

217
SeniorAdvanced

How can implicit conversions hurt query performance?

An implicit conversion occurs when SQL Server automatically converts one data type to another so values can be compared or combined. When the conversion is applied to an indexed table column, it can prevent an efficient seek and produce poor cardinality estimates. A common application example is comparing an NVARCHAR parameter with a VARCHAR column or using incompatible numeric types. Application parameters should normally match the SQL column's data type, length, precision, and scale as closely as practical.

218
SeniorAdvanced

What is predicate pushdown?

Predicate pushdown refers to applying filtering as close as possible to the data-access stage so fewer rows need to flow through later operators. SQL Server's optimizer can often push eligible predicates into scans, seeks, joins, columnstore processing, or other operators automatically. This reduces I/O and CPU when the storage engine can eliminate rows earlier. Whether pushdown occurs depends on query structure, data types, expressions, storage technology, and optimizer capabilities.

219
SeniorAdvanced

How does Query Store help with performance regression?

Query Store maintains historical query text, plans, and runtime statistics. When a query suddenly becomes slower, Query Store can help determine whether its execution plan changed and compare the performance of different plans over time. Depending on SQL Server capabilities and operational policy, a known-good plan can sometimes be forced while the underlying cause is investigated. Query Store hints can also apply certain query hints without changing application SQL in supported versions. Plan forcing should be monitored because data distributions and workloads can change; it is not a permanent substitute for understanding the regression.

220
SeniorAdvanced

A query is fast in SSMS but slow from an ASP.NET Core application. How would you investigate it?

First confirm that SSMS and the application are truly executing equivalent SQL against the same database, server, data, parameters, and transaction context. Capture the application's actual SQL and parameter values using appropriate application logging, Query Store, Extended Events, or database monitoring. Compare execution plans and runtime statistics. Check parameter data types and lengths, parameter-sensitive plans, SET options, connection/database context, blocking, transaction scope, network latency, result-set size, and ORM-generated SQL. With EF Core, also inspect whether the application is causing N+1 queries, unnecessary tracking, premature materialization, client-side processing, excessive Include operations, or retrieving columns/rows it does not need. The key interview point is that 'fast in SSMS' does not prove the database query is identical to the application's workload. Troubleshooting must compare the actual execution contexts before selecting a fix.