Module 3 Quiz: SQL Server Query Optimization
10 questions covering SARGability, join algorithms, cardinality estimation, and the 13-Hour Delete case study.
1. Why does `WHERE YEAR(order_date) = 2024` defeat an index on order_date?
A) YEAR() is not a valid function
B) Wrapping the column in a function means SQL Server must evaluate it per row instead of seeking the sorted index
C) 2024 is not a valid year
D) It doesn’t defeat the index
Show Answer
Answer: B
The index is sorted by the raw column; applying a function breaks that direct usability, forcing a scan.
2. What’s the SARGable rewrite of `WHERE YEAR(order_date) = 2024`?
A) There is no rewrite possible
B) `WHERE order_date >= ‘2024-01-01’ AND order_date < '2025-01-01'`
C) `WHERE order_date = 2024`
D) `WHERE CAST(order_date AS INT) = 2024`
Show Answer
Answer: B
Expressing it as a range on the untouched column lets the optimizer seek directly.
3. Why is an implicit data type conversion (e.g. comparing a VARCHAR column to an INT literal) a dangerous SARGability killer?
A) It throws a clear error immediately
B) It silently defeats the index with no error, often invisible until real data volume
C) It’s actually faster
D) SQL Server doesn’t allow this
Show Answer
Answer: B
No error, no warning — just a scan that only becomes visibly slow once the table is large. This is exactly the “gotcha” the course outline calls out.
4. When does the optimizer typically favor a Nested Loop join?
A) When both tables are enormous
B) When one side is small and the other is indexed on the join key
C) Only for UPDATE statements
D) Never, it’s deprecated
Show Answer
Answer: B
Nested Loop is cheap for small × large-with-index, but scales poorly for large × large without one.
5. What does seeing a Hash Match on a query you expected to be a quick lookup usually signal?
A) Everything is fine
B) One side of the join is larger than expected, or a useful index is missing
C) The query is guaranteed to be fast
D) The database is corrupted
Show Answer
Answer: B
Hash Match appears when both sides are large with no useful sort order — unexpected on a query you thought was selective.
6. What does the Cardinality Estimator use to predict row counts at each plan step?
A) The actual data, read in advance
B) Statistics/histograms, not the real data it hasn’t seen yet
C) A random number generator
D) The table’s name
Show Answer
Answer: B
Estimates are predictions based on statistics — which is exactly why stale statistics (Module 2) directly cause bad cardinality estimates.
7. What is parameter sniffing?
A) A security vulnerability
B) A cached plan compiled for one parameter value being reused for a very differently-shaped later call
C) A type of index
D) Encrypting stored procedure parameters
Show Answer
Answer: B
The plan cache reuses the first-compiled plan; if later calls have very different data distribution, that plan can be badly wrong.
8. What’s a fast diagnostic (not permanent fix) to confirm parameter sniffing is the cause of a slow procedure call?
A) Restart the server
B) Run the call with WITH RECOMPILE and compare
C) Rebuild all indexes
D) There’s no way to confirm it
Show Answer
Answer: B
If forcing a fresh compile for that specific parameter value fixes performance, you’ve confirmed the cached plan was the problem.
9. In the 13-Hour Delete case study, what wait type dominated the captured evidence?
A) LCK_M_X (lock waits)
B) PAGEIOLATCH_SH (waiting to read data pages from disk)
C) CXPACKET (parallelism waits)
D) No waits were present
Show Answer
Answer: B
Overwhelming PAGEIOLATCH_SH waits pointed to disk I/O from a full scan — not locking, not CPU — which directed the investigation toward the missing index.
10. Why did the 13-Hour Delete’s root cause (a missing index on a foreign key) go unnoticed for so long?
A) SQL Server automatically indexes foreign keys, so this couldn’t happen
B) Foreign keys are NOT automatically indexed in SQL Server, unlike primary keys
C) The table was too small to matter
D) It was actually a hardware failure
Show Answer
Answer: B
Unlike the primary key side, SQL Server never auto-creates an index on a foreign key column — a very common latent performance trap on tables that grow over time.
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.