Have you ever encountered a situation where the sp_spaceused stored procedure in SQL Server shows no values, even though the table has data? This can be quite puzzling and may seem like a bug at first glance. In this blog post, we will explore the reasons behind this behavior and how to handle it.

Let’s start by looking at an example. In the screenshot below, you can see a real table with values, but the output of sp_spaceused is not recording any values:

So, why is this happening? The answer lies in the type of table that has been created. SQL Server offers various types of tables, and the one in this example is an In-Memory OLTP table.

In-Memory OLTP is a feature introduced in SQL Server 2014 that allows you to store and process data in memory for faster performance. These tables are optimized for high-speed data access and are ideal for scenarios that require low-latency and high-concurrency operations.

When you create an In-Memory OLTP table, the data is stored in memory rather than on disk. This means that the traditional methods of measuring space usage, such as the sp_spaceused stored procedure, do not work for these tables. Instead, you need to use specialized methods to monitor and manage the space usage of In-Memory OLTP tables.

One way to monitor the space usage of In-Memory OLTP tables is by using the sys.dm_db_xtp_table_memory_stats dynamic management view. This view provides information about the memory usage of each In-Memory OLTP table in the database.

Here is an example of how you can use the sys.dm_db_xtp_table_memory_stats view to get the space usage information for the Products table in the InMem_OLTP database:

USE InMem_OLTP;
GO

SELECT OBJECT_NAME(object_id) AS TableName,
       memory_allocated_for_table_kb AS MemoryAllocatedKB,
       memory_used_by_table_kb AS MemoryUsedKB
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_NAME(object_id) = 'Products';
GO

This query will return the memory allocated and used by the Products table in kilobytes. You can use this information to monitor the space usage of your In-Memory OLTP tables and make any necessary adjustments.

So, the next time you encounter a situation where sp_spaceused shows no values for a table, remember to check if it is an In-Memory OLTP table. If it is, use the sys.dm_db_xtp_table_memory_stats view to get accurate space usage information.

Thank you for reading this blog post. If you have any questions or comments, please leave them below.