1

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,
       SUM(CASE
            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,
       cte.row_count
  FROM cte
ORDER BY 2 DESC
;

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

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 '17 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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