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