You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements.What should you create?

a table the has an IDENTITY property

a table that has a FOREIGN KEY constraint

a table the has an IDENTITY property

a system-versioned temporal table

You have an Azure virtual machine based on a custom image named VM1.VM1 hosts an instance of Microsoft SQL Server 2019 Standard.You need to automate the maintenance of VM1 to meet the following requirements:Automate the patching of SQL Server and Windows Server.Automate full database backups and transaction log backups of the databases on VM1.Minimize administrative effort.What should you do first?

Register VM1 to the Microsoft.SqlVirtualMachine resource provider

Enable a system-assigned managed identity for VM1

Register VM1 to the Microsoft.Sql resource provider

Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1

Register VM1 to the Microsoft.SqlVirtualMachine resource provider

You receive numerous alerts from Azure Monitor for an Azure SQL database.You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period. Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

Set the Alert logic threshold to Dynamic

Set Threshold Sensitivity to Low

Set Threshold Sensitivity to High

Set the Alert logic threshold to Dynamic

Set the Alert logic threshold to Static

Set Threshold Sensitivity to Low

You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases.The databases perform slower than expected.You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases on sqlsrv1. What should you do?

Review information provided by Query Performance Insight

Review information provided by SQL Server Profiler-based traces

Review information provided by Query Performance Insight

Run dynamic management view-based queries

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?

Double the value of Statistics Collection Interval.

Halve the value of Data Flush Interval.

Double the value of Statistics Collection Interval.

Halve the value of Statistics Collection Interval

Double the value of Data Flush Interval.

You have 50 Azure SQL databases.You need to notify the database owner when the database settings, such as the database size and pricing tier, are modified in Azure. What should you do?

Create an alert rule that uses an Activity Log signal type.

Create a diagnostic setting for the activity log that has the Security log enabled.

For the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled.

Create an alert rule that uses a Metric signal type.

Create an alert rule that uses an Activity Log signal type.

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?

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

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

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

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

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

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?

Increase the number of streaming units (SUs).

Remove any named consumer groups from the connection and use $default.

Change the compatibility level of the Stream Analytics job.

Create an additional output stream for the existing input stream.

Increase the number of streaming units (SUs).

You manage an enterprise data warehouse in Azure Synapse Analytics.Users report slow performance when they run commonly used queries. Users do not report performance changes for infrequently used queries. You need to monitor resource utilization to determine the source of the performance issues.Which metric should you monitor?

Data Warehouse Units (DWU) used

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?

Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

Connect to the built-in pool and run DBCC CHECKALLOC.

Connect to Pool1 and run DBCC CHECKALLOC.

Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

You have an Azure Synapse Analytics dedicated SQL pool.You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table. Which statement accurately describes the dbo.FactInternetSales table?

The table contains less than 10,000 rows.

All distributions contain data.

The table uses round-robin distribution

You have an Azure SQL database named db1 on a server named server1.You need to modify the MAXDOP settings for db1.What should you do?

Modify the database scoped configuration of db1.

Connect to db1 and run the sp_configure command.

Connect to the master database of server1 and run the sp_configure command.

Configure the extended properties of db1.

Modify the database scoped configuration of db1.

You have SQL Server on an Azure virtual machine.You need to add a 4-TB volume that meets the following requirements:Maximizes IOPsUses premium solid state drives (SSDs)What should you do?

Attach a stripe set that contains four 1-TB SSDs.

Attach a RAID-5 array that contains five 1-TB SSDs.

You have an Azure SQL database named db1 on a server named server1.The Intelligent Insights diagnostics log identifies that several tables are missing indexes.You need to ensure that indexes are created for the tables.What should you do?

Modify the automatic tuning settings for db1.

Run the DBCC DBREINDEX command.

Modify the automatic tuning settings for db1.

Modify the Query Store settings for db1.

You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2.You need to limit the number of IOPs that App2 queries generate on SQL1.Which two actions should you perform on SQL1? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

Run the Database Engine Tuning Advisor.

Reduce the Max Degree of Parallelism value.

You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.You plan to migrate DB1 to an Azure SQL Database managed instance.What should you use to minimize downtime and data loss during the migration?

distributed availability groups

You have 20 Azure SQL databases provisioned by using the vCore purchasing model.You plan to create an Azure SQL Database elastic pool and add the 20 databases.Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

total size of all the databases

number of concurrently peaking databases * peak CPU utilization per database

total number of databases * average CPU utilization per database

total size of all the databases

number of concurrently peaking databases * peak CPU utilization per database

maximum number of concurrent sessions for all the databases

total number of databases * average CPU utilization per database

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?

columnstore archival compression

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

Question 1 / 40

HOTSPOT

You plan to migrate on-premises Microsoft SQL Server databases to Azure.

You need to identify which deployment and resiliency options meet the following requirements:

Support user-initiated backups.

Support multiple automatically replicated instances across Azure regions.

Minimize administrative effort to implement and maintain business continuity.

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

NOTE: Each correct selection is worth one point.

Become a Premium Member for full access

Unlock Premium Member

Microsoft DP-300 Practice Test 4

Question

Microsoft DP-300 Practice Tests

Practice Tests