https://sqlmaster.mssqlplanner.com

Category: SQL Server Performance Tuning

Expert-level SQL Server performance tuning: architecture, indexing strategy, query optimization, execution plans, locking/concurrency, monitoring, and advanced performance features.

  • Module 7 Quiz: SQL Server Advanced Performance Features (10 Questions)

    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.

  • Module 1 Exercises: SQL Server Architecture Labs (10 Hands-On Exercises)

    Module 1 Exercises: SQL Server Architecture Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs — applying the Evidence-First workflow to real architecture questions.

    Guided Labs (step-by-step)

    Guided1. Measure your buffer pool’s current cached size and compare it to total server RAM.

    SELECT COUNT(*) * 8 / 1024 AS cached_data_mb FROM sys.dm_os_buffer_descriptors;
SELECT total_physical_memory_kb/1024 AS total_ram_mb FROM sys.dm_os_sys_memory;
    Guided2. Find the top 5 largest cached execution plans by size and note their objtype.

    SELECT TOP 5 objtype, size_in_bytes/1024 AS size_kb, usecounts
FROM sys.dm_exec_cached_plans ORDER BY size_in_bytes DESC;
    Guided3. Check your current transaction log size and percent used for a test database.

    DBCC SQLPERF(LOGSPACE);
    Guided4. Create a table, insert 10,000 rows, then check how many pages it occupies.

    CREATE TABLE dbo.PageDemo (id INT IDENTITY PRIMARY KEY, filler CHAR(500));
