Expert MS SQL Interview Questions

Query tuning is the process of optimizing SQL statements so they consume fewer CPU cycles, memory, I/O, and locking resources. Poorly written queries slow down the entire SQL Server instance by overusing limited system resources.

The goal is predictable, scalable performance by reducing logical reads, avoiding unnecessary work, using optimal joins, minimizing spills, and improving concurrency.

Query tuning is the process of analyzing how SQL Server executes a query and optimizing it to minimize resource usage. Because SQL Server has finite CPU, memory, I/O, and concurrency capacity, an inefficient query can slow down the entire system. Tuning ensures optimal execution paths, fewer logical reads, reduced data movement, faster joins, and short lock durations. The goal is predictable, scalable performance.

The estimated plan shows SQL Server's predicted execution strategy based on statistics before running the query. The actual plan shows what really happened: row counts, spills, memory usage, and operator execution. Estimated plans are safe for production; actual plans reveal real bottlenecks like bad estimates, scans, or sorts. Both are essential for diagnosing performance issues.

SQL Server uses estimated row counts to choose join types, memory grants, and index strategies. If estimates are inaccurate, SQL Server may choose poor plans. Overestimation causes excessive memory allocation, while underestimation causes spills, nested loops, and excessive lookups. Accurate cardinality estimation is fundamental to stable performance.

A query spills when SQL Server lacks sufficient memory for operations like sort or hash. SQL offloads intermediate results to TempDB, causing heavy disk I/O and slow execution. Frequent spills indicate bad estimates, missing indexes, or insufficient memory. Repeated spills degrade overall SQL Server performance.

Parameter sniffing allows SQL Server to reuse cached execution plans based on the first parameter values supplied. If those values are atypical, the plan may be inefficient for later executions. This leads to performance instability. Solutions include OPTION(RECOMPILE), OPTIMIZE FOR UNKNOWN, local variables, or plan forcing when appropriate.

Join type determines how SQL Server matches rows. Nested loops are ideal for small sets, merge joins require sorted input, and hash joins work on large, unsorted sets. Incorrect join choices cause excessive I/O, CPU load, and slowdowns. SQL chooses join types based on row estimates and indexes, making accurate statistics essential.

Hash matches occur when SQL Server must build a memory-based hash table for joins or aggregates. They appear when inputs are unsorted or not indexed. They become bottlenecks when memory is insufficient, causing spills to TempDB. Hash operations can be CPU-intensive and degrade concurrency.

A SARGable expression allows SQL Server to use indexes efficiently. Non-SARGable predicates (functions on columns, mismatched types) force scans, increasing logical reads and slowing queries. SARGability is foundational for scaling queries on large datasets.

Elapsed time varies with workload and server load, but logical reads measure actual data touched. Reducing logical reads consistently reduces CPU, I/O, and cache pressure. Logical reads are the primary, stable metric for performance tuning.

A covering index includes all columns a query needs. This eliminates key lookups and reduces I/O. Covering indexes provide dramatic performance improvements in OLTP systems by minimizing data access and improving plan efficiency.

SQL Server chooses an index seek when predicates match the index key order and are selective. Otherwise, SQL chooses a scan to avoid expensive random lookups. Seek-friendly index design is critical for optimal performance.

Key lookups occur when a non-clustered index lacks required columns. SQL must fetch missing columns from the clustered index for each qualifying row. With many rows, this becomes slow and I/O-heavy. Solutions include covering indexes or query redesign.

Statistics describe column data distribution. SQL Server uses them to estimate row counts and choose efficient plans. Outdated or missing statistics lead to poor estimates and unstable performance. Keeping statistics fresh is essential for reliable query optimization.

Implicit conversions prevent index usage by forcing SQL Server to convert values at runtime. This leads to scans instead of seeks, higher CPU usage, and slower joins. Matching data types between columns and parameters is vital.

Fragmentation scatters index pages, causing more I/O during seeks and reducing read-ahead efficiency. High fragmentation slows down queries and increases disk activity. Regular index maintenance helps restore performance.

TempDB handles sorts, hashes, temp tables, versioning, and spill operations. Heavy workloads can cause latch contention and I/O bottlenecks. Optimizing TempDB improves concurrency and stabilizes system-wide performance.

Memory grants are allocations for joins, sorts, and aggregates. Overestimated grants waste memory; underestimated grants cause spills to TempDB. Accurate row estimation and indexing ensure proper memory usage.

