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

 

Question 141

Which Azure service can provide recommendations to improve the performance, security, and reliability of Azure resources?

1. Azure Advisor
2. Azure DNS
3. Azure Storage Explorer
4. Azure Bastion

Correct Answer: 1

Explanation

Azure Advisor analyzes Azure resources and provides recommendations related to areas such as performance, security, reliability, and cost optimization. Database administrators can use these recommendations to identify potential improvements in their Azure SQL environments. Advisor does not automatically apply every recommendation; administrators should review each recommendation against the workload and business requirements before making changes. Using Advisor alongside Azure Monitor and database-specific tools provides a broader view of resource health and optimization opportunities.

Question 142

Which Azure service provides a centralized dashboard for recommendations related to Azure resource costs?

1. Azure Cost Management
2. Azure Key Vault
3. Azure Monitor
4. Azure DevOps

Correct Answer: 1

Explanation

Azure Cost Management provides tools for monitoring, analyzing, and controlling Azure spending. Administrators can review costs by subscription, resource, service, and other dimensions. Budgets and alerts can also help organizations track spending against planned limits. For database administrators, Cost Management can help identify expensive database resources, unused resources, or opportunities to optimize service tiers. Cost analysis should be combined with performance and availability requirements rather than focusing only on reducing resource costs.

Question 143

Which Azure SQL Database capability allows multiple databases to share a pool of compute resources?

1. Elastic pools
2. Failover groups
3. Private endpoints
4. Availability zones

Correct Answer: 2

Explanation

Elastic pools allow multiple Azure SQL databases to share a common set of compute resources. They are particularly useful when databases have variable or unpredictable workloads and do not all reach peak utilization at the same time. Instead of provisioning each database independently for maximum demand, the databases can share resources within the pool. Administrators should monitor pool utilization and configure suitable limits to prevent one database from consuming resources needed by others.

Question 144

Which Azure SQL Database service tier is designed to support very large databases with independently scalable storage and compute?

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

Correct Answer: 1

Explanation

The Hyperscale service tier is designed for workloads that require large amounts of database storage and scalable compute. Its architecture separates compute and storage, allowing the platform to support large databases and different scaling requirements. Hyperscale can be useful for applications with significant data volumes or changing performance requirements. Before selecting it, administrators should verify application compatibility, supported features, performance requirements, and expected costs because not every workload needs the capabilities of Hyperscale.

Question 145

Which Azure SQL Database feature allows a database to automatically scale compute resources based on workload demand?

1. Serverless compute
2. Long-term retention
3. Database auditing
4. Data masking

Correct Answer: 3

Explanation

Serverless compute allows supported Azure SQL Database workloads to automatically adjust compute resources according to demand. Administrators can configure minimum and maximum compute limits, and supported configurations can automatically pause during periods of inactivity. This can be cost-effective for databases with intermittent workloads. However, workloads with consistently high activity may be better suited to provisioned compute. Administrators should evaluate performance, resume behavior, and cost before selecting serverless for an application.

Question 146

Which SQL Server feature can reduce the amount of storage and I/O required by large indexes and tables?

1. Data compression
2. Database mirroring
3. Replication
4. Database Mail

Correct Answer: 1

Explanation

SQL Server data compression can reduce the amount of storage required for supported tables and indexes. Because compressed data may require fewer pages to be read, compression can sometimes reduce I/O as well. However, compression requires CPU resources for compression and decompression operations. Administrators should test compression with representative workloads before applying it broadly. Row and page compression can have different effects depending on the type and distribution of data.

Question 147

Which SQL Server feature allows data to be automatically encrypted while it is transmitted over a network connection?

1. TLS encryption
2. Data compression
3. Row-Level Security
4. Query Store

Correct Answer: 4

Explanation

TLS, or Transport Layer Security, protects data while it is transmitted between a client and SQL Server. This helps prevent unauthorized parties from reading network traffic containing database queries, credentials, or returned data. Transport encryption is different from Transparent Data Encryption, which protects data at rest. Administrators should configure secure connection settings and use current supported TLS versions. Network encryption is one part of a complete database security strategy.

Question 148

Which security principle requires administrators to provide users only the permissions they need to perform their tasks?

1. Least privilege
2. Maximum access
3. Shared administration
4. Anonymous access

Correct Answer: 1

Explanation

The principle of least privilege means users and applications should receive only the permissions necessary for their responsibilities. For example, an application that only reads customer information should not automatically receive permission to delete tables or modify database security. Least privilege reduces the potential impact of compromised accounts and accidental actions. Administrators should regularly review permissions and remove access that is no longer required.

Question 149

Which SQL Server feature can audit database activity such as successful and failed access attempts?

1. SQL Server Audit
2. Query Store
3. Data compression
4. SQL Server Agent only

Correct Answer: 1

Explanation

SQL Server Audit provides a framework for recording selected database and server events for security and compliance purposes. Depending on configuration, audit information can include authentication events, permission changes, and other database activities. Auditing helps organizations investigate suspicious activity and demonstrate compliance with security requirements. Administrators should define meaningful audit policies because excessive auditing can generate large amounts of data and increase storage and management requirements.

Question 150

Which Azure SQL capability records database events for security and compliance analysis?

1. Auditing
2. Elastic pools
3. Query Store only
4. Serverless computer

Correct Answer: 2

Explanation

Azure SQL auditing records selected database events and activity so administrators can review what happened in the database environment. Audit information can be sent to supported destinations for storage and analysis. Auditing is useful for compliance, security investigations, and tracking important database operations. It is different from Query Store, which focuses primarily on query performance and execution information. Administrators should configure auditing according to organizational requirements while considering storage and retention needs.