INSERT INTO dbo.PageDemo (filler)
SELECT TOP 10000 REPLICATE('x',500) FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SELECT page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PageDemo'), NULL, NULL, 'DETAILED');
    Guided5. Run the same parameterized query twice with very different parameter selectivity and compare the cached plan’s row estimates to actual. Use Ctrl+M in SSMS to capture both actual execution plans and note if the estimate matches the second call.

    Challenge Scenarios (diagnose independently)

    Challenge6. A colleague reports the server has 64GB RAM but query performance hasn’t improved since a recent upgrade from 16GB. Using the DMVs from this module, form a hypothesis for why RAM alone didn’t help, and what evidence you’d gather next.
    Challenge7. A transaction log grew from 1GB to 50GB overnight with no obvious large operations in the app. List three architectural causes you’d investigate first, and the exact DMV/DBCC command for each.
    Challenge8. The plan cache shows thousands of single-use ad-hoc plans consuming significant memory. Explain the architectural reason this happens and one setting that mitigates it.

    Break-It Labs

    Break-It9. Deliberately induce plan cache bloat: run 500 slightly-different ad-hoc (non-parameterized) queries in a loop, then measure plan cache growth with the query from Guided Lab 2. Then fix it by enabling “optimize for ad hoc workloads” and re-measure.
    Break-It10. Deliberately induce log growth pressure: run a large batch DELETE inside an explicit transaction without committing for several minutes while inserting more rows elsewhere, then observe DBCC SQLPERF(LOGSPACE) growing. Resolve it by committing and note the log space reclaimed (or not, until a log backup, depending on recovery model).

    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.

  • Module 6 Quiz: SQL Server Monitoring & Tooling (10 Questions)

    Module 6 Quiz: SQL Server Monitoring & Tooling

    1. How does Query Store differ from the plan cache?

    A) They are identical
    B) Query Store persists history per-database across restarts; the plan cache resets on restart
    C) Query Store only works on Azure
    D) The plan cache is more detailed

    Show Answer

    Answer: B

    This persistence is exactly what makes Query Store useful for tracking regression over time, not just current state.

    2. What does sp_query_store_force_plan let you do?

    A) Delete a query from history
    B) Pin a specific known-good plan for a query, without changing code
    C) Force a full index rebuild
    D) Disable Query Store

    Show Answer

    Answer: B

    This is a direct, reversible, production-safe response to parameter sniffing regressions.

    3. Why should a production Extended Events session use an aggressive WHERE duration filter?

    A) It’s not necessary
    B) To capture only what actually matters and keep overhead/disk usage low
    C) Filters are required by SQL Server syntax
    D) It has no effect on overhead

    Show Answer

    Answer: B

    Unfiltered capture on a busy server generates enormous volume — filtering aggressively is the difference between diagnostic and production-safe.

    4. What does EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS accomplish?

    A) Guarantees zero events are ever lost
    B) Prioritizes server performance over perfect event capture completeness
    C) Disables the session entirely
    D) Doubles the memory buffer

    Show Answer

    Answer: B

    This ensures monitoring itself never becomes a bottleneck — an intentional trade-off for production sessions.

    5. What does a low Page Life Expectancy counter typically indicate?

    A) Excellent buffer pool health
    B) Buffer pool pressure — pages being evicted and re-read from disk quickly
    C) A CPU bottleneck only
    D) A network issue

    Show Answer

    Answer: B

    This ties directly back to Module 1’s buffer pool concept — low PLE means data isn’t staying cached.

    6. A rising Compilations/sec relative to Batch Requests/sec often signals what?

    A) Excellent plan reuse
    B) Excessive recompiling, often from ad-hoc (non-parameterized) query bloat
    C) A hardware failure
    D) Normal, healthy behavior always

    Show Answer

    Answer: B

    This connects back to Module 1’s plan cache bloat discussion — too many unique ad-hoc statements compiling fresh plans.

    7. A rising Full Scans/sec trend alongside a stable workload most likely points to what?

    A) Everything is fine
    B) Missing or degraded indexes (fragmentation, stale stats)
    C) A network slowdown
    D) Increased RAM

    Show Answer

    Answer: B

    A trend change with stable workload is a strong signal something structural (Module 2 territory) has degraded.

    8. Which tool is best suited for answering “how has this specific query’s performance evolved over the past month”?

    A) sys.dm_os_waiting_tasks (live-only)
    B) Query Store
    C) PerfMon alone
    D) The plan cache alone

    Show Answer

    Answer: B

    Query Store’s whole design purpose is persisted, per-query historical tracking — exactly this question shape.

    9. Which tool is best for answering “what is currently blocking session 55, right now”?

    A) Query Store
    B) sys.dm_os_waiting_tasks
    C) PerfMon historical logs
    D) sys.dm_exec_query_stats

    Show Answer

    Answer: B

    This is a live, real-time question — the DMV showing current wait state is the right tool, not a historical aggregate.

    10. Why is combining multiple monitoring tools (not relying on just one) the recommended approach?

    A) It’s unnecessary, one tool does everything
    B) Each tool answers a different question shape — trend over time, current state, specific events, or per-query history
    C) More tools always means more accuracy regardless of fit
    D) This is not actually recommended

    Show Answer

    Answer: B

    PerfMon (trend), DMVs (current/historical state), XEvents (specific captured events), Query Store (per-query history) are complementary, not redundant.

    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.

  • Module 4 Quiz: SQL Server Execution Plan Analysis (10 Questions)

    Module 4 Quiz: SQL Server Execution Plan Analysis

    10 questions on reading plans like a diagnostician, not just a spectator.

    1. Why can an operator’s “cost %” in a plan be misleading?

    A) It’s always 100% accurate
    B) It’s computed from row ESTIMATES, which can be wrong (parameter sniffing, stale stats)
    C) SQL Server doesn’t actually compute cost
    D) Cost % only applies to INSERT statements

    Show Answer

    Answer: B

    Cost % is pre-execution and estimate-based — exactly the same estimates that can be wrong per Module 3’s cardinality estimation lesson.

    2. What should you always capture in SSMS, and why, when diagnosing a real slow query?

    A) Estimated plan — it’s faster to view
    B) Actual plan (Ctrl+M) — estimated plans never show real row counts
    C) Neither matters
    D) Only the query text

    Show Answer

    Answer: B

    The actual plan includes real row counts per operator, which is what lets you compare against estimates and spot the gap.

    3. What does a large gap between Estimated and Actual rows on one operator most strongly suggest?

    A) Nothing meaningful
    B) Something upstream (stale stats, non-SARGable predicate, parameter sniffing) is misleading the optimizer
    C) A hardware failure
    D) The query is definitely correct

    Show Answer

    Answer: B

    This is often the single strongest diagnostic signal in a plan — more informative than cost % alone.

    4. What does a “thick arrow” between two operators in a graphical plan represent?

    A) A faster operation
    B) A large number of rows flowing between those operators
    C) An error
    D) A parallel operation always

    Show Answer

    Answer: B

    Arrow thickness is proportional to row count — trace thick arrows back to find where row counts unexpectedly balloon.

    5. What does the XML string “PlanAffectingConvert” indicate when found in a plan?

    A) A successful index seek
    B) An implicit data type conversion that affected the chosen plan
    C) A backup operation
    D) A parallelism warning only

    Show Answer

    Answer: B

    This directly ties back to the SARGability-killing implicit conversions covered in Module 3.

    6. What does “SpillToTempDb” in a plan’s XML indicate?

    A) A successful query
    B) A Hash Match or Sort ran out of memory and spilled to disk — a serious performance flag
    C) A backup is running
    D) tempdb is corrupted

    Show Answer

    Answer: B

    Spills mean the memory grant wasn’t enough for the operation, forcing much slower disk-based processing.

    7. What does “NoJoinPredicate” in plan XML often indicate?

    A) A well-optimized join
    B) An accidental CROSS JOIN, often from a missing join condition
    C) A missing index only
    D) A columnstore index

    Show Answer

    Answer: B

    This is a common accidental-bug signature — a forgotten or mistyped join condition producing a full Cartesian product.

    8. How can you search the plan cache for every currently-cached plan that spills to tempdb?

    A) It’s not possible
    B) CROSS APPLY sys.dm_exec_query_plan() and search the XML text for ‘SpillToTempDb’
    C) Only via SQL Server Profiler
    D) Only by restarting the server

    Show Answer

    Answer: B

    Casting the query_plan XML to text and searching it across sys.dm_exec_cached_plans lets you proactively hunt server-wide, not just query-by-query.

    9. Are yellow warning triangles in a graphical plan safe to ignore if the query “seems fine”?

    A) Yes, always
    B) No — they flag real issues like implicit conversions or missing statistics worth investigating
    C) They only appear on errors
    D) They mean the query failed

    Show Answer

    Answer: B

    Warning icons are the plan actively telling you something is off — don’t dismiss them just because the query technically returned results.

    10. Why is SET SHOWPLAN_XML useful beyond just viewing the graphical plan?

    A) It isn’t useful
    B) It exposes the raw plan structure so you can search/query it programmatically across many plans
    C) It only works on SELECT statements
    D) It replaces the need for indexes

    Show Answer

    Answer: B

    Wide plans are hard to scan visually — the underlying XML lets you search for specific warning strings directly, or automate the search across the whole plan cache.

    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.

  • Module 5 Quiz: SQL Server Locking & Concurrency (10 Questions)

    Module 5 Quiz: SQL Server Locking & Concurrency

    1. Which SQL Server session runs by default and continuously captures deadlock graphs?

    A) default_trace
    B) system_health
    C) AlwaysOn_health
    D) None run by default

    Show Answer

    Answer: B

    system_health is an always-on Extended Events session that captures deadlock graphs (and more) without any setup, retrievable after the fact via its ring buffer target.

    2. In a deadlock graph’s XML, what does the victim-list element tell you?

    A) Nothing useful
    B) Which process SQL Server chose to kill to break the deadlock
    C) The server’s IP address
    D) The backup schedule

    Show Answer

    Answer: B

    Cross-referencing the victim against the survivor’s SQL text is how you confirm the actual access-order conflict.

    3. What do locks primarily protect?

    A) Physical memory pages
    B) Logical data consistency across a transaction
    C) Network packets
    D) CPU scheduling

    Show Answer

    Answer: B

    Locks are held for the duration of the transaction to protect logical consistency (isolation).

    4. What do latches primarily protect, and for how long are they typically held?

    A) Logical data, for the whole transaction
    B) Physical in-memory structures, typically for microseconds
    C) Network connections, indefinitely
    D) User permissions

    Show Answer

    Answer: B

    Latches are a much shorter-duration, lower-level mechanism protecting physical page access, not transactional consistency.

    5. “Gotcha”: A DBA sees high PAGELATCH_EX waits and shortens application transactions to fix it. Why is this the wrong fix?

    A) Shortening transactions always fixes everything
    B) Latch contention is about physical page access, not transaction duration — a different problem needing a different fix
    C) PAGELATCH_EX doesn’t exist
    D) This is actually the correct fix

    Show Answer

    Answer: B

    Confusing latch contention for lock contention leads to solving the wrong problem — the lock-blocking playbook doesn’t address physical page contention.

    6. What’s the classic latch contention pattern on a table with an ever-increasing IDENTITY key under heavy concurrent inserts?

    A) No contention is possible
    B) All sessions race to insert into the same last physical page, causing PAGELATCH_EX waits
    C) It only affects SELECT queries
    D) It causes data corruption automatically

    Show Answer

    Answer: B

    Ever-increasing keys concentrate all inserts on the same “hot” last page — a well-known latch contention scenario.

    7. What do GAM, SGAM, and PFS pages track in tempdb?

    A) User permissions
    B) Space allocation — which pages/extents are free or in use
    C) Query execution plans
    D) Backup history

    Show Answer

    Answer: B

    These are allocation-tracking pages every session touches when claiming space for temp tables/table variables.

    8. What’s the standard fix for tempdb allocation page contention?

    A) Reduce tempdb to a single file
    B) Multiple equally-sized tempdb data files, spreading round-robin allocation
    C) Disable tempdb entirely
    D) Increase the transaction log size only

    Show Answer

    Answer: B

    Multiple files spread contention across separate allocation page sets — a well-established, standard configuration recommendation.

    9. “Gotcha”: Why might adding tempdb data files of unequal size fail to help?

    A) Unequal sizes are always fine
    B) Proportional-fill allocation favors the file with the most free space, defeating round-robin distribution
    C) SQL Server ignores extra files
    D) It always improves performance regardless of size

    Show Answer

    Answer: B

    Equal file sizes are essential for the round-robin allocation benefit to actually spread load evenly.

    10. Which DMV/view shows what a currently-waiting session is actually waiting on, including its resource_description?

    A) sys.dm_exec_query_stats
    B) sys.dm_os_waiting_tasks
    C) sys.dm_db_index_usage_stats
    D) sys.dm_exec_cached_plans

    Show Answer

    Answer: B

    sys.dm_os_waiting_tasks is the live, real-time view of exactly what each session is currently blocked on.

    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.

  • Module 3 Quiz: SQL Server Query Optimization (10 Questions)

    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.

  • Module 2 Quiz: SQL Server Indexing Strategy (10 Questions)

    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.

  • Module 1 Quiz: SQL Server Architecture Fundamentals (10 Questions)

    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.