I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for all InMemory ones:

WITH cte AS 
SELECT t.name as table_name,
       SUM(s.used_page_count) AS used_pages_count,
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
           END) AS pages,
       MAX(s.row_count) AS row_count  
  FROM sys.dm_db_partition_stats s
  INNER JOIN sys.tables t 
          ON s.object_id = t.object_id
  INNER JOIN sys.indexes i 
          ON i.object_id = t.object_id 
         AND s.index_id = i.index_id
GROUP BY t.name
SELECT cte.table_name, 
       CAST((cte.pages * 8.)/1024 AS DECIMAL(10,3)) AS table_size_mb, 
       CAST(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
              END) * 8./1024) AS DECIMAL(10,3)) AS indexes_mb,
  FROM cte

I know that space used by table and indexes for InMemory table cannot be get by this query, but the row count should work.

When you execute just:

SELECT * FROM sys.dm_db_partition_stats

you can see that in row_count column is non-zero row count for all InMemory tables. Is there some bug in Microsoft SQL Server (I tried this on SQL2016SP1 and older versions)? Or am I doing something wrong? All InMemory tables I had created are not empty - so SELECT * displayed proper row count.

Another point - when you add filtering WHERE clause with table name or LIKE which filters InMemory tables only, non zero row count is also returned...

1 Answer 1


Details about In-Memory Tables cannot always be seen using the "normal" dynamic management views, or DMVs, such as sys.dm_db_partition_stats.

Instead, you'll need to use the In-Memory views detailed on MSDN.

For instance, this page talks about how to calculate row and table size for In-Memory tables.

MSSQLTips has a decent starting list of In-Memory related DMVs.

  • Thanks for your answer, I know about InMemory-related DMVs, but still dont know why above mentioned view returns non-consistent information.
    – XWildWolf
    Jan 22, 2017 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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