Microsoft DP-300 Exam Practice Test Instant Access

Question 1

Based on the PaaS prototype, which Azure SQL Database compute tier should you use?

ABusiness Critical 4-vCore

BHyperscale

CGeneral Purpose v-vCore

DServerless

Answer : A

There are CPU and Data I/O spikes for the PaaS prototype. Business Critical 4-vCore is needed.

https://docs.microsoft.com/en-us/azure/azure-sql/database/reserved-capacity-overview

Question 2

You create five Azure SQL Database instances on the same logical server.

In each database, you create a user for an Azure Active Directory (Azure AD) user named User1.

User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error.

You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases.

What should you do?

ACreate User1 in the master database.

BAssign User1 the db_datareader role for the master database.

CAssign User1 the db_datareader role for the databases that Userl creates.

DGrant select on sys.databases to public in the master database.

Answer : A

https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

Question 3

SIMULATION

Task 1

In an Azure SQL database named db1, you need to enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table.

ASee the explanation part fo rthe complete Solution

Answer : A

To enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table in db1, you can use the following Transact-SQL script:

-- Connect to the Azure SQL database named db1

USE db1;

-- Enable page compression on the clustered index

ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader

REBUILD WITH (DATA_COMPRESSION = PAGE);

This script will rebuild the clustered index with page compression, which can reduce the storage space and improve the query performance

The script solution consists of three parts:

The first part isUSE db1; GO. This part connects to the Azure SQL database named db1, where the SalesLT.SalesOrderHeader table is located. TheGOcommand separates the batches of Transact-SQL statements and sends them to the server.

The second part isALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader REBUILD WITH (DATA_COMPRESSION = PAGE); GO. This part enables page compression on the clustered index named PK_SalesOrderHeader_SalesOrderlD, which is defined on the SalesLT.SalesOrderHeader table. TheALTER INDEXstatement modifies the properties of an existing index. TheREBUILDoption rebuilds the index from scratch, which is required to change the compression setting. TheDATA_COMPRESSION = PAGEoption specifies that page compression is applied to the index, which means that both row and prefix compression are used. Page compression can reduce the storage space and improve the query performance by compressing the data at the page level. TheGOcommand ends the batch of statements.

The third part is optional, but it can be useful to verify the compression status of the index. It isSELECT name, index_id, data_compression_desc FROM sys.indexes WHERE object_id = OBJECT_ID('SalesLT.SalesOrderHeader');. This part queries the sys.indexes catalog view, which contains information about the indexes in the database. TheSELECTstatement returns the name, index_id, and data_compression_desc columns for the indexes that belong to the SalesLT.SalesOrderHeader table. TheOBJECT_IDfunction returns the object identification number for the table name. The data_compression_desc column shows the compression type of the index, which should be PAGE for the clustered index after the script is executed.

These are the steps of the script solution for enabling page compression on the clustered index of the SalesLT.SalesOrderHeader table in db1.

Question 4

You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in fiveminute intervals and report only events that arrive during the interval.

The output will be sent to a Delta Lake table.

Which output mode should you use?

Acomplete

Bappend

Cupdate

Answer : A

Complete mode: You can use Structured Streaming to replace the entire table with every batch.

Incorrect Answers:

B: By default, streams run in append mode, which adds new records to the table.

https://docs.databricks.com/delta/delta-streaming.html

Question 5

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 change the data file for the master database to autogrow by 10 percent.

Does this meet the goal?

AYes

BNo

Answer : B

https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Question 6

A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.

You need to recommend changes to the data types to reduce storage and improve performance.

Which two actions should you recommend? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

AChange Salary to the money data type.

BChange PhoneNumber to the float data type.

CChange LastHireDate to the datetime2(7) data type.

DChange PhoneNumber to the bigint data type.

EChange LastHireDate to the date data type.

Answer : A, E

Question 7

You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1.

Several users execute adhoc queries to DW1 concurrently.

You regularly perform automated data loads to DW1.

You need to ensure that the automated data loads have enough memory available to complete quickly and

successfully when the adhoc queries run.

What should you do?

AAssign a smaller resource class to the automated data load queries.

BCreate sampled statistics to every column in each table of DW1.

CAssign a larger resource class to the automated data load queries.

DHash distribute the large fact tables in DW1 before performing the automated data loads.

Answer : C

The performance capacity of a query is determined by the user's resource class.

Smaller resource classes reduce the maximum memory per query, but increase concurrency.

Larger resource classes increase the maximum memory per query, but reduce concurrency.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource-classes-for-workloadmanagement

Microsoft Administering Microsoft Azure SQL Solutions DP-300 Exam Practice Test