0

We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process. the nature of these staging tables is empty most of the time. The ETL process executed per organization asynchronicity.

Problem: The execution plan is generated with estimate row number = 1 (probably the table was empty for first execution)

Manually update statistics will not fix the issue since: the table may be empty or the no record for an organization that ETL will execute first.

I add OPTION recompile to query but still it not stable

I'm looking for correct pattern working with in-memory tables as the Staging table

MS SQL 2017 CU 13 Standard edition

In-Memory table schema:

CREATE TABLE [dbo].[process_aggregation_close] ( [organization_id] [bigint] NOT NULL, ...... INDEX [ix_process_aggregation_memory_computer_id_process_id_test] NONCLUSTERED ( [organization_id] ASC, [computer_id] ASC, [process_id] ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

Destination Clustered Column Store (disk base)

CREATE TABLE [dbo].[process_aggregation_5m]( [organization_id] [bigint] NOT NULL, ....... ) ON [ps_process_aggregation_close]([partkey]) GO CREATE CLUSTERED COLUMNSTORE INDEX [IX_process_aggregation_5m] ON [dbo].[process_aggregation_5m] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [ps_process_aggregation_close]([partkey])

Thank you Yuri

3

Are you using memory-optimized tables, or memory-optimized table variables? Memory-optimized table variables suffer from the same issues as "regular" table variables: the cardinality is assumed to be very low.

The other potential issue with using memory-optimized tables as a destination for staging ETL, is that all writes to memory-optimized tables are serial. So you will have to "roll your own" parallelism.

  • The parallel load is not an issue for now. – Yorik Mar 28 at 13:59
  • Are you using memory-optimized tables, or memory-optimized table variables? – NedOtter Mar 28 at 14:01
  • We use memory-optimized tables But we have a sutuation that ETL will be executed when statistics were recomputed during empty table. in this case out ETL will be stuck – Yorik Mar 28 at 14:01
  • Are you using native compilation? – NedOtter Mar 28 at 14:02
  • 1
    I'm assuming SELECT INTO is for a "traditional" table, using the memory-optimized table as the source, because SELECT INTO is not supported for memory-optimized tables. Can you post the schema and plans somewhere I can have a look? – NedOtter Mar 28 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.