ExamGecko
Home / Microsoft / DP-300 / List of questions
Ask Question

Microsoft DP-300 Practice Test - Questions Answers, Page 3

List of questions

Question 21

Report
Export
Collapse

You have an Azure SQL database.

You discover that the plan cache is full of compiled plans that were used only once.

You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

Microsoft DP-300 image Question 6 89904 10022024015907000000

You need relieve the memory pressure.

What should you configure?

LEGACY_CARDINALITY_ESTIMATION
LEGACY_CARDINALITY_ESTIMATION
QUERY_OPTIMIZER_HOTFIXES
QUERY_OPTIMIZER_HOTFIXES
OPTIMIZE_FOR_AD_HOC_WORKLOADS
OPTIMIZE_FOR_AD_HOC_WORKLOADS
ACCELERATED_PLAN_FORCING
ACCELERATED_PLAN_FORCING
Suggested answer: C

Explanation:

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. The default is OFF. Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint compared to the size of the full compiled plan.

Incorrect Answers:

A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.

B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

asked 02/10/2024
Nick Daniel
37 questions

Question 22

Report
Export
Collapse

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:

Microsoft DP-300 image Question 7 89905 10022024015907000000

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 1221065.
Force the query execution plan for plan 1221065.
Run the DBCC FREEPROCCACHE command.
Run the DBCC FREEPROCCACHE command.
Force the query execution plan for plan 1220917.
Force the query execution plan for plan 1220917.
Disable parameter sniffing.
Disable parameter sniffing.
Suggested answer: C

Explanation:

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

asked 02/10/2024
Anthony Steele
42 questions

Question 23

Report
Export
Collapse

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)?

For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
Run the SET SHOWPLAN_ALL Transact-SQL statement.
Run the SET SHOWPLAN_ALL Transact-SQL statement.
Save the actual execution plan.
Save the actual execution plan.
Enable Query Store for DB1.
Enable Query Store for DB1.
Suggested answer: C

Explanation:

The Plan Comparison menu option allows side-by-side comparison of two different execution plans, for easier identification of similarities and changes that explain the different behaviors for all the reasons stated above. This option can compare between:

Two previously saved execution plan files (.sqlplan extension).

One active execution plan and one previously saved query execution plan. Two selected query plans in Query Store.

asked 02/10/2024
DATA 7 DATA7
41 questions

Question 24

Report
Export
Collapse

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?

Performance Recommendations in the Azure portal
Performance Recommendations in the Azure portal
Extended Events in Microsoft SQL Server Management Studio (SSMS)
Extended Events in Microsoft SQL Server Management Studio (SSMS)
Query Store in Microsoft SQL Server Management Studio (SSMS)
Query Store in Microsoft SQL Server Management Studio (SSMS)
Query Performance Insight in the Azure portal
Query Performance Insight in the Azure portal
Suggested answer: C

Explanation:

Use the Query Store Page in SQL Server Management Studio.

Query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve. Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. This is referred to as plan forcing. Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

asked 02/10/2024
Aparna Roy
40 questions

Question 25

Report
Export
Collapse

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 consume more disk I/O.
Queries that use the index will consume more disk I/O.
Queries that use the index will retrieve fewer data pages.
Queries that use the index will retrieve fewer data pages.
The index will consume more disk space.
The index will consume more disk space.
The index will consume more memory.
The index will consume more memory.
Queries that use the index will consume more CPU resources.
Queries that use the index will consume more CPU resources.
Suggested answer: B, E

Explanation:

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.

asked 02/10/2024
rene laas
49 questions

Question 26

Report
Export
Collapse

HOTSPOT

You have SQL Server on an Azure virtual machine.

You review the query plan shown in the following exhibit.

Microsoft DP-300 image Question 11 89909 10022024015907000000

For each of the following statements, select yes if the statement is true. Otherwise, select no.

NOTE: Each correct selection is worth one point.


Microsoft DP-300 image Question 26 89909 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 26 89909 10022024015907000

Explanation:

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

asked 02/10/2024
pedro blanco
27 questions

Question 27

Report
Export
Collapse

HOTSPOT

You have SQL Server on an Azure virtual machine that contains a database named Db1.

You need to enable automatic tuning for Db1.

How should you complete the statements? To answer, select the appropriate answer in the answer area.

NOTE: Each correct selection is worth one point.


Microsoft DP-300 image Question 27 89910 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 27 89910 10022024015907000

Explanation:

Box 1: SET AUTOMATIC_TUNING = AUTO

To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO

Setting automatic tuning to AUTO will apply Azure Defaults.

Box 2: SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one:

ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option. Setting it to OFF will also override any setting that database inherited and disable the tuning option.

Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable

asked 02/10/2024
Sanaa CHOKIRI
45 questions

Question 28

Report
Export
Collapse

HOTSPOT

You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit.

Microsoft DP-300 image Question 13 89911 10022024015907000000

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.


Microsoft DP-300 image Question 28 89911 10022024015907000
Correct answer: Microsoft DP-300 image answer Question 28 89911 10022024015907000

Explanation:

Box 1: Yes

We see: Tuning option: Create index ON

CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.

Box 2: No

Box 3: Yes

FORCE LAST GOOD PLAN (automatic plan correction) - Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.

asked 02/10/2024
Matteo Di Pomponio
41 questions

Question 29

Report
Export
Collapse

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?

an IDENTITY column
an IDENTITY column
a GUID column
a GUID column
a sequence object
a sequence object
Suggested 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

asked 02/10/2024
CCF AG Alexander Seidler
35 questions

Question 30

Report
Export
Collapse

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 a DateTime key.
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.
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 built-in SQL functions to extract date attributes.
Use integer columns for the date fields.
Use integer columns for the date fields.
Use DateTime columns for the date fields.
Use DateTime columns for the date fields.
Suggested answer: B, D

Explanation:

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

asked 02/10/2024
lagwendon Scott
35 questions
Total 342 questions
Go to page: of 35
Search

Related questions