View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 281

Which Azure SQL Database feature allows a database to automatically scale compute resources up and down based on workload activity?

1. Serverless compute
2. Database snapshot
3. Azure SQL Auditing
4. BACPAC

Correct Answer: 1

Explanation

Serverless compute allows Azure SQL Database to automatically adjust compute resources according to workload demand within configured limits. It is useful for databases with intermittent or unpredictable usage because compute can scale based on activity. Depending on the configuration, a database can also automatically pause after a period of inactivity. Administrators should compare serverless with provisioned compute because consistently active workloads may benefit more from continuously allocated resources and predictable performance.

Question 282

Which Azure SQL Database service tier separates compute from storage and is designed for very large databases?

1. Basic
2. Hyperscale
3. Standard
4. General Purpose

Correct Answer: 2

Explanation

The Hyperscale service tier uses an architecture that separates compute from storage and is designed for very large databases and demanding workloads. It can provide scalable storage and fast database growth without requiring administrators to manage traditional storage infrastructure. Hyperscale is especially useful when database size and scalability are major requirements. Administrators should still evaluate workload characteristics, supported features, availability requirements, and pricing before selecting Hyperscale for a production database.

Question 283

Which Azure SQL Database option is best suited for a group of databases with unpredictable usage patterns that can share resources?

1. Elastic pool
2. Private Endpoint
3. Database snapshot
4. Failover group

Correct Answer: 4

Explanation

An elastic pool allows multiple Azure SQL Databases to share a pool of compute resources. It is particularly useful when databases have different or unpredictable workload patterns and do not all reach peak usage at the same time. Instead of provisioning each database for its individual maximum demand, the databases can share available capacity. Administrators should monitor pool utilization and ensure that the pool has sufficient resources for the combined workload.

Question 284

Which Azure SQL Database capability automatically creates recommendations for improving query performance and index usage?

1. Automatic tuning
2. Azure DNS
3. Azure Storage
4. Azure Policy

Correct Answer: 1

Explanation

Azure SQL Database automatic tuning analyzes database workload behavior and can provide recommendations for performance improvements. Depending on the supported recommendation and configuration, it can automatically apply actions such as creating or removing indexes or correcting certain plan-related issues. Administrators can choose whether recommendations are automatically applied or only reviewed. Automatic tuning can reduce manual administration, but administrators should still monitor performance and validate that automated changes are appropriate for the workload.

Question 285

Which Azure SQL feature can force a previously selected execution plan to be used for a query?

1. Query Store plan forcing
2. Dynamic Data Masking
3. Transparent Data Encryption
4. Azure SQL Auditing

Correct Answer: 2

Explanation

Query Store plan forcing allows administrators to instruct SQL Server or Azure SQL Database to use a specific execution plan for a query when that plan is known to provide better or more stable performance. This can be useful when a query begins using a poor plan after a change in statistics, indexes, parameters, or other conditions. Administrators should monitor the workload after forcing a plan because workload characteristics can change over time.

Question 286

Which SQL Server behavior can cause the optimizer to choose an execution plan based on parameter values used during compilation?

1. Parameter sniffing
2. Data compression
3. Partition switching
4. Log truncation

Correct Answer: 1

Explanation

Parameter sniffing occurs when SQL Server uses parameter values available during query compilation to help generate an execution plan. This can produce an efficient plan when the values represent the typical workload, but it can also cause performance problems when data distribution varies significantly between parameter values. Administrators can investigate parameter-sensitive performance using execution plans, Query Store, statistics, and other tools before deciding whether query or configuration changes are appropriate.

Question 287

Which execution plan operator is commonly used when SQL Server retrieves a small number of matching rows from an indexed table?

1. Table Scan
2. Index Seek
3. Sort
4. Hash Match

Correct Answer: 2

Explanation

An Index Seek allows SQL Server to navigate an index directly to the relevant key values rather than examining every row. It is often efficient when a query has selective predicates and a suitable index exists. However, an Index Seek is not automatically better than every other access method because SQL Server chooses plans based on estimated cost. Administrators should review the complete execution plan and workload characteristics rather than judging performance from one operator alone.

