Practice Test Geeks(MCM) Microsoft Certified Master Practice Test
← All MCM Flashcard Decks

SQL Server Flashcards

7 cards from real MCM practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 SQL Server flashcards as text

  1. In SQL Server, which checkpoint type is automatically triggered when the log is 70% full in Simple recovery model to prevent log space exhaustion?

    Answer: Internal checkpoint

    An internal checkpoint is triggered by SQL Server operations like backup, restore, and log truncation events including the 70% log-full threshold in Simple recovery model.

  2. Which SQL Server index feature allows you to create an index that only covers a subset of rows in a table based on a WHERE clause?

    Answer: Filtered index

    A filtered index uses a WHERE clause predicate to index only the qualifying subset of rows, reducing index size and maintenance overhead for sparse or selective data.

  3. What is the primary purpose of the 'Eager Write' mechanism in SQL Server's tempdb?

    Answer: To write row version store pages to disk proactively when tempdb grows under memory pressure

    Eager Write proactively flushes row version store pages from the buffer pool to disk when tempdb experiences memory pressure, preventing out-of-memory conditions.

  4. In an AlwaysOn Availability Group, what is 'automatic page repair' and which component provides the repair copy?

    Answer: Automatically requests a fresh copy of a corrupt page from a replica; source is a synchronized secondary

    When a primary (or secondary) detects a corrupt page, automatic page repair requests a good copy from a synchronized AG partner replica without DBA intervention.

  5. Which SQL Server feature allows queries to use a mix of row-mode and batch-mode execution within the same query plan, adapting to data volume at runtime?

    Answer: Batch Mode on Rowstore

    Batch Mode on Rowstore (SQL Server 2019+) allows the query processor to switch to batch-mode execution for analytical queries even on traditional B-tree rowstore indexes.

  6. In SQL Server, what is the effect of enabling 'optimize for ad hoc workloads' server configuration option?

    Answer: Stores only a small plan stub on first execution, caching the full plan only if executed again

    When enabled, SQL Server stores a lightweight compiled plan stub in cache after first execution; the full plan is only cached on second execution, reducing plan cache bloat from single-use queries.

  7. What SQL Server mechanism automatically adjusts the memory grant for a query based on actual rows processed in previous executions of the same plan?

    Answer: Memory Grant Feedback

    Memory Grant Feedback (introduced in SQL Server 2017 for batch mode, 2019 for row mode) adjusts future memory grants for a cached plan based on the difference between granted and actually-used memory.