DP-300 Free Practice Test

- (Exam Topic 5)
You have the following Transact-SQL query.

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

1. A. ColumnA
2. B. ColumnB
3. C. ColumnC
4. D. ColumnD

Correct Answer: C
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/

- (Exam Topic 5)
You have an Azure Data Factory instance named ADF1 and two Azure Synapse Analytics workspaces named WS1 and WS2.
ADF1 contains the following pipelines:
P1:Uses a copy activity to copy data from a nonpartitioned table in a dedicated SQL pool of WS1 to an Azure Data Lake Storage Gen2 account
P2:Uses a copy activity to copy data from text-delimited files in an Azure Data Lake Storage Gen2 account to a nonpartitioned table in a dedicated SQL pool of WS2
You need to configure P1 and P2 to maximize parallelism and performance.
Which dataset settings should you configure for the copy activity of each pipeline? To answer, select the appropriate options in the answer area.

Solution:
Graphical user interface, text, chat or text message Description automatically generated
P1: Set the Partition option to Dynamic Range.
The SQL Server connector in copy activity provides built-in data partitioning to copy data in parallel. P2: Set the Copy method to PolyBase
Polybase is the most efficient way to move data into Azure Synapse Analytics. Use the staging blob feature to achieve high load speeds from all types of data stores, including Azure Blob storage and Data Lake Store. (Polybase supports Azure Blob storage and Azure Data Lake Store by default.)
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse https://docs.microsoft.com/en-us/azure/data-factory/load-azure-sql-data-warehouse

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 1)
What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?

1. A. Configure SalesSQLDb1 as writable.
2. B. Update the connection strings of SalesSQLDb1App1.
3. C. Update the firewall rules of SalesSQLDb1.
4. D. Update the users in SalesSQLDb1.

Correct Answer: C
Scenario: SalesSQLDb1 uses database firewall rules and contained database users.

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

1. A. Force the query execution plan for plan 1221065.
2. B. Run the DBCC FREEPROCCACHE command.
3. C. Force the query execution plan for plan 1220917.
4. D. Disable parameter sniffing.

Correct Answer: C
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios

- (Exam Topic 2)
Which audit log destination should you use to meet the monitoring requirements?

1. A. Azure Storage
2. B. Azure Event Hubs
3. C. Azure Log Analytics

Correct Answer: C
Scenario: Use a single dashboard to review security and audit data for all the PaaS databases.
With dashboards can bring together operational data that is most important to IT across all your Azure resources, including telemetry from Azure Log Analytics.
Note: Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.
Reference:
https://docs.microsoft.com/en-us/azure/azure-monitor/visualize/tutorial-logs-dashboards