Excessive recompilation forces SQL Server to repeatedly rebuild execution plans, increasing CPU consumption and causing unpredictable performance. Causes include volatile schema, unstable statistics, and widely varying parameters.

Large IN lists complicate cardinality estimation and increase parsing overhead. SQL Server may misestimate row counts, leading to inefficient join choices. Using temp tables or joins improves accuracy and performance.

Most query time is spent in one or two heavy operators like hash joins, sorts, or scans. Identifying these bottlenecks allows focused tuning efforts, resulting in faster optimization and greater performance gains.

Slow production queries usually result from:

• Missing or poorly designed indexes
• Incorrect cardinality estimates
• High fragmentation
• Outdated statistics
• Parameter sniffing issues
• Excessive key lookups
• TempDB contention or spills
• Implicit conversions
• Heavy sorts/hashes causing CPU pressure
• Blocking or deadlocks

Most performance issues are caused by a combination rather than a single factor.

To identify slow queries, examine:

• sys.dm_exec_query_stats – CPU, I/O, execution time
• sys.dm_exec_requests – currently running queries
• sys.dm_tran_locks – blocking analysis
• Extended Events – deep tracing
• Query Store – historical regressions

This helps pinpoint which query and which operator is causing server slowdown.

Blocking is identified using:

• sys.dm_exec_requests – blocking_session_id
• sys.dm_os_waiting_tasks – wait chains
• Activity Monitor – high-level visualization

Blocking chains often originate from a long-running transaction holding critical locks.

Blocking occurs when one transaction holds a lock and others wait behind it.

Deadlock occurs when two transactions wait on each other, creating a cycle.

SQL Server detects deadlocks and kills one transaction (the victim) to resolve the cycle.

Common indexing mistakes include:

• Too many non-clustered indexes
• Lack of covering indexes for hot queries
• Poor clustered key choice (wide, GUID, non-sequential)
• Duplicate or overlapping indexes
• Not using filtered indexes

These increase I/O, fragmentation, and reduce plan efficiency.

Large scans:

• Generate heavy I/O
• Evict useful pages from buffer pool
• Increase CPU usage
• Slow concurrent users

One bad scan can impact dozens of users in a production environment.

Sudden regressions often occur due to:

• Updated statistics
• Parameter sniffing plan changes
• Index changes
• Fragmentation spikes
• Failover causing cold cache
• Data growth affecting plan shapes

Query Store provides:

• Plan history recording
• Runtime performance metrics
• Ability to revert to stable plans
• Insight into parameter sniffing behavior

It acts as a "black box recorder" for SQL Server.

TempDB handles:

• Sorts
• Hash operations
• Version store
• Spills
• Temporary objects

Slow TempDB I/O becomes a bottleneck for the entire SQL Server instance.

The clustered key is included in all non-clustered indexes. Wide keys increase:

• Storage footprint
• Read/write cost
• Fragmentation
• Memory usage

Narrow, sequential keys are ideal.

Troubleshooting steps:

• Identify high-CPU queries in DMVs
• Check plans for sorts, hashes, conversions
• Validate statistics
• Review index usage
• Investigate parallelism waits
• Check server MAXDOP settings

Causes include:

• Large memory grants
• Hash and sort operations
• Poor cardinality estimates
• Too many concurrent queries
• SQL caching large data pages

CROSS JOINs produce Cartesian products. They may:

• Explode row counts
• Consume massive CPU
• Stress memory
• Cause system freezes

They must be used intentionally and carefully.

Use partitioning when:

• Tables contain hundreds of millions of rows
• Fast archiving is required
• Hot/cold data separation is needed
• Maintenance takes too long

Large deletes cause heavy logging, lock escalation, and blocking. Solutions:

• Batch deletes
• Partition switching
• Mark-and-archive patterns

The OUTPUT clause returns affected rows without re-querying the table. It is efficient for:

• Auditing changes
• Logging
• Data replication

Missing foreign keys:

• Prevent automatic referential integrity
• Hurt query optimization because relationships are unknown
• Cause poor join strategy selection

Extra columns increase:

• Index size
• Maintenance overhead
• Memory usage

Indexes should be narrow and purpose-specific.

Filtered indexes are ideal when:

• Queries target selective subsets
• Columns contain sparse values
• Large tables need optimized predicate performance

Before rewriting a query:

• Check execution plan
• Validate statistics
• Check indexes
• Identify costly operators
• Try small predicate adjustments
• Confirm rewrite is necessary

Most problems are fixed by plan adjustments, not rewrites.