Module 2 Quiz: SQL Server Indexing Strategy
10 questions covering index decision-making, fragmentation, statistics, and columnstore — including the classic “always rebuild” misconception.
1. What does sys.dm_db_missing_index_details actually represent?
A) Indexes that were deleted
B) Indexes the optimizer logged wishing existed for past queries
C) Corrupted indexes
D) A list of all indexes on the server
Show Answer
Answer: B
It’s a hypothesis generator based on what the optimizer wanted, not a guaranteed-correct instruction — always verify against real query patterns first.
2. Why might an index with high user_seeks still be a bad index to keep?
A) This is never the case
B) If its write-maintenance cost on a heavily-written table outweighs the read benefit
C) Seeks are always bad
D) High seeks always mean the index is perfect
Show Answer
Answer: B
Every index decision is a trade-off — reads must be weighed against write cost on the same table, not evaluated in isolation.
3. At what fragmentation level does REORGANIZE typically become the recommended action?
A) 0-5%
B) 5-30%
C) Always, regardless of level
D) Only above 90%
Show Answer
Answer: B
Below 5%, do nothing. 5-30%, REORGANIZE (lighter, always online). Above 30%, REBUILD is the standard threshold.
4. “Gotcha”: Is “always rebuild indexes weekly regardless of fragmentation” a sound practice?
A) Yes, it’s always correct
B) No — it wastes CPU/IO on indexes that were never fragmented enough to matter
C) Rebuilding has no cost
D) Fragmentation is irrelevant to performance
Show Answer
Answer: B
This is one of the most common DBA misconceptions — measure fragmentation first (sys.dm_db_index_physical_stats), don’t schedule blind maintenance.
5. A small, rarely-scanned table shows 60% fragmentation. How much does this typically matter?
A) Critical, fix immediately
B) Usually very little — fragmentation mainly affects large tables read via range scans
C) It always causes corruption
D) It doubles storage cost
Show Answer
Answer: B
A tiny table accessed via seeks is largely unaffected by fragmentation — context (table size, access pattern) always matters more than the raw percentage.
6. What typically triggers SQL Server’s automatic statistics update, by default?
A) Every single INSERT
B) Roughly 20% of rows changing (with newer granular thresholds for large tables)
C) Never automatically
D) Only on server restart
Show Answer
Answer: B
The classic threshold is ~20% row modification; recent SQL Server versions added more granular auto-update behavior for very large tables.
7. A query suddenly gets a bad plan right after a huge bulk load. What’s the most likely first suspect?
A) Stale statistics not yet reflecting the new data volume
B) Corrupted transaction log
C) A hardware failure
D) Buffer pool being too large
Show Answer
Answer: A
Bulk loads change row counts dramatically; if auto-update hasn’t caught up, the optimizer estimates against outdated statistics — check sys.dm_db_stats_properties.
8. What’s the core structural difference between rowstore and columnstore indexes?
A) No real difference
B) Rowstore stores full rows together; columnstore stores each column together, compressed
C) Columnstore is just a faster rowstore
D) Rowstore is only for backups
Show Answer
Answer: B
This structural difference is exactly why columnstore compresses better and scans faster for analytical aggregation, but is worse for single-row OLTP lookups.
9. What execution mode do columnstore index queries typically use that boosts performance on large scans?
A) Row mode
B) Batch mode — processing ~900 rows per operator call
C) Single-threaded mode only
D) There’s no special execution mode
Show Answer
Answer: B
Batch mode dramatically reduces per-row CPU overhead for large aggregations compared to traditional row-by-row execution.
10. Why is a columnstore index usually a poor primary index choice for an OLTP order-lookup table?
A) Columnstore is always slower than rowstore
B) It’s optimized for bulk scan/aggregate patterns, not frequent single-row point lookups/updates
C) Columnstore can’t store integers
D) It’s deprecated
Show Answer
Answer: B
“Fetch order #4471” is exactly the point-lookup pattern columnstore is not designed for — reserve it for fact tables and reporting/analytical workloads.
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.