ExamGecko
Login
Home / Microsoft / DP-300 / List of questions
Ask Question

Microsoft DP-300 Practice Test - Questions Answers, Page 4

List of questions

Question 31

Report
Export
Collapse

You have an Azure Data Factory pipeline that is triggered hourly.

The pipeline has had 100% success for the past seven days.

The pipeline execution fails, and two retries that occur 15 minutes apart also fail. The third failure returns the following error.

Microsoft DP-300 image Question 16 89914 10022024015907000000

What is a possible cause of the error?

From 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON.
From 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON.
The parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect.
The parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect.
From 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect.
From 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect.
The pipeline was triggered too early.
The pipeline was triggered too early.
Suggested answer: B
asked 02/10/2024
Mostafa Mohamed
37 questions

Question 32

Report
Export
Collapse

HOTSPOT

You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema.

You plan to have a fact table for website visits. The table will be approximately 5 GB.

You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.

What should you recommend? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.


Microsoft DP-300 image Question 32 89915 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 32 89915 10022024015907000

Explanation:

Box 1: Hash

Consider using a hash-distributed table when:

The table size on disk is more than 2 GB.

The table has frequent insert, update, and delete operations.

Box 2: Clustered columnstore

Clustered columnstore tables offer both the highest level of data compression and the best overall query performance.

Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

asked 02/10/2024
Patrick Duglay Piceda
34 questions

Question 33

Report
Export
Collapse

HOTSPOT

You have an Azure SQL database.

You are reviewing a slow performing query as shown in the following exhibit.

Microsoft DP-300 image Question 18 89916 10022024015907000000

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.


Microsoft DP-300 image Question 33 89916 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 33 89916 10022024015907000

Explanation:

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15

asked 02/10/2024
Osman Taskiran
32 questions

Question 34

Report
Export
Collapse

You have an Azure SQL managed instance.

You need to gather the last execution of a query plan and its runtime statistics. The solution must minimize the impact on currently running queries.

What should you do?

Generate an estimated execution plan.
Generate an estimated execution plan.
Generate an actual execution plan.
Generate an actual execution plan.
Run sys.dm_exec_query_plan_stats.
Run sys.dm_exec_query_plan_stats.
Generate Live Query Statistics.
Generate Live Query Statistics.
Suggested answer: C

Explanation:

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=sql-server-ver15

asked 02/10/2024
William Hanna
30 questions

Question 35

Report
Export
Collapse

HOTSPOT

You have an Azure SQL database named db1 on a server named server1.

You use Query Performance Insight to monitor db1.

You need to modify the Query Store configuration to ensure that performance monitoring data is available as soon as possible.

Which configuration setting should you modify and which value should you configure? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.


Microsoft DP-300 image Question 35 89918 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 35 89918 10022024015907000
asked 02/10/2024
Athabile Landzela
35 questions

Question 36

Report
Export
Collapse

You have an Azure SQL Database managed instance.

The instance starts experiencing performance issues.

You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.

What should you use?

SQL Profiler
SQL Profiler
Extended Events
Extended Events
Query Store
Query Store
dynamic management views
dynamic management views
Suggested answer: D
asked 02/10/2024
Sankalp Wadiwa
34 questions

Question 37

Report
Export
Collapse

You have an Azure SQL database named DB1.

You need to display the estimated execution plan of a query by using the query editor in the Azure portal.

What should you do first?

Run the SET SHOWPLAN_ALL Transact-SQL statement.
Run the SET SHOWPLAN_ALL Transact-SQL statement.
For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
Run the SET FORCEPLAN Transact-SQL statement.
Run the SET FORCEPLAN Transact-SQL statement.
Enable Query Store for DB1.
Enable Query Store for DB1.
Suggested answer: A

Explanation:

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver15

asked 02/10/2024
Alan Coutinho
42 questions

Question 38

Report
Export
Collapse

HOTSPOT

You have an Azure SQL database.

You have a query and the associated execution plan as shown in the following exhibit.

