Exam DP-200 All Questions

View all questions & answers for the DP-200 exam

Exam DP-200 topic 4 question 20 discussion

Actual exam question from Microsoft's DP-200

Question #: 20
Topic #: 4

HOTSPOT -
You are implementing automatic tuning mode for Azure SQL databases.
Automatic tuning mode is configured as shown in the following table.

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:

Show Suggested Answer

Suggested Answer:

Automatic tuning options can be independently enabled or disabled per database, or they can be configured on SQL Database servers and applied on every database that inherits settings from the server. SQL Database servers can inherit Azure defaults for Automatic tuning settings. Azure defaults at this time are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning

by mvivarelli at March 15, 2020, 11:29 a.m.

Comments

Submit Cancel

mvivarelli

Highly Voted 5 years, 3 months ago

Since March 2020, defaults have changed. Force plan is ON, Create Index is OFF, Drop index is OFF

upvoted 93 times

cowtown

4 years, 12 months ago

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview#automatic-tuning-options

upvoted 2 times

...

2008hsk

Highly Voted 5 years, 3 months ago

As of March, 2020 changes to Azure defaults for automatic tuning will take effect as follows: New Azure defaults will be FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled.

upvoted 20 times

...

deepz8

Most Recent 4 years, 3 months ago

Answer: Y,N,N. Check out the link provided in the solution itself.

upvoted 1 times

...

dev2dev

4 years, 4 months ago

Haha. I was wondering how can sql create objects. Yeah may be they did it earlier but it might have outraged some people

upvoted 1 times

...

[Removed]

4 years, 6 months ago

Did anyone check it? When set to "inherit from azure defaults" on database level it sets ON, ON, OFF. But when I set it on server level is sets ON, OFF, OFF. So it seems new defaults are for server not database. Can anyone confirm that?

upvoted 2 times

KRV

4 years, 5 months ago

yes you're right Rdk , https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview , starting March 2020 the new defaults are in effect and hence the answer choices privided here should be incorrect and the correct ones are ON OFF OFF as you mentioned and in terms of the answer choices translates to "YES" , "NO", "NO"

upvoted 2 times

...

MMani

4 years, 7 months ago

yes - Since March 2020, defaults have changed. Force plan is ON, Create Index is OFF, Drop index is OFF.. Answer is Y,N,N

upvoted 2 times

...

Kuna_P

4 years, 11 months ago

From 31 March 2020, defaults have been changed FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled

upvoted 1 times

hello2090

4 years, 9 months ago

This setup is only for SQL instance right? For SQL DB, everythin is ON as per the document https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning Can someone clarify? Thanks.

upvoted 2 times

D_Duke

4 years, 9 months ago

No, it's for Azure SQL Database. For Azure Managed Instance, only FORCE_LAST_GOOD_PLAN is supported as per your referenced link.

upvoted 1 times

...

rajneesharora

4 years, 2 months ago

Yes, that seems to be correct as per documentation: For SQL Managed Instance: Yes, No, No For Azure SQL DB: Yes, Yes, Yes

upvoted 1 times

...

krisspark

5 years ago

Yes, answer should be changed.. only FORCE PLAN=YES, CREATE INDX = NO, DROP INDX=NO

upvoted 3 times

...

charlesCC

5 years, 2 months ago

upvoted 8 times

...

azurearch

5 years, 3 months ago

question is not what is the defaults, it is about what settings can be changed at db level and server level

upvoted 1 times

diulin

5 years ago

the question *do* asks about defaults! The `INHERIT` option for Server means it gets automatic tuning from Azure defaults and the latter has been changed after March 2020. So the answer should be corrected.

upvoted 2 times

avip

4 years, 9 months ago

Agree the answers should be corrected. But has MS communicated the change to the testing party? If not then the old setting will be treated as correct answers

upvoted 1 times

...