Hekaton Part 11 :- Finding row count in memory table

Finding a row count in a "in memory" Hekaton table can be a tricky task as the regular commands and DMVs don't give the desired result.

• sp_spaceused - doesn't work and returns a blank result
• sys.dm_db_partition_stats - doesn't track in memory tables.

So the  option we are left with is
    
      SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS  
     [Schema Name],

      OBJECT_NAME(Ind.OBJECT_ID) As [Table Name],

      ISNULL([rows],0) as [Total Records]

      FROM sys.dm_db_xtp_index_stats AS Ind

      CROSS APPLY sys.dm_db_stats_properties

      (Ind.object_id,Ind.index_id)

      WHERE Ind.index_id =2

      ORDER BY OBJECT_NAME(Ind.OBJECT_ID)

 

However, please note that the above query will give correct row counts if and only if statistics are updated for the table. For in memory tables, statistics are not automatically updated manually and hence will have to be updated  before one attempts to find the row count. So, please refer to the query below, to update the statistics for in memory table alone.

 

 

DECLARE @id int,@rowcnt int

DECLARE @in_mem_tbl_name varchar(500)

SET @id = 1

 

CREATE TABLE #in_memory_tbl(id int identity(1,1),in_mem_tbl_name varchar(500))

 

INSERT INTO #in_memory_tbl(in_mem_tbl_name)

SELECT object_name(object_id) FROM sys.dm_db_xtp_table_memory_stats

WHERE object_id > 0

SET @rowcnt = @@ROWCOUNT

WHILE @id <= @rowcnt

BEGIN

 

 

SELECT @in_mem_tbl_name = in_mem_tbl_name

FROM #in_memory_tbl

WHERE id = @id

 

EXEC( 'UPDATE STATISTICS ' + @in_mem_tbl_name + ' WITH FULLSCAN,NORECOMPUTE ' );

SET @id = @id + 1

 

END

 

DROP TABLE #in_memory_tbl

 

Please ensure to run the statistics section above, before attempting to find the row counts on "in memory tables"