DS0-001 Data Systems Management Practice Test 2 — 15 Questions

Question 1

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Accepted Answer

--no-data. The '--no-data' flag instructs mysqldump to export the schema (CREATE statements) without including the table content.

Answer

--quick

Answer

--add-drop-table

Answer

--all-databases

Question 2

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

Accepted Answer

Extended Events. SQL Server Profiler or Extended Events are the standard tools for tracing and capturing execution plans, especially for identifying expensive queries.

Answer

Performance Monitor

Answer

SQL Server Configuration Manager

Answer

Windows Event Viewer

Question 3

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

Accepted Answer

Move-ClusterGroup. In a WSFC environment, the 'Move' command in the Failover Cluster Manager is used to manually move the role (including the database resource) to another node.

Answer

SHUTDOWN WITH NOWAIT

Answer

ALTER SERVER ROLE

Answer

RESTORE DATABASE

Question 4

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

Accepted Answer

The certificate and its private key. To restore a TDE-encrypted database, the certificate used to protect the DEK must be backed up and moved to the new server.

Answer

The master data file

Answer

The SQL Server configuration file

Answer

The transaction log file

Question 5

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

Accepted Answer

DBCC SHRINKFILE. DBCC SHRINKFILE is the command used to shrink the size of data or log files in SQL Server.

Answer

DBCC SHRINKDATABASE

Answer

BACKUP LOG ... WITH TRUNCATE

Answer

ALTER DATABASE ... SET LOGFILE

Question 6

Which database object is specifically designed to enforce referential integrity between two tables?

Accepted Answer

Foreign Key. A Foreign Key constraint is designed to ensure that data in one table matches valid entries in another table, maintaining referential integrity.

Answer

Index

Answer

Primary Key

Answer

Unique Constraint

Question 7

A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?

Accepted Answer

Data type mismatch between column and parameter. An implicit conversion happens when the data type of a column does not match the data type of the constant or parameter being compared to it, forcing the engine to convert every row.

Answer

The database collation is incorrect

Answer

The table is not partitioned

Answer

The statistics are outdated

Question 8

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

Accepted Answer

SQL Server Management Studio. SQL Server Management Studio (SSMS) is the standard GUI tool for SQL Server management.

Answer

Performance Monitor

Answer

SQL Server Profiler

Answer

SQL Server Configuration Manager

Question 9

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Accepted Answer

Copy-only backup. A 'Copy-only' backup creates a copy of the database or log without affecting the differential or log backup sequence.

Answer

Full backup

Answer

Differential backup

Answer

Transaction log backup

Question 10

What is the primary function of an index in a database?

Accepted Answer

To speed up data retrieval. Indexes are designed to speed up data retrieval by providing a faster lookup mechanism than scanning every row in a table.

Answer

To enforce data encryption

Answer

To reduce disk space usage

Answer

To manage user permissions

Question 11

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

Accepted Answer

max server memory. The 'max server memory' setting in SQL Server configuration determines the ceiling for the buffer pool, preventing the instance from starving the OS.

Answer

Query memory grant

Answer

Working set size

Answer

min server memory

Question 12

You need to change the collation of a database. What is the impact of performing this operation?

Accepted Answer

It requires rebuilding the database or recreating tables. Changing a database collation is a destructive operation. It requires re-creating the database or recreating all tables, as column-level collations are not automatically updated for existing data.

Answer

It can be done instantly with an ALTER command

Answer

It is a metadata-only change

Answer

It only impacts system tables

Question 13

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

Accepted Answer

Create read-only replicas. Adding read-replicas, implementing caching, and creating appropriate indexes are all standard methods to offload and accelerate read operations.

Answer

Increase the number of write threads

Answer

Enable synchronous replication

Question 14

Which TWO of the following are common risks associated with performing a direct 'DROP' operation on a large production table?

Accepted Answer

Excessive transaction log growth. Dropping a large table can cause massive transaction log growth if the operation is logged, and it can cause immediate application failures if the application expects the table to exist.

Answer

Automatic loss of database backup files

Answer

Corruption of the master database

Answer

Permanent increase in database file size

Question 15

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Accepted Answer

Off-site or cloud storage of backups. A complete DR plan requires regular backups, a documented restoration procedure, and off-site storage to protect against site-wide disasters.

Answer

Using only RAID 0 for storage

Answer

Keeping all data in memory