Database Management Systems

The Database Maintenance Plan Wizard is designed specifically for junior DBAs to create backup strategies, maintenance tasks, and other database management plans through a guided interface. The Database Engine Tuning Advisor is for query optimization, Configuration Manager manages services, and the design surface is for advanced manual configuration.

DBCC CHECKDB WITH PHYSICAL_ONLY performs a streamlined check of page headers, record structure, and allocation pages - skipping expensive logical consistency checks. This reduces runtime significantly for large databases while catching corruptions like torn pages. The REPAIR options should never be run as part of routine maintenance, and DATA_PURITY adds checks that increase runtime.

Snapshot replication copies the entire set of data at a specific moment in time. Since the entire publication is not very large, the branch offices only need a daily update at 5 a.m., and transaction-by-transaction synchronization is not required, Snapshot replication is the most appropriate and simplest strategy.

Read Uncommitted isolation level allows dirty reads - it can read data that has been modified but not committed by other transactions. Since the requirement is to avoid dirty reads, this is the isolation level to avoid. Read Committed, Repeatable Read, and Serializable all prevent dirty reads at varying levels of strictness.

The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including columns like sql_handle, plan_handle, and execution_count that track recompilation events. The requests and sessions DMVs track current activity, while query_optimizer_info contains optimizer statistics but not per-query recompilation counts.

When a user creates an object (table, index, etc.) without specifying a TABLESPACE clause, Oracle stores the segment in that user's default tablespace, as defined when the user was created or altered. The system tablespace is for data dictionary objects, and there is no concept of a 'default tablespace for system' in this context.

In IMS DLI environments, USERLIB is the library type used for dynamically allocating database clusters. PSBLIB is for partitioned datasets, DBDLIB contains database descriptions, and ACBLIB holds access control blocks - none of these are used for dynamic cluster allocation.

SQL optimization quality is the most critical factor for statement performance because poorly written SQL cannot be fixed by hardware or infrastructure. While CPUs (A), parallelism (B), and bitmap indexes (C) can help, they cannot compensate for fundamentally inefficient query logic.

Introducing data redundancy (denormalization) is a key design feature used to optimize SQL performance in read-heavy applications. By replicating data, it reduces the need for expensive table joins. Although normal forms remove redundancy to protect data integrity, they can degrade query performance due to join overhead.

To answer this question, the user needs to understand the basics of SQL tuning and database optimization.

The correct answer is:

B. They may be able to be tuned to use an index

Explanation:

A full-table scan is a process in which the database engine reads all the rows of a table to find the relevant data. This can be a time-consuming process, especially for large tables. Full-table scans are not always sub-optimal, but they can be inefficient if the table is not properly indexed. When a table is not indexed correctly, the database engine must read every row of the table to find the relevant data, even if only a small subset of the data is needed.

Therefore, searching for full-table scans is critical to SQL tuning because it allows a database administrator to identify tables that are not properly indexed. Once identified, the administrator can then tune the full-table scan to use an index, which can improve performance and reduce the time it takes to retrieve the data.

Option A is incorrect because a full-table scan does not indicate an optimized execution plan. It may be the only option available, but it is not necessarily optimized.

Option C is incorrect because a full-table scan is not something that should be normalized from the database design. It is a method of retrieving data from a table and can be optimized through proper indexing.

Option D is incorrect because a full-table scan is not always sub-optimal. It depends on the size of the table and how well it is indexed.

The Database Maintenance Plan Wizard is a user-friendly tool built into SQL Server Management Studio designed to guide DBAs through setting up routine maintenance tasks, including backups. The Engine Tuning Advisor optimizes index performance, and Configuration Manager manages network protocols.

Using DBCC CHECKDB with the WITH PHYSICAL_ONLY option reduces check time on large databases by limiting checks to physical page structure, page integrity, and allocation. This skips resource-intensive logical checks. Options like REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD are used to fix errors.

Snapshot replication is ideal for this scenario because it delivers a complete data copy daily at 5am, matching the reporting requirement. Transactional replication is continuous, peer-to-peer is for multi-master scenarios, and merge replication handles conflict resolution - all unnecessary for this daily batch refresh pattern.

The requirement is to avoid dirty reads, which Read Uncommitted explicitly allows (it reads uncommitted data). Read Committed, Repeatable Read, and Serializable all prevent dirty reads. Since dirty reads must be avoided, Read Uncommitted is the level to avoid.