Updating Query-Optimization Statistics with Synapse SQL Resources in Azure

Not True: Guiding Principles for Statistics Update during Load Process

Prev Question Next Question

Question

You work in an Azure Transformational Logics (ATL) company and you have been given the responsibility to create and update query-optimization statistics utilizing the Synapse SQL resources in a dedicated SQL pool.The following are the guiding principles recommended for updating the statistics during the load process.

Which of the following is/are not true?

Answers

A. Ensure that every loaded table is having at least 1 statistics object updated.

B. Focus on the columns participating in ORDER BY, GROUP BY, JOIN and DISTINCT clauses.

C. Update "ascending key" columns like order dates more frequently as these values are not considered/included in the statistics histogram.

D. Update static distribution columns more frequently.

E. None of these.

Show Answer

Explanations

Click on the arrows to vote for the correct answer

A. B. C. D. E.

Correct Answer: D

The below-given guiding principles are recommended to update the statistics during the load process:

Ensure that each loaded table has at least one statistics object updated. This process updates the table size (row count and
page count) information as part of the statistics update.

Focus on columns participating in JOIN, GROUP BY, ORDER BY, and DISTINCT clauses.

Consider updating “ascending key" columns such as transaction dates more frequently because these values won't be included

in the statistics histogram.
Consider updating static distribution columns less frequently.
Remember, each statistic object is updated in sequence. Simply implementing UPDATE STATISTICS <TABLE_NANE> isn’t always

ideal, especially for wide tables with lots of statistics objects.

Option A is incorrect.

It is true that you should ensure that every loaded table is having at least 1 statistics object updated.

Option B is incorrect.The given one is also a guiding principle to update the statistics during the load process.

Option C is incorrect.

The given one is also a guiding principle to update the statistics during the load process.

Option D is correct.

Rather than more frequently, you need to update static distribution columns less frequently.

Option E is incorrect.

Option D describes the wrong principle.

To know more about Statistics in Synapse SQL, please visit the below-given link:

As per the guiding principles for updating query-optimization statistics utilizing the Synapse SQL resources in a dedicated SQL pool, the following points are recommended:

A. Ensure that every loaded table is having at least 1 statistics object updated: This statement is true. It is recommended to update statistics for every table that is loaded, as statistics play a vital role in query optimization. Updating statistics can help the query optimizer create better execution plans, leading to improved query performance.

B. Focus on the columns participating in ORDER BY, GROUP BY, JOIN and DISTINCT clauses: This statement is true. It is recommended to focus on the columns that are used in the ORDER BY, GROUP BY, JOIN, and DISTINCT clauses as these are the columns that are heavily used in query optimization.

C. Update "ascending key" columns like order dates more frequently as these values are not considered/included in the statistics histogram: This statement is false. Ascending key columns, such as order dates, are included in the statistics histogram. Therefore, there is no need to update these columns more frequently.

D. Update static distribution columns more frequently: This statement is false. Static distribution columns do not change frequently and therefore do not require frequent updates to the statistics. It is recommended to update the statistics of columns that have a high degree of data volatility, i.e., columns that frequently change, as these statistics help the query optimizer make better decisions about query execution plans.

Therefore, the answer is D. "Update static distribution columns more frequently" is not a true statement as per the recommended principles for updating query-optimization statistics utilizing the Synapse SQL resources in a dedicated SQL pool.

Prev Question Next Question