SQL Server Interview

SQL Server Internals & Administration

SQL Server interview questions covering sql server internals & administration.

20 Questions Filter by level

Interview questions

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

20 Results
261
SeniorAdvanced

What is tempdb and what is it used for?

tempdb is a SQL Server system database used for temporary objects and internal workspace. It stores local and global temporary tables, table variables' underlying storage, worktables, sorts, hashes, spools, and row-versioning data used by relevant engine features. tempdb is recreated whenever SQL Server starts, so permanent application data should never be stored there. Heavy tempdb activity can become a performance bottleneck, making file configuration, storage latency, query design, and version-store usage important operational concerns.

262
SeniorAdvanced

Why can tempdb become a performance bottleneck?

tempdb can become heavily used by temporary tables, large sorts, hash spills, row versioning, index operations, ETL, and other internal operations. Potential problems include storage latency, insufficient capacity, excessive autogrowth, allocation contention, and queries that spill large amounts of data because of inadequate memory grants. Troubleshooting should identify which workload is consuming tempdb rather than assuming that adding more files will solve every tempdb problem.

263
SeniorAdvanced

How should tempdb data files be configured?

A common starting principle is to use multiple equally sized tempdb data files when allocation contention or workload characteristics justify it, with identical autogrowth settings. Modern SQL Server setup provides guidance and defaults based on logical processor count. The exact number of files should be determined from workload and contention evidence; continuously adding files is not a universal optimization. tempdb should also have adequate preallocated space and storage performance so routine workload does not depend on frequent autogrowth.

264
SeniorAdvanced

What is the SQL Server transaction log?

The transaction log records database modifications and other information required for transactional consistency, rollback, crash recovery, backup/recovery, and several high-availability or replication technologies. SQL Server uses write-ahead logging: relevant log records must be hardened before corresponding modified data pages are written to disk. The log is not simply a historical audit trail. It is a core part of the database engine's recovery architecture.

265
SeniorAdvanced

What is write-ahead logging (WAL)?

Write-ahead logging means SQL Server writes the log record describing a data modification to stable log storage before the associated modified data page is written to the data file. This allows SQL Server to recover committed transactions and undo incomplete transactions after a crash. Data pages can remain modified in memory for some time after commit, while the required transaction log records have already been hardened according to the durability model.

266
SeniorAdvanced

What is a checkpoint?

A checkpoint writes dirty data pages for a database from memory to disk as part of SQL Server's recovery and buffer-management process. Checkpoints help limit the amount of work required during crash recovery. They do not mean every transaction log record can immediately be discarded; log truncation depends on recovery model and other factors that can prevent log reuse. SQL Server supports automatic, manual, indirect, and internal checkpoint behavior depending on configuration and circumstances.

267
SeniorAdvanced

What are VLFs in the transaction log?

VLF stands for Virtual Log File. SQL Server divides each physical transaction log file internally into VLFs. The number and size of VLFs are influenced by log creation and growth history. Excessive numbers of small VLFs can negatively affect some recovery and log-management operations. Modern SQL Server versions have improved VLF creation algorithms, but administrators should still size log files sensibly and avoid repeated tiny autogrowth events.

268
SeniorAdvanced

Why should transaction log files be pre-sized appropriately?

If the log repeatedly grows during normal workload, SQL Server must perform autogrowth operations and create additional VLFs. Growth events can introduce latency and lead to an undesirable log layout. The log should be sized for normal workload peaks, index maintenance, bulk operations, and recovery requirements, with a sensible fixed-size growth increment. Routine shrinking followed by regrowth is usually counterproductive. Fix the cause of excessive growth and then establish an appropriate steady-state size.

269
SeniorAdvanced

What is log truncation?

Log truncation marks inactive portions of the transaction log as reusable. It does not normally reduce the physical size of the .ldf file. Whether truncation can occur depends on the recovery model and whether anything still requires the log records. In FULL recovery, regular transaction log backups are generally required for normal log reuse after the log backup chain is established. The sys.databases log_reuse_wait_desc column helps identify why log space cannot currently be reused.

270
SeniorAdvanced

What is the buffer pool?

The buffer pool is a major SQL Server memory area used to cache database pages and reduce repeated physical disk reads. When a query needs a data or index page, SQL Server checks whether it is already in memory. If not, the page is read from storage into the buffer pool. Modified pages become dirty pages and can later be written to disk. Good performance depends on the interaction of memory, storage, query patterns, indexes, and workload—not simply on maximizing cache hit percentages.

271
SeniorAdvanced

What is the plan cache?

