Test Online Free Microsoft DP-300 Exam Questions and Answers
Practice a live sample before buying full access. This page keeps the free DP-300 question set organized by page so visitors and search engines can reach the canonical -questions.html URL directly.
HOTSPOT
You have an Azure SQL database.
You are reviewing a slow performing query as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Graphical user interface, text, application, email
Description automatically generated
Question 32Selectable Answer
You have an Azure subscription that contains 50 instances of SQL Server on Azure Virtual Machines. The instances host 500 Azure SQL databases. You need to ensure that all the databases have the same configuration.
The solution must meet the following requirements:
• Auditing must be enabled.
• Azure Defender must be enabled.
• Public network access must be disabled.
• Administrative effort must be minimized.
Which two resources should you create in the subscription? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Answer:
Question 33Selectable Answer
You have an Azure Stream Analytics job.
You need to ensure that the job has enough streaming units provisioned.
You configure monitoring of the SU % Utilization metric.
Which two additional metrics should you monitor? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Answer: Explanation:
To react to increased workloads and increase streaming units, consider setting an alert of 80% on the SU Utilization metric. Also, you can use watermark delay and backlogged events metrics to see if there is an impact.
Note: Backlogged Input Events: Number of input events that are backlogged. A non-zero value for this metric implies that your job isn't able to keep up with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job, by increasing the SUs.
Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-monitoring
Question 34Selectable Answer
You have an Azure SQL database.
You need to implement a disaster recovery solution that meets the following requirements:
• Minimizes how long it takes to recover the database if a datacenter fails
• Minimizes administrative effort
What should you include in the solution?
Answer:
Question 35Selectable Answer
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have SQL Server 2019 on an Azure virtual machine.
You are troubleshooting performance issues for a query in a SQL Server instance.
To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.
You need to improve system performance.
Solution: You reduce the use of table variables and temporary tables.
Does this meet the goal?
You deploy a database to an Azure SQL Database managed instance.
You need to prevent read queries from blocking queries that are trying to write to the database.
Which database option should set?
Answer: Explanation:
In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
✑ The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
✑ The SNAPSHOT isolation level.
If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
Question 37Selectable Answer
You have an Azure SQL database named sqldb1.
You need to minimize the possibility of Query Store transitioning to a read-only state.
What should you do?
Answer: Explanation:
The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
Question 38Written Answer
HOTSPOT
You have a SQL Server on Azure Virtual Machines instance that hosts a database named Db1.
You need to configure the autogrow and autoshrink settings for DB1.
Which statements should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
You have an Azure SQL database named DB1.
You need to encrypt DB1.
The solution must meet the following requirements;
• Encrypt data in motion.
• Support comparison operators.
• Provide randomized encryption.
What should you include in the solution?
Answer:
Question 40Selectable Answer
You manage 100 Azure SQL managed instances located across 10 Azure regions.
You need to receive voice message notifications when a maintenance event affects any of the 10 regions. The solution must minimize administrative effort.
What should you do?
Answer:
Question 41Selectable Answer
You have an Azure subscription that contains the resources shown in the following table.
App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.
You need to modify db1 to resolve the issues experienced by Appl as soon as possible, without considering immediate costs
What you do?
Answer:
Question 42Selectable Answer
You are monitoring an Azure Stream Analytics job.
You discover that the Backlogged input Events metric is increasing slowly and is consistently non-zero.
You need to ensure that the job can handle all the events.
What should you do?
Answer: Explanation:
Backlogged Input Events: Number of input events that are backlogged. A non-zero value for this metric implies that your job isn't able to keep up with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job, by increasing the SUs.
Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-monitoring
Question 43Written Answer
HOTSPOT
You have an Azure SQL database named DB1 that contains a table named Table 1.
You run a query to load data into Table1.
The performance metrics of Table1 during the load operation are shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Question 44Written Answer
DRAG DROP
You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Automating Azure SQL DB index and statistics maintenance using Azure Automation:
Question 45Selectable Answer
You have an Azure SQL database that contains a table named factSales.
FactSales contains the columns shown in the following table.
FactSales has 6 billion rows and is loaded nightly by using a batch process.
Which type of compression provides the greatest space reduction for the database?
Answer: Explanation:
Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression.
Note: Columnstore ― The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
