Microsoft Azure Database Administrator Associate (DP-300)

B and D are correct.

Explanation: Bulk loads of 102,400 rows or more use the optimized load path and go directly into the columnstore. This avoids creating new delta stores. Reorganizing the index with COMPRESS_ALL_ROW_GROUPS forces the existing delta stores to be compressed into the columnstore. Reading rows from open delta stores is less efficient than reading from the columnstore.

A is incorrect: Loads of fewer than 102,400 rows are written to a delta store. Smaller batches increase the number of open delta stores, which reduces query efficiency.

C is incorrect: A clustered b-tree index does not provide the benefits of a columnstore index for large aggregation workloads. In the load comparison, the page-compressed b-tree table took nearly 20 seconds compared with roughly 8 seconds for the columnstore table.

A and C are correct.

Explanation: If COMMIT TRANSACTION is not issued, the transaction stays open and its locks are not released. Adding COMMIT TRANSACTION fixes the cause. Querying sys.dm_tran_active_transactions with sys.dm_tran_session_transactions lets you find open transactions, their duration, and their state.

B is incorrect: Serializable is the highest isolation level. It holds read and write locks until the end of the transaction, so it does not release locks sooner.

D is incorrect: Lock escalation converts many row locks on one object into a single table lock to reduce memory use. It does not release locks held by an open transaction.

B is correct.

Explanation: Data warehouse workloads require a design that facilitates complex queries and large-scale data analysis. Denormalizing tables reduces the number of joins required for queries, and partitioning helps manage large datasets effectively.

A is incorrect: Minimizing transaction times and prioritizing data integrity are the design goals for OLTP systems, not for analytic queries over large datasets.

C is incorrect: Using integer types for numeric data instead of character types leads to faster processing and reduced storage space. Character types would increase storage and slow processing.

D is incorrect: Proper indexing strategies improve query performance by allowing the database engine to locate the required data quickly. Removing indexes does not support a query-heavy workload.

D is correct.

Explanation: Workloads become eligible for Intelligent Query Processing when you change the database compatibility level to 150 or higher. For SQL Server 2022 and Azure SQL, compatibility level 160 enables the full set of features. These include table variable deferred compilation and scalar user-defined function inlining.

A is incorrect: Legacy Cardinality Estimation makes the database use the older cardinality estimator. It doesn't enable Intelligent Query Processing features.

B is incorrect: Optimize for Ad Hoc Workloads reduces plan cache size for workloads with many single-use queries. It doesn't change how table variables or scalar functions are processed.

C is incorrect: The recovery model controls whether the database is in full or simple recovery. It doesn't affect query processing features.

B is correct.

Explanation: Dynamic Thresholds learn the historical behavior of a metric and raise an alert when the resource operates in an abnormal manner. They can detect seasonality in a workload and adjust the alerting accordingly.

A is incorrect: A static threshold requires you to provide a fixed value. It doesn't learn historical behavior or adjust for seasonality.

C is incorrect: An action group defines how to respond to an alert, such as a notification or an automation. It doesn't define the alert condition. Defining an action group is optional.

D is incorrect: Archiving to Azure Storage keeps metrics beyond the 93 days that active metrics are retained. It doesn't create an alert condition.

D is correct.

Explanation: The ShortLocation column is dependent on the ProductionCountry column, which isn't the key. This is a transitive dependency, meaning the table violates third normal form. Updating ShortLocation in only some rows causes an update anomaly. To prevent this, you should create a separate table to store country names and their shortened forms.

A is incorrect: First normal form requires that you eliminate repeating groups. The table has no repeating groups and already uses ProductID as a primary key. Adding repeating column groups would introduce a first normal form violation.

B is incorrect: Second normal form applies only to tables that have a composite key. This table has a single-column primary key, and adding a composite key does not remove the dependency of ShortLocation on ProductionCountry.

C is incorrect: Second normal form is not the issue because the table has a single-column key. A composite key does not remove the transitive dependency.

D is correct.

Explanation: Resource Governor uses a classifier function to place connections into workload groups. Each workload group uses one resource pool, and you can set limits on the pool. Classifying the maintenance login into its own group and pool limits the resources for the rebuilds. Test the classifier function for efficiency, because it runs every time a connection is established.

A is incorrect: The internal pool is reserved for critical SQL Server functions and cannot be restricted.

B is incorrect: Changes to a resource pool affect only new sessions, so they do not restrict a process that is already running.

C is incorrect: The maximum CPU percent applies only when there is CPU contention. Without contention, the workload can use up to 100 percent of the available CPU cycles.

B is correct.

Explanation: Azure allocates a fixed ratio of CPU cores to memory. Constrained cores reduce the vCPU count and the software licensing cost while you keep the full amount of memory, storage, and I/O bandwidth. This fits database workloads that are not CPU-intensive.

A is incorrect: More vCPUs increases the number of cores, and SQL Server licenses are based on cores.

C is incorrect: Ultra disk addresses storage latency. It does not change the number of cores that are licensed.

D is incorrect: A smaller size with fewer cores also reduces memory because of the fixed core-to-memory ratio, which does not meet the memory requirement.

C is correct.

Explanation: A nonclustered index can include extra nonkey columns at the leaf level. When the index includes the columns that the query needs, it covers the query. The execution plan no longer needs to access the table itself, so the key lookup is eliminated.

A is incorrect: A table can have only one clustered index because the rows can be stored in only one order.

B is incorrect: A heap is a table without a clustered index. Heaps are typically used only as staging tables. Dropping the index does not improve this query.

D is incorrect: A clustered index key should be kept as narrow as possible. Widening it to include every selected column conflicts with this design principle.

A is correct.

Explanation: On a production system, concurrent user activity, resource utilization, and the overall workload all influence query performance. Evaluate the change in that context by monitoring performance over time and analyzing execution plans. Also check for side effects, because a change that improves one query can degrade another.

B is incorrect: Elapsed time and logical reads are straightforward metrics for a single query in isolation. They do not show the effect of the change on a busy production workload.

C is incorrect: Concurrent user activity is one of the factors that can influence the performance of a query or data structure.

D is incorrect: Resource utilization is one factor to consider, but it does not show whether the change degraded other queries or how execution plans changed.