Memory optimized table variable and cardinality estimate

 

In a previous blog, I talked about memory optimized table consumes memory until end of the batch.   In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications.  By default memory optimized table variable behaves the same way as disk based table variable. It will have 1 row as an estimate.   In disk based table variable, you can control estimate by using option (recompile) at statement level (see this blog) or use trace flag 2453. 

You can control the same behavior using the two approaches on memory optimized table variable if you use it in an ad hoc query or inside a regular TSQL stored procedure.  The behavior will be the same. This little repro will show the estimate is correct with option (recompile).

create database IMOLTP
go
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA 
go
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\sqldata\imoltp_mod2') TO FILEGROUP imoltp_mod 
go

use IMOLTP
go

CREATE TYPE dbo.test_memory AS TABLE
(c1 INT NOT NULL INDEX ix_c1,
c2 CHAR(10))
WITH (MEMORY_OPTIMIZED=ON);

go

DECLARE @tv dbo.test_memory
set nocount on

declare @i int
set @i = 1
while @i < 10000
begin
    insert into @tv values (@i, 'n')
    set @i = @i + 1
end
set statistics xml on
--this will work and the etimate will be correct
select * from @tv t1 join @tv t2 on t1.c1=t2.c1 option (recompile, querytraceon 2453)
set statistics xml off
go

 

 

But the problem occurs when you use it inside a natively compiled stored procedure.  In this case, it will always estimate 1 row.  You can’t change it because natively compiled procedure doesn’t allow statement level recompile.  If you try to create a natively compiled procedure, you will get errors that disallow you to create the procedure.

create procedure test
with native_compilation, schemabinding 
as  
begin atomic with 
(transaction isolation level = snapshot, 
language = N'English') 

DECLARE @tv dbo.test_memory
declare @i int
set @i = 1
while @i < 10000
begin
    insert into @tv values (@i, 'n')
    set @i = @i + 1
end
--you can't add TF 3453 or recompile
select t1.c1, t2.c1 from @tv t1 join @tv t2 on t1.c1=t2.c1 option (recompile, querytraceon 2453)
end 
go

Msg 10794, Level 16, State 45, Procedure test, Line 17 [Batch Start Line 39]
The query hint 'RECOMPILE' is not supported with natively compiled modules.
Msg 10794, Level 16, State 45, Procedure test, Line 17 [Batch Start Line 39]
The query hint 'QUERYTRACEON' is not supported with natively compiled modules.

 

So that is the solution?   For natively compiled procedure, here are some advices

1. limit number of rows inserted into the memory optimized table variable.

2. if you are joining with memory optimized table variable that has lots of rows, consider use a schema_only memory optimized table

3. if  you know your data, you can potentially re-arrange the join and use option (force order) to get around it.

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  May 12, 2017
  For 2016 isn't trace flag 2453 behaviour on by default?
• Anonymous
  May 15, 2017
  The comment has been removed