Data Systems Management practice questions

Use the Move-Item cmdlet

Why it fails: This is a filesystem command, not a database administration command.

Shrink the database

Why it fails: Shrinking does not move specific tables to new storage tiers.

Disable the index

Why it fails: Disabling the index does not change physical storage location.

Differential

Why it fails: Differential is a backup type, not a database recovery model.

Full

Why it fails: Full recovery model keeps the log until a log backup is taken, which can lead to rapid growth during high-volume operations.

Simple

Why it fails: While it truncates automatically, it does not support point-in-time recovery required for many production environments.

Active Directory group

Why it fails: AD groups manage access, not encryption keys.

Service Master Key

Why it fails: The SMK protects the Database Master Key, but the DEK is protected by a certificate.

SSL/TLS certificate

Why it fails: SSL/TLS certificates are for network transport security, not data-at-rest encryption.

Innodb_buffer_pool_reads

Why it fails: Innodb_buffer_pool_reads counts physical reads that bypassed the buffer pool, an InnoDB performance metric unrelated to replication position. It is tempting because it is a familiar status variable, and it would be correct when diagnosing buffer pool sizing or disk I/O pressure rather than replica lag.

Slave_IO_Running

Why it fails: Slave_IO_Running reports only whether the replica's I/O thread is connected to the primary, not how far behind it is; Seconds_Behind_Master gives the lag. It is tempting because a stopped I/O thread does cause desynchronisation, so it is worth checking when replication has halted entirely.

Threads_connected

Why it fails: Threads_connected reports current client connections to the server, not the replica's position relative to the primary's binary log. It is tempting because connection counts can indicate load problems, and it would be correct when diagnosing connection exhaustion or max_connections tuning rather than replication delay.

Set the isolation level to Read Uncommitted

Why it fails: This affects concurrency, not the algorithm of a specific join operation.

Force the join order

Why it fails: Forcing join order does not necessarily change the join algorithm used by the engine.

Update statistics

Why it fails: Updating statistics helps the optimizer choose the right path but does not inherently change the algorithm if it's already considered optimal.

sys.dm_os_memory_nodes

Why it fails: This is for monitoring memory, not user activity.

sys.dm_exec_requests

Why it fails: This shows currently executing requests, not historical login dates.

sys.database_principals

Why it fails: This shows existing accounts but does not track login history.

Create a new database user

Why it fails: Users do not impact the physical file size.

Perform a full database backup

Why it fails: Backups do not change the size of the database file.

Clear the SQL Server error log

Why it fails: The error log is a text file, not a database data file.

LDAP Authentication

Why it fails: While AD uses LDAP, it is not a native direct authentication mode.

OAuth Authentication

Why it fails: Not a standard SQL Server native authentication mode.

Transparent Data Encryption (TDE)

Why it fails: TDE encrypts the entire database at rest, including data and log files, but it does not provide column-level encryption or key management within the database engine for specific columns. The scenario asks for encryption of a specific table's sensitive data, not the whole database, and TDE does not offer granular control.

Dynamic Data Masking

Why it fails: Dynamic Data Masking obscures data returned to non-privileged users but does not encrypt data at rest. The underlying data remains plaintext in storage, so it fails to meet the requirement for encryption at rest and key management by the database engine.

Always Encrypted

Why it fails: Always Encrypted protects data in use and at rest by encrypting columns, but the keys are managed outside the database engine, typically in a client driver or key store. The scenario requires keys managed by the database engine, which does not match Always Encrypted's client-side key management model.

Change the recovery model to bulk-logged and perform a full backup

Why it fails: The bulk-logged recovery model does not support point-in-time recovery for all operations. While it minimally logs bulk operations, certain bulk changes are not fully logged, making it impossible to recover to a specific point in time. It is not suitable for the requirement of recovering to any point in time within 30 days.

Enable transparent data encryption and perform a full backup

Why it fails: Transparent data encryption encrypts data at rest but does not affect recovery capabilities. It does not enable point-in-time recovery; that requires the full recovery model and transaction log backups. Encryption is unrelated to the recovery model and does not satisfy the requirement.

Keep the simple recovery model and perform full backups every hour

Why it fails: The simple recovery model does not support transaction log backups, so point-in-time recovery is not possible. Even with frequent full backups, you can only recover to the last backup, losing all changes since then. This does not meet the requirement to recover to any point in time within 30 days.

Range partitioning on a monotonically increasing key

Why it fails: Range partitioning on a monotonically increasing key, such as an auto-increment ID, concentrates writes on the highest partition, creating a hotspot. While it supports efficient range scans, the contention on the last partition can limit write throughput. It is not ideal for high write throughput because it does not distribute writes evenly.

List partitioning on a low-cardinality status column

Why it fails: List partitioning on a low-cardinality column like status can lead to uneven partition sizes and hotspots if certain statuses are more frequent. It does not distribute writes evenly and does not support efficient range scans on other columns. It is not suitable for high write throughput or range scan requirements.

