DP-300 Free Practice Test

- (Exam Topic 1)
You need to implement the monitoring of SalesSQLDb1. The solution must meet the technical requirements. How should you collect and stream metrics? To answer, select the appropriate options in the answer area. NOTE:Each correct selection is worth one point.

Solution:
Box 1: The server, the elastic pool, and the database
Senario:
SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool.
Litware technical requirements include: all SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.
Box 2: Azure Event hubs
Scenario: Migrate ManufacturingSQLDb1 to the Azure virtual machine platform. Event hubs are able to handle custom metrics.

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
Your company uses Azure Stream Analytics to monitor devices.
The company plans to double the number of devices that are monitored.
You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?

1. A. Input Deserialization Errors
2. B. Late Input Events
3. C. Early Input Events
4. D. Watermark delay

Correct Answer: D
The Watermark delay metric is computed as the wall clock time of the processing node minus the largest watermark it has seen so far.
The watermark delay metric can rise due to:
* 1. Not enough processing resources in Stream Analytics to handle the volume of input events.
* 2. Not enough throughput within the input event brokers, so they are throttled.
* 3. Output sinks are not provisioned with enough capacity, so they are throttled. Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling

- (Exam Topic 5)
You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 contains a fact table named Table.
You need to identify the extent of the data skew in Table1. What should you do in Synapse Studio?

1. A. Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.
2. B. Connect to the built-in pool and run DBCC CHECKALLOC.
3. C. Connect to Pool1 and run DBCC CHECKALLOC.
4. D. Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

Correct Answer: D
Use sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data. Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet

- (Exam Topic 5)
You have four Azure subscriptions. Each subscription contains multiple Azure SQL databases. You need to update the column and index statistics for the databases.
What should you use?

1. A. an Azure Automation runbook
2. B. a SQL Agent job
3. C. Azure SQL Analytics
4. D. automatic tuning in Azure SQL Database

Correct Answer: A
Reference:
https://www.sqlshack.com/automate-azure-sql-database-indexes-and-statistics-maintenance/

- (Exam Topic 5)
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?

1. A. PARAMETERIZATIONtoFORCED
2. B. PARAMETERIZATIONtoSIMPLE
3. C. Delayed Durability toForced
4. D. READ_COMMITTED_SNAPSHOTtoON

Correct Answer: D
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