MCM SQL Server 2 — Questions and Answers
Question 1: Which SQL Server feature allows you to define a threshold beyond which SQL Server automatically creates statistics on columns used in query predicates?
- Auto-Update Statistics
- Auto-Create Statistics (Correct answer)
- Statistics Threshold Manager
- Column Statistics Daemon
Correct answer: Auto-Create Statistics
Auto-Create Statistics automatically generates single-column statistics for columns referenced in query predicates when they don't already exist.
Question 2: In SQL Server AlwaysOn Availability Groups, what is the maximum number of synchronous-commit secondary replicas allowed?
Correct 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.
Question 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?
- Read Committed
- Read Committed Snapshot Isolation (RCSI) (Correct answer)
- Snapshot Isolation
- Repeatable Read
Correct 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.
Question 4: What is the purpose of the DBCC CHECKALLOC command in SQL Server?
- Checks logical and physical integrity of all database objects
- Checks consistency of disk space allocation structures for all databases (Correct answer)
- Verifies allocation of memory pages in the buffer pool
- Reallocates fragmented index pages
Correct 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.
Question 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?
- Clustered index
- Nonclustered index (Correct answer)
- Covering index
- Filtered index
Correct 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.
Question 6: Which SQL Server DMV provides information about the current state of all active user requests, including wait types and CPU time?
- sys.dm_exec_sessions
- sys.dm_exec_requests (Correct answer)
- sys.dm_os_waiting_tasks
- sys.dm_exec_query_stats
Correct 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.
Question 7: In SQL Server Service Broker, what is a 'conversation endpoint' used for?
- Defining the network port for broker traffic
- Representing one side of a reliable message exchange between two services (Correct answer)
- Specifying the encryption certificate for a route
- Identifying the activation stored procedure for a queue
Correct 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.
