Module 1 Quiz: SQL Server Architecture Fundamentals
10 questions, mixing conceptual theory with diagnostic interpretation — including a couple of deliberate misconception traps. Try each one yourself before revealing the answer.
1. What happens to a data page’s copy in the buffer pool after a row on it is updated, before a checkpoint?
A) Immediately discarded
B) Marked “dirty” and kept in memory until written to disk
C) Instantly written to disk
D) Moved to the plan cache
Show Answer
Answer: B
A modified page in the buffer pool is marked “dirty” and flushed to disk later (checkpoint or lazy writer) — not instantly, which is why the transaction log (not the data file) is what guarantees durability in the meantime.
2. Which SQL Server component is responsible for its own non-preemptive thread scheduling, largely independent of the OS scheduler?
A) The buffer pool
B) SQLOS
C) The transaction log
D) tempdb
Show Answer
Answer: B
SQLOS provides its own scheduling layer mapped roughly to CPU cores — this is why SQL Server CPU behavior differs from typical multi-threaded applications.
3. “Gotcha”: A DBA claims adding more RAM always fixes a slow query. What’s the flaw?
A) RAM never helps performance
B) It only helps if the bottleneck is actually buffer pool pressure/disk I/O, not CPU or locking
C) SQL Server ignores available RAM
D) More RAM always slows queries down
Show Answer
Answer: B
More RAM grows the buffer pool, which helps only when pages are being evicted and re-read from disk. It does nothing for a CPU-bound or lock-bound query — evidence-first diagnosis tells you which one you actually have.
4. What determines the execution plan cached for a parameterized query on its first compilation?
A) The average of all future parameter values
B) The specific parameter value(s) used at first compilation
C) SQL Server always recompiles per call
D) The plan is chosen randomly
Show Answer
Answer: B
This is parameter sniffing — the plan cache reuses the plan compiled for the first parameter value seen, which can be wrong for very differently-shaped later calls.
5. What is the size of a single SQL Server data page?
A) 4KB
B) 8KB
C) 16KB
D) 64KB
Show Answer
Answer: B
8KB per page is fixed and hasn’t changed across SQL Server versions; 8 contiguous pages (64KB) form one extent.
6. What guarantees a committed transaction survives a crash, per the ACID Durability property?
A) The buffer pool
B) The plan cache
C) The transaction log, written before the transaction is considered committed
D) tempdb
Show Answer
Answer: C
Write-Ahead Logging means the log record is durably written before commit acknowledgment — the data file itself may still be updated later.
7. “Gotcha”: True or false — a row can never span more than one 8KB page.
A) True, always
B) False — row-overflow and LOB storage allow larger rows to span pages
Show Answer
Answer: B
Wide rows (e.g. many VARCHAR(MAX) columns) can exceed one page via row-overflow storage — a common source of unexpected I/O for “wide” tables.
8. Which DMV shows current buffer pool memory usage by counting cached pages?
A) sys.dm_os_sys_memory
B) sys.dm_os_buffer_descriptors
C) sys.dm_exec_cached_plans
D) sys.dm_os_wait_stats
Show Answer
Answer: B
sys.dm_os_buffer_descriptors has one row per cached page; sys.dm_os_sys_memory reports overall system memory instead.
9. A bulk delete on a large table is taking hours and generating huge log growth. What architectural fact explains this?
A) DELETE doesn’t use the transaction log
B) Every deleted row is individually logged under Write-Ahead Logging
C) The buffer pool is too small always
D) This is unrelated to architecture
Show Answer
Answer: B
Row-by-row logging of a large DELETE is exactly why bulk deletes on huge tables can take hours and balloon the log — this sets up the Module 3 case study.
10. What does DBCC SQLPERF(LOGSPACE) report?
A) Buffer pool hit ratio
B) Plan cache size
C) Transaction log size and percent used, per database
D) CPU scheduler queue length
Show Answer
Answer: C
A quick, classic first check when a log is growing unexpectedly or a “log full” error appears.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.