Exam 70-764 topic 1 question 49 discussion

Actual exam question from Microsoft's 70-764

Question #: 49
Topic #: 1

HOTSPOT -
You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.
TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in
TableA affect recently added data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.
Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.
You need to address the performance issues with each table.
In the table below, identify the action that will resolve the issues for each table.
NOTE:
Make only one selection in each column.
Hot Area:

Show Suggested Answer

Suggested Answer:

Table A: Auto_update statistics off
Table A does not change much. There is no need to update the statistics on this table.
Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON
You can set the database to update statistics asynchronously:

ALTER DATABASE YourDBName -
SET AUTO_UPDATE_STATISTICS_ASYNC ON
If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query
Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments
References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

by tomzus at Nov. 30, 2019, 8:03 p.m.

Comments

Submit Cancel

tomzus

Highly Voted 5 years, 7 months ago

I would argue setting incremental ON for Table A could be a better option as the scenario suggests the table still gets update but just in the most recent partition. Setting incremental on would ensure the active partition statistics get updated whilst the partitions that aren't changing\active would be ignored making the completion time for stats to update faster

upvoted 6 times

Hoglet

4 years, 5 months ago

I can't think of a good argument for not going ASYNC with TableA. While the cardinality estimator won't use the partition level statistics / histogram directly, there has to be some mechanism that updates the table level stats from the partition level.

upvoted 1 times

Hoglet

4 years, 5 months ago

I meant "incremental" in my comment above

upvoted 1 times

...

norbert1

Most Recent 4 years, 9 months ago

so my vote for table B is ASSYNC

upvoted 1 times

...

norbert1

4 years, 9 months ago

MelK might be right - but i thinkt Microsoft prefers using their Features instead of turning off - and changing the time of updating statistics will help - at the time high perf is needet - and its done when ressources are available..

upvoted 2 times

...

KC

4 years, 10 months ago

I agree with the other two comments. Incremental on Table A and auto_update to off. https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/

upvoted 3 times

...

MelKr

5 years, 1 month ago

On table B I would try to set auto_update statistics to OFF. Async wouldn't change the number of update statistic events just the time they occur. Performance could be better off when statistics are updated on schedule and not automtic.

upvoted 1 times

Hoglet

4 years, 5 months ago

It really depends if having state stats (for a day) for TableB is an issue or not. Assume the worst, going ASYNC is least problematic

upvoted 1 times

...