Round-robin partitioning without a partitioning key

Why it fails: Round-robin partitioning distributes rows evenly without a key, which can help with write throughput but does not support efficient range scans because data is randomly distributed. It also complicates data management and querying. It is not a good choice when range scans are required.

Run DBCC SHOWCONTIG on the compressed tables to check for fragmentation.

Why it fails: DBCC SHOWCONTIG is deprecated and reports fragmentation, not compression overhead. While fragmentation can slow scans, the scenario states the execution plan is identical and compression was recently enabled, pointing to CPU overhead rather than physical fragmentation. Checking fragmentation would not confirm decompression as the cause and may waste time on an unrelated issue.

Query sys.dm_db_index_physical_stats to compare average page density before and after compression.

Why it fails: sys.dm_db_index_physical_stats returns page density and fragmentation information, which can show how well compression reduced page count. However, it does not measure decompression activity during query execution. The scenario already suspects CPU overhead from decompression, so this DMV would not provide the needed evidence. It is useful for verifying compression effectiveness, not runtime decompression cost.

Compare the wait statistics before and after compression, focusing on CXPACKET and SOS_SCHEDULER_YIELD.

Why it fails: CXPACKET indicates parallelism waits, and SOS_SCHEDULER_YIELD indicates CPU pressure, but neither is specific to decompression. Compression overhead typically appears as CPU time within the query rather than a distinct wait type. Comparing these waits might show general CPU pressure but would not conclusively confirm decompression as the cause. More targeted metrics are needed.

Enable the 'Encrypt Connection' option in each client application's connection string.

Why it fails: While clients can request encryption via connection string, this does not enforce it server-side. A client could still connect without encryption if the server does not force it. The requirement is to enforce encryption for all connections, so server-side configuration is necessary. Client-side settings alone cannot guarantee that all connections are encrypted.

Create a login with the ENCRYPTION option and grant it to all users.

Why it fails: SQL Server does not have a login option called ENCRYPTION. Encryption is configured at the server level, not per login. There is no such syntax or feature. Therefore, this approach is invalid and would not enforce encryption.

Use the SQL Server Configuration Manager to enable the 'Force Protocol Encryption' setting under the protocol properties for TCP/IP.

Why it fails: There is no 'Force Protocol Encryption' setting under protocol properties. The correct setting is 'Force Encryption' on the server's properties in SQL Server Configuration Manager, which applies to all protocols. Enabling it under TCP/IP alone would not cover other protocols like Shared Memory or Named Pipes, and the name is incorrect.

Full backup weekly, transaction log backup every 15 minutes.

Why it fails: This sequence omits differential backups, which means restoring to a point in time requires applying all log backups since the last full backup. That increases restore time and the number of log files that must be applied. While it supports point-in-time recovery, it does not minimize administrative overhead as effectively as including daily differentials to reduce the number of log backups needed during restore.

Differential backup daily, transaction log backup every 15 minutes.

Why it fails: A differential backup requires a base full backup to be meaningful. Without a full backup, the differential cannot be restored, and the log chain has no starting point. This sequence is invalid for point-in-time recovery because it lacks the foundational full backup. The administrator must include a full backup to establish the recovery chain.

Full backup daily, transaction log backup every hour.

Why it fails: Daily full backups with hourly log backups can support point-in-time recovery, but they generate more full backup files and larger backup sizes than necessary. The scenario asks to minimize the number of backup files and administrative overhead. A weekly full with daily differentials reduces the number of full backups while still meeting recovery goals, making this option less efficient.

Store the BLOBs in the PRIMARY filegroup to simplify management.

Why it fails: Storing BLOBs in the PRIMARY filegroup mixes large object I/O with system and user data, potentially causing contention and degrading performance. It also complicates backup strategies because the entire primary filegroup must be backed up together. For read-heavy BLOB workloads, this is not optimal and can lead to bottlenecks.

Create a single filegroup with multiple files to distribute I/O.

Why it fails: Using a single filegroup with multiple files can improve I/O throughput by allowing parallel operations, but it does not isolate BLOB I/O from other data. For read-heavy BLOB workloads, isolation is often more beneficial. Additionally, managing multiple files in one filegroup does not provide the same backup granularity or performance tuning opportunities as a dedicated filegroup.

Use a FILESTREAM filegroup to store the BLOBs on a network share.

Why it fails: FILESTREAM stores BLOBs in the file system, not on a network share directly; it requires local NTFS storage. While it can improve performance for large objects, using a network share is not supported. The scenario does not specify FILESTREAM requirements, and the question asks for a filegroup configuration, not a FILESTREAM setup. Moreover, network shares introduce latency and are not recommended for database files.

Create columnstore indexes on the fact tables to improve analytical query performance.

Why it fails: Columnstore indexes are a SQL Server and Azure SQL feature, not a native PostgreSQL index type. PostgreSQL does not support columnstore indexes out of the box and requires extensions such as Citus or Hydra. This action cannot be performed on PostgreSQL without major architectural changes, so it does not address the scenario's requirement to improve performance on the existing platform.