The plan cache stores compiled execution plans and related structures so SQL Server can reuse them for compatible statements. Plan reuse reduces compilation CPU, but poor parameter-sensitive plans or large numbers of single-use ad-hoc plans can create problems. DBAs and developers should investigate cache behavior using DMVs and Query Store rather than routinely clearing the plan cache, because clearing it can cause a burst of recompilation and temporarily degrade performance.

272
SeniorAdvanced

What are wait statistics?

Wait statistics summarize what SQL Server worker threads have been waiting for, such as storage I/O, locks, CPU scheduling, network activity, memory grants, or parallelism coordination. They are one of the most useful tools for identifying broad performance bottlenecks. However, waits are symptoms, not diagnoses. A high wait category must be interpreted in the context of workload, time interval, baseline, and query behavior. Instance-level waits accumulated since startup are often less useful than deltas measured over a representative problem period.

273
SeniorAdvanced

What are some common SQL Server wait types?

Examples include PAGEIOLATCH waits associated with waiting for database pages to be read from storage, LCK_M_* waits associated with lock blocking, RESOURCE_SEMAPHORE associated with query memory grant waits, WRITELOG associated with transaction-log flush latency, and SOS_SCHEDULER_YIELD associated with CPU scheduler pressure or CPU-intensive execution. Parallel-query waits include CXPACKET and CXCONSUMER, whose interpretation depends on SQL Server version and workload. No wait type should be treated as automatically bad; identify the queries and resource conditions producing it.

274
SeniorAdvanced

What is Extended Events?

Extended Events is SQL Server's lightweight event infrastructure for capturing detailed engine activity. It can record events related to queries, errors, deadlocks, waits, logins, recompilations, blocking, and many other areas. Sessions can apply filters and capture selected event fields and actions to targets such as event files. Extended Events is generally preferred over SQL Trace/Profiler for modern production diagnostics because it is more flexible and designed for lower-overhead event collection.

275
SeniorAdvanced

What is the system_health Extended Events session?

system_health is a built-in Extended Events session that SQL Server starts automatically. It captures useful diagnostic information about important engine conditions. It can contain information about deadlocks and other significant events, making it a valuable first place to investigate certain production incidents. Its retention is limited, so organizations requiring longer history should create purpose-built monitoring and event-retention processes.

276
SeniorAdvanced

What is SQL Server memory pressure?

Memory pressure occurs when SQL Server or the operating system does not have enough memory to satisfy workload demands efficiently. Symptoms can include reduced cache residency, increased physical I/O, memory grant waits, query spills, or operating-system pressure. Causes may include an inappropriate max server memory setting, oversized memory grants, concurrent analytical workloads, other processes consuming memory, or simply insufficient physical RAM. Troubleshooting requires examining both SQL Server and operating-system memory indicators.

277
SeniorAdvanced

What is max server memory?

max server memory limits how much memory SQL Server's buffer pool and certain associated memory allocations can consume under the setting's scope. It should normally be configured so the operating system and other processes have sufficient memory. Setting it equal to total physical RAM can create OS memory pressure. The correct value depends on total RAM, SQL Server features, workload, other services on the host, and deployment architecture.

278
SeniorAdvanced

What are SQL Server data files and log files?

SQL Server databases use data files to store database pages and transaction log files to store sequential log records. Primary data files commonly use the .mdf extension, secondary data files commonly use .ndf, and log files commonly use .ldf, although extensions are conventions rather than the fundamental storage definition. Data and log files have different access patterns and recovery roles. Multiple log files generally do not improve log throughput because SQL Server writes through the active log structure sequentially rather than striping log writes across files.

279
SeniorAdvanced

What are filegroups?

A filegroup is a logical container for one or more SQL Server data files. The PRIMARY filegroup exists by default, and additional user-defined filegroups can be created. Filegroups can support administrative, placement, partitioning, backup/restore, and large-database design requirements. They are not automatically a performance improvement; benefits depend on storage architecture and workload. Transaction log files are not members of data filegroups.

280
SeniorAdvanced

SQL Server becomes slow during peak hours but appears normal later. How would you investigate it?

The investigation should capture evidence during the peak period rather than relying only on the system after the problem disappears. Establish the time window and inspect Query Store, monitoring history, Extended Events, wait-stat deltas, CPU, storage latency, memory pressure, tempdb usage, blocking, deadlocks, active sessions, memory grants, transaction-log latency, and connection/workload volume. Identify which queries consumed the most resources during the affected period and compare their plans and runtime statistics with normal periods. Also check application changes, scheduled SQL Agent jobs, backups, ETL, index maintenance, reporting, and external workload spikes. The key senior-level interview point is to correlate SQL Server symptoms with the exact time window and workload. A server that looks healthy after peak time does not prove that CPU, I/O, blocking, memory, or concurrency were healthy during the incident.