Module 7 Quiz: SQL Server Advanced Performance Features
1. What problem from earlier in this course does In-Memory OLTP most directly address?
A) Missing indexes
B) Lock and latch contention under extreme concurrency (Module 5)
C) Stale statistics
D) SARGability
Show Answer
Answer: B
Memory-optimized tables use row-versioning and avoid traditional page structures, eliminating the lock/latch mechanisms entirely for those tables.
2. What makes a natively compiled stored procedure different from a regular one?
A) No difference at all
B) It compiles to actual machine code, with a restricted T-SQL surface area
C) It runs slower but is more flexible
D) It can only be called from the application tier
Show Answer
Answer: B
The performance ceiling is much higher, but you give up dynamic SQL and other T-SQL features to get it.
3. Is In-Memory OLTP a good default upgrade for a general-purpose reporting table?
A) Yes, always upgrade everything
B) No — it’s best reserved for confirmed extreme-contention scenarios, not a default choice
C) It’s required for all tables in modern SQL Server
D) Reporting tables can’t be disk-based
Show Answer
Answer: B
Reach for it only after evidence confirms lock/latch contention is the actual bottleneck — the Evidence-First principle applies here too.
4. What does a Resource Governor resource pool control?
A) Disk space quotas only
B) A capped slice of CPU and memory for a given workload group
C) User passwords
D) Backup schedules
Show Answer
Answer: B
Resource pools cap CPU/memory consumption, preventing one workload from starving another sharing the same instance.
5. What routes an incoming connection to a specific Resource Governor workload group?
A) The connection string alone
B) A classifier function evaluated at login
C) Random assignment
D) The database name only
Show Answer
Answer: B
The classifier function (e.g. checking SUSER_SNAME()) decides which workload group governs each new session.
6. Does Resource Governor fix a badly-written, missing-index query?
A) Yes, it automatically optimizes queries
B) No — it limits blast radius (CPU/memory cap), it doesn’t fix the query itself
C) It rewrites the query automatically
D) It adds missing indexes automatically
Show Answer
Answer: B
It’s a containment guarantee, not a substitute for the actual tuning work from Modules 2-4.
7. What does enabling memory-optimized tempdb metadata fix, and how does it differ from just adding more tempdb files?
A) It has the same effect as adding files
B) It moves tempdb’s system metadata into lock/latch-free structures, eliminating contention at the source rather than spreading it across files
C) It disables tempdb entirely
D) It only works on Azure
Show Answer
Answer: B
Multiple files (Module 5) dilute contention; memory-optimized metadata removes the underlying lock/latch mechanism for tempdb system tables entirely.
8. Is SQL Server Profiler available on Azure SQL Database?
A) Yes, identical to on-prem
B) No — Extended Events is the only option there
C) Only on weekends
D) Profiler is required, not optional, on Azure
Show Answer
Answer: B
This is exactly why Module 6 taught Extended Events thoroughly — it’s the portable skill across on-prem and Azure SQL Database.
9. Is Resource Governor available on Azure SQL Database (single/elastic pool)?
A) Yes, fully available
B) No — workload isolation instead comes from service tier/compute choice itself
C) Only for Enterprise Edition
D) It’s the default behavior with no configuration needed
Show Answer
Answer: B
On Azure SQL Database, the platform abstracts this — your service tier/vCore choice is the isolation mechanism instead.
10. Do the Evidence-First diagnostic principles from this entire course still apply on Azure SQL Database?
A) No, Azure requires an entirely different approach
B) Yes — the diagnostic principles are identical; only which infrastructure-level levers are directly available changes
C) Only Query Store works on Azure
D) Azure SQL doesn’t support DMVs
Show Answer
Answer: B
SARGability, execution plans, DMVs, and Query Store all apply the same way — Azure just abstracts some infrastructure control (Resource Governor, tempdb files, Profiler).
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.