DP-300 practice questions 211–220

1. Question 211 · 354 in bank

   HOTSPOT - You are building an Azure Stream Analytics job to retrieve game data. You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game. How should you complete the Stream Analytics query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: TopOne() OVER(PARTITION BY Game ORDER BY Score Desc) TopOne returns the top-rank record, where rank defines the ranking position of the event in the window according to the specified ordering. Ordering/ranking is based on event columns and can be specified in ORDER BY clause. Analytic Function Syntax: TopOne() OVER ([<PARTITION BY clause>] ORDER BY (<column name> [ASC |DESC])+ <LIMIT DURATION clause> [<WHEN clause>]) Box 2: Tumbling(minute 5) Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window. Reference: https://docs.microsoft.com/en-us/stream-analytics-query/topone-azure-stream-analytics https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md

   

   Next question

2. Question 212 · 354 in bank

   Select 2

   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.

   • AEnable Last_Query_Plan_Stats in the master database
   • BEnable Lightweight_Query_Profiling in DB1
   • CEnable Last_Query_Plan_Stats in DB1
   • DEnable Lightweight_Query_Profiling in the master database
   • EEnable PARAMETER_SNIFFING in DB1

   Show answer

   Answer: BC

   References

   • Microsoft Learn

   Next question

3. Question 213 · 354 in bank

   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?

   • Aan IDENTITY column
   • Ba GUID column
   • Ca sequence object

   Show answer

   Answer: A

   Explanation

   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

   Source: Adapted from community discussion — quermi

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

4. Question 214 · 354 in bank

   Select 2

   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.

   • ACreate a date dimension table that has a DateTime key.
   • BCreate a date dimension table that has an integer key in the format of YYYYMMDD.
   • CUse built-in SQL functions to extract date attributes.
   • DUse integer columns for the date fields.
   • EUse DateTime columns for the date fields.

   Show answer

   Answer: BD

   Explanation

   Why use a Date Dimension Table in a Data Warehouse. The Date dimension is one of these dimension tables related to the Fact. Here is a simple Data Diagram for a Data Mart of Internet Sales information for the Adventure Works DW database which can be obtained for free from CodePlex or other online sources. The relationship is created by the surrogate keys columns (integer data type) rather than the date data type. The query users have to write against a Data Mart are much simpler than against a transaction database. There are less joins because of the one to many relationships between the fact dimension table(s). The dimension tables are confusing to someone who has been normalizing databases as a career. The dimension is a flattened or de-normalized table. This creates cases of duplicate data, but the simplistic query overrides the duplicate data in a dimensional model. Reference: https://www.mssqltips.com/sqlservertip/3117/defining-role-playing-dimensions-for-sql-server-analysis-services/ https://community.idera.com/database-tools/blog/b/community_blog/posts/why-use-a-date-dimension-table-in-a-data-warehouse

   

   Next question

5. Question 215 · 354 in bank

   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. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   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

   

   Next question

6. Question 216 · 354 in bank

   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?

   • AFrom 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON.
   • BThe parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect.
   • CFrom 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect.
   • DThe pipeline was triggered too early.

   Show answer

   Answer: B

   Explanation

   A file is missing. Incorrect: Not A, not C, not D: Time of the error is 07:45.

   Next question

7. Question 217 · 354 in bank

   HOTSPOT - You have an Azure SQL database. You are reviewing a slow performing query as shown in the following exhibit.

   

   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. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Live Query Statistics - Live Query Statistics as it a percentage of the execution. Box 2: Key Lookup - The use of a Key Lookup operator in a query plan indicates that the query might benefit from performance tuning. For example, query performance might be improved by adding a covering index. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference

   Source: Adapted from community discussion — gp_engine

   References

   • Microsoft Learn

   

   Next question

8. Question 218 · 354 in bank

   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?

   • AGenerate an estimated execution plan.
   • BGenerate an actual execution plan.
   • CRun sys.dm_exec_query_plan_stats.
   • DGenerate Live Query Statistics.

   Show answer

   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

   References

   • Microsoft Learn

   Next question

9. Question 219 · 354 in bank

   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. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: INTERVAL_LENGTH_MINUTES - INTERVAL_LENGTH_MINUTES defines size of time window during which collected runtime statistics for query plans are aggregated and persisted. Every active query plan has at most one row for a period of time defined with this configuration. Default: 60 - Box 2: 1 - Statistics Collection Interval (INTERVAL_LENGTH_MINUTES): 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. Use SQL Server Management Studio or Transact-SQL to set a different value for Statistics Collection Interval: ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60); Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

   Source: Adapted from community discussion — quermi

   References

   • Microsoft Learn

   

   Next question

10. Question 220 · 354 in bank

    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?

    • ASQL Profiler
    • BExtended Events
    • CQuery Store
    • Ddynamic management views

    Show answer

    Answer: D

    Explanation

    I believe this is D: Dynamic Management Views, because you need to see the queries causing the performance issues right now, not the past. Whenever I have to handle a performance issue in real time, I'm grabbing the DMVs, not jumping into Query Store.

    Source: Adapted from community discussion — JJJR

    References

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 211. 354 questions available.