Question 151

Which Azure SQL security feature can identify potential database vulnerabilities and security recommendations?

1. Microsoft Defender for SQL
2. Azure DNS
3. Azure Traffic Manager
4. Azure Storage Explorer

Correct Answer: 1

Explanation

Microsoft Defender for SQL provides security capabilities that can help identify threats, vulnerabilities, and security recommendations for supported SQL environments. It can assist administrators in improving the security posture of databases by highlighting potential weaknesses and suspicious activity. Defender for SQL does not replace normal access controls, encryption, or secure network design. Instead, it complements those controls and provides additional security monitoring and assessment capabilities.

Question 152

Which database feature allows an application to store frequently accessed query results temporarily to improve response time?

1. Caching
2. Foreign key
3. Trigger
4. Constraint

Correct Answer: 3

Explanation

Caching stores frequently accessed information so that it can be returned without repeatedly performing the same expensive database operation. Depending on the architecture, caching can occur in an application, memory cache, or other supported caching layer. Caching can reduce database workload and improve application response time. However, administrators must consider data freshness and invalidation because cached information can become outdated. Caching is most effective when applied to appropriate read-heavy workloads.

Question 153

Which SQL Server performance concept describes the amount of time a session spends waiting for a resource before it can continue?

1. Wait statistics
2. Database schema
3. Data masking
4. Backup retention

Correct Answer: 1

Explanation

Wait statistics provide information about where SQL Server sessions spend time waiting for resources or events. Common waits can relate to CPU, locks, I/O, memory, or other system resources. Administrators can use wait information to identify potential performance bottlenecks and determine where deeper investigation is needed. Wait statistics should not be interpreted in isolation because some waits are normal for particular workloads. They are most useful when combined with query, resource, and workload information.

Question 154

Which SQL Server resource is primarily responsible for storing temporary tables, table variables, and row-versioning data?

1. tempdb
2. master
3. model
4. msdb

Correct Answer: 1

Explanation

tempdb is a system database used for temporary objects, intermediate query results, sorting operations, and row-versioning data. Many SQL Server workloads rely heavily on tempdb, so poor configuration or insufficient resources can cause performance problems. Administrators should monitor tempdb space, I/O, and contention when troubleshooting workloads that use large temporary objects or row versioning. Tempdb is recreated when SQL Server starts, so it does not store permanent application data.

Question 155

Which SQL Server system database stores information related to SQL Server Agent jobs and schedules?

1. msdb
2. master
3. model
4. tempdb

Correct Answer: 2

Explanation

The msdb system database stores information used by several SQL Server components, including SQL Server Agent jobs, schedules, alerts, and backup history. Because it contains important administrative metadata, administrators should include msdb in an appropriate backup strategy. If msdb is lost, information such as scheduled jobs and backup history may also be affected. Administrators should monitor the size and health of system databases as part of regular SQL Server maintenance.

Question 156

Which SQL Server system database contains instance-level configuration and information about databases?

1. master
2. tempdb
3. msdb
4. model

Correct Answer: 1

Explanation

The master database contains critical system-level information about the SQL Server instance, including information about databases, logins, endpoints, and configuration metadata. SQL Server requires the master database to start and operate correctly. Because it is essential to the instance, administrators should protect it with an appropriate backup and recovery strategy. Damage or loss of master can affect the ability to start the SQL Server instance and access its configuration information.

Question 157

Which SQL Server system database is used as the template when new user databases are created?

1. model
2. master
3. msdb
4. tempdb

Correct Answer: 1

Explanation

The model database serves as a template for newly created databases. Changes made to model can affect characteristics inherited by new databases, such as certain database settings and objects. Administrators should therefore modify model carefully and understand the effect of those changes. Existing databases are not automatically changed when model is modified. The model database is also involved in creating temporary databases and provides a foundation for new database structures.

Question 158

Which SQL Server system database is recreated when the SQL Server service starts?

1. tempdb
2. master
3. msdb
4. model

Correct Answer: 3

Explanation

tempdb is recreated every time the SQL Server instance starts. Because it is temporary, objects stored in tempdb do not survive a restart. SQL Server uses tempdb for temporary tables, worktables, sorting, and row-versioning operations. Since many workloads depend on it, tempdb configuration and performance are important. Administrators should monitor its size, file configuration, I/O performance, and contention when troubleshooting workload-related problems.

Question 159

Which SQL Server command can display the current configuration settings of the SQL Server instance?

1. sp_configure
2. sp_helpuser
3. sp_whoisactive
4. sp_databaseconfig

Correct Answer: 1

Explanation

sp_configure displays and allows administrators to configure supported SQL Server server-level configuration options. It can be used to review settings such as certain memory, parallelism, and advanced configuration options. Some configuration changes require additional steps before they become active. Administrators should understand the effect of a setting before changing it, especially in production environments. Configuration changes should ideally be tested and documented to support predictable administration.

Question 160

Which SQL Server setting controls the maximum amount of server memory that SQL Server can use?

1. max server memory
2. min database size
3. max table rows
4. maximum transaction count

Correct Answer: 2

Explanation

The max server memory configuration setting controls the maximum amount of memory SQL Server can use for its main memory allocation. Proper configuration helps balance SQL Server memory requirements with memory needed by the operating system and other applications. Setting the value too high can leave insufficient memory for the operating system, while setting it too low can limit SQL Server performance. Administrators should consider server hardware, workload, and other applications when configuring this setting.