Microsoft DP-300 image Question 23 89921 10022024015907000000

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.


Microsoft DP-300 image Question 38 89921 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 38 89921 10022024015907000

Explanation:

Box 1: Key Lookup

The Key Lookup cost is 99% so that is the performance bottleneck.

Box 2: nonclustered index

The key lookup on the clustered index is used because the nonclustered index does not include the required columns to resolve the query. If you add the required columns to the nonclustered index, the key lookup will not be required.

asked 02/10/2024
TIAM HERVE
47 questions

Question 39

Report
Export
Collapse

You have an Azure SQL database named DB3.

You need to provide a user named DevUser with the ability to view the properties of DB3 from Microsoft SQL Server Management Studio (SSMS) as shown in the exhibit. (Click the Exhibit tab.)

Microsoft DP-300 image Question 1 89967 10022024015907000000

Which Transact-SQL command should you run?

GRANT SHOWPLAN TO DevUser
GRANT SHOWPLAN TO DevUser
GRANT VIEW DEFINITION TO DevUser
GRANT VIEW DEFINITION TO DevUser
GRANT VIEW DATABASE STATE TO DevUser
GRANT VIEW DATABASE STATE TO DevUser
GRANT SELECT TO DevUser
GRANT SELECT TO DevUser
Suggested answer: C

Explanation:

The exhibits displays Database [State] properties.

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-options-page

asked 02/10/2024
mohamed mamdouh
43 questions

Question 40

Report
Export
Collapse

You have the following Transact-SQL query.

Microsoft DP-300 image Question 2 89968 10022024015907000000

Which column returned by the query represents the free space in each file?

ColumnA
ColumnA
ColumnB
ColumnB
ColumnC
ColumnC
ColumnD
ColumnD
Suggested answer: C

Explanation:

Example:

Free space for the file in the below query result set will be returned by the FreeSpaceMB column.

SELECT DB_NAME() AS DbName,

name AS FileName,

type_desc,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type IN (0,1);

Reference:

https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

asked 02/10/2024
lawrence Ajibolade
49 questions
Total 342 questions
Go to page: of 35
Search
Open VPLUS File
Convert VPLUS to PDF

Related questions

You have an Azure SQL Database managed instance named SQLMI1. A Microsoft SQL Server Agent job runs on SQLMI1. You need to ensure that an automatic email notification is sent once the job completes. What should you include in the solution?
You have an Azure subscription. You create a logical SQL server that hosts four databases Each database will be used by a separate customer. You need to ensure that each customer can access only its own database. The solution must minimize administrative effort Which two actions should you perform? Each correct answer presents part of the solution NOTE: Each correct selection is worth one point.
Task 5 You need to configure a disaster recovery solution for db1. When a failover occurs, the connection strings to the database must remain the same. The secondary server must be in the West US 3 Azure region.
Task 10 You need to protect all the databases on sql37006S95 from SQL injection attacks.
DRAG DROP You have an Azure SQL database that contains a table named Employees. Employees contains a column named Salary. You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the Salary column and must provide the most secure encryption. Which three 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.
HOTSPOT You have an Azure SQL database named DB1 that contains two tables named Table1 and Table2. Both tables contain a column named a Column1. Column1 is used for joins by an application named App1. You need to protect the contents of Column1 at rest, in transit, and in use. How should you protect the contents of Column1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
You are planning disaster recovery for the failover group of an Azure SQL Database managed instance. Your company's SLA requires that the database in the failover group become available as quickly as possible if a major outage occurs. You set the Read/Write failover policy to Automatic. What are two results of the configuration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information. You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information. What should you include in the recommendation?
You have two on-premises Microsoft SQL Server 2019 instances named SQL1 and SQL2. You need to migrate the databases hosted on SQL 1 to Azure. The solution must meet the following requirements: The service that hosts the migrated databases must be able to communicate with SQL2 by using linked server connections. Administrative effort must be minimized. What should you use to host the databases?
You need to recommend a solution to meet the security requirements and the business requirements for DB3. What should you recommend as the first step of the solution?