Implement table partitioning on the fact tables by date range to enable partition pruning.

Why it fails: Partitioning by date range can improve query performance when queries filter on the partition key, but the scenario states analysts run concurrent reporting queries that may not always filter by date. Partitioning alone does not eliminate sequential scans on the full table for queries without a date predicate, and it does not directly address the disk I/O bottleneck for all analytical workloads.

Add more RAM and increase the shared_buffers setting to cache the fact tables in memory.

Why it fails: Adding RAM and increasing shared_buffers can reduce physical I/O, but a 5 TB warehouse cannot be cached in 64 GB or even a few hundred GB of RAM. The sequential scans will still read from disk for most data. This is a partial mitigation at best and does not address the fundamental I/O bottleneck for large analytical scans on this dataset size.

Change the primary key to a surrogate auto-increment column and create a secondary index on (customer_id, order_date).

Why it fails: Changing the primary key to a surrogate key would require rebuilding the table and all secondary indexes, a massive operation on a 500 GB table. It also does not directly address the locking issue for range updates on order_date. While it could reduce page splits on inserts, the scenario focuses on update performance and locking, which are better addressed by partitioning or adjusting transaction isolation.

Set the transaction isolation level to READ UNCOMMITTED for the batch process.

Why it fails: READ UNCOMMITTED reduces read locks but does not eliminate write locks for UPDATE statements. InnoDB still acquires exclusive locks on modified rows. Moreover, this isolation level can cause dirty reads and is generally unsafe for production. It does not address the root cause of locking during range updates and may introduce data consistency issues for concurrent readers.

Increase the innodb_buffer_pool_size to cache the entire table in memory.

Why it fails: Increasing the buffer pool can reduce disk I/O, but a 500 GB table likely exceeds available RAM, and the buffer pool only caches pages, not entire tables. Locking during updates is a concurrency issue, not purely an I/O one. Even with all pages cached, InnoDB still holds row locks for the duration of the transaction, so this does not solve the locking problem.

The secondaries automatically promoted a new primary during the partition, causing the old primary's writes to be discarded.

Why it fails: In a three-node replica set, if the primary is isolated, the remaining two secondaries can elect a new primary because they form a majority. However, the scenario states the application continued to write to the original primary and rollbacks occurred after the partition healed. The rollback is due to the old primary stepping down and rolling back unreplicated writes, not simply because a new primary was elected.

The write concern was set to w:1, so the primary acknowledged writes without waiting for replication.

Why it fails: w:1 means the primary acknowledges the write, but it does not by itself cause rollback. Rollback occurs when a primary that was isolated accepts writes and later steps down, and those writes were not replicated to a majority. w:1 increases the risk but is not the direct mechanism that triggers rollback; the rollback is due to the lack of majority replication.

The replica set used majority read concern, which allowed reading uncommitted data.

Why it fails: Majority read concern returns data that has been acknowledged by a majority of nodes and cannot be rolled back. It does not allow reading uncommitted data; that is local read concern. This option misstates the behavior of majority read concern and is not the cause of rollbacks.

Enable synchronous_commit = off to improve write performance.

Why it fails: Setting synchronous_commit = off can improve write performance by not waiting for WAL writes to disk, but it risks losing recent transactions in a crash. For a high-traffic web application, data durability is often important. This setting is not a general optimization for OLTP and should be used only when some data loss is acceptable. Therefore, it is not a recommended action here.

Increase the value of max_connections to a very high number to handle many concurrent users.

Why it fails: Setting max_connections too high can exhaust memory because each connection consumes dedicated memory and a backend process. PostgreSQL is process-based, and excessive connections cause context-switching overhead and memory pressure. A better approach is to use a connection pooler like PgBouncer. Thus, this action is not recommended for optimizing a high-traffic OLTP workload.

Set effective_cache_size to the total amount of RAM to inform the planner about available cache.

Why it fails: effective_cache_size is an estimate of how much memory is available for caching, including shared_buffers and OS cache. Setting it to the total RAM is incorrect because it should account for other processes. Typically, it is set to 50-75% of total RAM. Overstating it can cause the planner to favor index scans incorrectly, leading to suboptimal plans. Thus, this is not a recommended action.

Automatic Failover

Why it fails: Automatic Failover governs which replica takes over after a failure, not whether a secondary accepts read-only connections. It is tempting because it is a core replica setting, but the Readable Secondary option is what permits reporting workloads on the secondary node.

Synchronous Commit

Why it fails: Synchronous Commit controls transaction durability and data-loss exposure between replicas, not read access. It is tempting because availability mode is a prominent replica setting, but the Readable Secondary setting is what allows read-only reporting connections to the secondary.

Primary Role

Why it fails: Primary Role controls read-write behaviour on the primary replica, not read-only routing to secondaries. It is tempting because it governs the replica's operational mode, but the Readable Secondary setting (and read-only routing) is what enables reporting connections on the secondary.