Question 288

Which execution plan operator reads rows from an entire table or heap when no more efficient access method is selected?

1. Index Seek
2. Nested Loops
3. Table Scan
4. Key Lookup

Correct Answer: 3

Explanation

A Table Scan reads rows across the table or heap to find those that satisfy a query. A scan is not always a problem. For example, scanning a small table may be cheaper than using an index. However, scanning a very large table for a highly selective query can create significant I/O and CPU usage. Administrators should examine predicates, indexes, statistics, and the execution plan before deciding whether a Table Scan requires optimization.

Question 289

Which join algorithm is commonly effective when one input is small and the other input has a useful index on the join column?

1. Hash Match
2. Merge Join
3. Nested Loops
4. Table Scan

Correct Answer: 3

Explanation

Nested Loops can be efficient when one input contains a relatively small number of rows and the other input can be efficiently searched using an index. SQL Server takes a row from the outer input and searches the inner input for matching rows. This can be very efficient for selective queries. However, Nested Loops can become expensive when the outer input is large and the inner side requires many repeated scans or lookups.

Question 290

Which join algorithm is often useful for joining large, unsorted inputs?

1. Nested Loops
2. Hash Match
3. Merge Join
4. Index Seek

Correct Answer: 2

Explanation

Hash Match can be effective when joining large inputs that are not already sorted on the join columns. SQL Server builds a hash structure from one input and uses it to find matching rows from the other input. Hash joins can consume significant memory, so memory pressure or insufficient memory grants can affect performance. Administrators should review execution plans, cardinality estimates, indexes, and statistics when investigating expensive Hash Match operations.

Question 291

Which join algorithm can be efficient when both inputs are already sorted on the join columns?

1. Merge Join
2. Hash Match
3. Nested Loops
4. Table Scan

Correct Answer: 1

Explanation

A Merge Join can be highly efficient when both inputs are sorted according to the join keys. SQL Server can move through the sorted inputs and match rows without building a hash table. Appropriate indexes may provide the required ordering, although sorting may also be added to the execution plan when necessary. Administrators should consider the complete plan and estimated row counts because SQL Server can choose a different join algorithm when another strategy has a lower estimated cost.

Question 292

Which SQL Server object provides historical query performance information for troubleshooting regressions?

1. Query Store
2. tempdb
3. model
4. Database Mail

Correct Answer: 3

Explanation

Query Store retains historical information about queries, execution plans, and runtime statistics. This makes it valuable when investigating performance regressions that occurred after a deployment, configuration change, statistics update, or other event. Administrators can compare query behavior over time and identify changes in execution plans or resource consumption. Query Store can also support plan forcing when an administrator identifies a known good plan that should be used for a problematic query.

Question 293

Which SQL Server maintenance operation rebuilds an index and can also update its statistics as part of the operation?

1. ALTER INDEX … REBUILD
2. ALTER INDEX … DISABLE
3. DROP DATABASE
4. CREATE VIEW

Correct Answer: 1

Explanation

ALTER INDEX … REBUILD rebuilds an index and can update index statistics as part of the operation. Rebuilding creates a new index structure and can help address significant fragmentation. It can also require substantial resources depending on the index size and rebuild options. Administrators should schedule index rebuilds carefully and consider online capabilities, available resources, workload impact, and whether reorganizing the index would be sufficient.

Question 294

Which SQL Server maintenance operation performs a less intensive physical reorganization of an index?

1. ALTER INDEX … REORGANIZE
2. ALTER INDEX … REBUILD
3. DBCC CHECKDB
4. UPDATE DATABASE

Correct Answer: 2

Explanation

ALTER INDEX … REORGANIZE performs an online operation that reorganizes the leaf level of an index. It generally uses fewer resources at a time than a full rebuild and can be useful for moderate fragmentation. However, reorganize does not provide all the same benefits as rebuilding an index. Administrators should base the maintenance method on fragmentation levels, workload requirements, available resources, and the specific index rather than automatically rebuilding every index.

