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. Which SQL Server feature allows you to define a threshold beyond which SQL Server automatically creates statistics on columns used in query predicates?

    Answer: Auto-Create Statistics

    Auto-Create Statistics automatically generates single-column statistics for columns referenced in query predicates when they don't already exist.

  2. In SQL Server AlwaysOn Availability Groups, what is the maximum number of synchronous-commit secondary replicas allowed?

    Answer: 3

    SQL Server supports up to 3 synchronous-commit secondary replicas in an Availability Group, for a total of 4 synchronous replicas including the primary.

  3. Which isolation level in SQL Server uses row versioning and allows readers to see the last committed version of a row without blocking writers?

    Answer: Read Committed Snapshot Isolation (RCSI)

    RCSI stores committed row versions in tempdb and serves readers the last committed version, eliminating reader-writer blocking at the cost of tempdb overhead.

  4. What is the purpose of the DBCC CHECKALLOC command in SQL Server?

    Answer: Checks consistency of disk space allocation structures for all databases

    DBCC CHECKALLOC checks the consistency of disk space allocation structures (IAM pages, PFS, GAM, SGAM) for a specified database.

  5. In SQL Server, which type of index is stored separately from the data pages and contains a copy of selected columns from the base table?

    Answer: Nonclustered index

    A nonclustered index is a separate structure from the heap or clustered index, containing the indexed key columns plus a row locator back to the base data.

  6. Which SQL Server DMV provides information about the current state of all active user requests, including wait types and CPU time?

    Answer: sys.dm_exec_requests

    sys.dm_exec_requests returns one row for each request currently executing in SQL Server, including wait type, CPU time, and elapsed time.

  7. In SQL Server Service Broker, what is a 'conversation endpoint' used for?

    Answer: Representing one side of a reliable message exchange between two services

    A conversation endpoint represents one side of a Service Broker conversation, maintaining state and sequencing for reliable, ordered message delivery.