You need to identify the cause of the performance issues on SalesSQLDb1.Which two dynamic management views should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

sys.dm_exec_compute_node_errors

You need to implement a solution to notify the administrators. The solution must meet the monitoring requirements.What should you do?

Create an Azure Monitor alert rule that has a dynamic threshold and assign the alert rule to an action group.

Create an Azure Monitor alert rule that has a static threshold and assign the alert rule to an action group.

Add a diagnostic setting that logs QueryStoreRuntimeStatistics and streams to an Azure event hub.

Add a diagnostic setting that logs Timeouts and streams to an Azure event hub.

Create an Azure Monitor alert rule that has a dynamic threshold and assign the alert rule to an action group.

What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?

Update the connection strings of SalesSQLDb1App1.

Configure SalesSQLDb1 as writable.

Update the connection strings of SalesSQLDb1App1.

Update the firewall rules of SalesSQLDb1.

Update the users in SalesSQLDb1.

You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements.What should you include in the plan?

Configure the Manage Backups settings for ResearchSrv01.

Configure the Deleted databases settings for ResearchSrv01.

Deploy and configure an Azure Backup server.

Configure the Advanced Data Security settings for ResearchDB1.

Configure the Manage Backups settings for ResearchSrv01.

What should you use to migrate the PostgreSQL database?

Azure Database Migration Service

You need to recommend a solution to ensure that the customers can create the database objects. The solution must meet the business goals. What should you include in the recommendation?

For each customer, create an additional schema and grant the customer ddl_admin to the new schema.

For each customer, grant the customer ddl_admin to the existing schema.

For each customer, create an additional schema and grant the customer ddl_admin to the new schema.

For each customer, create an additional schema and grant the customer db_writer to the new schema.

For each customer, grant the customer db_writer to the existing schema.

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.

Change Salary to the money data type.

Change LastHireDate to the date data type.

Change Salary to the money data type.

Change PhoneNumber to the float data type.

Change LastHireDate to the datetime2(7) data type.

Change PhoneNumber to the bigint data type.

Change LastHireDate to the date data type.

You have SQL Server on an Azure virtual machine that contains a database named DB1.You have an application that queries DB1 to generate a sales report.You need to see the parameter values from the last time the query was executed.Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

Enable Lightweight_Query_Profiling in DB1

Enable PARAMETER_SNIFFING in DB1

Enable Last_Query_Plan_Stats in the master database

Enable Lightweight_Query_Profiling in DB1

Enable Last_Query_Plan_Stats in DB1

Enable Lightweight_Query_Profiling in the master database

Enable PARAMETER_SNIFFING in DB1

You have SQL Server on an Azure virtual machine that contains a database named DB1.You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit: What should you do to ensure that the query uses the execution plan which executes in the least amount of time?

Force the query execution plan for plan 1220917.

Force the query execution plan for plan 1221065.

Run the DBCC FREEPROCCACHE command.

Force the query execution plan for plan 1220917.

You have an Azure SQL database named DB1. You run a query while connected to DB1.You review the actual execution plan for the query, and you add an index to a table referenced by the query. You need to compare the previous actual execution plan for the query to the Live Query Statistics. What should you do first in Microsoft SQL Server Management Studio (SSMS)?

Save the actual execution plan.

For DB1, set QUERY_CAPTURE_MODE of Query Store to All.

Run the SET SHOWPLAN_ALL Transact-SQL statement.

Save the actual execution plan.

You have an Azure SQL database.Users report that the executions of a stored procedure are slower than usual. You suspect that a regressed query is causing the performance issue. You need to view the query execution plan to verify whether a regressed query is causing the issue. The solution must minimize effort. What should you use?

Query Store in Microsoft SQL Server Management Studio (SSMS)

Performance Recommendations in the Azure portal

Extended Events in Microsoft SQL Server Management Studio (SSMS)

Query Store in Microsoft SQL Server Management Studio (SSMS)

Query Performance Insight in the Azure portal

You have an Azure SQL database. The database contains a table that uses a columnstore index and is accessed infrequently. You enable columnstore archival compression.What are two possible results of the configuration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

Queries that use the index will retrieve fewer data pages.

Queries that use the index will consume more CPU resources.

Queries that use the index will consume more disk I/O.

Queries that use the index will retrieve fewer data pages.

The index will consume more disk space.

The index will consume more memory.

Queries that use the index will consume more CPU resources.

You are designing a star schema for a dataset that contains records of online orders. Each record includes an order date, an order due date, and an order ship date. You need to ensure that the design provides the fastest query times of the records when querying for arbitrary date ranges and aggregating by fiscal calendar attributes. Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

Create a date dimension table that has an integer key in the format of YYYYMMDD.

Use integer columns for the date fields.

Create a date dimension table that has a DateTime key.

Create a date dimension table that has an integer key in the format of YYYYMMDD.

Use built-in SQL functions to extract date attributes.

Use integer columns for the date fields.

Use DateTime columns for the date fields.

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. What is a possible cause of the error?

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, there was no data in wwi/BIKES/CARBON.

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.

The pipeline was triggered too early.

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?

Run sys.dm_exec_query_plan_stats.

Generate an estimated execution plan.

Generate an actual execution plan.

Run sys.dm_exec_query_plan_stats.

Generate Live Query Statistics.

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.

For DB1, set QUERY_CAPTURE_MODE of Query Store to All.

Run the SET FORCEPLAN Transact-SQL statement.

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.) Which Transact-SQL command should you run?

GRANT VIEW DATABASE STATE TO DevUser

GRANT VIEW DEFINITION TO DevUser

GRANT VIEW DATABASE STATE TO DevUser

Microsoft DP-300 Practice Test 1 - Free Online Exam Prep & Questions

Question 1 / 40

DRAG DROP

You need to configure user authentication for the SERVER1 databases. The solution must meet the security and compliance requirements.

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.

Microsoft DP-300 image Question 1 5217311 10022024015907000

Correct answer:

Microsoft DP-300 image answer Question 1 5217311 10022024015907000

Show Case Study

Comment (0)

Explanation:

Scenario: Authenticate database users by using Active Directory credentials.

The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

1. Create and populate Azure AD.

2. Optional: Associate or change the active directory that is currently associated with your Azure Subscription.

3. Create an Azure Active Directory administrator. (Step 1)

4. Connect to the databases using an Azure AD account (the Administrator account that was configured in the previous step). (Step 2)

5. Create contained database users in your database mapped to Azure AD identities. (Step 3)

Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell

asked 02/10/2024

Janina Loveria

47 questions

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

Microsoft DP-300 Practice Test 1

Question

Microsoft DP-300 Practice Tests

Practice Tests