azure-sqldw - Azure SQL DW如何在没有统计信息的情况下知道行数？

Question

如果我运行CREATE EXTERNAL TABLE cetasTable AS SELECT命令，请运行：

EXPLAIN
select * from cetasTable

我在分布式查询计划中看到：

<operation_cost cost="4231.099968" accumulative_cost="4231.099968" average_rowsize="2056" output_rows="428735" />

似乎知道正确的行数，但是，如果我看该表没有创建任何统计信息，因为此查询返回零行：

select * from sys.stats where object_id = object_id('cetasTable')

如果我已经在blob存储中有文件，并且运行了CREATE EXTERNAL TABLE cetTable命令，请运行：

EXPLAIN
select * from cetTable 

分布式查询计划显示SQL DW认为外部表中只有1000行：

  <operation_cost cost="4.512" accumulative_cost="4.512" average_rowsize="940" output_rows="1000" />

当然，我可以创建统计信息以确保SQL DW在创建分布式查询计划时知道正确的行数。但是有人可以解释一下它如何在某些时候知道正确的行数以及正确的行数存储在哪里吗？

Answer 1

您将看到使用CxTAS创建的表（CTAS，CETAS或CRTAS）与CREATE TABLE之间的区别。

当您运行CREATE TABLE时，行数和页数的值是固定的，因为表为空。如果使用内存，则固定值为1000行和100页。使用CTAS创建表时，它们是不固定的。实际值对于CTAS命令是已知的，因为它只是在单个命令中创建并填充了表。因此，使用CxTAS时，元数据正确反映了表SIZE。很好当通过CxTAS创建了一个表而不是CREATE表时，基于APS / SQLDW成本的优化器可以立即根据表SIZE为MPP计划生成做出更好的估算。

准确了解表大小很重要。

假设您有一个使用CREATE TABLE创建的表，然后使用INSERT将10亿行插入到该表中。 Shell数据库仍然认为该表具有1000行和100页。但是，事实显然并非如此。原因是因为此时表大小属性不会自动更新。

现在假设触发了一个查询，该查询需要在此表上移动数据。事情可能开始变糟了。现在，您更可能会看到引擎对MPP计划的选择不正确（通常使用BROADCAST而不是SHUFFLE），因为它不了解表格大小。

您可以采取什么措施来改善此状况？

每个表至少创建一个列级统计对象。一般来说，您将在查询中JOINS，GROUP BY，WHERE和ORDER BY中使用的所有列上创建统计对象。我将在稍后解释生成统计信息的基本过程。我只想强调一下，这里的号召性用语是确保您创建和维护统计对象。

当对一列执行CREATE STATISTICS时，实际上会发生三个事件。

1）在CONTROL节点上更新表级别信息

2）在COMPUTE节点上的每个分布上创建列级统计对象

3）在CONTROL节点上创建并更新列级统计对象

1）在CONTROL节点上更新表级别信息

第一步是更新表级别信息。为此，APS / SQLDW对每个物理分发执行带有STAT_STREAM的DBCC SHOW_STATISTICS（table_name）；合并结果并将其存储在Shell数据库的目录元数据中。行数保存在sys.partitions中，页数保存在sys.allocation_units中。 Sys.partitions在SQLDW和APS中对您都是可见的。但是，sys.allocation_units目前对最终用户不可见。我为熟悉SQL Server内部知识的人员参考了该位置，以获取信息和上下文。

在此阶段结束时，CONTROL节点上的外壳数据库中保存的元数据已针对行数和页数进行了更新。现在，由CREATE TABLE创建的表和CTAS之间没有区别-两者都知道大小。

2）在COMPUTE节点上的每个分布上创建列级统计对象

必须在每个COMPUTE节点上的每个分发中创建统计信息对象。通过创建重要的统计对象，已创建了该列的详细统计数据（尤其是直方图和密度向量）。

APS和SQLDW使用此信息来生成分发级别的SMP计划。 SMP计划仅由APS / SQLDW在物理层使用。因此，此时统计数据不在可用于生成MPP计划的位置。信息已分发且无法及时访问，以进行基于成本的优化。因此，第三步是必要的...

3）在CONTROL节点上创建并更新列级统计对象

在COMPUTE层中的分布上以物理方式创建数据后，必须将其汇总在一起并以逻辑方式保存，以促进基于MPP计划成本的优化。 CONTROL节点上的Shell数据库还创建了一个统计对象。这是统计对象的逻辑表示。

但是，shell数据库stat尚未反映在COMPUTE节点上的分布中物理保留的列级统计信息。因此，在创建CONTROL节点后，外壳数据库中的统计对象需要立即更新。

具有STAT_STREAM的DBCC SHOW_STATISTICS（表名，状态名）用于执行此操作。

请注意，该命令具有第二个参数。这将更改结果集；向APS / SQLDW提供构建该列统计对象的LOGICAL视图所需的所有信息。

我希望这可以解释您所看到的内容，还可以解释如何创建统计信息以及为什么统计信息对于Azure SQL DW和APS非常重要。