SQL Server Flashcards

In SQL Server, which log sequence number (LSN) marks the oldest active transaction's begin record and is the primary driver of how far back the log must be retained?

Answer: MinLSN (Minimum Recovery LSN)

The MinLSN is the LSN of the oldest begin transaction record of any active transaction; the log cannot be truncated past this point because crash recovery must replay from here.

Which SQL Server feature allows you to specify that a column should only store non-NULL values sparsely, reducing storage for columns that are mostly NULL?

Answer: SPARSE columns

SPARSE columns optimize storage for columns with many NULL values by using no storage for NULLs at the cost of slightly more overhead per non-NULL value.

What is the purpose of SQL Server's 'Availability Group Listener' in a multi-subnet configuration?

Answer: Provides a single virtual IP that follows the primary replica across subnets using DNS TTL rotation

The AG Listener uses a virtual network name with multiple IP addresses (one per subnet); DNS TTL is set to 0 so clients quickly discover the new IP when the primary fails over to a different subnet.

In SQL Server's Lock Manager, what is 'lock escalation' and what is the default threshold that triggers it?

Answer: Promotion from row/page locks to a table lock when a transaction holds ~5,000 locks or consumes 40% of the lock memory

Lock escalation promotes granular locks to a coarser table-level lock when a statement acquires ~5,000 locks or when locks consume 40% of dynamic lock memory, reducing overhead.

Which SQL Server component is responsible for writing dirty pages from the buffer pool to disk on a periodic basis to advance the checkpoint LSN?

Answer: Database Writer (DBWRITER)

The Database Writer (DBWRITER) background thread periodically flushes dirty pages from the buffer pool to the data files, allowing the checkpoint LSN to advance and enabling log truncation.

In SQL Server, what does the DMV sys.dm_os_memory_clerks provide that sys.dm_os_process_memory does not?

Answer: Per-component memory allocation breakdown within the SQL Server process

sys.dm_os_memory_clerks shows how internal components (buffer pool, plan cache, connection memory, etc.) allocate memory within SQL Server, whereas sys.dm_os_process_memory shows overall process-level memory counters.

Which SQL Server execution plan operator indicates that the query optimizer chose to perform a Hash Join but at runtime switched to a nested-loop join due to a row count underestimate?

Answer: Adaptive Join operator

The Adaptive Join operator (SQL Server 2017+) defers the join type decision until after the build-side rows are counted, switching between Hash Join and Nested Loops based on actual row count vs. the adaptive threshold.