Question 295

Which SQL Server feature automatically updates column statistics when SQL Server determines that they have become sufficiently outdated?

1. Auto Update Statistics
2. Auto Shrink
3. Auto Close
4. Database Mail

Correct Answer: 1

Explanation

Auto Update Statistics allows SQL Server to automatically refresh statistics when it determines that existing statistics may no longer accurately represent the underlying data. Updated statistics help the query optimizer estimate row counts and choose appropriate execution plans. Keeping statistics current is important for query performance, especially when tables experience significant data changes. Administrators can monitor statistics behavior and manually update statistics when workload requirements or troubleshooting indicate that an update is necessary.

Question 296

Which database option allows SQL Server to automatically create statistics for columns used in query predicates when appropriate?

1. AUTO_CREATE_STATISTICS
2. AUTO_CLOSE
3. AUTO_SHRINK
4. AUTO_UPDATE_DATABASE

Correct Answer: 1

Explanation

The AUTO_CREATE_STATISTICS database option allows SQL Server to automatically create statistics for columns used in queries when the optimizer determines that statistics are needed. These statistics help SQL Server estimate the number of rows that a query will return. Accurate cardinality estimates can lead to better execution plans. Administrators should generally understand how automatic statistics creation works before disabling it because the optimizer relies heavily on statistics for effective query optimization.

Question 297

Which SQL Server feature helps improve performance by allowing queries to access only relevant partitions of a partitioned table?

1. Partition elimination
2. Row-Level Security
3. Data masking
4. Transparent Data Encryption

Correct Answer: 4

Explanation

Partition elimination allows SQL Server to skip partitions that cannot contain rows matching a query predicate. For example, a table partitioned by transaction date may allow a query for one month to access only the relevant partitions. This can reduce I/O and improve performance when partitioning and query predicates are designed appropriately. Administrators should verify actual execution plans to confirm that partition elimination is occurring rather than assuming that partitioning automatically improves every query.

Question 298

Which Azure SQL Database feature allows administrators to monitor resource consumption by individual queries?

1. Query Performance Insight
2. Azure Key Vault
3. Private Link
4. Azure Firewall

Correct Answer: 1

Explanation

Query Performance Insight provides a way to examine query-level resource consumption in Azure SQL Database. Administrators can use it to identify queries that consume significant CPU, data I/O, or other resources and then investigate those queries further. It is useful for prioritizing optimization work because administrators can focus on the queries having the greatest impact. Query Performance Insight can be used alongside Query Store and execution plans for more detailed analysis.

Question 299

Which Azure SQL Database capability provides automatic backups without requiring administrators to create traditional backup jobs?

1. Automated backups
2. SQL Server Agent
3. Database Mail
4. Extended Events

Correct Answer: 2

Explanation

Azure SQL Database provides automated backups as part of the managed service. Microsoft manages the underlying backup infrastructure, allowing administrators to focus on retention, recovery requirements, and appropriate backup redundancy options rather than creating traditional SQL Server Agent backup jobs. These automated backups support recovery capabilities such as point-in-time restore within the applicable retention period. Administrators should still understand retention and redundancy settings to ensure they meet business recovery requirements.

Question 300

Which Azure SQL Database capability can retain backups for a much longer period than the normal short-term backup retention window?

1. Long-term retention
2. Query Store
3. Elastic pools
4. Dynamic Data Masking

Correct Answer: 3

Explanation

Long-term retention (LTR) allows Azure SQL Database backups to be retained for extended periods beyond normal short-term backup retention. It is useful when organizations need to preserve database backups for compliance, auditing, or long-term recovery requirements. Administrators can configure retention policies according to organizational needs. LTR should be considered separately from point-in-time restore because the two capabilities serve different purposes: short-term recovery focuses on recent changes, while LTR preserves backups for longer-term requirements.