Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 141:

  You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

  You scale up the virtual machine to 8 vCPUSs and 64 GB of memory.

  You need to provide the lowest latency for tempdb.

  What is the total number of data files that tempdb should contain?

  A. 2

  B. 4

  C. 8

  D. 64

  Correct Answer: C

  The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

• Question 142:

  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?

  A. Double the value of Data Flush interval

  B. Decrease by half the value of Data Flush Interval

  C. Double the value of Statistics Collection Interval

  D. Decrease by half the value of Statistics Collection interval

  Correct Answer: B

  The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode.

  Incorrect Answers:

  C: Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

• Question 143:

  You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

  You scale up the virtual machine to 16 vCPUSs and 64 GB of memory.

  You need to provide the lowest latency for tempdb.

  What is the total number of data files that tempdb should contain?

  A. 2

  B. 4

  C. 8

  D. 64

  Correct Answer: C

  If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code. https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

• Question 144:

  You have an Azure SQL database named sqldb1.

  You need to minimize the amount of space by the data and log files of sqldb1.

  What should you run?

  A. DBCC SHRINKDATABASE

  B. sp_clean_db_free_space

  C. sp_clean_db_file_free_space

  D. DBCC SHRINKFILE

  Correct Answer: A

  DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database. Incorrect Answers:

  D: To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql

• Question 145:

  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?

  A. Run Query Store-based queries

  B. Review information provided by SQL Server Profiler-based traces

  C. Review information provided by Query Performance Insight

  D. Run dynamic management view-based queries

  Correct Answer: D

  Microsoft Azure SQL Database and Azure SQL Managed Instance enable a subset of dynamic management views to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor

  query plans, and so on.

  This include edentifying tempdb performance issues:

  When identifying IO performance issues, the top wait types associated with tempdb issues is PAGELATCH_* (not PAGEIOLATCH_*).

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs

• Question 146:

  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?

  A. From 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON.

  B. The parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect.

  C. From 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect.

  D. The pipeline was triggered too early.

  Correct Answer: A

• Question 147:

  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.

  A. Set Threshold Sensitivity to High

  B. Set the Alert logic threshold to Dynamic

  C. Set the Alert logic threshold to Static

  D. Set Threshold Sensitivity to Low

  E. Set Force Plan to On

  Correct Answer: BD

  B: Dynamic Thresholds continuously learns the data of the metric series and tries to model it using a set of algorithms and methods. It detects patterns in the data such as seasonality (Hourly / Daily / Weekly), and is able to handle noisy

  metrics (such as machine CPU or memory) as well as metrics with low dispersion (such as availability and error rate).

  D: Alert threshold sensitivity is a high-level concept that controls the amount of deviation from metric behavior required to trigger an alert.

  Low - The thresholds will be loose with more distance from metric series pattern. An alert rule will only trigger on large deviations, resulting in fewer alerts.

  Incorrect Answers:

  A: High - The thresholds will be tight and close to the metric series pattern. An alert rule will be triggered on the smallest deviation, resulting in more alerts.

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresholds

• Question 148:

  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.

  A. Create a date dimension table that has a DateTime key.

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

  C. Use built-in SQL functions to extract date attributes.

  D. Use integer columns for the date fields.

  E. Use DateTime columns for the date fields.

  Correct Answer: BD

  Reference:

  https://community.idera.com/database-tools/blog/b/community_blog/posts/why-use-a-date-dimension- table-in-a-data-warehouse

• Question 149:

  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.

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

  B. Queries that use the index will retrieve fewer data pages.

  C. The index will consume more disk space.

  D. The index will consume more memory.

  E. Queries that use the index will consume more CPU resources.

  Correct Answer: BE

  For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.

  Use columnstore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data.

• Question 150:

  You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.

  You need to create a surrogate key for the table. The solution must provide the fastest query performance.

  What should you use for the surrogate key?

  A. an IDENTITY column

  B. a GUID column

  C. a sequence object

  Correct Answer: A

  Dedicated SQL pool supports many, but not all, of the table features offered by other databases. Surrogate keys are not supported. Implement it with an Identity column.

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