MCM SQL Server 3 — Questions and Answers
Question 1: Which SQL Server feature enables you to encrypt data at rest by encrypting the entire database files, including log files, using a Database Encryption Key?
- Always Encrypted
- Transparent Data Encryption (TDE) (Correct answer)
- Cell-Level Encryption
- Column Master Key
Correct answer: Transparent Data Encryption (TDE)
TDE encrypts the entire database at the file level using a Database Encryption Key, protecting data at rest without requiring application changes.
Question 2: In SQL Server, what happens to a transaction that encounters a deadlock?
- Both transactions are rolled back and retried automatically
- The transaction with the lowest deadlock priority is chosen as the victim and rolled back (Correct answer)
- The newer transaction is always chosen as the deadlock victim
- SQL Server suspends both transactions until resources free up
Correct answer: The transaction with the lowest deadlock priority is chosen as the victim and rolled back
SQL Server's deadlock monitor selects a victim based on the lowest deadlock priority (SET DEADLOCK_PRIORITY) and, if equal, the transaction that is cheapest to roll back.
Question 3: Which extended event action captures the T-SQL text of a batch or statement currently executing when an event fires?
- sql_text (Correct answer)
- tsql_stack
- sql_batch_text
- statement
Correct answer: sql_text
The sql_text action captures the full text of the SQL batch or statement associated with the event at the time it fires.
Question 4: What is the function of a SQL Server 'Resource Governor' classifier function?
- Prioritizes disk I/O for specific filegroups
- Routes incoming sessions to workload groups based on session attributes (Correct answer)
- Allocates CPU affinity to specific databases
- Enforces memory limits per query plan
Correct answer: Routes incoming sessions to workload groups based on session attributes
The classifier function is a scalar T-SQL function that Resource Governor calls at session login to determine which workload group (and thus resource pool) the session belongs to.
Question 5: In SQL Server replication, which type of replication is best suited for scenarios where subscribers occasionally connect and need to synchronize accumulated changes?
- Transactional replication
- Snapshot replication
- Merge replication (Correct answer)
- Peer-to-peer replication
Correct answer: Merge replication
Merge replication tracks changes at both publisher and subscriber and reconciles them during synchronization, making it ideal for occasionally-connected subscribers.
Question 6: Which SQL Server feature allows a DBA to capture a workload on a production server and replay it on a test server for performance analysis?
- SQL Server Profiler trace replay
- Distributed Replay (Correct answer)
- Extended Events file target replay
- Query Store workload replay
Correct answer: Distributed Replay
Distributed Replay captures a workload trace and replays it across multiple client machines to simulate concurrent production load on a test server.
Question 7: In SQL Server, what does the NOLOCK table hint actually do at the engine level?
- Acquires shared locks but releases them immediately after each row read
- Reads data without acquiring any shared locks, allowing dirty reads (Correct answer)
- Promotes reads to schema stability locks only
- Forces a range scan to avoid lock escalation
Correct answer: Reads data without acquiring any shared locks, allowing dirty reads
NOLOCK (equivalent to READ UNCOMMITTED) skips shared lock acquisition entirely, allowing the query to read uncommitted data including rows being modified or rolled back.
MCM SQL Server 3
Which SQL Server feature enables you to encrypt data at rest by encrypting the entire database files, including log files, using a Database Encryption Key?