MCM SQL Server 2

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?

Accepted Answer

Auto-Create Statistics

Answer

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?

Accepted Answer

3

Answer

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?

Accepted Answer

Read Committed Snapshot Isolation (RCSI)

Answer

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?

Accepted Answer

Checks consistency of disk space allocation structures for all databases

Answer

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?

Accepted Answer

Nonclustered index

Answer

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.

(MCM) Microsoft Certified Master Practice Test

(MCM) Microsoft Certified Master Practice Test

MCM SQL Server 2