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