CompTIA
Checkpoint
ISC
HP
Dell
EC-Council
EXIN
Fortinet
ITIL®
Juniper
PMI
Microsoft
VMware
Avaya
Google
Salesforce
Amazon
Palo Alto Networks
Certiport
ISC2
All Vendors
  2. Home
  3. Exams
  4. Microsoft
  5. DP-300

Free DP-300 Exam Braindumps (page: 22)

Page 22 of 76
PDF with 352 Questions

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. Force the query execution plan for plan 1221065.
  2. Run the DBCC FREEPROCCACHE command.
  3. Force the query execution plan for plan 1220917.
  4. Disable parameter sniffing.

Answer(s): C


Reference:

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



HOTSPOT (Drag and Drop is not supported)
You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit.



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

NOTE: Each correct selection is worth one point.
Hot Area:

  1. See Explanation section for answer.

Answer(s): A

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.



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

  1. For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
  2. Run the SET SHOWPLAN_ALL Transact-SQL statement.
  3. Save the actual execution plan.
  4. Enable Query Store for DB1.

Answer(s): 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.



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?

  1. Performance Recommendations in the Azure portal
  2. Extended Events in Microsoft SQL Server Management Studio (SSMS)
  3. Query Store in Microsoft SQL Server Management Studio (SSMS)
  4. Query Performance Insight in the Azure portal

Answer(s): 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



Page 22 of 76



Post your Comments and Discuss Microsoft DP-300 exam with other Community members:

laks commented on December 26, 2024
so far seems good
UNITED STATES
upvote

Jack commented on October 24, 2024
Muito bom as perguntas
Anonymous
upvote

TheUser commented on October 23, 2024
So far seems good
Anonymous
upvote

anonymus commented on October 23, 2024
master database differential backup is not supported in sql server
EUROPEAN UNION
upvote

Ntombi commented on October 17, 2024
i find the questions helpful for my exam preparation
Anonymous
upvote

Ntombi commented on October 17, 2024
The questions help me to see if I understood what I have learned
Anonymous
upvote

ntombi commented on October 17, 2024
writing exam at the end of the month
Anonymous
upvote

Raby commented on August 13, 2024
Wonderful work guys. The PDF version helped me pass. Thank you
EUROPEAN UNION
upvote