Exam DP-200 All Questions

View all questions & answers for the DP-200 exam

Go to Exam

Exam DP-200 topic 11 question 2 discussion

Actual exam question from Microsoft's DP-200

Question #: 2
Topic #: 11

[All DP-200 Questions]

What should you implement to optimize SQL Database for Race Central to meet the technical requirements?

A. the sp_update_stats stored procedure
B. automatic tuning
C. Query Store
D. the dbcc checkdb command

Show Suggested Answer

Suggested Answer: A 🗳️
Scenario: The query performance of Race Central must be stable, and the administrative time it takes to perform optimizations must be minimized. sp_updatestats updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
Incorrect Answers:
D: dbcc checkdchecks the logical and physical integrity of all the objects in the specified database
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updatestats-transact-sql?view=sql-server-ver15

by STH at Nov. 22, 2019, 10:21 p.m.

Comments

Submit Cancel

STH

Highly Voted 5 years, 6 months ago

answer B : automatic tunning

upvoted 31 times

emmanuelcs

4 years, 8 months ago

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

upvoted 1 times

...

ThijsN

4 years, 4 months ago

don't think automatic tuning is available for Azure Sql SWH (which is required)

upvoted 1 times

ThijsN

4 years, 4 months ago

Nevermind. The other application is in sql dwh, this one is sql db. Than I agree.

upvoted 1 times

...

memo43

4 years ago

i saw this question another dump and the answer was "automatic tunning" too

upvoted 1 times

...

samok

Highly Voted 5 years, 3 months ago

I believe this is a typo and it meant sp_updatestats. In that case A is correct. There is no sp_update procedure

upvoted 15 times

...

syu31svc

Most Recent 4 years, 6 months ago

Azure SQL Database includes database advisors that provide performance tuning recommendations for single and pooled databases. These recommendations are available in the Azure portal as well as by using PowerShell. You can also enable automatic tuning so that Azure SQL Database can automatically implement these tuning recommendations. B it is.

upvoted 2 times

dumpsm42

4 years, 6 months ago

agree. the text says it all "...The query performance of Race Central must be stable, and the administrative time it takes to perform optimizations must be minimized..." to be stable means that we must have an automatic process and not a manual one when needed => B regards

upvoted 3 times

...

Nieswurz

4 years, 10 months ago

The question leaves unclear, over what dimension the query time should be stable: 1) Same query over time, 2) same query with different parameters ("carId"), or 3) initial run or repeated queries (cache). Happy guessing the intention of the one who asks!

upvoted 1 times

...

wyxh

5 years ago

with automatic tuning , depending ot the workload, all the hints are deployed or in a waiting approval state. then manually you approve and deploy it. so the optimization is done with a minimal manual process. and only updating stats is not enough to optimize , you need to check indexes.

upvoted 2 times

...

cdume

5 years, 3 months ago

sp_updatestats is not the same with sp_update, isn't it ? I think the correct answer here is: B: automatic tunning

upvoted 3 times

...

Abbas

5 years, 5 months ago

Automatic tuning is one option but here it is talking about the administrative time should be minimal, which means manual process and hence sp_updatestats.

upvoted 3 times

...