Sql-server – Execution plan for staging In-Memory tables estimate row count =1

execution-planmemory-optimized-tablessql serversql-server-2017statistics

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

Best Answer

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.

Related Solutions

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Sql-server – Incremental statistics: find out which partitions have been resampled

My first question would be to ask why you're actually using incremental in the first place. Here's an answer that I posted regarding incremental statistics, a blog post by Erin Stellato that illuminates one of the primary complaints and pitfalls with incremental statistics (they're not used at the partition level by the optimizer), and two blog posts by me that work through evaluating any potential use case for incremental statistics.

Having said that, to know when a partition's stats have been sampled you can use an undocumented DMF (sys.dm_db_stats_properties_internal()) to get partition-level information. I have a comment on this blog post that describes how to understand the hierarchy at a fairly high level.

select 
    sysdatetime(),                          
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    parition_number = isnull(sp.partition_number,1),
    s.has_filter,                       
    s.is_incremental,
    s.auto_created,
    sp.last_updated,    
    sp.rows,
    sp.rows_sampled,                        
    sp.unfiltered_rows,
    modification_counter = coalesce(sp.modification_counter, n1.modification_counter) 
from sys.stats s 
join sys.tables t 
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i 
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = '<<TABLENAME>>'
    and s.name like '<<STATNAME>>%'
order by s.stats_id,isnull(sp.partition_number,1);

Best Answer

Related Solutions

Sql-server – deteriorating stored procedure running times

Sql-server – Incremental statistics: find out which partitions have been resampled

Related Question