Serverless SQL Pool Statistics: True or False? | DP-203 Exam Answer
Question
As the queries are submitted, a dedicated SQL pool query optimizer attempts to check which Data access paths will result in the minimum amount of effort to get the data needed to resolve the query.
This cost-based optimizer will compare the cost of different query plans, and will then select the plan that has the minimum cost.
Statistics in a serverless SQL pool have the same aim of utilizing a cost-based optimizer to select a plan with the fastest execution.
The thing is that how it generates the statistics is different.
Statement: In a serverless SQL pool, if statistics are missing, the query optimizer creates statistics on the whole tables in the query predicate or join condition to enhance the cardinality estimates for the query plan.
Select whether the statement is true or false.
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B.Correct Answer: B
As per the Azure documentation:
/answer/imgdp-19.png)
To know more about Statistics in Synapse SQL, please visit the below-given link:
The statement is true.
In a serverless SQL pool, when a query is submitted, the query optimizer attempts to determine the best way to retrieve the required data. To do this, the query optimizer needs to estimate the cardinality of the data involved in the query, which is the number of distinct values in a column or set of columns.
Cardinality estimates are important because they help the query optimizer to determine which query plan to use. A query plan is a sequence of operations that the query optimizer will use to retrieve the data needed for the query. The goal of the query optimizer is to choose the query plan that requires the least amount of work.
To estimate the cardinality of the data, the query optimizer uses statistics about the data. Statistics are metadata about the data that describe the distribution of values in one or more columns. The query optimizer uses statistics to estimate the number of rows that will be returned by a query, which is called the row count.
In a serverless SQL pool, if statistics are missing, the query optimizer creates statistics on the whole tables in the query predicate or join condition to enhance the cardinality estimates for the query plan. This is done using a random sample of the data from the table. The sample size is determined by the size of the table and the complexity of the query. The query optimizer then uses these statistics to estimate the cardinality of the data and choose the best query plan.
In summary, the statement is true. In a serverless SQL pool, if statistics are missing, the query optimizer creates statistics on the whole tables in the query predicate or join condition to enhance the cardinality estimates for the query plan.