How does Azure SQL DW know the number of rows without statistics?

Question

If I run the CREATE EXTERNAL TABLE cetasTable AS SELECT command, run:

EXPLAIN
select * from cetasTable

I see in terms of distributed queries:

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

It seems that it knows the correct row counter, however, if you look, there is no statistics created in this table, since this query returns zero rows:

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

If I already have the files in the blob repository and I run the CREATE EXTERNAL TABLE cetTable command, run:

EXPLAIN
select * from cetTable 

The distributed query plan shows that SQL DW considers that there are only 1,000 rows in the external table:

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

Of course, I can create statistics to ensure that SQL DW knows the correct row count when creating a distributed query plan. But can someone explain how he knows the correct number of lines for a while and where the correct line counter is stored?

Answer 1

, , - , CxTAS (CTAS, CETAS CRTAS) CREATE TABLE.

CREATE TABLE , . , 1000 100 . CTAS, . CTAS, . , SIZE, CxTAS. . , APS/SQLDW, MPP SIZE, CxTAS, CREATE.

.

, , CREATE TABLE, 1 INSERT . , 1000 100 . . , .

, , . . , , , MPP ( BROADCAST, SHUFFLE), .

, ?

. , , JOINS, GROUP BY, WHERE ORDER BY . - . , , .

CREATE STATISTICS , .

1) CONTROL node

2) COMPUTE

3) CONTROL node

1) CONTROL node

. APS/SQLDW DBCC SHOW_STATISTICS (table_name) STAT_STREAM ; . sys.partitions count sys.allocation_units. Sys.partitions SQLDW, APS. sys.allocation_units . , SQL Server, .

, CONTROL node, , . , CREATE TABLE CTAS - .

2) COMPUTE

COMPUTE node. , ( , ) .

APS SQLDW SMP . SMP APS/SQLDW . , MPP. . ...

3) CONTROL node

COMPUTE, LOGICALLY MPP. CONTROL node . .

, , , COMPUTE. , CONTROL node .

DBCC SHOW_STATISTICS (table_name, stat_name) STAT_STREAM.

, . ; APS/SQLDW , .

, , , Azure SQL DW APS.