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...