Exam 70-767 topic 1 question 6 discussion

Actual exam question from Microsoft's 70-767

Question #: 6
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows.
Users report performance degradation when they run the following stored procedure:

You need to optimize performance.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️
You can specify the sample size as a percent. A 5% statistics sample size would be helpful.
References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-statistics

by tubis at June 18, 2020, 9:23 a.m.

Comments

Submit Cancel

tubis

Highly Voted 5 years ago

For less than 1 billion it should be at least the default sampling (20 percent). So shouldn't it be No?

upvoted 8 times

eggzamtaker

4 years, 5 months ago

Correct, the answer is NO.

upvoted 1 times

...

Anette

Highly Voted 4 years, 10 months ago

Less than 1 billion rows, use default sampling (20 percent). With more than 1 billion rows, use sampling of two percent. Since we have less than 1 billion rows, it must be 20 percent, not 5 percent. I think the answer is NO.

upvoted 7 times

...

bhrv

Most Recent 4 years, 7 months ago

why this is yes, when the optimal would be 20% unless 1 bil ?

upvoted 2 times

...

setumo

4 years, 11 months ago

One best practice is to update statistics on date columns each day as new dates are added. Each time new rows are loaded into the SQL pool, new load dates or transaction dates are added. These additions change the data distribution and make the statistics out of date. Statistics on a country/region column in a customer table might never need to be updated since the distribution of values doesn't generally change. Assuming the distribution is constant between customers, adding new rows to the table variation isn't going to change the data distribution. However, if your SQL pool only contains one country/region, and you bring in data from a new country/region, resulting in data from multiple countries/regions being stored, then you need to update statistics on the country/region column. The following are recommendations updating statistics: UPDATE STATISTICS Frequency of stats updates Conservative: Daily After loading or transforming your data Sampling Less than 1 billion rows, use default sampling (20 percent). With more than 1 billion rows, use sampling of two percent.

upvoted 1 times

...

eceb

4 years, 11 months ago

in the reference link it says "The following are RECOMMENDATIONS updating statistics: Sampling Less than 1 billion rows, use default sampling (20 percent)". That's different that it should be neccesary at least 20%. So maybe 5% would be helpful.

upvoted 2 times

Tinashe16

4 years, 6 months ago

So what are you saying? Is it Yes or No? Seems your reference suggests NO but you’re saying the answer is YES. You’re confusing people.

upvoted 1 times

...

setumo

4 years, 11 months ago

it is No 5% is too few

upvoted 3 times

...